pg.ddx.io pgsql-hackers@postgresql.org mailing list archive
help / color / mirror / Atom feed[BUG] pg_upgrade test fails from older versions.
19+ messages / 4 participants
[nested] [flat]
Commits from this thread:
2022-12-27 9814ff5500 Add custom filtering rules to the TAP tests of pg_upgrade
2022-12-25 d3c0cc4447 Switch query fixing aclitems in ~15 from O(N^2) to O(N) in upgrade_adapt.sql
2022-12-23 f4f2f2b84a Update upgrade_adapt.sql to handle tables using aclitem as data type
2022-12-23 3022cb1433 9c48a0f000 fd36b82c5d d4fea2e89d a2688c4e03 Fix some incorrectness in upgrade_adapt.sql on query for WITH OIDS
* [BUG] pg_upgrade test fails from older versions.
@ 2022-12-19 00:50 Anton A. Melnikov <aamelnikov@inbox.ru>
2022-12-19 01:56 ` Re: [BUG] pg_upgrade test fails from older versions. Tom Lane <tgl@sss.pgh.pa.us>
2022-12-19 03:10 ` Re: [BUG] pg_upgrade test fails from older versions. Michael Paquier <michael@paquier.xyz>
0 siblings, 2 replies; 19+ messages in thread
From: Anton A. Melnikov @ 2022-12-19 00:50 UTC (permalink / raw)
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
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
Attachments:
v1-0001-Fix-pg_upgrade-test.patch (text/x-patch, 2.1K) download
commit e2c917694acc7ae20d6a9654de87a9fdb5863103
Author: Anton A. Melnikov <a.melnikov@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
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [BUG] pg_upgrade test fails from older versions.
2022-12-19 00:50 [BUG] pg_upgrade test fails from older versions. Anton A. Melnikov <aamelnikov@inbox.ru>
@ 2022-12-19 01:56 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-12-19 03:16 ` Re: [BUG] pg_upgrade test fails from older versions. Michael Paquier <michael@paquier.xyz>
1 sibling, 1 reply; 19+ messages in thread
From: Tom Lane @ 2022-12-19 01:56 UTC (permalink / raw)
To: Anton A. Melnikov <aamelnikov@inbox.ru>; +Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
"Anton A. Melnikov" <aamelnikov@inbox.ru> writes:
> 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.
Isn't that likely to mask actual bugs?
regards, tom lane
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [BUG] pg_upgrade test fails from older versions.
2022-12-19 00:50 [BUG] pg_upgrade test fails from older versions. Anton A. Melnikov <aamelnikov@inbox.ru>
2022-12-19 01:56 ` Re: [BUG] pg_upgrade test fails from older versions. Tom Lane <tgl@sss.pgh.pa.us>
@ 2022-12-19 03:16 ` Michael Paquier <michael@paquier.xyz>
2022-12-22 06:59 ` Re: [BUG] pg_upgrade test fails from older versions. Anton A. Melnikov <aamelnikov@inbox.ru>
0 siblings, 1 reply; 19+ messages in thread
From: Michael Paquier @ 2022-12-19 03:16 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Anton A. Melnikov <aamelnikov@inbox.ru>; PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
On Sun, Dec 18, 2022 at 08:56:48PM -0500, Tom Lane wrote:
> "Anton A. Melnikov" <aamelnikov@inbox.ru> writes:
>> 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.
>
> Isn't that likely to mask actual bugs?
+ # Replace specific privilegies with ALL
+ $dump_contents =~ s/^(GRANT\s|REVOKE\s)(\S*)\s/$1ALL /mgx;
Yes, this would silence some diffs in the dumps taken from the old and
the new clusters. It seems to me that it is one of the things where
the original dumps have better be tweaked, as this does not cause a
hard failure when running pg_upgrade.
While thinking about that, an extra idea popped in my mind as it may
be interesting to be able to filter out some of the diffs in some
contexts. So what about adding in 002_pg_upgrade.pl a small-ish hook
in the shape of a new environment variable pointing to a file adds
some custom filtering rules?
--
Michael
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [BUG] pg_upgrade test fails from older versions.
2022-12-19 00:50 [BUG] pg_upgrade test fails from older versions. Anton A. Melnikov <aamelnikov@inbox.ru>
2022-12-19 01:56 ` Re: [BUG] pg_upgrade test fails from older versions. Tom Lane <tgl@sss.pgh.pa.us>
2022-12-19 03:16 ` Re: [BUG] pg_upgrade test fails from older versions. Michael Paquier <michael@paquier.xyz>
@ 2022-12-22 06:59 ` Anton A. Melnikov <aamelnikov@inbox.ru>
2022-12-23 02:42 ` Re: [BUG] pg_upgrade test fails from older versions. Michael Paquier <michael@paquier.xyz>
0 siblings, 1 reply; 19+ messages in thread
From: Anton A. Melnikov @ 2022-12-22 06:59 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Hello!
Divided patch into two parts: first part refers to the modification of
the old dump while the second one relates to dump filtering.
1) v2-0001-Remove-aclitem-from-old-dump.patch
On 19.12.2022 06:10, Michael Paquier wrote:
> This is forgetting about materialized views, which is something that
> pg_upgrade would also complain about when checking for relations with
> aclitems. As far as I can see, the only place in the main regression
> test suite where we have an aclitem attribute is tab_core_types for
> HEAD and the stable branches, so it would be enough to do this
> change. Anyway, wouldn't it be better to use the same conditions as
> the WITH OIDS queries a few lines above, at least for consistency?
>
> Note that check_for_data_types_usage() checks for tables, matviews and
> indexes.
Found that 'ALTER ... ALTER COLUMN SET DATA TYPE text'
is not applicable to materialized views and indexes as well as DROP COLUMN.
So couldn't make anything better than drop its in the old dump if they
contain at least one column of 'aclitem' type.
i've tested this script with:
CREATE TABLE acltable AS SELECT 1 AS int, 'postgres=a/postgres'::aclitem AS aclitem;
CREATE MATERIALIZED VIEW aclmview AS SELECT 'postgres=a/postgres'::aclitem AS aclitem;
CREATE INDEX aclindex on acltable (int) INCLUDE (aclitem);
performed in the regression database before creating the old dump.
The only thing i haven't been able to find a case when an an 'acltype' column would
be preserved in the index when this type was replaced in the parent table.
So passing relkind = 'i' is probably redundant.
If it is possible to find such a case, it would be very interesting.
Also made the replacement logic for 'acltype' in the tables more closer
to above the script that removes OIDs columns. In this script found likewise that
ALTER TABLE ... SET WITHOUT OIDS is not applicable to materialized views
and ALTER MATERIALIZED VIEW doesn't support WITHOUT OIDS clause.
Besides i couldn't find any legal way to create materialized view with oids in versions 11 or lower.
Command 'CREATE MATERIALIZED VIEW' doesn't support WITH OIDS or (OIDS) clause,
as well as ALTER MATERIALIZED VIEW as mentioned above.
Even with GUC default_with_oids = true":
CREATE TABLE oidtable AS SELECT 1 AS int;
CREATE MATERIALIZED VIEW oidmv AS SELECT * FROM oidtable;
give:
postgres=# SELECT oid::regclass::text FROM pg_class WHERE relname !~ '^pg_' AND relhasoids;
oid
----------
oidtable
(1 row)
So suggest to exclude the check of materialized views from this DO block.
Would be grateful for remarks if i didn't consider some cases.
2) v2-0002-Additional-dumps-filtering.patch
On 19.12.2022 06:16, Michael Paquier wrote:
>
> While thinking about that, an extra idea popped in my mind as it may
> be interesting to be able to filter out some of the diffs in some
> contexts. So what about adding in 002_pg_upgrade.pl a small-ish hook
> in the shape of a new environment variable pointing to a file adds
> some custom filtering rules?
Yes. Made a hook that allows to proceed an external text file with additional
filtering rules and example of such file. Please take a look on it.
With the best wishes,
--
Anton A. Melnikov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
Attachments: download all (2 files, .tar.gz)
v2-0001-Remove-aclitem-from-old-dump.patch (text/x-patch, 2.4K) download
commit f4a6f18f008ab4acd0d6923ddce7aa6d20bef08f
Author: Anton A. Melnikov <a.melnikov@postgrespro.ru>
Date: Thu Dec 22 07:22:53 2022 +0300
Remove type 'aclitem' from old dump when test upgrade from older versions.
diff --git a/src/bin/pg_upgrade/upgrade_adapt.sql b/src/bin/pg_upgrade/upgrade_adapt.sql
index 27c4c7fd01..84389f3e5b 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
@@ -72,7 +73,7 @@ DO $stmt$
FROM pg_class
WHERE relname !~ '^pg_'
AND relhasoids
- AND relkind in ('r','m')
+ AND relkind = 'r'
ORDER BY 1
LOOP
execute 'ALTER TABLE ' || rec || ' SET WITHOUT OIDS';
@@ -89,3 +90,58 @@ 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 in tables and drop materialized views
+-- and indexes that contain column(s) of "aclitem" type.
+\if :oldpgversion_le15
+DO $$
+ DECLARE
+ rec text;
+ col text;
+ BEGIN
+ FOR rec in
+ SELECT oid::regclass::text
+ FROM pg_class
+ WHERE relname !~ '^pg_'
+ AND relkind = 'r'
+ ORDER BY 1
+ LOOP
+ FOR col in
+ SELECT attname
+ FROM pg_attribute
+ WHERE attrelid::regclass::text = rec
+ AND atttypid = 'aclitem'::regtype
+ LOOP
+ execute 'ALTER TABLE ' || rec || ' ALTER COLUMN '
+ || col || ' SET DATA TYPE text';
+ END LOOP;
+ END LOOP;
+ END; $$;
+
+DO $$
+ DECLARE
+ obj text;
+ BEGIN
+ FOR obj in
+ SELECT type || ' ' || name AS obj
+ FROM (SELECT
+ CASE
+ WHEN relkind = 'm' THEN 'MATERIALIZED VIEW'
+ WHEN relkind = 'i' THEN 'INDEX'
+ END AS type,
+ oid::regclass::text AS name
+ FROM pg_class
+ WHERE relname !~ '^pg_'
+ AND relkind in ('m','i')
+ AND EXISTS
+ (SELECT attname
+ FROM pg_attribute
+ WHERE attrelid = oid
+ AND atttypid = 'aclitem'::regtype)
+ ) AS foo
+ LOOP
+ execute 'DROP '|| obj || ' CASCADE';
+ END LOOP;
+ END; $$;
+\endif
v2-0002-Additional-dumps-filtering.patch (text/x-patch, 2.3K) download
commit 036e9d372241e7046e7b83baef991325d39730c3
Author: Anton A. Melnikov <a.melnikov@postgrespro.ru>
Date: Thu Dec 22 08:01:40 2022 +0300
Additional dumps filtering during upgrade test.
diff --git a/src/bin/pg_upgrade/TESTING b/src/bin/pg_upgrade/TESTING
index 127dc30bbb..8309aaeb05 100644
--- a/src/bin/pg_upgrade/TESTING
+++ b/src/bin/pg_upgrade/TESTING
@@ -56,3 +56,8 @@ Once the dump is created, it can be repeatedly used with $olddump and
the dump out of the new database and the comparison of the dumps between
the old and new databases. The contents of the dumps can also be manually
compared.
+
+You can use additional dump filtering. To do this, you need to define the
+'filter' environment variable and specify the path to the file with
+filtering rules in it. There is an example of such a file in
+src/bin/pg_upgrade/filter/regex.
diff --git a/src/bin/pg_upgrade/t/002_pg_upgrade.pl b/src/bin/pg_upgrade/t/002_pg_upgrade.pl
index 4cc1469306..027b3fa35c 100644
--- a/src/bin/pg_upgrade/t/002_pg_upgrade.pl
+++ b/src/bin/pg_upgrade/t/002_pg_upgrade.pl
@@ -44,6 +44,33 @@ 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;
+
+ if (defined($ENV{filter}))
+ {
+ # Use the external filter
+ my $ext_filter_file = $ENV{filter};
+ die "no file with external filter found!" unless -e $ext_filter_file;
+
+ open my $ext_filter_handle, '<', $ext_filter_file;
+ chomp(my @ext_filter_lines = <$ext_filter_handle>);
+ close $ext_filter_handle;
+
+ foreach (@ext_filter_lines)
+ {
+ my @ext_filter = split('\/', $_);
+
+ if (defined $ext_filter[1] && defined $ext_filter[2])
+ {
+ $dump_contents =~ s/$ext_filter[1]/$ext_filter[2]/mgx;
+ }
+ elsif (defined $ext_filter[1])
+ {
+ $dump_contents =~ s/$ext_filter[1]//mgx;
+ }
+ }
+ }
my $dump_file_filtered = "${dump_file}_filtered";
open(my $dh, '>', $dump_file_filtered)
diff --git a/src/bin/pg_upgrade/t/filter.regex b/src/bin/pg_upgrade/t/filter.regex
new file mode 100644
index 0000000000..8b4c06c5f3
--- /dev/null
+++ b/src/bin/pg_upgrade/t/filter.regex
@@ -0,0 +1,5 @@
+# examples:
+# Remove all CREATE POLICY statements
+/^CREATE\sPOLICY.*//
+# Replace REFRESH with DROP for materialized views
+/^REFRESH\s(MATERIALIZED\sVIEW)/DROP $1/
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [BUG] pg_upgrade test fails from older versions.
2022-12-19 00:50 [BUG] pg_upgrade test fails from older versions. Anton A. Melnikov <aamelnikov@inbox.ru>
2022-12-19 01:56 ` Re: [BUG] pg_upgrade test fails from older versions. Tom Lane <tgl@sss.pgh.pa.us>
2022-12-19 03:16 ` Re: [BUG] pg_upgrade test fails from older versions. Michael Paquier <michael@paquier.xyz>
2022-12-22 06:59 ` Re: [BUG] pg_upgrade test fails from older versions. Anton A. Melnikov <aamelnikov@inbox.ru>
@ 2022-12-23 02:42 ` Michael Paquier <michael@paquier.xyz>
2022-12-23 03:27 ` Re: [BUG] pg_upgrade test fails from older versions. Justin Pryzby <pryzby@telsasoft.com>
0 siblings, 1 reply; 19+ messages in thread
From: Michael Paquier @ 2022-12-23 02:42 UTC (permalink / raw)
To: Anton A. Melnikov <aamelnikov@inbox.ru>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
On Thu, Dec 22, 2022 at 09:59:18AM +0300, Anton A. Melnikov wrote:
> 2) v2-0002-Additional-dumps-filtering.patch
+ # Replace specific privilegies with ALL
+ $dump_contents =~ s/^(GRANT\s|REVOKE\s)(\S*)\s/$1ALL /mgx;
This should not be in 0002, I guess..
> Yes. Made a hook that allows to proceed an external text file with additional
> filtering rules and example of such file. Please take a look on it.
>
> With the best wishes,
Hmm. 0001 does a direct check on aclitem as data type used in an
attribute, but misses anything related to arrays, domains or even
composite types, not to mention that we'd miss uses of aclitems in
index expressions.
That's basically the kind of thing check_for_data_types_usage() does.
I am not sure that it is a good idea to provide a limited coverage if
we do that for matviews and indexes, and the complexity induced in
upgrade_adapt.sql is not really appealing either. For now, I have
fixed the most pressing part for tables to match with the buildfarm
code that just drops the aclitem column rather than doing that for all
the relations that could have one.
The part on WITH OIDS has been addressed in its own commit down to
v12, removing the handling for matviews but adding one for foreign
tables where the operation is supported.
--
Michael
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [BUG] pg_upgrade test fails from older versions.
2022-12-19 00:50 [BUG] pg_upgrade test fails from older versions. Anton A. Melnikov <aamelnikov@inbox.ru>
2022-12-19 01:56 ` Re: [BUG] pg_upgrade test fails from older versions. Tom Lane <tgl@sss.pgh.pa.us>
2022-12-19 03:16 ` Re: [BUG] pg_upgrade test fails from older versions. Michael Paquier <michael@paquier.xyz>
2022-12-22 06:59 ` Re: [BUG] pg_upgrade test fails from older versions. Anton A. Melnikov <aamelnikov@inbox.ru>
2022-12-23 02:42 ` Re: [BUG] pg_upgrade test fails from older versions. Michael Paquier <michael@paquier.xyz>
@ 2022-12-23 03:27 ` Justin Pryzby <pryzby@telsasoft.com>
2022-12-23 08:51 ` Re: [BUG] pg_upgrade test fails from older versions. Michael Paquier <michael@paquier.xyz>
2022-12-23 09:17 ` Re: [BUG] pg_upgrade test fails from older versions. Anton A. Melnikov <aamelnikov@inbox.ru>
0 siblings, 2 replies; 19+ messages in thread
From: Justin Pryzby @ 2022-12-23 03:27 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; +Cc: Anton A. Melnikov <aamelnikov@inbox.ru>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-hackers@lists.postgresql.org
On Fri, Dec 23, 2022 at 11:42:39AM +0900, Michael Paquier wrote:
> Hmm. 0001 does a direct check on aclitem as data type used in an
> attribute,
> For now, I have fixed the most pressing part for tables to match with
> the buildfarm
+DO $$
+ DECLARE
+ rec text;
+ col text;
+ BEGIN
+ FOR rec in
+ SELECT oid::regclass::text
+ FROM pg_class
+ WHERE relname !~ '^pg_'
+ AND relkind IN ('r')
+ ORDER BY 1
+ LOOP
+ FOR col in SELECT attname FROM pg_attribute
+ WHERE attrelid::regclass::text = rec
+ AND atttypid = 'aclitem'::regtype
+ LOOP
+ EXECUTE 'ALTER TABLE ' || quote_ident(rec) || ' ALTER COLUMN ' ||
+ quote_ident(col) || ' SET DATA TYPE text';
+ END LOOP;
+ END LOOP;
+ END; $$;
This will do a seq scan around pg_attribute for each relation (currently
~600)...
Here, that takes a few seconds in a debug build, and I guess it'll be
more painful when running under valgrind/discard_caches/antiquated
hardware/etc.
This would do a single seqscan:
SELECT format('ALTER TABLE %I ALTER COLUMN %I TYPE TEXT', attrelid::regclass, attname) FROM pg_attribute WHERE atttypid='aclitem'::regtype; -- AND ...
\gexec
--
Justin
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [BUG] pg_upgrade test fails from older versions.
2022-12-19 00:50 [BUG] pg_upgrade test fails from older versions. Anton A. Melnikov <aamelnikov@inbox.ru>
2022-12-19 01:56 ` Re: [BUG] pg_upgrade test fails from older versions. Tom Lane <tgl@sss.pgh.pa.us>
2022-12-19 03:16 ` Re: [BUG] pg_upgrade test fails from older versions. Michael Paquier <michael@paquier.xyz>
2022-12-22 06:59 ` Re: [BUG] pg_upgrade test fails from older versions. Anton A. Melnikov <aamelnikov@inbox.ru>
2022-12-23 02:42 ` Re: [BUG] pg_upgrade test fails from older versions. Michael Paquier <michael@paquier.xyz>
2022-12-23 03:27 ` Re: [BUG] pg_upgrade test fails from older versions. Justin Pryzby <pryzby@telsasoft.com>
@ 2022-12-23 08:51 ` Michael Paquier <michael@paquier.xyz>
2022-12-23 09:43 ` Re: [BUG] pg_upgrade test fails from older versions. Anton A. Melnikov <aamelnikov@inbox.ru>
2022-12-23 16:39 ` Re: [BUG] pg_upgrade test fails from older versions. Justin Pryzby <pryzby@telsasoft.com>
1 sibling, 2 replies; 19+ messages in thread
From: Michael Paquier @ 2022-12-23 08:51 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; +Cc: Anton A. Melnikov <aamelnikov@inbox.ru>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-hackers@lists.postgresql.org
On Thu, Dec 22, 2022 at 09:27:24PM -0600, Justin Pryzby wrote:
> This would do a single seqscan:
> SELECT format('ALTER TABLE %I ALTER COLUMN %I TYPE TEXT',
> attrelid::regclass, attname) FROM pg_attribute WHERE
> atttypid='aclitem'::regtype; -- AND ...
> \gexec
FWIW, I find the use of a FOR loop with a DO block much cleaner to
follow in this context, so something like the attached would be able
to group the two queries and address your point on O(N^2). Do you
like that?
--
Michael
Attachments:
tweak_upgrade_query.sql (application/sql, 1.2K) download
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [BUG] pg_upgrade test fails from older versions.
2022-12-19 00:50 [BUG] pg_upgrade test fails from older versions. Anton A. Melnikov <aamelnikov@inbox.ru>
2022-12-19 01:56 ` Re: [BUG] pg_upgrade test fails from older versions. Tom Lane <tgl@sss.pgh.pa.us>
2022-12-19 03:16 ` Re: [BUG] pg_upgrade test fails from older versions. Michael Paquier <michael@paquier.xyz>
2022-12-22 06:59 ` Re: [BUG] pg_upgrade test fails from older versions. Anton A. Melnikov <aamelnikov@inbox.ru>
2022-12-23 02:42 ` Re: [BUG] pg_upgrade test fails from older versions. Michael Paquier <michael@paquier.xyz>
2022-12-23 03:27 ` Re: [BUG] pg_upgrade test fails from older versions. Justin Pryzby <pryzby@telsasoft.com>
2022-12-23 08:51 ` Re: [BUG] pg_upgrade test fails from older versions. Michael Paquier <michael@paquier.xyz>
@ 2022-12-23 09:43 ` Anton A. Melnikov <aamelnikov@inbox.ru>
2022-12-26 02:52 ` Re: [BUG] pg_upgrade test fails from older versions. Michael Paquier <michael@paquier.xyz>
1 sibling, 1 reply; 19+ messages in thread
From: Anton A. Melnikov @ 2022-12-23 09:43 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; Justin Pryzby <pryzby@telsasoft.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-hackers@lists.postgresql.org
Sorry, didn't get to see the last letter!
On 23.12.2022 11:51, Michael Paquier wrote:
>
> FWIW, I find the use of a FOR loop with a DO block much cleaner to
> follow in this context, so something like the attached would be able
> to group the two queries and address your point on O(N^2). Do you
> like that?
> --
> Michael
The query:
DO $$
DECLARE
rec record;
BEGIN
FOR rec in
SELECT oid::regclass::text as rel, attname as col
FROM pg_class c, pg_attribute a
WHERE c.relname !~ '^pg_'
AND c.relkind IN ('r')
AND a.attrelid = c.oid
AND a.atttypid = 'aclitem'::regtype
ORDER BY 1
LOOP
EXECUTE 'ALTER TABLE ' || quote_ident(rec.rel) || ' ALTER COLUMN ' ||
quote_ident(rec.col) || ' SET DATA TYPE text';
END LOOP;
END; $$;
gives the average time of 36 ms at the same conditions.
With the best wishes!
--
Anton A. Melnikov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [BUG] pg_upgrade test fails from older versions.
2022-12-19 00:50 [BUG] pg_upgrade test fails from older versions. Anton A. Melnikov <aamelnikov@inbox.ru>
2022-12-19 01:56 ` Re: [BUG] pg_upgrade test fails from older versions. Tom Lane <tgl@sss.pgh.pa.us>
2022-12-19 03:16 ` Re: [BUG] pg_upgrade test fails from older versions. Michael Paquier <michael@paquier.xyz>
2022-12-22 06:59 ` Re: [BUG] pg_upgrade test fails from older versions. Anton A. Melnikov <aamelnikov@inbox.ru>
2022-12-23 02:42 ` Re: [BUG] pg_upgrade test fails from older versions. Michael Paquier <michael@paquier.xyz>
2022-12-23 03:27 ` Re: [BUG] pg_upgrade test fails from older versions. Justin Pryzby <pryzby@telsasoft.com>
2022-12-23 08:51 ` Re: [BUG] pg_upgrade test fails from older versions. Michael Paquier <michael@paquier.xyz>
2022-12-23 09:43 ` Re: [BUG] pg_upgrade test fails from older versions. Anton A. Melnikov <aamelnikov@inbox.ru>
@ 2022-12-26 02:52 ` Michael Paquier <michael@paquier.xyz>
2022-12-26 06:22 ` Re: [BUG] pg_upgrade test fails from older versions. Anton A. Melnikov <aamelnikov@inbox.ru>
0 siblings, 1 reply; 19+ messages in thread
From: Michael Paquier @ 2022-12-26 02:52 UTC (permalink / raw)
To: Anton A. Melnikov <aamelnikov@inbox.ru>; +Cc: Justin Pryzby <pryzby@telsasoft.com>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-hackers@lists.postgresql.org
On Fri, Dec 23, 2022 at 12:43:00PM +0300, Anton A. Melnikov wrote:
> Sorry, didn't get to see the last letter!
No worries, the result is the same :)
I was looking at 0002 to add a callback to provide custom filtering
rules.
+ my @ext_filter = split('\/', $_);
Are you sure that enforcing a separation with a slash is a good idea?
What if the filters include directory paths or characters that are
escaped, for example?
Rather than introducing a filter.regex, I would tend to just document
that in the README with a small example. I have been considering a
few alternatives while making this useful in most cases, still my mind
alrways comes back to the simplest thing we to just read each line of
the file, chomp it and apply the pattern to the log file..
--
Michael
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [BUG] pg_upgrade test fails from older versions.
2022-12-19 00:50 [BUG] pg_upgrade test fails from older versions. Anton A. Melnikov <aamelnikov@inbox.ru>
2022-12-19 01:56 ` Re: [BUG] pg_upgrade test fails from older versions. Tom Lane <tgl@sss.pgh.pa.us>
2022-12-19 03:16 ` Re: [BUG] pg_upgrade test fails from older versions. Michael Paquier <michael@paquier.xyz>
2022-12-22 06:59 ` Re: [BUG] pg_upgrade test fails from older versions. Anton A. Melnikov <aamelnikov@inbox.ru>
2022-12-23 02:42 ` Re: [BUG] pg_upgrade test fails from older versions. Michael Paquier <michael@paquier.xyz>
2022-12-23 03:27 ` Re: [BUG] pg_upgrade test fails from older versions. Justin Pryzby <pryzby@telsasoft.com>
2022-12-23 08:51 ` Re: [BUG] pg_upgrade test fails from older versions. Michael Paquier <michael@paquier.xyz>
2022-12-23 09:43 ` Re: [BUG] pg_upgrade test fails from older versions. Anton A. Melnikov <aamelnikov@inbox.ru>
2022-12-26 02:52 ` Re: [BUG] pg_upgrade test fails from older versions. Michael Paquier <michael@paquier.xyz>
@ 2022-12-26 06:22 ` Anton A. Melnikov <aamelnikov@inbox.ru>
2022-12-27 05:44 ` Re: [BUG] pg_upgrade test fails from older versions. Michael Paquier <michael@paquier.xyz>
0 siblings, 1 reply; 19+ messages in thread
From: Anton A. Melnikov @ 2022-12-26 06:22 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; +Cc: Justin Pryzby <pryzby@telsasoft.com>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-hackers@lists.postgresql.org
Hello!
On 23.12.2022 05:42, Michael Paquier wrote:
> On Thu, Dec 22, 2022 at 09:59:18AM +0300, Anton A. Melnikov wrote:
>> 2) v2-0002-Additional-dumps-filtering.patch
>
> + # Replace specific privilegies with ALL
> + $dump_contents =~ s/^(GRANT\s|REVOKE\s)(\S*)\s/$1ALL /mgx;
> This should not be in 0002, I guess..
Made a separate patch for it: v3-0001-Fix-dumps-filtering.patch
On 26.12.2022 05:52, Michael Paquier wrote:
> On Fri, Dec 23, 2022 at 12:43:00PM +0300, Anton A. Melnikov wrote:
> I was looking at 0002 to add a callback to provide custom filtering
> rules.
>
> + my @ext_filter = split('\/', $_);
> Are you sure that enforcing a separation with a slash is a good idea?
> What if the filters include directory paths or characters that are
> escaped, for example?
>
> Rather than introducing a filter.regex, I would tend to just document
> that in the README with a small example. I have been considering a
> few alternatives while making this useful in most cases, still my mind
> alrways comes back to the simplest thing we to just read each line of
> the file, chomp it and apply the pattern to the log file..
Thanks for your attention!
Yes, indeed. It will be really simpler.
Made it in the v3-0002-Add-external-dumps-filtering.patch
With the best wishes,
--
Anton A. Melnikov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
Attachments: download all (2 files, .tar.gz)
v3-0001-Fix-dumps-filtering.patch (text/x-patch, 835B) download
commit 602627daf32a6d33ae96ce8fbae918c9f00f0633
Author: Anton A. Melnikov <a.melnikov@postgrespro.ru>
Date: Mon Dec 26 06:21:32 2022 +0300
Fix dupms filtering in pg_upgrade test from older versions. 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)
v3-0002-Add-external-dumps-filtering.patch (text/x-patch, 2.0K) download
commit 3870fb8263dc7e3fa240753b4eb89945b8d229be
Author: Anton A. Melnikov <a.melnikov@postgrespro.ru>
Date: Thu Dec 22 08:01:40 2022 +0300
Add external dumps filtering during upgrade test.
diff --git a/src/bin/pg_upgrade/TESTING b/src/bin/pg_upgrade/TESTING
index 127dc30bbb..b8cce2bae1 100644
--- a/src/bin/pg_upgrade/TESTING
+++ b/src/bin/pg_upgrade/TESTING
@@ -56,3 +56,12 @@ Once the dump is created, it can be repeatedly used with $olddump and
the dump out of the new database and the comparison of the dumps between
the old and new databases. The contents of the dumps can also be manually
compared.
+
+You can use additional dump filtering. To do this, you need to define the
+'filter' environment variable and specify the path to the file with
+filtering rules in it. Here is an example contens of such a file:
+# examples:
+# Remove all CREATE POLICY statements
+s/^CREATE\sPOLICY.*//mgx
+# Replace REFRESH with DROP for materialized views
+s/^REFRESH\s(MATERIALIZED\sVIEW)/DROP $1/mgx
diff --git a/src/bin/pg_upgrade/t/002_pg_upgrade.pl b/src/bin/pg_upgrade/t/002_pg_upgrade.pl
index 4cc1469306..e094fd08c3 100644
--- a/src/bin/pg_upgrade/t/002_pg_upgrade.pl
+++ b/src/bin/pg_upgrade/t/002_pg_upgrade.pl
@@ -45,6 +45,30 @@ sub filter_dump
# Remove empty lines.
$dump_contents =~ s/^\n//mgx;
+ if (defined($ENV{filter}))
+ {
+ # Use the external filter
+ my $ext_filter_file = $ENV{filter};
+ die "no file with external filter found!" unless -e $ext_filter_file;
+
+ open my $ext_filter_handle, '<', $ext_filter_file;
+ chomp(my @ext_filter_lines = <$ext_filter_handle>);
+ close $ext_filter_handle;
+
+ foreach (@ext_filter_lines)
+ {
+ # omit lines with comments
+ if (substr($_, 0, 1) eq '#')
+ {
+ next;
+ }
+
+ # apply lines with filters
+ my $filter = "\$dump_contents =~ $_";
+ eval $filter;
+ }
+ }
+
my $dump_file_filtered = "${dump_file}_filtered";
open(my $dh, '>', $dump_file_filtered)
|| die "opening $dump_file_filtered";
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [BUG] pg_upgrade test fails from older versions.
2022-12-19 00:50 [BUG] pg_upgrade test fails from older versions. Anton A. Melnikov <aamelnikov@inbox.ru>
2022-12-19 01:56 ` Re: [BUG] pg_upgrade test fails from older versions. Tom Lane <tgl@sss.pgh.pa.us>
2022-12-19 03:16 ` Re: [BUG] pg_upgrade test fails from older versions. Michael Paquier <michael@paquier.xyz>
2022-12-22 06:59 ` Re: [BUG] pg_upgrade test fails from older versions. Anton A. Melnikov <aamelnikov@inbox.ru>
2022-12-23 02:42 ` Re: [BUG] pg_upgrade test fails from older versions. Michael Paquier <michael@paquier.xyz>
2022-12-23 03:27 ` Re: [BUG] pg_upgrade test fails from older versions. Justin Pryzby <pryzby@telsasoft.com>
2022-12-23 08:51 ` Re: [BUG] pg_upgrade test fails from older versions. Michael Paquier <michael@paquier.xyz>
2022-12-23 09:43 ` Re: [BUG] pg_upgrade test fails from older versions. Anton A. Melnikov <aamelnikov@inbox.ru>
2022-12-26 02:52 ` Re: [BUG] pg_upgrade test fails from older versions. Michael Paquier <michael@paquier.xyz>
2022-12-26 06:22 ` Re: [BUG] pg_upgrade test fails from older versions. Anton A. Melnikov <aamelnikov@inbox.ru>
@ 2022-12-27 05:44 ` Michael Paquier <michael@paquier.xyz>
2022-12-27 12:26 ` Re: [BUG] pg_upgrade test fails from older versions. Anton A. Melnikov <aamelnikov@inbox.ru>
0 siblings, 1 reply; 19+ messages in thread
From: Michael Paquier @ 2022-12-27 05:44 UTC (permalink / raw)
To: Anton A. Melnikov <aamelnikov@inbox.ru>; +Cc: Justin Pryzby <pryzby@telsasoft.com>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-hackers@lists.postgresql.org
On Mon, Dec 26, 2022 at 09:22:08AM +0300, Anton A. Melnikov wrote:
> Made a separate patch for it: v3-0001-Fix-dumps-filtering.patch
Well, the thing about this part is that is it is not needed: the same
can be achieved with 0002 in place.
> Yes, indeed. It will be really simpler.
> Made it in the v3-0002-Add-external-dumps-filtering.patch
I have fixed a few things in the patch, like switching the step
skipping comments with a regexp, adding one step to ignore empty
lines, applying a proper indentation and fixing comments here and
there (TESTING was incorrect, btw).
It is worth noting that perlcritic was complaining here, as eval is
getting used with a string. I have spent a few days looking at that,
and I really want a maximum of flexibility in the rules that can be
applied so I have put a "no critic" rule, which is fine by me as this
extra file is something owned by the user and it would apply only to
cross-version upgrades.
So it looks like we are now done here.. With all these pieces in
place in the tests, I don't see why it would not be possible to
automate the cross-version tests of pg_upgrade.
--
Michael
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [BUG] pg_upgrade test fails from older versions.
2022-12-19 00:50 [BUG] pg_upgrade test fails from older versions. Anton A. Melnikov <aamelnikov@inbox.ru>
2022-12-19 01:56 ` Re: [BUG] pg_upgrade test fails from older versions. Tom Lane <tgl@sss.pgh.pa.us>
2022-12-19 03:16 ` Re: [BUG] pg_upgrade test fails from older versions. Michael Paquier <michael@paquier.xyz>
2022-12-22 06:59 ` Re: [BUG] pg_upgrade test fails from older versions. Anton A. Melnikov <aamelnikov@inbox.ru>
2022-12-23 02:42 ` Re: [BUG] pg_upgrade test fails from older versions. Michael Paquier <michael@paquier.xyz>
2022-12-23 03:27 ` Re: [BUG] pg_upgrade test fails from older versions. Justin Pryzby <pryzby@telsasoft.com>
2022-12-23 08:51 ` Re: [BUG] pg_upgrade test fails from older versions. Michael Paquier <michael@paquier.xyz>
2022-12-23 09:43 ` Re: [BUG] pg_upgrade test fails from older versions. Anton A. Melnikov <aamelnikov@inbox.ru>
2022-12-26 02:52 ` Re: [BUG] pg_upgrade test fails from older versions. Michael Paquier <michael@paquier.xyz>
2022-12-26 06:22 ` Re: [BUG] pg_upgrade test fails from older versions. Anton A. Melnikov <aamelnikov@inbox.ru>
2022-12-27 05:44 ` Re: [BUG] pg_upgrade test fails from older versions. Michael Paquier <michael@paquier.xyz>
@ 2022-12-27 12:26 ` Anton A. Melnikov <aamelnikov@inbox.ru>
2022-12-27 13:50 ` Re: [BUG] pg_upgrade test fails from older versions. Michael Paquier <michael@paquier.xyz>
0 siblings, 1 reply; 19+ messages in thread
From: Anton A. Melnikov @ 2022-12-27 12:26 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; +Cc: Justin Pryzby <pryzby@telsasoft.com>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-hackers@lists.postgresql.org
Hello!
On 27.12.2022 08:44, Michael Paquier wrote:
>
> It is worth noting that perlcritic was complaining here, as eval is
> getting used with a string. I have spent a few days looking at that,
> and I really want a maximum of flexibility in the rules that can be
> applied so I have put a "no critic" rule, which is fine by me as this
> extra file is something owned by the user and it would apply only to
> cross-version upgrades.
I think it's a very smart decision. Thank you very match!
> So it looks like we are now done here.. With all these pieces in
> place in the tests, I don't see why it would not be possible to
> automate the cross-version tests of pg_upgrade.
I've checked the cross-upgrade test form 9.5+ to current master and
have found no problem with accuracy up to dumps filtering.
For cross-version tests automation one have to write additional
filtering rules in the external files.
I would like to try realize this, better in a separate thread.
If there are no other considerations could you close the corresponding
record on the January CF, please?
With the best wishes!
--
Anton A. Melnikov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [BUG] pg_upgrade test fails from older versions.
2022-12-19 00:50 [BUG] pg_upgrade test fails from older versions. Anton A. Melnikov <aamelnikov@inbox.ru>
2022-12-19 01:56 ` Re: [BUG] pg_upgrade test fails from older versions. Tom Lane <tgl@sss.pgh.pa.us>
2022-12-19 03:16 ` Re: [BUG] pg_upgrade test fails from older versions. Michael Paquier <michael@paquier.xyz>
2022-12-22 06:59 ` Re: [BUG] pg_upgrade test fails from older versions. Anton A. Melnikov <aamelnikov@inbox.ru>
2022-12-23 02:42 ` Re: [BUG] pg_upgrade test fails from older versions. Michael Paquier <michael@paquier.xyz>
2022-12-23 03:27 ` Re: [BUG] pg_upgrade test fails from older versions. Justin Pryzby <pryzby@telsasoft.com>
2022-12-23 08:51 ` Re: [BUG] pg_upgrade test fails from older versions. Michael Paquier <michael@paquier.xyz>
2022-12-23 09:43 ` Re: [BUG] pg_upgrade test fails from older versions. Anton A. Melnikov <aamelnikov@inbox.ru>
2022-12-26 02:52 ` Re: [BUG] pg_upgrade test fails from older versions. Michael Paquier <michael@paquier.xyz>
2022-12-26 06:22 ` Re: [BUG] pg_upgrade test fails from older versions. Anton A. Melnikov <aamelnikov@inbox.ru>
2022-12-27 05:44 ` Re: [BUG] pg_upgrade test fails from older versions. Michael Paquier <michael@paquier.xyz>
2022-12-27 12:26 ` Re: [BUG] pg_upgrade test fails from older versions. Anton A. Melnikov <aamelnikov@inbox.ru>
@ 2022-12-27 13:50 ` Michael Paquier <michael@paquier.xyz>
2022-12-27 13:55 ` Re: [BUG] pg_upgrade test fails from older versions. Anton A. Melnikov <aamelnikov@inbox.ru>
0 siblings, 1 reply; 19+ messages in thread
From: Michael Paquier @ 2022-12-27 13:50 UTC (permalink / raw)
To: Anton A. Melnikov <aamelnikov@inbox.ru>; +Cc: Justin Pryzby <pryzby@telsasoft.com>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-hackers@lists.postgresql.org
On Tue, Dec 27, 2022 at 03:26:10PM +0300, Anton A. Melnikov wrote:
> I would like to try realize this, better in a separate thread.
I don't think that this should be added into the tree, but if you have
per-version filtering rules, of course feel free to publish that to
the lists. I am sure that this could be helpful for others.
> If there are no other considerations could you close the corresponding
> record on the January CF, please?
Indeed, now marked as committed.
--
Michael
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [BUG] pg_upgrade test fails from older versions.
2022-12-19 00:50 [BUG] pg_upgrade test fails from older versions. Anton A. Melnikov <aamelnikov@inbox.ru>
2022-12-19 01:56 ` Re: [BUG] pg_upgrade test fails from older versions. Tom Lane <tgl@sss.pgh.pa.us>
2022-12-19 03:16 ` Re: [BUG] pg_upgrade test fails from older versions. Michael Paquier <michael@paquier.xyz>
2022-12-22 06:59 ` Re: [BUG] pg_upgrade test fails from older versions. Anton A. Melnikov <aamelnikov@inbox.ru>
2022-12-23 02:42 ` Re: [BUG] pg_upgrade test fails from older versions. Michael Paquier <michael@paquier.xyz>
2022-12-23 03:27 ` Re: [BUG] pg_upgrade test fails from older versions. Justin Pryzby <pryzby@telsasoft.com>
2022-12-23 08:51 ` Re: [BUG] pg_upgrade test fails from older versions. Michael Paquier <michael@paquier.xyz>
2022-12-23 09:43 ` Re: [BUG] pg_upgrade test fails from older versions. Anton A. Melnikov <aamelnikov@inbox.ru>
2022-12-26 02:52 ` Re: [BUG] pg_upgrade test fails from older versions. Michael Paquier <michael@paquier.xyz>
2022-12-26 06:22 ` Re: [BUG] pg_upgrade test fails from older versions. Anton A. Melnikov <aamelnikov@inbox.ru>
2022-12-27 05:44 ` Re: [BUG] pg_upgrade test fails from older versions. Michael Paquier <michael@paquier.xyz>
2022-12-27 12:26 ` Re: [BUG] pg_upgrade test fails from older versions. Anton A. Melnikov <aamelnikov@inbox.ru>
2022-12-27 13:50 ` Re: [BUG] pg_upgrade test fails from older versions. Michael Paquier <michael@paquier.xyz>
@ 2022-12-27 13:55 ` Anton A. Melnikov <aamelnikov@inbox.ru>
0 siblings, 0 replies; 19+ messages in thread
From: Anton A. Melnikov @ 2022-12-27 13:55 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; +Cc: Justin Pryzby <pryzby@telsasoft.com>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-hackers@lists.postgresql.org
On 27.12.2022 16:50, Michael Paquier wrote:
>> If there are no other considerations could you close the corresponding
>> record on the January CF, please?
>
> Indeed, now marked as committed.
-
Thanks a lot!
Merry Christmas!
--
Anton A. Melnikov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [BUG] pg_upgrade test fails from older versions.
2022-12-19 00:50 [BUG] pg_upgrade test fails from older versions. Anton A. Melnikov <aamelnikov@inbox.ru>
2022-12-19 01:56 ` Re: [BUG] pg_upgrade test fails from older versions. Tom Lane <tgl@sss.pgh.pa.us>
2022-12-19 03:16 ` Re: [BUG] pg_upgrade test fails from older versions. Michael Paquier <michael@paquier.xyz>
2022-12-22 06:59 ` Re: [BUG] pg_upgrade test fails from older versions. Anton A. Melnikov <aamelnikov@inbox.ru>
2022-12-23 02:42 ` Re: [BUG] pg_upgrade test fails from older versions. Michael Paquier <michael@paquier.xyz>
2022-12-23 03:27 ` Re: [BUG] pg_upgrade test fails from older versions. Justin Pryzby <pryzby@telsasoft.com>
2022-12-23 08:51 ` Re: [BUG] pg_upgrade test fails from older versions. Michael Paquier <michael@paquier.xyz>
@ 2022-12-23 16:39 ` Justin Pryzby <pryzby@telsasoft.com>
2022-12-24 00:55 ` Re: [BUG] pg_upgrade test fails from older versions. Michael Paquier <michael@paquier.xyz>
2022-12-25 23:02 ` Re: [BUG] pg_upgrade test fails from older versions. Michael Paquier <michael@paquier.xyz>
1 sibling, 2 replies; 19+ messages in thread
From: Justin Pryzby @ 2022-12-23 16:39 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; +Cc: Anton A. Melnikov <aamelnikov@inbox.ru>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-hackers@lists.postgresql.org
On Fri, Dec 23, 2022 at 05:51:28PM +0900, Michael Paquier wrote:
> On Thu, Dec 22, 2022 at 09:27:24PM -0600, Justin Pryzby wrote:
> > This would do a single seqscan:
> > SELECT format('ALTER TABLE %I ALTER COLUMN %I TYPE TEXT',
> > attrelid::regclass, attname) FROM pg_attribute WHERE
> > atttypid='aclitem'::regtype; -- AND ...
> > \gexec
>
> FWIW, I find the use of a FOR loop with a DO block much cleaner to
> follow in this context, so something like the attached would be able
> to group the two queries and address your point on O(N^2). Do you
> like that?
LGTM. Thanks.
--
Justin
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [BUG] pg_upgrade test fails from older versions.
2022-12-19 00:50 [BUG] pg_upgrade test fails from older versions. Anton A. Melnikov <aamelnikov@inbox.ru>
2022-12-19 01:56 ` Re: [BUG] pg_upgrade test fails from older versions. Tom Lane <tgl@sss.pgh.pa.us>
2022-12-19 03:16 ` Re: [BUG] pg_upgrade test fails from older versions. Michael Paquier <michael@paquier.xyz>
2022-12-22 06:59 ` Re: [BUG] pg_upgrade test fails from older versions. Anton A. Melnikov <aamelnikov@inbox.ru>
2022-12-23 02:42 ` Re: [BUG] pg_upgrade test fails from older versions. Michael Paquier <michael@paquier.xyz>
2022-12-23 03:27 ` Re: [BUG] pg_upgrade test fails from older versions. Justin Pryzby <pryzby@telsasoft.com>
2022-12-23 08:51 ` Re: [BUG] pg_upgrade test fails from older versions. Michael Paquier <michael@paquier.xyz>
2022-12-23 16:39 ` Re: [BUG] pg_upgrade test fails from older versions. Justin Pryzby <pryzby@telsasoft.com>
@ 2022-12-24 00:55 ` Michael Paquier <michael@paquier.xyz>
1 sibling, 0 replies; 19+ messages in thread
From: Michael Paquier @ 2022-12-24 00:55 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; +Cc: Anton A. Melnikov <aamelnikov@inbox.ru>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-hackers@lists.postgresql.org
On Fri, Dec 23, 2022 at 10:39:25AM -0600, Justin Pryzby wrote:
> On Fri, Dec 23, 2022 at 05:51:28PM +0900, Michael Paquier wrote:
>> FWIW, I find the use of a FOR loop with a DO block much cleaner to
>> follow in this context, so something like the attached would be able
>> to group the two queries and address your point on O(N^2). Do you
>> like that?
>
> LGTM. Thanks.
I am a bit busy for the next few days, but I may be able to get that
done next Monday.
--
Michael
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [BUG] pg_upgrade test fails from older versions.
2022-12-19 00:50 [BUG] pg_upgrade test fails from older versions. Anton A. Melnikov <aamelnikov@inbox.ru>
2022-12-19 01:56 ` Re: [BUG] pg_upgrade test fails from older versions. Tom Lane <tgl@sss.pgh.pa.us>
2022-12-19 03:16 ` Re: [BUG] pg_upgrade test fails from older versions. Michael Paquier <michael@paquier.xyz>
2022-12-22 06:59 ` Re: [BUG] pg_upgrade test fails from older versions. Anton A. Melnikov <aamelnikov@inbox.ru>
2022-12-23 02:42 ` Re: [BUG] pg_upgrade test fails from older versions. Michael Paquier <michael@paquier.xyz>
2022-12-23 03:27 ` Re: [BUG] pg_upgrade test fails from older versions. Justin Pryzby <pryzby@telsasoft.com>
2022-12-23 08:51 ` Re: [BUG] pg_upgrade test fails from older versions. Michael Paquier <michael@paquier.xyz>
2022-12-23 16:39 ` Re: [BUG] pg_upgrade test fails from older versions. Justin Pryzby <pryzby@telsasoft.com>
@ 2022-12-25 23:02 ` Michael Paquier <michael@paquier.xyz>
1 sibling, 0 replies; 19+ messages in thread
From: Michael Paquier @ 2022-12-25 23:02 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; +Cc: Anton A. Melnikov <aamelnikov@inbox.ru>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-hackers@lists.postgresql.org
On Fri, Dec 23, 2022 at 10:39:25AM -0600, Justin Pryzby wrote:
> LGTM. Thanks.
Done as of d3c0cc4.
--
Michael
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [BUG] pg_upgrade test fails from older versions.
2022-12-19 00:50 [BUG] pg_upgrade test fails from older versions. Anton A. Melnikov <aamelnikov@inbox.ru>
2022-12-19 01:56 ` Re: [BUG] pg_upgrade test fails from older versions. Tom Lane <tgl@sss.pgh.pa.us>
2022-12-19 03:16 ` Re: [BUG] pg_upgrade test fails from older versions. Michael Paquier <michael@paquier.xyz>
2022-12-22 06:59 ` Re: [BUG] pg_upgrade test fails from older versions. Anton A. Melnikov <aamelnikov@inbox.ru>
2022-12-23 02:42 ` Re: [BUG] pg_upgrade test fails from older versions. Michael Paquier <michael@paquier.xyz>
2022-12-23 03:27 ` Re: [BUG] pg_upgrade test fails from older versions. Justin Pryzby <pryzby@telsasoft.com>
@ 2022-12-23 09:17 ` Anton A. Melnikov <aamelnikov@inbox.ru>
1 sibling, 0 replies; 19+ messages in thread
From: Anton A. Melnikov @ 2022-12-23 09:17 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; Michael Paquier <michael@paquier.xyz>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-hackers@lists.postgresql.org
Hello!
On 23.12.2022 06:27, Justin Pryzby wrote:
>
> This would do a single seqscan:
> SELECT format('ALTER TABLE %I ALTER COLUMN %I TYPE TEXT', attrelid::regclass, attname) FROM pg_attribute WHERE atttypid='aclitem'::regtype; -- AND ...
> \gexec
>
Touched a bit on how long it takes to execute different types of queries on my PC.
At each measurement, the server restarted with a freshly copied regression database.
1)
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;
$$;
2)
DO $$
DECLARE
rec text;
col text;
BEGIN
FOR rec in
SELECT oid::regclass::text
FROM pg_class
WHERE relname !~ '^pg_'
AND relkind IN ('r')
ORDER BY 1
LOOP
FOR col in SELECT attname FROM pg_attribute
WHERE attrelid::regclass::text = rec
AND atttypid = 'aclitem'::regtype
LOOP
EXECUTE 'ALTER TABLE ' || quote_ident(rec) || ' ALTER COLUMN ' ||
quote_ident(col) || ' SET DATA TYPE text';
END LOOP;
END LOOP;
END; $$;
3)
SELECT format('ALTER TABLE %I ALTER COLUMN %I TYPE TEXT', attrelid::regclass, attname) FROM pg_attribute WHERE atttypid='aclitem'::regtype;
\gexec
4) The same as 3) but in the DO block
DO $$
DECLARE
change_aclitem_type TEXT;
BEGIN
FOR change_aclitem_type IN
SELECT 'ALTER TABLE ' || attrelid::regclass || ' ALTER COLUMN ' ||
attname || ' TYPE TEXT;'
AS change_aclitem_type
FROM pg_attribute
WHERE atttypid = 'aclitem'::regtype
LOOP
EXECUTE change_aclitem_type;
END LOOP;
END;
$$;
Average execution time for three times:
_____________________________________
|N of query: | 1 | 2 | 3 | 4 |
|____________________________________
|Avg time, ms: | 58 | 1076 | 51 | 33 |
|____________________________________
Raw results in timing.txt
Best wishes,
--
Anton A. Melnikov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
Attachments:
timing.txt (text/plain, 537B) download
1) Time: 53,699 ms
Time: 60,146 ms
Time: 60,594 ms
Avg: 58,1 ms
2) Time: 1020,832 ms
Time: 1061,554 ms (00:01,062)
Time: 1148,029 ms (00:01,148)
Avg: 1076 ms
3) Time: 20,972 ms
regression=# \gexec
ALTER TABLE
Time: 12,601 ms
Time: 3,106 ms
sum = 36,67
Time: 22,087 ms
regression=# \gexec
ALTER TABLE
Time: 40,768 ms
Time: 3,154 ms
sum = 66,01
Time: 13,865 ms
regression=# \gexec
ALTER TABLE
Time: 34,619 ms
Time: 3,063 ms
sum = 51,55
Avg: 51,4 ms
4) Time: 25,518 ms
ime: 35,746 ms
Time: 39,232 ms
Avg: 33,4 ms
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [BUG] pg_upgrade test fails from older versions.
2022-12-19 00:50 [BUG] pg_upgrade test fails from older versions. Anton A. Melnikov <aamelnikov@inbox.ru>
@ 2022-12-19 03:10 ` Michael Paquier <michael@paquier.xyz>
1 sibling, 0 replies; 19+ messages in thread
From: Michael Paquier @ 2022-12-19 03:10 UTC (permalink / raw)
To: Anton A. Melnikov <aamelnikov@inbox.ru>; +Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
On Mon, Dec 19, 2022 at 03:50:19AM +0300, Anton A. Melnikov wrote:
> +-- 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
This is forgetting about materialized views, which is something that
pg_upgrade would also complain about when checking for relations with
aclitems. As far as I can see, the only place in the main regression
test suite where we have an aclitem attribute is tab_core_types for
HEAD and the stable branches, so it would be enough to do this
change. Anyway, wouldn't it be better to use the same conditions as
the WITH OIDS queries a few lines above, at least for consistency?
Note that check_for_data_types_usage() checks for tables, matviews and
indexes.
--
Michael
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 19+ messages in thread
end of thread, other threads:[~2022-12-27 13:55 UTC | newest]
Thread overview: 19+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-12-19 00:50 [BUG] pg_upgrade test fails from older versions. Anton A. Melnikov <aamelnikov@inbox.ru>
2022-12-19 01:56 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-12-19 03:16 ` Michael Paquier <michael@paquier.xyz>
2022-12-22 06:59 ` Anton A. Melnikov <aamelnikov@inbox.ru>
2022-12-23 02:42 ` Michael Paquier <michael@paquier.xyz>
2022-12-23 03:27 ` Justin Pryzby <pryzby@telsasoft.com>
2022-12-23 08:51 ` Michael Paquier <michael@paquier.xyz>
2022-12-23 09:43 ` Anton A. Melnikov <aamelnikov@inbox.ru>
2022-12-26 02:52 ` Michael Paquier <michael@paquier.xyz>
2022-12-26 06:22 ` Anton A. Melnikov <aamelnikov@inbox.ru>
2022-12-27 05:44 ` Michael Paquier <michael@paquier.xyz>
2022-12-27 12:26 ` Anton A. Melnikov <aamelnikov@inbox.ru>
2022-12-27 13:50 ` Michael Paquier <michael@paquier.xyz>
2022-12-27 13:55 ` Anton A. Melnikov <aamelnikov@inbox.ru>
2022-12-23 16:39 ` Justin Pryzby <pryzby@telsasoft.com>
2022-12-24 00:55 ` Michael Paquier <michael@paquier.xyz>
2022-12-25 23:02 ` Michael Paquier <michael@paquier.xyz>
2022-12-23 09:17 ` Anton A. Melnikov <aamelnikov@inbox.ru>
2022-12-19 03:10 ` Michael Paquier <michael@paquier.xyz>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox