PG compatibility: orchestration, Makefile targets, and CI (Tasks 9-11)

[renecannao]

Context

This issue tracks Tasks 9-11 from docs/superpowers/plans/2026-06-11-postgresql-libpg-query-compatibility.md.

Goal

Wire the compatibility harness into the repo build, test, and CI surfaces.

Files

• Create scripts/pg_compat/run_compat.py
• Modify Makefile
• Modify .gitignore
• Modify .github/workflows/ci.yml

What the next agent should do

• Add build, test, full, and refresh orchestration modes.
• Add make build-pg-compat, make test-pg-compat, make pg-compat, and make pg-compat-refresh targets.
• Add deterministic PR CI and scheduled full-suite CI.
• Keep the workflow pinned to committed upstream sources unless refresh mode is explicitly used.

Acceptance

• make test-pg-compat is deterministic.
• The PR job runs the committed CI cases.
• The scheduled job runs the full pinned PG17/PG18 suite.
• The workflow does not silently advance upstream branch pins.

[renecannao]

Implemented Tasks 9-11 on PR #50.

Commits

• 5d1a12d build: add PostgreSQL compatibility commands
• 5fcdd18 test: baseline ParserSQL against PostgreSQL 18
• e8241f9 ci: check PostgreSQL parser compatibility

Task 9: orchestration and Makefile integration

• Added scripts/pg_compat/run_compat.py with build, test, full, and refresh modes.
• Added Makefile targets: build-pg-compat, test-pg-compat, pg-compat, pg-compat-refresh.
• Added local binary ignores for pg_compat_17 and pg_compat_18.
• Added runner orchestration tests, including batched committed-case replay and safe delimiter handling for SQL ending in line comments.

Task 10: PostgreSQL 18 baseline/report

• Classified the previously unmapped PG18 top-level libpg_query nodes in tools/pg_compat/statement_type_map.cpp and tests/pg_compat/statement_type_cases.cpp.
• Generated committed artifacts:
  • tests/pg_compat/expected_results.jsonl: 51,415 baseline rows.
  • tests/pg_compat/ci_cases.jsonl: 24,080 committed CI cases.
  • docs/compatibility/postgresql-18.md: PostgreSQL 18 compatibility report.
• Added PG18 structural witness links for grammar/keyword deltas, including virtual generated columns, NOT ENFORCED constraints, temporal PERIOD/WITHOUT OVERLAPS constraints, RETURNING WITH OLD/NEW aliases, opclass purpose annotations, default privileges on large objects, and VACUUM relation column lists.
• Added reviewed structural dispositions for parse_node and protobuf schema deltas as internal_non_syntax, with reasons in tests/pg_compat/structural_dispositions.json and the generated report.
• The report now has no unwitnessed structural features.

Task 11: CI

• Added a PR/push pg-compat job that runs make test-pg-compat against committed CI cases.
• Added a weekly scheduled pg-compat-full job that runs make pg-compat against the full pinned PG17/PG18 baseline.
• Added Actions cache coverage for /tmp/parsersql-pg-compat, keyed by tests/pg_compat/upstream_pins.json.
• Guarded the pre-existing CI jobs from scheduled runs so the weekly schedule only runs the full pg_compat validation.

Verification

• make pg-compat-refresh PG_COMPAT_CACHE=/tmp/parsersql-pg-compat-task9: regenerated the PG18 baseline/report successfully.
• make pg-compat PG_COMPAT_CACHE=/tmp/parsersql-pg-compat-task9: checked 24,080 committed pg_compat CI cases; committed baseline matches.
• make test-pg-compat PG_COMPAT_CACHE=/tmp/parsersql-pg-compat-task9: 87 tests passed; checked 24,080 committed pg_compat CI cases.
• .github/workflows/ci.yml parsed with PyYAML and includes pg-compat and pg-compat-full jobs.
• git diff --check: passed before committing Task 10 and Task 11.

[renecannao]

Follow-up after enabling PR CI:

• 7bbe5d1 fix: release PostgreSQL cache lock on termination
• 2dcc0d4 test: recover PostgreSQL cache lock after termination

The new pg-compat GitHub Actions job exposed a CI-only Bash/signal behavior in the fetch-script termination test. The script now explicitly releases the lock on handled termination, and the test verifies that any stale lock left by platform-specific signal handling is recoverable by the next fetch.

Final PR #50 check status for head 2dcc0d43979a0bd36d0ab2f14eca8f1aa2dc5242:

• pg-compat: passed in 2m25s.
• Existing Linux/macOS/build/corpus/benchmark checks: passed.
• pg-compat-full: skipped on PR as intended; it is schedule-only.