supabase-postgres.git / summary / log / commit / refs
commit bbfc14b72be1f7fc4ff2d8fa7437acff521db8ad
Author: Jean-François Roche <jfroche@pyxel.be>
Commit: GitHub <noreply@github.com>
Date: Thu Oct 09 20:07:26 2025 +0000
fix(pgmq): replace drop_queue function if exists (#1828)
* fix(pgmq): replace drop_queue function if exists
We introduced a patch to override pgmq.drop_queue(TEXT) to conditionally
drop objects only if they are part of the extension in `ansible/ansible/files/postgresql_extension_custom_scripts/pgmq/after-create.sql`
. This script might have been installed on installations with existing 1.4.4 extensions.
When the user tries to upgrade pgmq from 1.4.4 to 1.5.1, the upgrade
process will fail because the upgrade script doesn't expect
`pgmq.drop_queue(TEXT)` to exist.
This change introduce the a patch to the pgmq extension to use
`CREATE OR REPLACE FUNCTION` instead of `CREATE FUNCTION` for
`pgmq.drop_queue(TEXT)` in the upgrade script from 1.4.5 to 1.5.0.
* feat: test that the pgmq extension works well with the after create script
We create a new custom nixos test for the pgmq extension to test that the extension works
well with the after-create.sql script present in
`ansible/files/postgresql_extension_custom_scripts/pgmq/after-create.sql`.
* chore: bump versions to release post-rebase
---------
Co-authored-by: Sam Rose <samuel@supabase.io>
ansible/vars.yml | 6 +-
...fix-replace-drop_queue-function-if-exists.patch | 29 ++++
pgmq/default.nix} | 6 +-
nix/ext/tests/default.nix | 1 -
nix/ext/tests/lib.py | 5 +
nix/ext/tests/pgmq.nix | 163 +++++++++++++++++++++
nix/packages/postgres.nix | 2 +-
7 files changed, 206 insertions(+), 6 deletions(-)
diff --git a/ansible/vars.yml b/ansible/vars.yml
index 4013a105..c3006992 100644
--- a/ansible/vars.yml
+++ b/ansible/vars.yml
@@ -10,9 +10,9 @@ postgres_major:
# Full version strings for each major version
postgres_release:
- postgresorioledb-17: "17.5.1.040-orioledb"
- postgres17: "17.6.1.019"
- postgres15: "15.14.1.019"
+ postgresorioledb-17: "17.5.1.041-orioledb"
+ postgres17: "17.6.1.020"
+ postgres15: "15.14.1.020"
# Non Postgres Extensions
pgbouncer_release: 1.19.0
diff --git a/nix/ext/pgmq/0001-fix-replace-drop_queue-function-if-exists.patch b/nix/ext/pgmq/0001-fix-replace-drop_queue-function-if-exists.patch
new file mode 100644
index 00000000..a354af55
--- /dev/null
+++ b/nix/ext/pgmq/0001-fix-replace-drop_queue-function-if-exists.patch
@@ -0,0 +1,29 @@
+From 31a9b2d736d8a85f113d592754f4832a0097bb2d Mon Sep 17 00:00:00 2001
+From: =?UTF-8?q?Jean-Fran=C3=A7ois=20Roche?= <jfroche@pyxel.be>
+Date: Thu, 9 Oct 2025 10:53:50 +0200
+Subject: [PATCH] fix: replace drop_queue function if exists
+
+Function drop_queue might have been created by running `postgresql_extension_custom_scripts/pgmq/after-create.sql`
+on a previous version of the extension. In that case, running the migration script will fail because the function already exists.
+
+We use `CREATE OR REPLACE FUNCTION` to avoid that issue.
+---
+ pgmq-extension/sql/pgmq--1.4.5--1.5.0.sql | 2 +-
+ 1 file changed, 1 insertion(+), 1 deletion(-)
+
+diff --git a/pgmq-extension/sql/pgmq--1.4.5--1.5.0.sql b/pgmq-extension/sql/pgmq--1.4.5--1.5.0.sql
+index 5754eed..de03788 100644
+--- a/pgmq-extension/sql/pgmq--1.4.5--1.5.0.sql
++++ b/pgmq-extension/sql/pgmq--1.4.5--1.5.0.sql
+@@ -122,7 +122,7 @@ BEGIN
+ END;
+ $$ LANGUAGE plpgsql;
+
+-CREATE FUNCTION pgmq.drop_queue(queue_name TEXT)
++CREATE OR REPLACE FUNCTION pgmq.drop_queue(queue_name TEXT)
+ RETURNS BOOLEAN AS $$
+ DECLARE
+ qtable TEXT := pgmq.format_table_name(queue_name, 'q');
+--
+2.51.0
+
diff --git a/nix/ext/pgmq.nix b/nix/ext/pgmq/default.nix
similarity index 93%
rename from nix/ext/pgmq.nix
rename to nix/ext/pgmq/default.nix
index 0f7ab841..f1b288ce 100644
--- a/nix/ext/pgmq.nix
+++ b/nix/ext/pgmq/default.nix
@@ -9,7 +9,7 @@ let
pname = "pgmq";
# Load version configuration from external file
- allVersions = (builtins.fromJSON (builtins.readFile ./versions.json)).${pname};
+ allVersions = (builtins.fromJSON (builtins.readFile ../versions.json)).${pname};
# Filter versions compatible with current PostgreSQL version
supportedVersions = lib.filterAttrs (
@@ -37,6 +37,10 @@ let
inherit hash;
};
+ patches = lib.optionals (version == latestVersion) [
+ ./0001-fix-replace-drop_queue-function-if-exists.patch
+ ];
+
buildPhase = ''
cd pgmq-extension
'';
diff --git a/nix/ext/tests/default.nix b/nix/ext/tests/default.nix
index d1f3d89a..9a379d58 100644
--- a/nix/ext/tests/default.nix
+++ b/nix/ext/tests/default.nix
@@ -206,7 +206,6 @@ builtins.listToAttrs (
"pg_graphql"
"pg_jsonschema"
"pg_net"
- "pgmq"
"vector"
"wrappers"
]
diff --git a/nix/ext/tests/lib.py b/nix/ext/tests/lib.py
index 9f81ec11..3383f249 100644
--- a/nix/ext/tests/lib.py
+++ b/nix/ext/tests/lib.py
@@ -41,6 +41,11 @@ class PostgresExtensionTest(object):
f"""sudo -u postgres psql -t -A -F\",\" -c \"{query}\" """
).strip()
+ def run_sql_file(self, file: str) -> str:
+ return self.vm.succeed(
+ f"""sudo -u postgres psql -v ON_ERROR_STOP=1 -f \"{file}\""""
+ ).strip()
+
def drop_extension(self):
self.run_sql(f"DROP EXTENSION IF EXISTS {self.extension_name};")
diff --git a/nix/ext/tests/pgmq.nix b/nix/ext/tests/pgmq.nix
new file mode 100644
index 00000000..eabebff9
--- /dev/null
+++ b/nix/ext/tests/pgmq.nix
@@ -0,0 +1,163 @@
+{ self, pkgs }:
+let
+ pname = "pgmq";
+ inherit (pkgs) lib;
+ installedExtension =
+ postgresMajorVersion: self.packages.${pkgs.system}."psql_${postgresMajorVersion}/exts/${pname}-all";
+ versions = postgresqlMajorVersion: (installedExtension postgresqlMajorVersion).versions;
+ postgresqlWithExtension =
+ postgresql:
+ let
+ majorVersion = lib.versions.major postgresql.version;
+ pkg = pkgs.buildEnv {
+ name = "postgresql-${majorVersion}-${pname}";
+ paths = [
+ postgresql
+ postgresql.lib
+ (installedExtension majorVersion)
+ ];
+ passthru = {
+ inherit (postgresql) version psqlSchema;
+ lib = pkg;
+ withPackages = _: pkg;
+ };
+ nativeBuildInputs = [ pkgs.makeWrapper ];
+ pathsToLink = [
+ "/"
+ "/bin"
+ "/lib"
+ ];
+ postBuild = ''
+ wrapProgram $out/bin/postgres --set NIX_PGLIBDIR $out/lib
+ wrapProgram $out/bin/pg_ctl --set NIX_PGLIBDIR $out/lib
+ wrapProgram $out/bin/pg_upgrade --set NIX_PGLIBDIR $out/lib
+ '';
+ };
+ in
+ pkg;
+ psql_15 = postgresqlWithExtension self.packages.${pkgs.system}.postgresql_15;
+ psql_17 = postgresqlWithExtension self.packages.${pkgs.system}.postgresql_17;
+in
+self.inputs.nixpkgs.lib.nixos.runTest {
+ name = "timescaledb";
+ hostPkgs = pkgs;
+ nodes.server =
+ { config, ... }:
+ {
+ services.postgresql = {
+ enable = true;
+ package = (postgresqlWithExtension psql_15);
+ settings = (installedExtension "15").defaultSettings or { };
+ };
+
+ specialisation.postgresql17.configuration = {
+ services.postgresql = {
+ package = lib.mkForce psql_17;
+ };
+
+ systemd.services.postgresql-migrate = {
+ serviceConfig = {
+ Type = "oneshot";
+ RemainAfterExit = true;
+ User = "postgres";
+ Group = "postgres";
+ StateDirectory = "postgresql";
+ WorkingDirectory = "${builtins.dirOf config.services.postgresql.dataDir}";
+ };
+ script =
+ let
+ oldPostgresql = psql_15;
+ newPostgresql = psql_17;
+ oldDataDir = "${builtins.dirOf config.services.postgresql.dataDir}/${oldPostgresql.psqlSchema}";
+ newDataDir = "${builtins.dirOf config.services.postgresql.dataDir}/${newPostgresql.psqlSchema}";
+ in
+ ''
+ if [[ ! -d ${newDataDir} ]]; then
+ install -d -m 0700 -o postgres -g postgres "${newDataDir}"
+ ${newPostgresql}/bin/initdb -D "${newDataDir}"
+ ${newPostgresql}/bin/pg_upgrade --old-datadir "${oldDataDir}" --new-datadir "${newDataDir}" \
+ --old-bindir "${oldPostgresql}/bin" --new-bindir "${newPostgresql}/bin"
+ else
+ echo "${newDataDir} already exists"
+ fi
+ '';
+ };
+
+ systemd.services.postgresql = {
+ after = [ "postgresql-migrate.service" ];
+ requires = [ "postgresql-migrate.service" ];
+ };
+ };
+ };
+ testScript =
+ { nodes, ... }:
+ let
+ pg17-configuration = "${nodes.server.system.build.toplevel}/specialisation/postgresql17";
+ in
+ ''
+ from pathlib import Path
+ versions = {
+ "15": [${lib.concatStringsSep ", " (map (s: ''"${s}"'') (versions "15"))}],
+ "17": [${lib.concatStringsSep ", " (map (s: ''"${s}"'') (versions "17"))}],
+ }
+ extension_name = "${pname}"
+ support_upgrade = True
+ pg17_configuration = "${pg17-configuration}"
+ ext_has_background_worker = ${
+ if (installedExtension "15") ? hasBackgroundWorker then "True" else "False"
+ }
+ sql_test_directory = Path("${../../tests}")
+ pg_regress_test_name = "${(installedExtension "15").pgRegressTestName or pname}"
+
+ ${builtins.readFile ./lib.py}
+
+ start_all()
+
+ server.wait_for_unit("multi-user.target")
+ server.wait_for_unit("postgresql.service")
+
+ test = PostgresExtensionTest(server, extension_name, versions, sql_test_directory, support_upgrade)
+
+ with subtest("Check upgrade path with postgresql 15"):
+ test.check_upgrade_path("15")
+ test.run_sql_file("${../../../ansible/files/postgresql_extension_custom_scripts/pgmq/after-create.sql}")
+
+ with subtest("Check pg_regress with postgresql 15 after extension upgrade"):
+ test.check_pg_regress(Path("${psql_15}/lib/pgxs/src/test/regress/pg_regress"), "15", pg_regress_test_name)
+
+ last_version = None
+ with subtest("Check the install of the last version of the extension"):
+ last_version = test.check_install_last_version("15")
+ test.run_sql_file("${../../../ansible/files/postgresql_extension_custom_scripts/pgmq/after-create.sql}")
+
+ if ext_has_background_worker:
+ with subtest("Test switch_${pname}_version"):
+ test.check_switch_extension_with_background_worker(Path("${psql_15}/lib/${pname}.so"), "15")
+
+ with subtest("Check pg_regress with postgresql 15 after installing the last version"):
+ test.check_pg_regress(Path("${psql_15}/lib/pgxs/src/test/regress/pg_regress"), "15", pg_regress_test_name)
+
+ with subtest("switch to postgresql 17"):
+ server.succeed(
+ f"{pg17_configuration}/bin/switch-to-configuration test >&2"
+ )
+
+ with subtest("Check last version of the extension after postgresql upgrade"):
+ test.assert_version_matches(last_version)
+ test.run_sql_file("${../../../ansible/files/postgresql_extension_custom_scripts/pgmq/after-create.sql}")
+
+ with subtest("Check upgrade path with postgresql 17"):
+ test.check_upgrade_path("17")
+ test.run_sql_file("${../../../ansible/files/postgresql_extension_custom_scripts/pgmq/after-create.sql}")
+
+ with subtest("Check pg_regress with postgresql 17 after extension upgrade"):
+ test.check_pg_regress(Path("${psql_17}/lib/pgxs/src/test/regress/pg_regress"), "17", pg_regress_test_name)
+
+ with subtest("Check the install of the last version of the extension"):
+ test.check_install_last_version("17")
+ test.run_sql_file("${../../../ansible/files/postgresql_extension_custom_scripts/pgmq/after-create.sql}")
+
+ with subtest("Check pg_regress with postgresql 17 after installing the last version"):
+ test.check_pg_regress(Path("${psql_17}/lib/pgxs/src/test/regress/pg_regress"), "17", pg_regress_test_name)
+ '';
+}
diff --git a/nix/packages/postgres.nix b/nix/packages/postgres.nix
index ce7d22c4..bc13180d 100644
--- a/nix/packages/postgres.nix
+++ b/nix/packages/postgres.nix
@@ -22,7 +22,7 @@
../ext/pgroonga.nix
../ext/index_advisor.nix
../ext/wal2json.nix
- ../ext/pgmq.nix
+ ../ext/pgmq
../ext/pg_repack.nix
../ext/pg-safeupdate.nix
../ext/plpgsql-check.nix
[parent: 098740642e5b]