Don't assume to be installed as an extension

[axelfontaine]

Now that the migration to pure PL/pgSQL is complete, it is very easy to run pgmq on any PostgreSQL instance by simply:

1. Creating a pgmq schema
2. Executing pgmq.sql

... or so it should be!

Unfortunately it doesn't quite work out of the box and the following changes are necessary:

Index: C:/Users/axel/AppData/Local/Temp/pgmq.sql
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
diff --git a/C:/Users/axel/AppData/Local/Temp/pgmq.sql b/sprinters/src/main/resources/flyway/pgmq/pgmq--1.4.5.sql
rename from C:/Users/axel/AppData/Local/Temp/pgmq.sql
rename to sprinters/src/main/resources/flyway/pgmq/pgmq--1.4.5.sql
--- a/C:/Users/axel/AppData/Local/Temp/pgmq.sql	
+++ b/sprinters/src/main/resources/flyway/pgmq/pgmq--1.4.5.sql	(date 1731965677611)
@@ -390,16 +390,6 @@
 END
 $$ LANGUAGE plpgsql;
 
--- unassign archive, so it can be kept when a queue is deleted
-CREATE FUNCTION pgmq."detach_archive"(queue_name TEXT)
-RETURNS VOID AS $$
-DECLARE
-  atable TEXT := pgmq.format_table_name(queue_name, 'a');
-BEGIN
-  EXECUTE format('ALTER EXTENSION pgmq DROP TABLE pgmq.%I', atable);
-END
-$$ LANGUAGE plpgsql;
-
 -- pop a single message
 CREATE FUNCTION pgmq.pop(queue_name TEXT)
 RETURNS SETOF pgmq.message_record AS $$
@@ -468,20 +458,6 @@
     atable TEXT := pgmq.format_table_name(queue_name, 'a');
     fq_atable TEXT := 'pgmq.' || atable;
 BEGIN
-    EXECUTE FORMAT(
-        $QUERY$
-        ALTER EXTENSION pgmq DROP TABLE pgmq.%I
-        $QUERY$,
-        qtable
-    );
-
-    EXECUTE FORMAT(
-        $QUERY$
-        ALTER EXTENSION pgmq DROP TABLE pgmq.%I
-        $QUERY$,
-        atable
-    );
-
     EXECUTE FORMAT(
         $QUERY$
         DROP TABLE IF EXISTS pgmq.%I
@@ -530,26 +506,6 @@
   END IF;
 END;
 $$ LANGUAGE plpgsql;
-
-CREATE FUNCTION pgmq._belongs_to_pgmq(table_name TEXT)
-RETURNS BOOLEAN AS $$
-DECLARE
-    sql TEXT;
-    result BOOLEAN;
-BEGIN
-  SELECT EXISTS (
-    SELECT 1
-    FROM pg_depend
-    WHERE refobjid = (SELECT oid FROM pg_extension WHERE extname = 'pgmq')
-    AND objid = (
-        SELECT oid
-        FROM pg_class
-        WHERE relname = table_name
-    )
-  ) INTO result;
-  RETURN result;
-END;
-$$ LANGUAGE plpgsql;
 
 CREATE FUNCTION pgmq.create_non_partitioned(queue_name TEXT)
 RETURNS void AS $$
@@ -586,14 +542,6 @@
     atable
   );
 
-  IF NOT pgmq._belongs_to_pgmq(qtable) THEN
-      EXECUTE FORMAT('ALTER EXTENSION pgmq ADD TABLE pgmq.%I', qtable);
-  END IF;
-
-  IF NOT pgmq._belongs_to_pgmq(atable) THEN
-      EXECUTE FORMAT('ALTER EXTENSION pgmq ADD TABLE pgmq.%I', atable);
-  END IF;
-
   EXECUTE FORMAT(
     $QUERY$
     CREATE INDEX IF NOT EXISTS %I ON pgmq.%I (vt ASC);
@@ -654,14 +602,6 @@
     atable
   );
 
