[ISSUE #7520] Reuse PostgreSQL initialization for openGauss. - #7530

Reviewed locally at fcb6092, checked against the merge base with master (380afb5):

• CI scripts pass on this head: test-native-sql-matrix.py (26 tests OK) and test-agent-gateway-migrations.py (10 tests OK — all five seeds report 24 matching Agent Gateway rows).
• Comparing the deleted db/init/og/create-table.sql with the reconciled db/init/pg/create-table.sql: the 40 openGauss-only grant pairs all reference resources that do not exist in the PostgreSQL resource table, and 15 further rows duplicate a (role, resource) grant that already exists under a different id — no effective grant is lost.
• The openGauss-only plugin_handle rows are exactly the intended cleanups: the stale Dubbo loadBalance/loadStrategy duplicate (the proper loadBalance handle remains), and two rows where PostgreSQL's copy is the correct one — basicAuth's defaultHandleJson ext_obj is unescaped/invalid JSON in the openGauss seed and properly escaped in PostgreSQL, and indexName for loggingElasticSearch carries required:1. The cache plugin's cacheType/timeoutSeconds rows exist in both files (only the id prefixes differ), so nothing is dropped there.

Same two structural requests as the OceanBase sibling (#7529):

1. Rebase needed. git merge-tree --write-tree gh/master <head> reports a modify/delete conflict on db/init/og/create-table.sql — master removed 68 orphan permission rows from that file after your base (same cleanup class, so this should resolve cleanly once you re-run the seed reconciliation on top of current master).
2. State the scope. The PR also edits db/init/pg/create-table.sql, so it changes what a fresh PostgreSQL install gets, not only openGauss. Please say so in the description (or split it); that is the part of the diff a PostgreSQL user actually experiences.

Documenting each schema/seed decision in the initialization guide, and adding the duplicate-natural-key / invalid-JSON / grant checks to the native matrix, is the right call for this kind of consolidation — much easier to review than the deleted duplicate scripts were.

Review: [ISSUE #7520] Reuse PostgreSQL initialization for openGauss

Approved. A clean and well-reasoned consolidation of the openGauss initialization onto the PostgreSQL schema.

What's good

• Fresh-install openGauss now reuses db/init/pg/create-table.sql: native-sql-matrix.py now maps og to "db/init/pg/create-table.sql" (was db/init/og/create-table.sql), and the entire 2418-line db/init/og/create-table.sql is deleted. The e2e script storage_init_opengauss.sh is updated accordingly (cp db/init/pg/create-table.sql ...).
• Upgrade compatibility is preserved correctly: the matrix's og + upgrade flow still validates against the historical git show {BASELINE}:db/init/og/create-table.sql (the released openGauss schema), and test-native-sql-matrix.py explicitly asserts the working-tree db/init/og/create-table.sql no longer exists (assertFalse). This is the right design — fresh installs converge, but upgrade drift is still caught against the prior release.
• The PostgreSQL file is brought to a superset of the two dialects: the additions add the plugin/plugin_handle/namespace_plugin_rel seeds (aiProxy, paramMapping, modifyResponse, loggingKafka, default-namespace bindings) that openGauss previously carried, and the deletions remove duplicate permission seed rows to reconcile the two schemas. The schema now carries a "Shared fresh-install schema for PostgreSQL and openGauss" header.
• native-sql-matrix.md and the new test-native-sql-matrix.py document and validate the shared matrix; check-agent-gateway-migrations.py and test-agent-gateway-migrations.py are adjusted in tandem.

Verification / CI

• build, the full shenyu-integrated-test-* matrix, agent_gateway_migrations, and CodeQL all pass. The native-sql-matrix is the gate for the pg/og seed reconciliation.
• The e2e failure shown (e2e-logging-rocketmq-compose) is the fleet-wide rocketmq-logging e2e job and is unrelated to this DB-init PR (no logging/rocketmq code is touched here). Not blocking.

Non-blocking follow-ups

• Please confirm the removed permission seed rows (consecutive-ID chunks, e.g. 1529403932881850584..592, 1572525965658820609..617) are genuine duplicates rather than intended distinct entries — they look like dedup, and the matrix validates seeds, but a second set of eyes on the content is worth it.
• Same for the added plugin seeds (aiProxy/paramMapping/modifyResponse/loggingKafka): ensure they are intended for both pg and og fresh installs (they now are, by construction).

ci: build + integrated-tests + migrations green; e2e infra failure unrelated. Merging.

Reuses the PostgreSQL fresh-install schema for openGauss (deletes the duplicate db/init/og/create-table.sql and routes og fresh/upgrade validation to the shared PG source). The change genuinely removes duplication, leaves every other dialect untouched, preserves the og upgrade path via the git baseline, and ships meaningful CI checks — APPROVE with one pre-merge verification.

No blocking defects found

The PR does what it claims. Below are the non-blocking observations and the single item that must be green in CI before merge.

Notes

• Pre-merge verification (critical, not a defect): db/upgrade/2.7.1-upgrade-2.7.2-og.sql is not part of this diff, yet check-agent-gateway-migrations.py now validates the og dialect against db/init/pg/create-table.sql (schema) vs db/upgrade/2.7.1-upgrade-2.7.2-og.sql (migration). The new test test_opengauss_uses_shared_schema_and_its_own_upgrade writes the real pg/create-table.sql and the real og upgrade into a temp tree and asserts CHECK.check(root) passes (only a deliberately corrupted upgrade raises og: fresh-install/upgrade mismatch). That test, together with the existing test_all_dialects_match, is exactly the guard that would catch a reconciliation mismatch — so the design intent (pg fresh == og upgrade target) is verified by CI, not by inspection. Confirm the CI run for this PR passes test-agent-gateway-migrations and test-native-sql-matrix. I could not execute these locally (the provided repo snapshot contains no working tree and gh is disallowed), so this is the one fact I could not personally confirm.

• SENTINEL reference in the new check_fresh_postgres_seeds: check_fresh_postgres_seeds (native-sql-matrix.py) uses SENTINEL (e.g. p.id<>'{SENTINEL}'). Confirm SENTINEL is defined at module scope. An undefined name would break module import and fail all matrix tests. Low risk — the existing check_fresh_mysql_schema already relies on the same constant (verify by grepping the module).

• Residual on-disk references to db/init/og/create-table.sql: The file is deleted from the working tree and now exists only at the immutable git baseline (mirroring the already-deleted db/init/ob/schema.sql pattern, which is read via git show {BASELINE}:db/init/ob/schema.sql). Grep the repo (e2e scripts, docker/compose, docs, and any Java DB-init code that may resolve og/create-table.sql from the classpath) to ensure nothing still expects it on disk. The two known references (storage_init_opengauss.sh and both CI scripts) are already updated in this PR.

• nit: The verify_index_rows change from column.strip().strip('"') to column.strip().split()[0].strip('"') correctly handles pg_catalog-decorated index columns (e.g. (plugin_id COLLATE pg_catalog."default" …)) and is exercised by the new test_pg_and_og_catalogs sub-case. No action needed.

What the review confirmed

1. Genuine de-duplication, not relocation: db/init/og/create-table.sql (2,891 lines) is fully deleted; both check-agent-gateway-migrations.py (schema_dialect = {"ob": "mysql", "og": "pg"}) and native-sql-matrix.py (DIALECTS["og"][1] == DIALECTS["pg"][1], asserted by test_opengauss_shares_current_postgres_schema_but_keeps_released_baseline) now resolve the og fresh-install schema to db/init/pg/create-table.sql. The shared pg/create-table.sql is then enriched to become the canonical PG+openGauss 2.7.2 fresh state (removed orphan permission seeds, ruleHandlePageType for paramMapping/modifyResponse, bootstrapServer for loggingKafka, loadBalance spelling/default for Dubbo, unique discovery_upstream_discovery_handler_id_idx — fixing a previously non-unique-but-renamed index — and aiProxy fallback without the obsolete prompt field).

2. Other dialects untouched: Only pg/og paths in the scripts and the two pg/og SQL files changed. mysql, ob, oracle, h2 init/upgrade paths and their CI wiring are unchanged. The og upgrade flow still reads its historical db/init/og/create-table.sql via git show {BASELINE}:… (same mechanism as ob), so existing openGauss installations upgrading from 2.7.1 are unaffected.

3. Tests are meaningful, not empty shells: test_opengauss_uses_shared_schema_and_its_own_upgrade runs the real CHECK.check and proves a one-character upgrade mutation is detected; test_opengauss_storage_initialization_copies_the_shared_schema actually executes the k8s storage script (with mkdir/wget/cp mocked) and asserts the copied bytes equal pg/create-table.sql; the new check_fresh_postgres_schema/check_fresh_postgres_seeds/check_discovery_upstream_uniqueness perform real column/nullability/primary-key/index/JSON/orphan checks and a live duplicate-insert rejection against the engine. These exercise the exact behavior the PR changes.

Conclusion

APPROVE.

Key verified facts:

• The duplicate openGauss init script is genuinely removed and openGauss now shares PostgreSQL's pg/create-table.sql across the CI matrix, the migration checker, the storage script, and the init guide.
• MySQL/Oracle/OpenGauss-upgrade/H2 paths are not regressed; the og upgrade continues via the git baseline, identical to the existing ob convention.
• The added tests are substantive and directly exercise the new shared-schema contract (including a deliberate mismatch that must be caught).

One action before merge: ensure CI is green for test-agent-gateway-migrations / test-native-sql-matrix (the og-against-pg-fresh consistency is validated only by those tests, which I could not run locally).