PGMQ queue creation fails on preview branch reset: relation "pgmq.q_my_queue_msg_id_seq" does not exist

[ToJen]

Bug report

• [X] 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

When resetting a Supabase preview branch, PGMQ queue creation fails with a sequence-related error. The pgmq.create() call for a queue (e.g., my_queue) references a sequence (pgmq.q_my_queue_msg_id_seq) that doesn't exist, causing the migration to fail.

To Reproduce

Steps to reproduce the behavior, please provide code snippets or a repository:

1. Create a preview branch with migrations that create PGMQ queues:

   CREATE EXTENSION IF NOT EXISTS pgmq;
   SELECT pgmq.create('my_queue');
   SELECT pgmq.create('my_queue_dlq');

2. Let the branch build successfully (first build works).
3. Reset the preview branch via the Supabase dashboard.

• Go to the manage preview branches page -> https://supabase.com/dashboard/project/{your-project-id}/branches
• Click on 'Reset branch' under the menu on the right of your branch name

4. The branch rebuilds and the migration fails.
5. View the logs of the latest build
6. See error

Expected behavior

• Queues should be created successfully on branch reset.
• PGMQ extension state should be consistent after reset.
• No sequence-related errors should occur.

Actual behavior

• Migration fails with:

  ERROR: relation "pgmq.q_my_queue_msg_id_seq" does not exist (SQLSTATE 42P01)
  At statement: 786
  SELECT pgmq.create('my_queue_dlq')

• The first queue (my_queue) may be created, but the second queue (my_queue_dlq) fails.
• This only occurs on branch reset, not on first build or new commits.

System information

• Supabase: Preview branch reset functionality
• PGMQ extension: Latest version (via CREATE EXTENSION IF NOT EXISTS pgmq)
• Migration file: Baseline migration or any migration creating PGMQ queues

[ToJen]

Hey @sweatybridge, any thoughts on this

[sweatybridge]

Can you show me the full migration file that you are trying to apply? The error happens at statement 786 but your example only has 3 statements.

[kallebysantos]

Hi @sweatybridge I believe this issue is related with platform branching, not the migrations command 😕 So probably the user doesn't have access to full the migration script

[ToJen]

Hi @sweatybridge it's not about migrations but rather about a behaviour of the Preview Branches. Once the extension pgmq has been enabled and a queue has been created and the preview branch is restarted, that error always happens.

I've described the steps to reproduce. You could even run the CREATE EXTENSION IF NOT EXISTS pgmq inside the SQL Editor and the create a queue. Then restart the preview branch. It's not a migrations issue.

[sweatybridge]

Could you share your postgres version and project ref?

I tried reproducing here but to no avail. https://github.com/qiao-supabase/branching-demo/pull/25/files

Migrations ran successfully on every reset of preview branch.

[ToJen]

Made a mistake when cleaning different migrations files. I hadn't enabled pgmq and created the queue at the right position before using it.

[ToJen]

Looks like the issue has returned @sweatybridge @kallebysantos. It tends to happen when a branch is left active overnight. Also, I rebased my local feature branch and git push --force-with-lease to the remote feature branch. Then the Supabase Preview Branch integration triggered the branch rebuild then the same error happened again. I think something isn't being properly cleared/restored by Supabase for the pgmq extension.

[qualitate-samparl]

We experience this as well. It seems as though the CLI command supabase db reset isn't properly resetting the pgmq schema. We testing the fix and updated an old migration by adding the extra line DROP SCHEMA IF EXISTS pgmq CASCADE; before running CREATE EXTENSION IF NOT EXISTS pgmq;. Now we can reset our branches without issue.