CREATE EXTENSION pgmq fails in Postgres 17.6.1.016+ due to function overload handling bug in after-create script

[star26bsd]

Summary

When attempting to create the pgmq extension on Supabase Postgres images version 17.6.1.016 and later, the installation fails with a PostgreSQL error 42725: function name "pgmq.drop_queue" is not unique. This is caused by Supabase's custom after-create script (/etc/postgresql-custom/extension-custom-scripts/pgmq/after-create.sql) attempting to drop an overloaded function without specifying the full function signature.

Environment

• Supabase CLI Version: 2.53.6
• Postgres Docker Image: public.ecr.aws/supabase/postgres:17.6.1.024 (and likely all versions 17.6.1.016 through latest)
• pgmq Extension Version: 1.5.1 (bundled with Postgres images 17.6.1.016+)
• Operating System: macOS (Darwin 24.6.0), but reproducible on any platform
• Affected Command: npx supabase db reset (or any fresh database initialization with pgmq migration)

Steps to Reproduce

Minimal Reproduction:

1. Create a new Supabase project or use existing local setup
2. Create a migration file containing:

   CREATE EXTENSION IF NOT EXISTS pgmq;
   

3. Run: npx supabase db reset

Full Reproduction (with actual project):

1. Clone a project with pgmq migration
2. Ensure Supabase CLI is v2.53.6
3. Run npx supabase db reset
4. Observe failure during migration application

Expected Behavior

The CREATE EXTENSION pgmq statement should execute successfully, installing the pgmq extension and completing all after-create scripts without errors.

Actual Behavior

The extension creation fails with the following error:

Applying migration 20250901133326_image-variant-with-message-queue.sql... ERROR: function name "pgmq.drop_queue" is not unique (SQLSTATE 42725) At statement: 0 -- Migration for async image variant generation with message queues -- This migration enables required extensions and grants permissions for queue operations

-- Enable required extensions -- Note: pgmq MUST be installed in its own schema, not in extensions CREATE EXTENSION IF NOT EXISTS pgmq

Message: "function name "pgmq.drop_queue" is not unique" Detail: "" Hint: "Specify the argument list to select the function unambiguously." Position: 0 Where: SQL statement "alter extension pgmq drop function pgmq.drop_queue" PL/pgSQL function inline_code_block line 21 at SQL statement

This indicates the error originates from Supabase's custom after-create script, not from user code.

---

Root Cause Analysis

1. pgmq Version Change

