From: Justin Pryzby <pryzby@telsasoft.com>
To: Michael Paquier <michael@paquier.xyz>
Cc: Jacob Champion <pchampion@vmware.com>
Cc: tgl@sss.pgh.pa.us
Cc: peter.eisentraut@enterprisedb.com
Cc: pgsql-hackers@lists.postgresql.org
Cc: buschmann@nidsa.net
Cc: andrew@dunslane.net
Cc: noah@leadboat.com
Cc: tomas.vondra@2ndquadrant.com
Cc: bruce@momjian.us
Cc: andres@anarazel.de
Subject: Re: pg_upgrade test for binary compatibility of core data types
Date: Wed, 17 Nov 2021 22:47:28 -0600
Message-ID: <20211118044728.GI17618@telsasoft.com> (raw)
In-Reply-To: <YZSov33oDwW44uMs@paquier.xyz>
References: <66753439-83ae-7be5-6e41-9537a6041a39@dunslane.net>
<d9f14840-9f6e-183d-f43a-9f9b9e1b6d19@enterprisedb.com>
<1575064.1615060903@sss.pgh.pa.us>
<20210430183348.GQ27406@telsasoft.com>
<a31d593da98da15fc2302a16b3f3b32e491d3e49.camel@vmware.com>
<20210912005116.GF26465@telsasoft.com>
<YVa/se5gxr1PsXDy@paquier.xyz>
<YWPNxDWFia+V4Y4g@paquier.xyz>
<20211107192200.GK17618@telsasoft.com>
<YZSov33oDwW44uMs@paquier.xyz>
On Wed, Nov 17, 2021 at 04:01:19PM +0900, Michael Paquier wrote:
> On Sun, Nov 07, 2021 at 01:22:00PM -0600, Justin Pryzby wrote:
> > That may be good enough for test.sh, but if the kludges were moved to a .sql
> > script which was also run by the buildfarm (in stead of its hardcoded kludges), then
> > it might be necessary to handle the additional stuff my patch did, like:
> >
> > + DROP TRANSFORM FOR integer LANGUAGE sql CASCADE;"
> > + DROP FUNCTION boxarea(box);"
> > + DROP FUNCTION funny_dup17();"
>
> These apply for an old version <= v10.
>
> > + DROP TABLE abstime_tbl;"
> > + DROP TABLE reltime_tbl;"
> > + DROP TABLE tinterval_tbl;"
>
> old version <= 9.3.
>
> > + DROP AGGREGATE first_el_agg_any(anyelement);"
>
> Not sure about this one.
See 97f73a978fc1aca59c6ad765548ce0096d95a923
> These are on 9.4. It is worth noting that TestUpgradeXversion.pm
> recreates those objects. I'd agree to close the gap completely rather
> than just moving what test.sh does to wipe out a maximum client code
> for the buildfarm.
>>Or, maybe it's guaranteed that the animals all run latest version of old
>>branches, in which case I think some of the BF's existing logic could be
>>dropped, which would help to reconcile these two scripts:
>>
>> my $missing_funcs = q{drop function if exists public.boxarea(box);
>> drop function if exists public.funny_dup17();
>>..
>> $prstmt = join(';',
>> 'drop operator @#@ (NONE, bigint)',
>>..
>> 'drop aggregate if exists public.array_cat_accum(anyarray)',
>>
I'm not sure if everything the buildfarm does is needed anymore, or if any of
it could be removed now, rather than being implemented in test.sh.
boxarea, funny_dup - see also db3af9feb19f39827e916145f88fa5eca3130cb2
https://github.com/PGBuildFarm/client-code/commit/9ca42ac1783a8cf99c73b4f7c52bd05a6024669d
array_larger_accum/array_cat_accum - see also 97f73a978fc1aca59c6ad765548ce0096d95a923
https://github.com/PGBuildFarm/client-code/commit/a55c89869f30db894ab823df472e739cee2e8c91
@#@ 76f412ab310554acb970a0b73c8d1f37f35548c6 ??
https://github.com/PGBuildFarm/client-code/commit/b3fdb743d89dc91fcea47bd9651776c503f774ffhttps://github.com/PGBuildFarm/client-code/commit/b44e9390e2d8d904ff8cabd906a2d4b5c8bd300ahttps://github.com/PGBuildFarm/client-code/commit/3844503c8fde134f7cc29b3fb147d590b6d2fcc1
abstime:
https://github.com/PGBuildFarm/client-code/commit/f027d991d197036028ffa9070f4c9193076ed5ed
putenv
https://github.com/PGBuildFarm/client-code/commit/fa86d0b1bc7a8d7b9f15b1da8b8e43f4d3a08e2bAttachments:
[text/x-diff] v7-0001-Move-pg_upgrade-kludges-to-sql-script.patch (4.1K, ../20211118044728.GI17618@telsasoft.com/2-v7-0001-Move-pg_upgrade-kludges-to-sql-script.patch)
download | inline diff:
From 2e420708f28574e73a86f8eba185d89a52d46509 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryzbyj@telsasoft.com>
Date: Sat, 6 Mar 2021 18:35:26 -0600
Subject: [PATCH v7 1/2] Move pg_upgrade kludges to sql script
NOTE, "IF EXISTS" isn't necessary in fa66b6dee
---
src/bin/pg_upgrade/test-upgrade.sql | 52 +++++++++++++++++++++++++++++
src/bin/pg_upgrade/test.sh | 48 +-------------------------
2 files changed, 53 insertions(+), 47 deletions(-)
create mode 100644 src/bin/pg_upgrade/test-upgrade.sql
diff --git a/src/bin/pg_upgrade/test-upgrade.sql b/src/bin/pg_upgrade/test-upgrade.sqlnew file mode 100644index 0000000000..5d74232c2b--- /dev/null+++ b/src/bin/pg_upgrade/test-upgrade.sql@@ -0,0 +1,52 @@+-- This file has a bunch of kludges needed for testing upgrades across major versions+-- It supports testing the most recent version of an old release (not any arbitrary minor version).++SELECT+ ver <= 804 AS oldpgversion_le84,+ ver < 1000 AS oldpgversion_lt10,+ ver < 1200 AS oldpgversion_lt12,+ ver < 1400 AS oldpgversion_lt14+ FROM (SELECT current_setting('server_version_num')::int/100 AS ver) AS v;+\gset++\if :oldpgversion_le84+DROP FUNCTION public.myfunc(integer);+\endif++\if :oldpgversion_lt10+-- last in 9.6 -- commit 5ded4bd21+DROP FUNCTION public.oldstyle_length(integer, text);+\endif++\if :oldpgversion_lt14+-- last in v13 commit 7ca37fb04+DROP FUNCTION IF EXISTS public.putenv(text);++-- last in v13 commit 76f412ab3+-- public.!=- This one is only needed for v11+ ??+-- Note, until v10, operators could only be dropped one at a time+DROP OPERATOR public.#@# (pg_catalog.int8, NONE);+DROP OPERATOR public.#%# (pg_catalog.int8, NONE);+DROP OPERATOR public.!=- (pg_catalog.int8, NONE);+DROP OPERATOR public.#@%# (pg_catalog.int8, NONE);+\endif++\if :oldpgversion_lt12+-- WITH OIDS is not supported anymore in v12, so remove support+-- for any relations marked as such.+DO $stmt$+ DECLARE+ rec text;+ BEGIN+ FOR rec in+ SELECT oid::regclass::text+ FROM pg_class+ WHERE relname !~ '^pg_'+ AND relhasoids+ AND relkind in ('r','m')+ ORDER BY 1+ LOOP+ execute 'ALTER TABLE ' || rec || ' SET WITHOUT OIDS';+ END LOOP;+ END; $stmt$;+\endifdiff --git a/src/bin/pg_upgrade/test.sh b/src/bin/pg_upgrade/test.shindex 8593488907..46a1ebb4ab 100644--- a/src/bin/pg_upgrade/test.sh+++ b/src/bin/pg_upgrade/test.sh@@ -181,53 +181,7 @@ if "$MAKE" -C "$oldsrc" installcheck-parallel; then
# Before dumping, tweak the database of the old instance depending
# on its version.
if [ "$newsrc" != "$oldsrc" ]; then
- fix_sql=""- # Get rid of objects not feasible in later versions- case $oldpgversion in- 804??)- fix_sql="DROP FUNCTION public.myfunc(integer);"- ;;- esac-- # Last appeared in v9.6- if [ $oldpgversion -lt 100000 ]; then- fix_sql="$fix_sql- DROP FUNCTION IF EXISTS- public.oldstyle_length(integer, text);"- fi- # Last appeared in v13- if [ $oldpgversion -lt 140000 ]; then- fix_sql="$fix_sql- DROP FUNCTION IF EXISTS- public.putenv(text); -- last in v13- DROP OPERATOR IF EXISTS -- last in v13- public.#@# (pg_catalog.int8, NONE),- public.#%# (pg_catalog.int8, NONE),- public.!=- (pg_catalog.int8, NONE),- public.#@%# (pg_catalog.int8, NONE);"- fi- psql -X -d regression -c "$fix_sql;" || psql_fix_sql_status=$?-- # WITH OIDS is not supported anymore in v12, so remove support- # for any relations marked as such.- if [ $oldpgversion -lt 120000 ]; then- fix_sql="DO \$stmt\$- DECLARE- rec text;- BEGIN- FOR rec in- SELECT oid::regclass::text- FROM pg_class- WHERE relname !~ '^pg_'- AND relhasoids- AND relkind in ('r','m')- ORDER BY 1- LOOP- execute 'ALTER TABLE ' || rec || ' SET WITHOUT OIDS';- END LOOP;- END; \$stmt\$;"- psql -X -d regression -c "$fix_sql;" || psql_fix_sql_status=$?- fi+ psql -X -d regression -f "test-upgrade.sql" || psql_fix_sql_status=$?
# Handling of --extra-float-digits gets messy after v12.
# Note that this changes the dumps from the old and new
--
2.17.0
[text/x-diff] v7-0002-wip-support-pg_upgrade-from-older-versions.patch (2.0K, ../20211118044728.GI17618@telsasoft.com/3-v7-0002-wip-support-pg_upgrade-from-older-versions.patch)
download | inline diff:
From 79242aed5b38f7498a089c6cd972ca24f8e357d1 Mon Sep 17 00:00:00 2001
From: Justin Pryzby <pryzbyj@telsasoft.com>
Date: Wed, 17 Nov 2021 11:27:20 -0600
Subject: [PATCH v7 2/2] wip: support pg_upgrade from older versions
---
src/bin/pg_upgrade/test-upgrade.sql | 36 +++++++++++++++++++++++++++++
1 file changed, 36 insertions(+)
diff --git a/src/bin/pg_upgrade/test-upgrade.sql b/src/bin/pg_upgrade/test-upgrade.sqlindex 5d74232c2b..1d0a840ec4 100644--- a/src/bin/pg_upgrade/test-upgrade.sql+++ b/src/bin/pg_upgrade/test-upgrade.sql@@ -3,6 +3,9 @@
SELECT
ver <= 804 AS oldpgversion_le84,
+ ver >= 905 AND ver <= 1300 AS oldpgversion_95_13,+ ver >= 906 AND ver <= 1300 AS oldpgversion_96_13,+ ver >= 906 AND ver <= 1000 AS oldpgversion_96_10,
ver < 1000 AS oldpgversion_lt10,
ver < 1200 AS oldpgversion_lt12,
ver < 1400 AS oldpgversion_lt14
@@ -31,9 +34,42 @@ DROP OPERATOR public.!=- (pg_catalog.int8, NONE);
DROP OPERATOR public.#@%# (pg_catalog.int8, NONE);
\endif
+\if :oldpgversion_ge10+-- commit 068503c76511cdb0080bab689662a20e86b9c845+DROP TRANSFORM FOR integer LANGUAGE sql CASCADE;+\endif++\if :oldpgversion_96_10+-- commit db3af9feb19f39827e916145f88fa5eca3130cb2+DROP FUNCTION boxarea(box);+DROP FUNCTION funny_dup17();++-- commit cda6a8d01d391eab45c4b3e0043a1b2b31072f5f+DROP TABLE abstime_tbl;+DROP TABLE reltime_tbl;+DROP TABLE tinterval_tbl;+\endif++\if :oldpgversion_96_13+-- Various things removed for v14+-- commit 9e38c2bb5 and 97f73a978+DROP AGGREGATE first_el_agg_any(anyelement);+\endif++\if :oldpgversion_95_13+-- commit 9e38c2bb5 and 97f73a978+-- DROP AGGREGATE array_larger_accum(anyarray);+DROP AGGREGATE array_cat_accum(anyarray);++-- commit 76f412ab3+-- DROP OPERATOR @#@(bigint,NONE);+DROP OPERATOR @#@(NONE,bigint);+\endif+
\if :oldpgversion_lt12
-- WITH OIDS is not supported anymore in v12, so remove support
-- for any relations marked as such.
+-- commit 578b22971: OIDS removed in v12
DO $stmt$
DECLARE
rec text;
--
2.17.0
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: pryzby@telsasoft.com, michael@paquier.xyz, pchampion@vmware.com, tgl@sss.pgh.pa.us, peter.eisentraut@enterprisedb.com, pgsql-hackers@lists.postgresql.org, buschmann@nidsa.net, andrew@dunslane.net, noah@leadboat.com, tomas.vondra@2ndquadrant.com, bruce@momjian.us, andres@anarazel.de
Subject: Re: pg_upgrade test for binary compatibility of core data types
In-Reply-To: <20211118044728.GI17618@telsasoft.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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