Hi,
While working on changing the pg_stat_lock.wait_time datatype in powa after
commit c776550e466, I ran some tests to check the database state after the powa
extension is updated and found an unexpected difference.
If an extension marks a table as needing to be dumped with
pg_extension_config_dump() and then drops that table, the dropped table oid
will stay in pg_extension.extconfig. It seems problematic as obviously that
oid could eventually be reused for any unrelated relation, which could lead to
wrong behavior in pg_dump.
The documentation says:
> The only way to mark a table as no longer a configuration table is to
> dissociate it from the extension with ALTER EXTENSION ... DROP TABLE.
However I think that here it means an existing table, and dropping a table in
the extension script should do the same cleanup automatically.
Before spending too much time on that, is it a bug that should be fixed or
should the documentation be updated to say that if your extension drops a table
it needs to do an ALTER EXTENSION ... DROP TABLE or similar first?
AS a simple reproducer I attach a diff to show the problem in test_pg_dump
extension, with a new 1.1 version that drops and recreate the exact same table
and a then checks that all relations referenced in extconfig do exist.
diff --git a/src/test/modules/test_pg_dump/Makefile b/src/test/modules/test_pg_dump/Makefile
index 6123b994f60..3b4fc7d055d 100644
--- a/src/test/modules/test_pg_dump/Makefile
+++ b/src/test/modules/test_pg_dump/Makefile
@@ -4,7 +4,7 @@ MODULE = test_pg_dump
PGFILEDESC = "test_pg_dump - Test pg_dump with an extension"
EXTENSION = test_pg_dump
-DATA = test_pg_dump--1.0.sql
+DATA = test_pg_dump--1.0.sql test_pg_dump--1.0--1.1.sql
REGRESS = test_pg_dump
TAP_TESTS = 1
diff --git a/src/test/modules/test_pg_dump/expected/test_pg_dump.out b/src/test/modules/test_pg_dump/expected/test_pg_dump.out
index 98c9cd481e7..68fb9c7390e 100644
--- a/src/test/modules/test_pg_dump/expected/test_pg_dump.out
+++ b/src/test/modules/test_pg_dump/expected/test_pg_dump.out
@@ -1,5 +1,18 @@
CREATE ROLE regress_dump_test_role;
CREATE EXTENSION test_pg_dump;
+-- all relations configured as dumpable should exist
+SELECT c.oid
+FROM (
+ SELECT unnest(extconfig) AS dump_oid
+ FROM pg_extension
+ WHERE extname = 'test_pg_dump'
+) ext
+LEFT JOIN pg_class c ON c.oid = ext.dump_oid
+WHERE c.oid IS NULL;
+ dump_oid
+----------
+(0 rows)
+
ALTER EXTENSION test_pg_dump ADD DATABASE postgres; -- error
ERROR: cannot add an object of this type to an extension
CREATE TABLE test_pg_dump_t1 (c1 int, junk text);
diff --git a/src/test/modules/test_pg_dump/meson.build b/src/test/modules/test_pg_dump/meson.build
index 1d2f5736092..ca85e2dfeb4 100644
--- a/src/test/modules/test_pg_dump/meson.build
+++ b/src/test/modules/test_pg_dump/meson.build
@@ -3,6 +3,7 @@
test_install_data += files(
'test_pg_dump.control',
'test_pg_dump--1.0.sql',
+ 'test_pg_dump--1.1.sql',
)
tests += {
diff --git a/src/test/modules/test_pg_dump/sql/test_pg_dump.sql b/src/test/modules/test_pg_dump/sql/test_pg_dump.sql
index 87e66cae6e3..6955cc8521a 100644
--- a/src/test/modules/test_pg_dump/sql/test_pg_dump.sql
+++ b/src/test/modules/test_pg_dump/sql/test_pg_dump.sql
@@ -1,6 +1,16 @@
CREATE ROLE regress_dump_test_role;
CREATE EXTENSION test_pg_dump;
+-- all relations configured as dumpable should exist
+SELECT ext.dump_oid
+FROM (
+ SELECT unnest(extconfig) AS dump_oid
+ FROM pg_extension
+ WHERE extname = 'test_pg_dump'
+) ext
+LEFT JOIN pg_class c ON c.oid = ext.dump_oid
+WHERE c.oid IS NULL;
+
ALTER EXTENSION test_pg_dump ADD DATABASE postgres; -- error
CREATE TABLE test_pg_dump_t1 (c1 int, junk text);
diff --git a/src/test/modules/test_pg_dump/test_pg_dump.control b/src/test/modules/test_pg_dump/test_pg_dump.control
index fe3450db76c..67f1f1a7d5f 100644
--- a/src/test/modules/test_pg_dump/test_pg_dump.control
+++ b/src/test/modules/test_pg_dump/test_pg_dump.control
@@ -1,3 +1,3 @@
comment = 'Test pg_dump with an extension'
-default_version = '1.0'
+default_version = '1.1'
relocatable = true