Starting with pgmq 1.5.0 (released in https://github.com/tembo-io/pgmq/pull/319: "Make drop queue infer queue type"), the drop_queue function was changed to support function overloading:

pgmq 1.4.4 and earlier: CREATE FUNCTION pgmq.drop_queue(queue_name TEXT) ...

pgmq 1.5.0+: CREATE FUNCTION pgmq.drop_queue(queue_name TEXT) ... CREATE FUNCTION pgmq.drop_queue(queue_name TEXT, partitioned BOOLEAN DEFAULT FALSE) ...

2. Supabase After-Create Script Issue

Supabase's after-create script at /etc/postgresql-custom/extension-custom-scripts/pgmq/after-create.sql contains:

alter extension pgmq drop function pgmq.drop_queue;

This command is ambiguous when multiple overloaded versions exist, as PostgreSQL cannot determine which function to drop.

3. Affected Versions

Postgres Version	pgmq Version	Status
≤ 17.6.1.015	1.4.4	✅ Works (no overloading)
≥ 17.6.1.016	1.5.1	❌ Fails (has overloading)

According to https://github.com/orgs/supabase/discussions/39378: "We recently rolled out a change that updates pgmq version from 1.4.4 to 1.5.1 for new projects created with release version 17.6.1.016 and newer."

---

Impact Assessment

⚠️ High Impact Scenarios

1. Local Development Blocked: - Any project with pgmq migrations cannot run npx supabase db reset - Developers cannot reset their local databases for testing - New developers cannot initialize local environments
2. New Projects: - Cannot install pgmq extension via migrations - Must manually enable via Dashboard (workaround)

ℹ️ Low Impact Scenarios

1. Production Databases: - Not affected if pgmq was installed before Postgres upgrade to 17.6.1.016+ - Extension persists through Postgres version upgrades - After-create script only runs on initial CREATE EXTENSION
2. Existing Projects: - Projects that already have pgmq enabled continue working - Only fresh installations fail

---

Proposed Fix

Update /etc/postgresql-custom/extension-custom-scripts/pgmq/after-create.sql to specify function signatures when dropping:

Current Code (Buggy) ❌

alter extension pgmq drop function pgmq.drop_queue; drop function pgmq.drop_queue;

Fixed Code (Option 1: Simple) ✅

-- Drop all overloaded versions of drop_queue alter extension pgmq drop function pgmq.drop_queue(TEXT); alter extension pgmq drop function pgmq.drop_queue(TEXT, BOOLEAN);

drop function if exists pgmq.drop_queue(TEXT); drop function if exists pgmq.drop_queue(TEXT, BOOLEAN);

Fixed Code (Option 2: Robust for future overloads) ✅

-- Drop all overloaded versions using a loop DO $$ DECLARE func_sig text; BEGIN FOR func_sig IN SELECT pg_get_function_identity_arguments(p.oid) FROM pg_proc p JOIN pg_namespace n ON p.pronamespace = n.oid WHERE n.nspname = 'pgmq' AND p.proname = 'drop_queue' LOOP EXECUTE format('ALTER EXTENSION pgmq DROP FUNCTION pgmq.drop_queue(%s)', func_sig); EXECUTE format('DROP FUNCTION IF EXISTS pgmq.drop_queue(%s)', func_sig); END LOOP; END $$;

---

Temporary Workaround

Until fixed, users can work around this by:

Option 1: Comment out pgmq in migration

-- TODO: TEMPORARILY DISABLED due to Supabase bug in Postgres 17.6.1.016+ -- Bug: after-create script fails with "function name pgmq.drop_queue is not unique" -- Workaround: Enable pgmq manually via Supabase Dashboard after db reset -- Bug report: https://github.com/supabase/postgres/issues/[ISSUE_NUMBER] -- CREATE EXTENSION IF NOT EXISTS pgmq;

Then manually enable via Supabase Dashboard after each db reset.

Option 2: Conditional check in migration

DO $$ BEGIN IF NOT EXISTS (SELECT 1 FROM pg_extension WHERE extname = 'pgmq') THEN RAISE NOTICE 'pgmq extension not enabled - please enable manually via Dashboard'; RAISE NOTICE 'This is a workaround for Supabase bug in Postgres 17.6.1.016+'; ELSE RAISE NOTICE 'pgmq extension already enabled'; END IF; END $$;

---

Related Issues

• supabase/supabase#32392: pgmq extension not available in local development
• supabase/supabase#32531: Generated migrations broken with pgmq extension
• https://github.com/orgs/supabase/discussions/39378: Potential breaking change in pgmq from 1.4.4 to 1.5.1
• supabase/supabase#1391: Update PGMQ Extension to Latest Version (1.5.0)

---

Additional Context

Why Production Works ✅

Production databases that already have pgmq installed are unaffected because:

1. The extension was likely installed on an older Postgres version (< 17.6.1.016) with pgmq 1.4.4
2. During Postgres upgrades, existing extensions are not recreated
3. The after-create script only runs on initial CREATE EXTENSION, not on upgrades
4. Therefore, production never encounters the buggy after-create script

Why Local Development Fails ❌

Local development environments using npx supabase db reset:

1. Drop and recreate the entire database from scratch
2. Apply all migrations fresh, including CREATE EXTENSION pgmq
3. Trigger the buggy after-create script every time
4. Cannot proceed without manual intervention

---

Verification Commands

This bug can be independently verified by:

1. Check pgmq version in Postgres image: docker run --rm public.ecr.aws/supabase/postgres:17.6.1.024
   psql -U postgres -c "SELECT extversion FROM pg_extension WHERE extname = 'pgmq';"

2. Inspect the after-create script: docker run --rm --entrypoint cat
   public.ecr.aws/supabase/postgres:17.6.1.024
   /etc/postgresql-custom/extension-custom-scripts/pgmq/after-create.sql

3. Verify function overloading in pgmq 1.5.1: docker run --rm public.ecr.aws/supabase/postgres:17.6.1.024
   psql -U postgres -c "CREATE EXTENSION pgmq;
   SELECT proname, pg_get_function_identity_arguments(oid)
   FROM pg_proc WHERE proname = 'drop_queue';"

---

Priority Justification

🔴 High Priority because:

• ❌ Blocks core local development workflow (db reset)
• ❌ Affects all new projects using pgmq
• ⚠️ Workaround requires manual intervention after every reset
• ✅ Simple fix (add function signatures)
• 📈 Affects growing user base as more projects adopt Supabase Queues feature

[infrabyanubhav]

Thanks for raising this! I’d love to take this issue and work on the fix. Could you point me to the correct location in the repo?

[star26bsd]

Hi @infrabyanubhav,

Thank you for offering to help with this fix!

The file that needs to be updated is located in the supabase/postgres repository:

File path: ansible/files/postgresql_extension_custom_scripts/pgmq/after-create.sql Direct link: https://github.com/supabase/postgres/blob/develop/ansible/files/postgresql_extension_custom_scripts/pgmq/after-create.sql

The Problem

The script attempts to drop the pgmq.drop_queue function using:

alter extension pgmq drop function pgmq.drop_queue(TEXT);
drop function pgmq.drop_queue(TEXT);

However, in pgmq 1.5.1, drop_queue now has multiple overloaded versions, so the function name alone is not unique. PostgreSQL requires the full function signature when multiple overloads exist.

The Fix

The DROP FUNCTION and ALTER EXTENSION ... DROP FUNCTION statements need to be updated to specify the complete function signature(s), similar to how the migration patch was fixed in nix/ext/pgmq/0001-fix-replace-drop_queue-function-if-exists.patch

You'll need to identify all overloaded versions of drop_queue in pgmq 1.5.1 and update the script accordingly.

Hope this helps! Let me know if you need any clarification.

(Full disclosure: this analysis has been done by Claude Code.)

[Hallidayo]

Transferred from supabase repo.

[harperkdavis]

Having this issue as well on latest version. Are there any workarounds while we wait for a fix?

[aaaaahaaaaa]

This is not only breaking the installation via the SQL statement but also via the UI

[aaaaahaaaaa]

I had to downgrade the CLI all the way to version 2.49 to be able to install the extension locally.

[willopez]

@jfroche @samrose this issue seems to have been fixed here https://github.com/supabase/postgres/pull/1828 However, I have attempted to enable the pgmq extension using the cli version v2.58.5 and the beta version and I still see that same error. what I am missing? Thanks in advance

[samrose]

@willopez will look into this and report back here