Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Generated migrations are broken with pgmq extension #32531

Open
2 tasks done
mattrigg9 opened this issue Jan 2, 2025 · 1 comment
Open
2 tasks done

Generated migrations are broken with pgmq extension #32531

mattrigg9 opened this issue Jan 2, 2025 · 1 comment
Labels
bug Something isn't working external-issue to-triage

Comments

@mattrigg9
Copy link

Bug report

  • I confirm this is a bug with Supabase, not with my own application.
  • I confirm I have searched the Docs, GitHub Discussions, and Discord.

Describe the bug

Running supabase db pull (with or without the --schema flag) after enabling pgmq and creating queues results in unstable migration files. There appear to be a few bugs going on:

  1. Statements like grant select on table "pgmq"."a_queue_name" to "pg_monitor"; are run on tables that don't yet exist. The migration file never creates the table, it must be copied from the schema definition.
  • Error: ERROR: relation "pgmq.a_queue_name" does not exist (SQLSTATE 42P01) At statement 6: grant select on table "pgmq"."a_queue_name" to "pg_monitor"
  1. Migration statements that attempt to create system default types for pgmq result in "already created" errors:
  • Statement: create type "pgmq"."message_record" as ("msg_id" bigint, "read_ct" integer, "enqueued_at" timestamp with time zone, "vt" timestamp with time zone, "message" jsonb); result in
  • Error: ERROR: type "message_record" already exists (SQLSTATE 42710)
  1. I worked around the above bugs by patching the generated migration file with the table definition (e.g., create table pgmq.q_queue_name), which allows my local database to be rebuilt successfully. However, subsequent runs of supabase db pull --schema public,pgmq,pgmq_public results in a new migration file, dropping all queue tables:
alter table "pgmq"."a_queue_name" drop constraint "a_queue_name_pkey";

alter table "pgmq"."q_queue_name" drop constraint "q_queue_name_pkey";

drop index if exists "pgmq"."a_queue_name_pkey";

drop index if exists "pgmq"."archived_at_idx_queue_name";

drop index if exists "pgmq"."q_queue_name_pkey";

drop index if exists "pgmq"."q_queue_name_vt_idx";

drop table "pgmq"."a_queue_name";

drop table "pgmq"."q_queue_name";

To Reproduce

  1. Enable queues through integration in supabase studio
  2. Turn on Expose Queues via PostgREST setting
  3. Create a basic queue
  4. Run supabase db pull. The migration file that's created performs commands on tables that don't exist yet, and create types that already exist (from enabling pgmq).
  5. Patch the generated migration file by copying the table definition from the Studio
  6. Run supabase db pull again: The queue tables are dropped in a newly generated migration file.

Expected behavior

Stable and consistent migration files to manage pgmq

Screenshots

If applicable, add screenshots to help explain your problem.

System information

  • OS: MacOS
  • Version of supabase-js: 2.2.1
  • Version of Node.js: v20.17.0
@mattrigg9 mattrigg9 added the bug Something isn't working label Jan 2, 2025
@hala94
Copy link

hala94 commented Jan 4, 2025

Same issue for me.

Could be related with -> #32392

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
bug Something isn't working external-issue to-triage
Projects
None yet
Development

No branches or pull requests

2 participants