-  IF NOT pgmq._belongs_to_pgmq(qtable) THEN
-      EXECUTE FORMAT('ALTER EXTENSION pgmq ADD TABLE pgmq.%I', qtable);
-  END IF;
-
-  IF NOT pgmq._belongs_to_pgmq(atable) THEN
-      EXECUTE FORMAT('ALTER EXTENSION pgmq ADD TABLE pgmq.%I', atable);
-  END IF;
-
   EXECUTE FORMAT(
     $QUERY$
     CREATE INDEX IF NOT EXISTS %I ON pgmq.%I (vt ASC);
@@ -760,10 +700,6 @@
     qtable, partition_col
   );
 
-  IF NOT pgmq._belongs_to_pgmq(qtable) THEN
-      EXECUTE FORMAT('ALTER EXTENSION pgmq ADD TABLE pgmq.%I', qtable);
-  END IF;
-
   -- https://github.com/pgpartman/pg_partman/blob/master/doc/pg_partman.md
   -- p_parent_table - the existing parent table. MUST be schema qualified, even if in public schema.
   EXECUTE FORMAT(
@@ -836,10 +772,6 @@
     atable, a_partition_col
   );
 
-  IF NOT pgmq._belongs_to_pgmq(atable) THEN
-      EXECUTE FORMAT('ALTER EXTENSION pgmq ADD TABLE pgmq.%I', atable);
-  END IF;
-
   -- https://github.com/pgpartman/pg_partman/blob/master/doc/pg_partman.md
   -- p_parent_table - the existing parent table. MUST be schema qualified, even if in public schema.
   EXECUTE FORMAT(

It would be great if for example _belongs_to_pgmq could check whether a pgmq extensions exists. If yes, the behavior stays as-is, otherwise true is returned rendering the operations guarded by that function into no-ops. The remaining calls in pop and detach_archivewould then also need to be guarded and then the solution would be complete without impacting existing users of the extension.

What do you think?

Related to:

• Question: Does this work on hosted providers? #205

[ChuckHend]

First, thank you for following the project! I am very much in favor of making it easier to install pgmq. There's several projects in the community that have been trying to improve this for Postgres extensions in general...although for this particular project I agree it would be nice if we could go around the Postgres extension API since it does present challenges.

I have not tried before, but what errors do you get when the diff above is not applied?

[axelfontaine]

Running

do
$$
    begin
        perform pgmq."create"(queue_name := 'test-queue');
    end
$$;

on a database with fresh pgmq with the original pgmq.sql applied results in:

[2024-11-19 20:37:28] [42704] ERROR: extension "pgmq" does not exist
[2024-11-19 20:37:28] Where: SQL statement "ALTER EXTENSION pgmq ADD TABLE pgmq."q_test-queue""
[2024-11-19 20:37:28] PL/pgSQL function pgmq.create_non_partitioned(text) line 36 at EXECUTE
[2024-11-19 20:37:28] SQL statement "SELECT pgmq.create_non_partitioned(queue_name)"
[2024-11-19 20:37:28] PL/pgSQL function pgmq."create"(text) line 3 at PERFORM
[2024-11-19 20:37:28] SQL statement "SELECT pgmq."create"(queue_name := 'test-queue')"
[2024-11-19 20:37:28] PL/pgSQL function inline_code_block line 3 at PERFORM

Ideally the solution is probably not what I originally suggested but rather a function like this

create function _check_pgmq_extension_installed() returns boolean
    language plpgsql
as
$$
DECLARE
    extension_exists BOOLEAN;
BEGIN
    SELECT EXISTS (
        SELECT 1
        FROM pg_extension
        WHERE extname = 'pgmq'
    ) INTO extension_exists;

    RETURN extension_exists;
END;
$$;

used to guard all code blocks that currently assume the pgmq extension is installed.