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

Start with /etc/postgresql-custom/extension-custom-scripts/pgmq/after-create.sql in the Postgres image and inspect the ALTER EXTENSION and DROP FUNCTION statements implicated by SQLSTATE 42725. Reproduce with CREATE EXTENSION pgmq on an affected 17.6.1.016+ image, then verify fresh initialization completes without the overload error.

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