Hello!
Found that pg_upgrade test has broken for upgrades from older versions.
This happened for two reasons.
1) In 7b378237a the format of "aclitem" changed so upgrade from <=15
fails with error:
"Your installation contains the "aclitem" data type in user tables.
The internal format of "aclitem" changed in PostgreSQL version 16
so this cluster cannot currently be upgraded... "
Tried to fix it by changing the column type in the upgrade_adapt.sql.
Please see the patch attached.
2) In 60684dd83 and b5d63824 there are two changes in the set of specific
privileges.
The thing is that in the privileges.sql test there is REVOKE DELETE command
which becomes pair of REVOKE ALL and GRANT all specific privileges except DELETE
in the result dump. Therefore, any change in the set of specific privileges
will lead to
a non-zero dumps diff.
To avoid this, i propose to replace any specific GRANT and REVOKE in the result
dumps with ALL.
This also made in the patch attached.
Would be glad to any remarks.
With best regards,
--
Anton A. Melnikov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
commit e2c917694acc7ae20d6a9654de87a9fdb5863103
Author: Anton A. Melnikov <a.melni...@postgrespro.ru>
Date: Sun Dec 18 16:23:24 2022 +0300
Replace aclitem with text type in pg_upgrade test due to 7b378237
Replace specific privilegies in dumps with ALL due to b5d63824
and 60684dd8.
diff --git a/src/bin/pg_upgrade/t/002_pg_upgrade.pl b/src/bin/pg_upgrade/t/002_pg_upgrade.pl
index 4cc1469306..d23c4b2253 100644
--- a/src/bin/pg_upgrade/t/002_pg_upgrade.pl
+++ b/src/bin/pg_upgrade/t/002_pg_upgrade.pl
@@ -44,6 +44,8 @@ sub filter_dump
$dump_contents =~ s/^\-\-.*//mgx;
# Remove empty lines.
$dump_contents =~ s/^\n//mgx;
+ # Replace specific privilegies with ALL
+ $dump_contents =~ s/^(GRANT\s|REVOKE\s)(\S*)\s/$1ALL /mgx;
my $dump_file_filtered = "${dump_file}_filtered";
open(my $dh, '>', $dump_file_filtered)
diff --git a/src/bin/pg_upgrade/upgrade_adapt.sql b/src/bin/pg_upgrade/upgrade_adapt.sql
index 27c4c7fd01..fac77ec968 100644
--- a/src/bin/pg_upgrade/upgrade_adapt.sql
+++ b/src/bin/pg_upgrade/upgrade_adapt.sql
@@ -19,7 +19,8 @@ SELECT
ver <= 906 AS oldpgversion_le96,
ver <= 1000 AS oldpgversion_le10,
ver <= 1100 AS oldpgversion_le11,
- ver <= 1300 AS oldpgversion_le13
+ ver <= 1300 AS oldpgversion_le13,
+ ver <= 1500 AS oldpgversion_le15
FROM (SELECT current_setting('server_version_num')::int / 100 AS ver) AS v;
\gset
@@ -89,3 +90,24 @@ DROP OPERATOR public.#%# (pg_catalog.int8, NONE);
DROP OPERATOR public.!=- (pg_catalog.int8, NONE);
DROP OPERATOR public.#@%# (pg_catalog.int8, NONE);
\endif
+
+-- The internal format of "aclitem" changed in PostgreSQL version 16
+-- so replace it with text type
+\if :oldpgversion_le15
+DO $$
+DECLARE
+ change_aclitem_type TEXT;
+BEGIN
+ FOR change_aclitem_type IN
+ SELECT 'ALTER TABLE ' || table_schema || '.' ||
+ table_name || ' ALTER COLUMN ' ||
+ column_name || ' SET DATA TYPE text;'
+ AS change_aclitem_type
+ FROM information_schema.columns
+ WHERE data_type = 'aclitem' and table_schema != 'pg_catalog'
+ LOOP
+ EXECUTE change_aclitem_type;
+ END LOOP;
+END;
+$$;
+\endif
