Local PostgreSQL plan, index and read-only role fixture
Reported passed · v9ba96abc800a.bb1
View input and acceptance criteria
Input
Using a new temporary PostgreSQL 17 database and synthetic users, inspect plain and analyzed JSON plans, partial and expression index applicability, reader privileges, denied writes and DDL, concurrent-index transaction restrictions and a statement timeout. Run native PostgreSQL only; do not connect any production database, AI client, postgres-mcp or Azure service.
Acceptance criteria
Six package/source-integrity and twenty-two local PostgreSQL observations match. The result does not evaluate all source recommendations, an AI client, MCP tools, RLS, vector extensions, replication, Azure, model services or production performance.
Recorded outcome
Six package/source-integrity observations and twenty-two local PostgreSQL plan, index, privilege and timeout observations on synthetic data. No AI client or postgres-mcp execution; no RLS, Azure, vector extension, replication, model call or production performance benchmark.
{
"executed_at": "2026-10-04T04:00:43.770377+00:00",
"uid": 10001,
"source_commit": "9ba96abc800a574a0872f3d6b912ea8be1a6735e",
"package_sha256": "8db174e9a57ce2c7300ae922a72cc8cdc82ab49c741fd814f6b35a5173e9b7ab",
"batch_sha256": "42a53bffc7318f93c8df330f17690ef193fbe84e240a4dcbe798f1fa84c82b67",
"probe_sha256": "7178aa3689bc901210a5674b77d15402eb96ba8686a4abeb59b6f63fa3f5e4a5",
"postgresql_version": "17.11",
"server_image_id": "sha256:b0f9560a2de083e2cc7382e75f808c7381a32852a7ec49117deedb300e552b24",
"client_image_id": "sha256:817f3dee0c1e14dd21429fe9a847b84952f182c31b2885d778a380841e80c177",
"assertions": 28,
"package_integrity_observations": 6,
"postgresql_observations": 22,
"cases": [
{
"name": "package_sha256",
"status": "passed",
"detail": null
},
{
"name": "all_29_preserved_source_and_license_files",
"status": "passed",
"detail": null
},
{
"name": "all_29_source_git_blobs",
"status": "passed",
"detail": null
},
{
"name": "all_23_original_router_references",
"status": "passed",
"detail": null
},
{
"name": "complete_MIT_notice",
"status": "passed",
"detail": null
},
{
"name": "current_review_and_untested_agent_scope",
"status": "passed",
"detail": null
},
{
"name": "postgresql_17_fixture",
"status": "passed",
"detail": "17.11"
},
{
"name": "reader_has_no_elevated_role_attributes",
"status": "passed",
"detail": [
false,
false,
false,
false,
false
]
},
{
"name": "reader_has_no_role_memberships",
"status": "passed",
"detail": null
},
{
"name": "reader_select_only_table_privileges",
"status": "passed",
"detail": [
true,
false,
false,
false
]
},
{
"name": "reader_usage_without_schema_create",
"status": "passed",
"detail": null
},
{
"name": "reader_has_no_temporary_table_privilege",
"status": "passed",
"detail": null
},
{
"name": "plain_explain_contains_estimates_without_actual_rows",
"status": "passed",
"detail": null
},
{
"name": "matching_partial_predicate_uses_expected_index",
"status": "passed",
"detail": null
},
{
"name": "analyzed_select_returns_one_actual_row",
"status": "passed",
"detail": null
},
{
"name": "analyzed_select_reports_buffer_observations",
"status": "passed",
"detail": null
},
{
"name": "broad_aggregate_includes_sequential_scan",
"status": "passed",
"detail": null
},
{
"name": "broad_aggregate_result_preserved",
"status": "passed",
"detail": null
},
{
"name": "nonmatching_predicate_does_not_use_partial_index",
"status": "passed",
"detail": null
},
{
"name": "matching_expression_uses_lower_index",
"status": "passed",
"detail": null
},
{
"name": "different_expression_does_not_use_lower_index",
"status": "passed",
"detail": null
},
{
"name": "reader_insert_rejected",
"status": "passed",
"detail": {
"sqlstate": "42501"
}
},
{
"name": "reader_update_rejected",
"status": "passed",
"detail": {
"sqlstate": "42501"
}
},
{
"name": "reader_create_index_rejected",
"status": "passed",
"detail": {
"sqlstate": "42501"
}
},
{
"name": "read_only_transaction_rejects_delete",
"status": "passed",
"detail": {
"sqlstate": "25006"
}
},
{
"name": "concurrent_index_inside_transaction_rejected",
"status": "passed",
"detail": {
"sqlstate": "25001"
}
},
{
"name": "reader_statement_timeout_cancels_slow_query",
"status": "passed",
"detail": {
"sqlstate": "57014"
}
},
{
"name": "fixture_rows_and_identity_sum_unchanged",
"status": "passed",
"detail": null
}
],
"plans": {
"selective_estimated": {
"Plan": {
"Node Type": "Index Scan",
"Parallel Aware": false,
"Async Capable": false,
"Scan Direction": "Forward",
"Index Name": "idx_active_users",
"Relation Name": "users",
"Alias": "users",
"Startup Cost": 0.28,
"Total Cost": 8.3,
"Plan Rows": 1,
"Plan Width": 26,
"Index Cond": "(email = '[email protected]'::text)"
}
},
"selective_analyzed": {
"Plan": {
"Node Type": "Index Scan",
"Parallel Aware": false,
"Async Capable": false,
"Scan Direction": "Forward",
"Index Name": "idx_active_users",
"Relation Name": "users",
"Alias": "users",
"Startup Cost": 0.28,
"Total Cost": 8.3,
"Plan Rows": 1,
"Plan Width": 26,
"Actual Rows": 1,
"Actual Loops": 1,
"Index Cond": "(email = '[email protected]'::text)",
"Rows Removed by Index Recheck": 0,
"Shared Hit Blocks": 1,
"Shared Read Blocks": 2,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0
},
"Planning": {
"Shared Hit Blocks": 0,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0
},
"Planning Time": 0.122,
"Triggers": [],
"Execution Time": 0.089
},
"broad_aggregate_analyzed": {
"Plan": {
"Node Type": "Aggregate",
"Strategy": "Plain",
"Partial Mode": "Simple",
"Parallel Aware": false,
"Async Capable": false,
"Startup Cost": 605.0,
"Total Cost": 605.01,
"Plan Rows": 1,
"Plan Width": 8,
"Actual Rows": 1,
"Actual Loops": 1,
"Shared Hit Blocks": 355,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Relation Name": "users",
"Alias": "users",
"Startup Cost": 0.0,
"Total Cost": 555.0,
"Plan Rows": 20000,
"Plan Width": 4,
"Actual Rows": 20000,
"Actual Loops": 1,
"Shared Hit Blocks": 355,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0
}
]
},
"Planning": {
"Shared Hit Blocks": 5,
"Shared Read Blocks": 1,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0
},
"Planning Time": 0.114,
"Triggers": [],
"Execution Time": 1.837
},
"nonmatching_partial_predicate": {
"Plan": {
"Node Type": "Seq Scan",
"Parallel Aware": false,
"Async Capable": false,
"Relation Name": "users",
"Alias": "users",
"Startup Cost": 0.0,
"Total Cost": 605.0,
"Plan Rows": 1,
"Plan Width": 4,
"Filter": "((NOT active) AND (email = '[email protected]'::text))"
}
},
"matching_lower_expression": {
"Plan": {
"Node Type": "Index Scan",
"Parallel Aware": false,
"Async Capable": false,
"Scan Direction": "Forward",
"Index Name": "idx_users_lower_email",
"Relation Name": "users",
"Alias": "users",
"Startup Cost": 0.29,
"Total Cost": 8.3,
"Plan Rows": 1,
"Plan Width": 4,
"Index Cond": "(lower(email) = '[email protected]'::text)"
}
},
"nonmatching_upper_expression": {
"Plan": {
"Node Type": "Seq Scan",
"Parallel Aware": false,
"Async Capable": false,
"Relation Name": "users",
"Alias": "users",
"Startup Cost": 0.0,
"Total Cost": 655.0,
"Plan Rows": 100,
"Plan Width": 4,
"Filter": "(upper(email) = '[email protected]'::text)"
}
}
},
"rejected_operations": [
{
"operation": "reader_insert_rejected",
"sqlstate": "42501"
},
{
"operation": "reader_update_rejected",
"sqlstate": "42501"
},
{
"operation": "reader_create_index_rejected",
"sqlstate": "42501"
},
{
"operation": "read_only_transaction_rejects_delete",
"sqlstate": "25006"
},
{
"operation": "concurrent_index_inside_transaction_rejected",
"sqlstate": "25001"
},
{
"operation": "reader_statement_timeout_cancels_slow_query",
"sqlstate": "57014"
}
],
"isolation": "Temporary PostgreSQL 17 fixture with 20,000 synthetic users; internal Docker network without external access, memory and CPU limits, non-root read-only client container. No production data or volume.",
"scope": "Six package/source-integrity observations and twenty-two local PostgreSQL plan, index, privilege and timeout observations on synthetic data. No AI client or postgres-mcp execution; no RLS, Azure, vector extension, replication, model call or production performance benchmark.",
"runtime_tested": false
}Environment
Temporary PostgreSQL 17 fixture with 20,000 synthetic users; internal Docker network without external access, memory and CPU limits, non-root read-only client container. No production data or volume. PostgreSQL 17.11; server sha256:b0f9560a2de083e2cc7382e75f808c7381a32852a7ec49117deedb300e552b24; client sha256:817f3dee0c1e14dd21429fe9a847b84952f182c31b2885d778a380841e80c177.
Package SHA-256: 8db174e9a57ce2c7300ae922a72cc8cdc82ab49c741fd814f6b35a5173e9b7ab
Outcome recorded: 2026-10-04 04:00 UTC