declarative: When pgmq is enabled, the declarative schema `generate` and `sync` commands fail.

[tolgap]

Describe the bug
There seems to be an issue with the experimental declarative command

To Reproduce
Steps to reproduce the behavior:

1. Ensure pgmq extension is enabled in your existing DB
2. Run the declarative command
3. Observe error about pgmq
4. The generate command still succeeds in generating the files though
5. Run sync command, and observe error about any function that refers to pgmq schema not existing

Commands:

supabase db schema declarative generate --local --experimental --schema pgmq,public,<all my schemas>
supabase db schema declarative sync --experimental --schema pgmq,public,<all my schemas>

Expected behavior
I would expect the pgmq schema to be created in the shadow DB, because the cluster/extensions/pgmq.sql does contain pgmq enabling.

Screenshots

Here's the output of errors.txt:

failed to parse pg-delta apply output: json: cannot unmarshal object into Go struct field ApplyResult.stuckStatements of type string                                                                  stdout: {"status":"stuck","totalStatements":15,"totalRounds":2,"totalApplied":10,"totalSkipped":0,"errors":[],"stuckStatements":[{"statement":{"id":"cluster/extensions/pgmq.sql:0","sql":"CREATE EXTENSION pgmq WITH SCHEMA pgmq;","statementClass":"CREATE_EXTENSION"},"code":"3F000","message":"schema \"pgmq\" does not exist","isDependencyError":true},{"statement":{"id":"schemas/public/functions/pgmq_read.sql:0","sql":"CREATE FUNCTION public.pgmq_read (\n  queue_name    text,\n  sleep_seconds integer DEFAULT 0,\n  n             integer DEFAULT 1\n)\n  RETURNS SETOF pgmq.message_record\n  LANGUAGE plpgsql\n  SECURITY DEFINER\n  SET search_path TO 'pgmq'\n  AS $function$\nBEGIN\n    -- Validate input parameters\n    IF queue_name IS NULL OR queue_name = '' THEN\n        RAISE EXCEPTION 'queue_name cannot be null or empty';\n    END IF;\n    \n    IF sleep_seconds IS NULL OR sleep_seconds < 0 THEN\n        RAISE EXCEPTION 'sleep_seconds must be non-negative, got: %', sleep_seconds;\n    END IF;\n    \n    IF n IS NULL OR n <= 0 THEN\n        RAISE EXCEPTION 'n must be a positive integer, got: %', n;\n    END IF;\n    \n    RETURN QUERY\n    SELECT * FROM pgmq.read(queue_name, sleep_seconds, n);\nEND;\n$function$;","statementClass":"CREATE_FUNCTION"},"code":"3F000","message":"schema \"pgmq\" does not exist","isDependencyError":true},{"statement":{"id":"schemas/public/functions/pgmq_read.sql:1","sql":"GRANT ALL ON FUNCTION public.pgmq_read(text, integer, integer) TO anon;","statementClass":"GRANT"},"code":"42883","message":"function public.pgmq_read(text, integer, integer) does not exist","isDependencyError":true},{"statement":{"id":"schemas/public/functions/pgmq_read.sql:2","sql":"GRANT ALL ON FUNCTION public.pgmq_read(text, integer, integer) TO authenticated;","statementClass":"GRANT"},"code":"42883","message":"function public.pgmq_read(text, integer, integer) does not exist","isDependencyError":true},{"statement":{"id":"schemas/public/functions/pgmq_read.sql:3","sql":"GRANT ALL ON FUNCTION public.pgmq_read(text, integer, integer) TO service_role;","statementClass":"GRANT"},"code":"42883","message":"function public.pgmq_read(text, integer, integer) does not exist","isDependencyError":true}]}

System information
Rerun the failing command with --create-ticket flag.

• Ticket ID: [e.g. 5991fd15383b41a1bd0749e11164af6d]
• Version of OS: MacOS 26.4.1
• Version of CLI: v2.91.1 (pre-release)
• Version of Docker: v4.69.0
• Versions of services:

Additional context
I have included the generated supabase/database folder as a ZIP, but I removed all schema's that I own. But the error happens in supabase/database/public/functions/pgmq_read.sql anyway.

database.zip

[avallete]

Hey there !

Thank's for taking the time to try out this experimental feature and to give feedback / do a bug report !

I'm looking into it, I think the issue is that pgmq create it's own schema when the extension get created. So CREATE EXTENSION pgmq WITH SCHEMA pgmq fail because the schema doesn't exist (since it needs the extension to get created). I'm looking into this bug.

One thing tough, is there a reason you have to pass the --schema flags to the command ?
What is the result without it ?:

supabase db schema declarative generate --local --experimental

[tolgap]

Thanks for the quick response @avallete.

Same exact error when omitting --schema. There's no particular reason I used it, other than seeing someone suggest it in one of the older supabase/cli issues (but that was about the new db pull --experimental --use-pg-delta)

[avallete]

Alright thank's for the feedback @tolgap.

Just as a follow-up question, if within the generated declarative schema you turn the CREATE EXTENSION pgmq WITH SCHEMA pgmq into CREATE EXTENSION pgmq then run the declarative sync. Do the statements apply without errors ?

I'll be pushing up a fix later on today to pg-delta but meanwhile that could your case.

[tolgap]

@avallete

here's the only diff I did (your suggestion):

- CREATE EXTENSION pgmq WITH SCHEMA pgmq;
+ CREATE EXTENSION pgmq;

supabase db schema declarative sync --experimental

Creating shadow database...
Applying declarative schemas via pg-delta...
Applied 1621 statements in 1 round(s).

It works! its apparently just that WITH SCHEMA pgmq that was holding me back 😄

[avallete]

Nice ! Thank's for the report ! This will be fixed soon !