SDL/declarative (PostgreSQL): CREATE EXTENSION rejected by the SDL parser but required by the differ, so extension-owned functions get DROP FUNCTION

SDL/declarative (PostgreSQL): CREATE EXTENSION is rejected by the SDL parser but required by the SDL differ, so extension-owned functions get DROP FUNCTION

Summary

In declarative (SDL) mode against PostgreSQL, a database with an installed extension cannot be managed at all. The two halves of the feature contradict each other:

1. Export Schema emits CREATE EXTENSION, but the SDL parser rejects it. The exported schema is not round-trip-clean — feed Bytebase's own export back in as an SDL file and check fails.
2. Remove the CREATE EXTENSION statement and the differ then treats the extension's functions as orphans, generating DROP FUNCTION for each. PostgreSQL refuses, because extension-owned functions cannot be dropped individually.

There is no third configuration. Declaring the extension fails validation; not declaring it produces DDL PostgreSQL will not execute.

Environment

Bytebase	3.21.0 (self-hosted, Docker), commit a9294ccea1a69d49e7ccf31c3cd21415d6152544
Action	bytebase/bytebase-action:3.21.0, check and rollout with --declarative
Target	PostgreSQL 16
Extension	pgcrypto (installed into public)

Reproduction

1. A PostgreSQL database with pgcrypto installed: CREATE EXTENSION IF NOT EXISTS pgcrypto;

2. In Bytebase, Export Schema for that database. The output contains:

   sql复制

   CREATE EXTENSION IF NOT EXISTS "pgcrypto" WITH SCHEMA "public" VERSION '1.3';
   COMMENT ON EXTENSION "pgcrypto" IS 'cryptographic functions';

3. Use that exported file as the SDL file and run:

   bash复制

   bytebase-action check --declarative --file-pattern="schemas/*.sql" \
     --targets instances/<inst>/databases/<db> --check-release=FAIL_ON_ERROR

   Result — case A:

   json复制

   {
     "code": 239,
     "ruleType": "PARSER_BASED",
     "status": "ERROR",
     "title": "Disallowed statement in SDL file",
     "content": "Statement type 'CREATE_EXTENSION' is not allowed in SDL files.\n\nSDL files should only contain CREATE and COMMENT statements to declare the desired schema."
   }

4. Remove the CREATE EXTENSION and COMMENT ON EXTENSION lines (and, in a further attempt, every other reference to public). check now passes. Run bytebase-action rollout --declarative ... against an empty database that has pgcrypto installed.

   Result — case B, task fails during Command Execute:

   复制

   ERROR: cannot drop function armor(bytea) because extension pgcrypto requires it (SQLSTATE 2BP01)

   The transaction rolls back, so nothing is damaged — but the rollout can never succeed.

   Removing COMMENT ON SCHEMA "public" as well does not help: the differ compares against the whole database, not only the schemas named in the SDL file, so the extension's functions are still seen as undeclared objects to drop.

Expected behaviour

Either:

• the differ excludes extension-owned objects — they are identifiable via pg_depend (deptype = 'e'), which is the conventional approach and what pgschema does; or
• SDL accepts CREATE EXTENSION as a declarable object, so the differ knows the extension is desired and leaves its objects alone.

The first seems more consistent with SDL only permitting CREATE/COMMENT. Either way, Export Schema output should be valid as an SDL file — currently it is not, for any database with an extension.

Actual behaviour

Declarative mode is unusable on any PostgreSQL database with an installed extension. Since pgcrypto and uuid-ossp are extremely common, this likely affects many real schemas.

Notes

• This looks like a more severe instance of #7882 ("Postgres should filter out internal objects from timescaledb extension") — there the extension's objects are processed unnecessarily; here they are actively dropped. #10513 (uuid_generate_v4() does not exist) appears to share the root cause of extension objects not being modelled.
• Happy to provide the full SDL file or check/rollout JSON if useful.