public inbox for [email protected]  

help / color / mirror / Atom feed

[PATCH v4 1/3] WIP: pg_upgrade/test.sh: changes needed to allow testing upgrade to v14dev from v9.5-v13
31+ messages / 3 participants
[nested] [flat]

* [PATCH v4 1/3] WIP: pg_upgrade/test.sh: changes needed to allow testing upgrade to v14dev from v9.5-v13
@ 2020-12-06 04:31  Justin Pryzby <[email protected]>
  0 siblings, 0 replies; 31+ messages in thread

From: Justin Pryzby @ 2020-12-06 04:31 UTC (permalink / raw)

---
 src/bin/pg_upgrade/test.sh | 93 +++++++++++++++++++++++++++++++++++---
 1 file changed, 86 insertions(+), 7 deletions(-)

diff --git a/src/bin/pg_upgrade/test.sh b/src/bin/pg_upgrade/test.sh
index ca923ba01b..b36fca4233 100644
--- a/src/bin/pg_upgrade/test.sh
+++ b/src/bin/pg_upgrade/test.sh
@@ -177,18 +177,97 @@ if "$MAKE" -C "$oldsrc" installcheck-parallel; then
 		esac
 		fix_sql="$fix_sql
 				 DROP FUNCTION IF EXISTS
-					public.oldstyle_length(integer, text);	-- last in 9.6
+					public.oldstyle_length(integer, text);"	# last in 9.6 -- commit 5ded4bd21
+		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.putenv(text);"	# last in v13
+		# 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
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
+					public.#@# (pg_catalog.int8, NONE);"
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
+					public.#%# (pg_catalog.int8, NONE);"
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
+					public.!=- (pg_catalog.int8, NONE);"
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
 					public.#@%# (pg_catalog.int8, NONE);"
+
+		# commit 068503c76511cdb0080bab689662a20e86b9c845
+		case $oldpgversion in
+			10????)
+				fix_sql="$fix_sql
+					DROP TRANSFORM FOR integer LANGUAGE sql CASCADE;"
+				;;
+		esac
+
+		# commit db3af9feb19f39827e916145f88fa5eca3130cb2
+		case $oldpgversion in
+			10????)
+				fix_sql="$fix_sql
+					DROP FUNCTION boxarea(box);"
+				fix_sql="$fix_sql
+					DROP FUNCTION funny_dup17();"
+				;;
+		esac
+
+		# commit cda6a8d01d391eab45c4b3e0043a1b2b31072f5f
+		case $oldpgversion in
+			10????)
+				fix_sql="$fix_sql
+					DROP TABLE abstime_tbl;"
+				fix_sql="$fix_sql
+					DROP TABLE reltime_tbl;"
+				fix_sql="$fix_sql
+					DROP TABLE tinterval_tbl;"
+				;;
+		esac
+
+		# Various things removed for v14
+		case $oldpgversion in
+			906??|10????|11????|12????|13????)
+				fix_sql="$fix_sql
+					DROP AGGREGATE first_el_agg_any(anyelement);"
+				;;
+		esac
+		case $oldpgversion in
+			90[56]??|10????|11????|12????|13????)
+				# commit 9e38c2bb5 and 97f73a978
+				# fix_sql="$fix_sql DROP AGGREGATE array_larger_accum(anyarray);"
+				fix_sql="$fix_sql
+					DROP AGGREGATE array_cat_accum(anyarray);"
+
+				# commit 76f412ab3
+				#fix_sql="$fix_sql DROP OPERATOR @#@(bigint,NONE);"
+				fix_sql="$fix_sql
+					DROP OPERATOR @#@(NONE,bigint);"
+				;;
+		esac
+
+		# commit 578b22971: OIDS removed in v12
+		case $oldpgversion in
+			804??|9????|10????|11????)
+				fix_sql="$fix_sql
+					ALTER TABLE public.tenk1 SET WITHOUT OIDS;"
+				fix_sql="$fix_sql
+					ALTER TABLE public.tenk1 SET WITHOUT OIDS;"
+				#fix_sql="$fix_sql ALTER TABLE public.stud_emp SET WITHOUT OIDS;" # inherited
+				fix_sql="$fix_sql
+					ALTER TABLE public.emp SET WITHOUT OIDS;"
+				fix_sql="$fix_sql
+					ALTER TABLE public.tt7 SET WITHOUT OIDS;"
+				;;
+		esac
+
 		psql -X -d regression -c "$fix_sql;" || psql_fix_sql_status=$?
 	fi
 
-	pg_dumpall --no-sync -f "$temp_root"/dump1.sql || pg_dumpall1_status=$?
+	echo "fix_sql: $oldpgversion: $fix_sql" >&2
+	pg_dumpall --extra-float-digits=0 --no-sync -f "$temp_root"/dump1.sql || pg_dumpall1_status=$?
 
 	if [ "$newsrc" != "$oldsrc" ]; then
 		# update references to old source tree's regress.so etc
-- 
2.17.0


--kadn00tgSopKmJ1H
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
 filename="v4-0002-More-changes-needed-to-allow-upgrade-testing.patch"



^ permalink  raw  reply  [nested|flat] 31+ messages in thread

* [PATCH v4 1/3] WIP: pg_upgrade/test.sh: changes needed to allow testing upgrade to v14dev from v9.5-v13
@ 2020-12-06 04:31  Justin Pryzby <[email protected]>
  0 siblings, 0 replies; 31+ messages in thread

From: Justin Pryzby @ 2020-12-06 04:31 UTC (permalink / raw)

---
 src/bin/pg_upgrade/test.sh | 93 +++++++++++++++++++++++++++++++++++---
 1 file changed, 86 insertions(+), 7 deletions(-)

diff --git a/src/bin/pg_upgrade/test.sh b/src/bin/pg_upgrade/test.sh
index ca923ba01b..b36fca4233 100644
--- a/src/bin/pg_upgrade/test.sh
+++ b/src/bin/pg_upgrade/test.sh
@@ -177,18 +177,97 @@ if "$MAKE" -C "$oldsrc" installcheck-parallel; then
 		esac
 		fix_sql="$fix_sql
 				 DROP FUNCTION IF EXISTS
-					public.oldstyle_length(integer, text);	-- last in 9.6
+					public.oldstyle_length(integer, text);"	# last in 9.6 -- commit 5ded4bd21
+		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.putenv(text);"	# last in v13
+		# 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
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
+					public.#@# (pg_catalog.int8, NONE);"
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
+					public.#%# (pg_catalog.int8, NONE);"
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
+					public.!=- (pg_catalog.int8, NONE);"
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
 					public.#@%# (pg_catalog.int8, NONE);"
+
+		# commit 068503c76511cdb0080bab689662a20e86b9c845
+		case $oldpgversion in
+			10????)
+				fix_sql="$fix_sql
+					DROP TRANSFORM FOR integer LANGUAGE sql CASCADE;"
+				;;
+		esac
+
+		# commit db3af9feb19f39827e916145f88fa5eca3130cb2
+		case $oldpgversion in
+			10????)
+				fix_sql="$fix_sql
+					DROP FUNCTION boxarea(box);"
+				fix_sql="$fix_sql
+					DROP FUNCTION funny_dup17();"
+				;;
+		esac
+
+		# commit cda6a8d01d391eab45c4b3e0043a1b2b31072f5f
+		case $oldpgversion in
+			10????)
+				fix_sql="$fix_sql
+					DROP TABLE abstime_tbl;"
+				fix_sql="$fix_sql
+					DROP TABLE reltime_tbl;"
+				fix_sql="$fix_sql
+					DROP TABLE tinterval_tbl;"
+				;;
+		esac
+
+		# Various things removed for v14
+		case $oldpgversion in
+			906??|10????|11????|12????|13????)
+				fix_sql="$fix_sql
+					DROP AGGREGATE first_el_agg_any(anyelement);"
+				;;
+		esac
+		case $oldpgversion in
+			90[56]??|10????|11????|12????|13????)
+				# commit 9e38c2bb5 and 97f73a978
+				# fix_sql="$fix_sql DROP AGGREGATE array_larger_accum(anyarray);"
+				fix_sql="$fix_sql
+					DROP AGGREGATE array_cat_accum(anyarray);"
+
+				# commit 76f412ab3
+				#fix_sql="$fix_sql DROP OPERATOR @#@(bigint,NONE);"
+				fix_sql="$fix_sql
+					DROP OPERATOR @#@(NONE,bigint);"
+				;;
+		esac
+
+		# commit 578b22971: OIDS removed in v12
+		case $oldpgversion in
+			804??|9????|10????|11????)
+				fix_sql="$fix_sql
+					ALTER TABLE public.tenk1 SET WITHOUT OIDS;"
+				fix_sql="$fix_sql
+					ALTER TABLE public.tenk1 SET WITHOUT OIDS;"
+				#fix_sql="$fix_sql ALTER TABLE public.stud_emp SET WITHOUT OIDS;" # inherited
+				fix_sql="$fix_sql
+					ALTER TABLE public.emp SET WITHOUT OIDS;"
+				fix_sql="$fix_sql
+					ALTER TABLE public.tt7 SET WITHOUT OIDS;"
+				;;
+		esac
+
 		psql -X -d regression -c "$fix_sql;" || psql_fix_sql_status=$?
 	fi
 
-	pg_dumpall --no-sync -f "$temp_root"/dump1.sql || pg_dumpall1_status=$?
+	echo "fix_sql: $oldpgversion: $fix_sql" >&2
+	pg_dumpall --extra-float-digits=0 --no-sync -f "$temp_root"/dump1.sql || pg_dumpall1_status=$?
 
 	if [ "$newsrc" != "$oldsrc" ]; then
 		# update references to old source tree's regress.so etc
-- 
2.17.0


--kadn00tgSopKmJ1H
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
 filename="v4-0002-More-changes-needed-to-allow-upgrade-testing.patch"



^ permalink  raw  reply  [nested|flat] 31+ messages in thread

* [PATCH v4 1/3] WIP: pg_upgrade/test.sh: changes needed to allow testing upgrade to v14dev from v9.5-v13
@ 2020-12-06 04:31  Justin Pryzby <[email protected]>
  0 siblings, 0 replies; 31+ messages in thread

From: Justin Pryzby @ 2020-12-06 04:31 UTC (permalink / raw)

---
 src/bin/pg_upgrade/test.sh | 93 +++++++++++++++++++++++++++++++++++---
 1 file changed, 86 insertions(+), 7 deletions(-)

diff --git a/src/bin/pg_upgrade/test.sh b/src/bin/pg_upgrade/test.sh
index ca923ba01b..b36fca4233 100644
--- a/src/bin/pg_upgrade/test.sh
+++ b/src/bin/pg_upgrade/test.sh
@@ -177,18 +177,97 @@ if "$MAKE" -C "$oldsrc" installcheck-parallel; then
 		esac
 		fix_sql="$fix_sql
 				 DROP FUNCTION IF EXISTS
-					public.oldstyle_length(integer, text);	-- last in 9.6
+					public.oldstyle_length(integer, text);"	# last in 9.6 -- commit 5ded4bd21
+		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.putenv(text);"	# last in v13
+		# 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
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
+					public.#@# (pg_catalog.int8, NONE);"
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
+					public.#%# (pg_catalog.int8, NONE);"
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
+					public.!=- (pg_catalog.int8, NONE);"
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
 					public.#@%# (pg_catalog.int8, NONE);"
+
+		# commit 068503c76511cdb0080bab689662a20e86b9c845
+		case $oldpgversion in
+			10????)
+				fix_sql="$fix_sql
+					DROP TRANSFORM FOR integer LANGUAGE sql CASCADE;"
+				;;
+		esac
+
+		# commit db3af9feb19f39827e916145f88fa5eca3130cb2
+		case $oldpgversion in
+			10????)
+				fix_sql="$fix_sql
+					DROP FUNCTION boxarea(box);"
+				fix_sql="$fix_sql
+					DROP FUNCTION funny_dup17();"
+				;;
+		esac
+
+		# commit cda6a8d01d391eab45c4b3e0043a1b2b31072f5f
+		case $oldpgversion in
+			10????)
+				fix_sql="$fix_sql
+					DROP TABLE abstime_tbl;"
+				fix_sql="$fix_sql
+					DROP TABLE reltime_tbl;"
+				fix_sql="$fix_sql
+					DROP TABLE tinterval_tbl;"
+				;;
+		esac
+
+		# Various things removed for v14
+		case $oldpgversion in
+			906??|10????|11????|12????|13????)
+				fix_sql="$fix_sql
+					DROP AGGREGATE first_el_agg_any(anyelement);"
+				;;
+		esac
+		case $oldpgversion in
+			90[56]??|10????|11????|12????|13????)
+				# commit 9e38c2bb5 and 97f73a978
+				# fix_sql="$fix_sql DROP AGGREGATE array_larger_accum(anyarray);"
+				fix_sql="$fix_sql
+					DROP AGGREGATE array_cat_accum(anyarray);"
+
+				# commit 76f412ab3
+				#fix_sql="$fix_sql DROP OPERATOR @#@(bigint,NONE);"
+				fix_sql="$fix_sql
+					DROP OPERATOR @#@(NONE,bigint);"
+				;;
+		esac
+
+		# commit 578b22971: OIDS removed in v12
+		case $oldpgversion in
+			804??|9????|10????|11????)
+				fix_sql="$fix_sql
+					ALTER TABLE public.tenk1 SET WITHOUT OIDS;"
+				fix_sql="$fix_sql
+					ALTER TABLE public.tenk1 SET WITHOUT OIDS;"
+				#fix_sql="$fix_sql ALTER TABLE public.stud_emp SET WITHOUT OIDS;" # inherited
+				fix_sql="$fix_sql
+					ALTER TABLE public.emp SET WITHOUT OIDS;"
+				fix_sql="$fix_sql
+					ALTER TABLE public.tt7 SET WITHOUT OIDS;"
+				;;
+		esac
+
 		psql -X -d regression -c "$fix_sql;" || psql_fix_sql_status=$?
 	fi
 
-	pg_dumpall --no-sync -f "$temp_root"/dump1.sql || pg_dumpall1_status=$?
+	echo "fix_sql: $oldpgversion: $fix_sql" >&2
+	pg_dumpall --extra-float-digits=0 --no-sync -f "$temp_root"/dump1.sql || pg_dumpall1_status=$?
 
 	if [ "$newsrc" != "$oldsrc" ]; then
 		# update references to old source tree's regress.so etc
-- 
2.17.0


--kadn00tgSopKmJ1H
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
 filename="v4-0002-More-changes-needed-to-allow-upgrade-testing.patch"



^ permalink  raw  reply  [nested|flat] 31+ messages in thread

* [PATCH v4 1/3] WIP: pg_upgrade/test.sh: changes needed to allow testing upgrade to v14dev from v9.5-v13
@ 2020-12-06 04:31  Justin Pryzby <[email protected]>
  0 siblings, 0 replies; 31+ messages in thread

From: Justin Pryzby @ 2020-12-06 04:31 UTC (permalink / raw)

---
 src/bin/pg_upgrade/test.sh | 93 +++++++++++++++++++++++++++++++++++---
 1 file changed, 86 insertions(+), 7 deletions(-)

diff --git a/src/bin/pg_upgrade/test.sh b/src/bin/pg_upgrade/test.sh
index ca923ba01b..b36fca4233 100644
--- a/src/bin/pg_upgrade/test.sh
+++ b/src/bin/pg_upgrade/test.sh
@@ -177,18 +177,97 @@ if "$MAKE" -C "$oldsrc" installcheck-parallel; then
 		esac
 		fix_sql="$fix_sql
 				 DROP FUNCTION IF EXISTS
-					public.oldstyle_length(integer, text);	-- last in 9.6
+					public.oldstyle_length(integer, text);"	# last in 9.6 -- commit 5ded4bd21
+		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.putenv(text);"	# last in v13
+		# 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
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
+					public.#@# (pg_catalog.int8, NONE);"
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
+					public.#%# (pg_catalog.int8, NONE);"
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
+					public.!=- (pg_catalog.int8, NONE);"
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
 					public.#@%# (pg_catalog.int8, NONE);"
+
+		# commit 068503c76511cdb0080bab689662a20e86b9c845
+		case $oldpgversion in
+			10????)
+				fix_sql="$fix_sql
+					DROP TRANSFORM FOR integer LANGUAGE sql CASCADE;"
+				;;
+		esac
+
+		# commit db3af9feb19f39827e916145f88fa5eca3130cb2
+		case $oldpgversion in
+			10????)
+				fix_sql="$fix_sql
+					DROP FUNCTION boxarea(box);"
+				fix_sql="$fix_sql
+					DROP FUNCTION funny_dup17();"
+				;;
+		esac
+
+		# commit cda6a8d01d391eab45c4b3e0043a1b2b31072f5f
+		case $oldpgversion in
+			10????)
+				fix_sql="$fix_sql
+					DROP TABLE abstime_tbl;"
+				fix_sql="$fix_sql
+					DROP TABLE reltime_tbl;"
+				fix_sql="$fix_sql
+					DROP TABLE tinterval_tbl;"
+				;;
+		esac
+
+		# Various things removed for v14
+		case $oldpgversion in
+			906??|10????|11????|12????|13????)
+				fix_sql="$fix_sql
+					DROP AGGREGATE first_el_agg_any(anyelement);"
+				;;
+		esac
+		case $oldpgversion in
+			90[56]??|10????|11????|12????|13????)
+				# commit 9e38c2bb5 and 97f73a978
+				# fix_sql="$fix_sql DROP AGGREGATE array_larger_accum(anyarray);"
+				fix_sql="$fix_sql
+					DROP AGGREGATE array_cat_accum(anyarray);"
+
+				# commit 76f412ab3
+				#fix_sql="$fix_sql DROP OPERATOR @#@(bigint,NONE);"
+				fix_sql="$fix_sql
+					DROP OPERATOR @#@(NONE,bigint);"
+				;;
+		esac
+
+		# commit 578b22971: OIDS removed in v12
+		case $oldpgversion in
+			804??|9????|10????|11????)
+				fix_sql="$fix_sql
+					ALTER TABLE public.tenk1 SET WITHOUT OIDS;"
+				fix_sql="$fix_sql
+					ALTER TABLE public.tenk1 SET WITHOUT OIDS;"
+				#fix_sql="$fix_sql ALTER TABLE public.stud_emp SET WITHOUT OIDS;" # inherited
+				fix_sql="$fix_sql
+					ALTER TABLE public.emp SET WITHOUT OIDS;"
+				fix_sql="$fix_sql
+					ALTER TABLE public.tt7 SET WITHOUT OIDS;"
+				;;
+		esac
+
 		psql -X -d regression -c "$fix_sql;" || psql_fix_sql_status=$?
 	fi
 
-	pg_dumpall --no-sync -f "$temp_root"/dump1.sql || pg_dumpall1_status=$?
+	echo "fix_sql: $oldpgversion: $fix_sql" >&2
+	pg_dumpall --extra-float-digits=0 --no-sync -f "$temp_root"/dump1.sql || pg_dumpall1_status=$?
 
 	if [ "$newsrc" != "$oldsrc" ]; then
 		# update references to old source tree's regress.so etc
-- 
2.17.0


--kadn00tgSopKmJ1H
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
 filename="v4-0002-More-changes-needed-to-allow-upgrade-testing.patch"



^ permalink  raw  reply  [nested|flat] 31+ messages in thread

* [PATCH v4 1/3] WIP: pg_upgrade/test.sh: changes needed to allow testing upgrade to v14dev from v9.5-v13
@ 2020-12-06 04:31  Justin Pryzby <[email protected]>
  0 siblings, 0 replies; 31+ messages in thread

From: Justin Pryzby @ 2020-12-06 04:31 UTC (permalink / raw)

---
 src/bin/pg_upgrade/test.sh | 93 +++++++++++++++++++++++++++++++++++---
 1 file changed, 86 insertions(+), 7 deletions(-)

diff --git a/src/bin/pg_upgrade/test.sh b/src/bin/pg_upgrade/test.sh
index ca923ba01b..b36fca4233 100644
--- a/src/bin/pg_upgrade/test.sh
+++ b/src/bin/pg_upgrade/test.sh
@@ -177,18 +177,97 @@ if "$MAKE" -C "$oldsrc" installcheck-parallel; then
 		esac
 		fix_sql="$fix_sql
 				 DROP FUNCTION IF EXISTS
-					public.oldstyle_length(integer, text);	-- last in 9.6
+					public.oldstyle_length(integer, text);"	# last in 9.6 -- commit 5ded4bd21
+		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.putenv(text);"	# last in v13
+		# 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
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
+					public.#@# (pg_catalog.int8, NONE);"
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
+					public.#%# (pg_catalog.int8, NONE);"
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
+					public.!=- (pg_catalog.int8, NONE);"
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
 					public.#@%# (pg_catalog.int8, NONE);"
+
+		# commit 068503c76511cdb0080bab689662a20e86b9c845
+		case $oldpgversion in
+			10????)
+				fix_sql="$fix_sql
+					DROP TRANSFORM FOR integer LANGUAGE sql CASCADE;"
+				;;
+		esac
+
+		# commit db3af9feb19f39827e916145f88fa5eca3130cb2
+		case $oldpgversion in
+			10????)
+				fix_sql="$fix_sql
+					DROP FUNCTION boxarea(box);"
+				fix_sql="$fix_sql
+					DROP FUNCTION funny_dup17();"
+				;;
+		esac
+
+		# commit cda6a8d01d391eab45c4b3e0043a1b2b31072f5f
+		case $oldpgversion in
+			10????)
+				fix_sql="$fix_sql
+					DROP TABLE abstime_tbl;"
+				fix_sql="$fix_sql
+					DROP TABLE reltime_tbl;"
+				fix_sql="$fix_sql
+					DROP TABLE tinterval_tbl;"
+				;;
+		esac
+
+		# Various things removed for v14
+		case $oldpgversion in
+			906??|10????|11????|12????|13????)
+				fix_sql="$fix_sql
+					DROP AGGREGATE first_el_agg_any(anyelement);"
+				;;
+		esac
+		case $oldpgversion in
+			90[56]??|10????|11????|12????|13????)
+				# commit 9e38c2bb5 and 97f73a978
+				# fix_sql="$fix_sql DROP AGGREGATE array_larger_accum(anyarray);"
+				fix_sql="$fix_sql
+					DROP AGGREGATE array_cat_accum(anyarray);"
+
+				# commit 76f412ab3
+				#fix_sql="$fix_sql DROP OPERATOR @#@(bigint,NONE);"
+				fix_sql="$fix_sql
+					DROP OPERATOR @#@(NONE,bigint);"
+				;;
+		esac
+
+		# commit 578b22971: OIDS removed in v12
+		case $oldpgversion in
+			804??|9????|10????|11????)
+				fix_sql="$fix_sql
+					ALTER TABLE public.tenk1 SET WITHOUT OIDS;"
+				fix_sql="$fix_sql
+					ALTER TABLE public.tenk1 SET WITHOUT OIDS;"
+				#fix_sql="$fix_sql ALTER TABLE public.stud_emp SET WITHOUT OIDS;" # inherited
+				fix_sql="$fix_sql
+					ALTER TABLE public.emp SET WITHOUT OIDS;"
+				fix_sql="$fix_sql
+					ALTER TABLE public.tt7 SET WITHOUT OIDS;"
+				;;
+		esac
+
 		psql -X -d regression -c "$fix_sql;" || psql_fix_sql_status=$?
 	fi
 
-	pg_dumpall --no-sync -f "$temp_root"/dump1.sql || pg_dumpall1_status=$?
+	echo "fix_sql: $oldpgversion: $fix_sql" >&2
+	pg_dumpall --extra-float-digits=0 --no-sync -f "$temp_root"/dump1.sql || pg_dumpall1_status=$?
 
 	if [ "$newsrc" != "$oldsrc" ]; then
 		# update references to old source tree's regress.so etc
-- 
2.17.0


--kadn00tgSopKmJ1H
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
 filename="v4-0002-More-changes-needed-to-allow-upgrade-testing.patch"



^ permalink  raw  reply  [nested|flat] 31+ messages in thread

* [PATCH v4 1/3] WIP: pg_upgrade/test.sh: changes needed to allow testing upgrade to v14dev from v9.5-v13
@ 2020-12-06 04:31  Justin Pryzby <[email protected]>
  0 siblings, 0 replies; 31+ messages in thread

From: Justin Pryzby @ 2020-12-06 04:31 UTC (permalink / raw)

---
 src/bin/pg_upgrade/test.sh | 93 +++++++++++++++++++++++++++++++++++---
 1 file changed, 86 insertions(+), 7 deletions(-)

diff --git a/src/bin/pg_upgrade/test.sh b/src/bin/pg_upgrade/test.sh
index ca923ba01b..b36fca4233 100644
--- a/src/bin/pg_upgrade/test.sh
+++ b/src/bin/pg_upgrade/test.sh
@@ -177,18 +177,97 @@ if "$MAKE" -C "$oldsrc" installcheck-parallel; then
 		esac
 		fix_sql="$fix_sql
 				 DROP FUNCTION IF EXISTS
-					public.oldstyle_length(integer, text);	-- last in 9.6
+					public.oldstyle_length(integer, text);"	# last in 9.6 -- commit 5ded4bd21
+		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.putenv(text);"	# last in v13
+		# 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
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
+					public.#@# (pg_catalog.int8, NONE);"
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
+					public.#%# (pg_catalog.int8, NONE);"
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
+					public.!=- (pg_catalog.int8, NONE);"
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
 					public.#@%# (pg_catalog.int8, NONE);"
+
+		# commit 068503c76511cdb0080bab689662a20e86b9c845
+		case $oldpgversion in
+			10????)
+				fix_sql="$fix_sql
+					DROP TRANSFORM FOR integer LANGUAGE sql CASCADE;"
+				;;
+		esac
+
+		# commit db3af9feb19f39827e916145f88fa5eca3130cb2
+		case $oldpgversion in
+			10????)
+				fix_sql="$fix_sql
+					DROP FUNCTION boxarea(box);"
+				fix_sql="$fix_sql
+					DROP FUNCTION funny_dup17();"
+				;;
+		esac
+
+		# commit cda6a8d01d391eab45c4b3e0043a1b2b31072f5f
+		case $oldpgversion in
+			10????)
+				fix_sql="$fix_sql
+					DROP TABLE abstime_tbl;"
+				fix_sql="$fix_sql
+					DROP TABLE reltime_tbl;"
+				fix_sql="$fix_sql
+					DROP TABLE tinterval_tbl;"
+				;;
+		esac
+
+		# Various things removed for v14
+		case $oldpgversion in
+			906??|10????|11????|12????|13????)
+				fix_sql="$fix_sql
+					DROP AGGREGATE first_el_agg_any(anyelement);"
+				;;
+		esac
+		case $oldpgversion in
+			90[56]??|10????|11????|12????|13????)
+				# commit 9e38c2bb5 and 97f73a978
+				# fix_sql="$fix_sql DROP AGGREGATE array_larger_accum(anyarray);"
+				fix_sql="$fix_sql
+					DROP AGGREGATE array_cat_accum(anyarray);"
+
+				# commit 76f412ab3
+				#fix_sql="$fix_sql DROP OPERATOR @#@(bigint,NONE);"
+				fix_sql="$fix_sql
+					DROP OPERATOR @#@(NONE,bigint);"
+				;;
+		esac
+
+		# commit 578b22971: OIDS removed in v12
+		case $oldpgversion in
+			804??|9????|10????|11????)
+				fix_sql="$fix_sql
+					ALTER TABLE public.tenk1 SET WITHOUT OIDS;"
+				fix_sql="$fix_sql
+					ALTER TABLE public.tenk1 SET WITHOUT OIDS;"
+				#fix_sql="$fix_sql ALTER TABLE public.stud_emp SET WITHOUT OIDS;" # inherited
+				fix_sql="$fix_sql
+					ALTER TABLE public.emp SET WITHOUT OIDS;"
+				fix_sql="$fix_sql
+					ALTER TABLE public.tt7 SET WITHOUT OIDS;"
+				;;
+		esac
+
 		psql -X -d regression -c "$fix_sql;" || psql_fix_sql_status=$?
 	fi
 
-	pg_dumpall --no-sync -f "$temp_root"/dump1.sql || pg_dumpall1_status=$?
+	echo "fix_sql: $oldpgversion: $fix_sql" >&2
+	pg_dumpall --extra-float-digits=0 --no-sync -f "$temp_root"/dump1.sql || pg_dumpall1_status=$?
 
 	if [ "$newsrc" != "$oldsrc" ]; then
 		# update references to old source tree's regress.so etc
-- 
2.17.0


--kadn00tgSopKmJ1H
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
 filename="v4-0002-More-changes-needed-to-allow-upgrade-testing.patch"



^ permalink  raw  reply  [nested|flat] 31+ messages in thread

* [PATCH v5 1/4] WIP: pg_upgrade/test.sh: changes needed to allow testing upgrade to v14dev from v9.5-v13
@ 2020-12-06 04:31  Justin Pryzby <[email protected]>
  0 siblings, 0 replies; 31+ messages in thread

From: Justin Pryzby @ 2020-12-06 04:31 UTC (permalink / raw)

test like:
time make -C src/bin/pg_upgrade check oldsrc=`pwd`/11 oldbindir=`pwd`/11/tmp_install/usr/local/pgsql/bin
---
 src/bin/pg_upgrade/test.sh | 93 +++++++++++++++++++++++++++++++++++---
 1 file changed, 86 insertions(+), 7 deletions(-)

diff --git a/src/bin/pg_upgrade/test.sh b/src/bin/pg_upgrade/test.sh
index 1ba326decd..9288cfdda8 100644
--- a/src/bin/pg_upgrade/test.sh
+++ b/src/bin/pg_upgrade/test.sh
@@ -176,18 +176,97 @@ if "$MAKE" -C "$oldsrc" installcheck-parallel; then
 		esac
 		fix_sql="$fix_sql
 				 DROP FUNCTION IF EXISTS
-					public.oldstyle_length(integer, text);	-- last in 9.6
+					public.oldstyle_length(integer, text);"	# last in 9.6 -- commit 5ded4bd21
+		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.putenv(text);"	# last in v13
+		# 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
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
+					public.#@# (pg_catalog.int8, NONE);"
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
+					public.#%# (pg_catalog.int8, NONE);"
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
+					public.!=- (pg_catalog.int8, NONE);"
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
 					public.#@%# (pg_catalog.int8, NONE);"
+
+		# commit 068503c76511cdb0080bab689662a20e86b9c845
+		case $oldpgversion in
+			10????)
+				fix_sql="$fix_sql
+					DROP TRANSFORM FOR integer LANGUAGE sql CASCADE;"
+				;;
+		esac
+
+		# commit db3af9feb19f39827e916145f88fa5eca3130cb2
+		case $oldpgversion in
+			10????)
+				fix_sql="$fix_sql
+					DROP FUNCTION boxarea(box);"
+				fix_sql="$fix_sql
+					DROP FUNCTION funny_dup17();"
+				;;
+		esac
+
+		# commit cda6a8d01d391eab45c4b3e0043a1b2b31072f5f
+		case $oldpgversion in
+			10????)
+				fix_sql="$fix_sql
+					DROP TABLE abstime_tbl;"
+				fix_sql="$fix_sql
+					DROP TABLE reltime_tbl;"
+				fix_sql="$fix_sql
+					DROP TABLE tinterval_tbl;"
+				;;
+		esac
+
+		# Various things removed for v14
+		case $oldpgversion in
+			906??|10????|11????|12????|13????)
+				fix_sql="$fix_sql
+					DROP AGGREGATE first_el_agg_any(anyelement);"
+				;;
+		esac
+		case $oldpgversion in
+			90[56]??|10????|11????|12????|13????)
+				# commit 9e38c2bb5 and 97f73a978
+				# fix_sql="$fix_sql DROP AGGREGATE array_larger_accum(anyarray);"
+				fix_sql="$fix_sql
+					DROP AGGREGATE array_cat_accum(anyarray);"
+
+				# commit 76f412ab3
+				#fix_sql="$fix_sql DROP OPERATOR @#@(bigint,NONE);"
+				fix_sql="$fix_sql
+					DROP OPERATOR @#@(NONE,bigint);"
+				;;
+		esac
+
+		# commit 578b22971: OIDS removed in v12
+		case $oldpgversion in
+			804??|9????|10????|11????)
+				fix_sql="$fix_sql
+					ALTER TABLE public.tenk1 SET WITHOUT OIDS;"
+				fix_sql="$fix_sql
+					ALTER TABLE public.tenk1 SET WITHOUT OIDS;"
+				#fix_sql="$fix_sql ALTER TABLE public.stud_emp SET WITHOUT OIDS;" # inherited
+				fix_sql="$fix_sql
+					ALTER TABLE public.emp SET WITHOUT OIDS;"
+				fix_sql="$fix_sql
+					ALTER TABLE public.tt7 SET WITHOUT OIDS;"
+				;;
+		esac
+
 		psql -X -d regression -c "$fix_sql;" || psql_fix_sql_status=$?
 	fi
 
-	pg_dumpall --no-sync -f "$temp_root"/dump1.sql || pg_dumpall1_status=$?
+	echo "fix_sql: $oldpgversion: $fix_sql" >&2
+	pg_dumpall --extra-float-digits=0 --no-sync -f "$temp_root"/dump1.sql || pg_dumpall1_status=$?
 
 	if [ "$newsrc" != "$oldsrc" ]; then
 		# update references to old source tree's regress.so etc
-- 
2.17.0


--54u2kuW9sGWg/X+X
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
 filename="v5-0002-More-changes-needed-to-allow-upgrade-testing.patch"



^ permalink  raw  reply  [nested|flat] 31+ messages in thread

* [PATCH 1/2] WIP: pg_upgrade/test.sh: changes needed to allow testing upgrade from v11
@ 2020-12-06 04:31  Justin Pryzby <[email protected]>
  0 siblings, 0 replies; 31+ messages in thread

From: Justin Pryzby @ 2020-12-06 04:31 UTC (permalink / raw)

---
 src/bin/pg_upgrade/check.c |  2 +-
 src/bin/pg_upgrade/test.sh | 44 ++++++++++++++++++++++++++++++++------
 2 files changed, 39 insertions(+), 7 deletions(-)

diff --git a/src/bin/pg_upgrade/check.c b/src/bin/pg_upgrade/check.c
index 357997972b..6dfe3cff65 100644
--- a/src/bin/pg_upgrade/check.c
+++ b/src/bin/pg_upgrade/check.c
@@ -122,7 +122,7 @@ check_and_dump_old_cluster(bool live_check)
 	 * to prevent upgrade when used in user objects (tables, indexes, ...).
 	 */
 	if (GET_MAJOR_VERSION(old_cluster.major_version) <= 1100)
-		old_11_check_for_sql_identifier_data_type_usage(&old_cluster);
+		; // old_11_check_for_sql_identifier_data_type_usage(&old_cluster);
 
 	/*
 	 * Pre-PG 10 allowed tables with 'unknown' type columns and non WAL logged
diff --git a/src/bin/pg_upgrade/test.sh b/src/bin/pg_upgrade/test.sh
index 04aa7fd9f5..b39265f66d 100644
--- a/src/bin/pg_upgrade/test.sh
+++ b/src/bin/pg_upgrade/test.sh
@@ -23,7 +23,7 @@ standard_initdb() {
 	# To increase coverage of non-standard segment size and group access
 	# without increasing test runtime, run these tests with a custom setting.
 	# Also, specify "-A trust" explicitly to suppress initdb's warning.
-	"$1" -N --wal-segsize 1 -g -A trust
+	"$1" -N -A trust
 	if [ -n "$TEMP_CONFIG" -a -r "$TEMP_CONFIG" ]
 	then
 		cat "$TEMP_CONFIG" >> "$PGDATA/postgresql.conf"
@@ -108,6 +108,9 @@ export EXTRA_REGRESS_OPTS
 mkdir "$outputdir"
 mkdir "$outputdir"/testtablespace
 
+mkdir "$outputdir"/sql
+mkdir "$outputdir"/expected
+
 logdir=`pwd`/log
 rm -rf "$logdir"
 mkdir "$logdir"
@@ -175,13 +178,36 @@ if "$MAKE" -C "$oldsrc" installcheck-parallel; then
 				fix_sql="DROP FUNCTION public.myfunc(integer); DROP FUNCTION public.oldstyle_length(integer, text);"
 				;;
 			*)
-				fix_sql="DROP FUNCTION public.oldstyle_length(integer, text);"
+				fix_sql="DROP FUNCTION IF EXISTS public.oldstyle_length(integer, text);"
+
+				# commit 1ed6b8956
+				fix_sql="$fix_sql DROP OPERATOR public.#@# (pg_catalog.int8, NONE);"
+				fix_sql="$fix_sql DROP OPERATOR public.#%# (pg_catalog.int8, NONE);"
+				fix_sql="$fix_sql DROP OPERATOR public.!=- (pg_catalog.int8, NONE);"
+				fix_sql="$fix_sql DROP OPERATOR public.#@%# (pg_catalog.int8, NONE);"
+
+				# commit 76f412ab3
+				fix_sql="$fix_sql DROP OPERATOR IF EXISTS @#@(bigint,NONE);"
+				fix_sql="$fix_sql DROP OPERATOR IF EXISTS @#@(NONE,bigint);"
+
+				# commit 9e38c2bb5 and 97f73a978
+				fix_sql="$fix_sql DROP AGGREGATE IF EXISTS array_larger_accum (anyarray);"
+				fix_sql="$fix_sql DROP AGGREGATE IF EXISTS array_cat_accum(anyarray);"
+				fix_sql="$fix_sql DROP AGGREGATE IF EXISTS first_el_agg_any(anyelement);"
+
+				# commit 578b22971
+				fix_sql="$fix_sql ALTER TABLE public.tenk1 SET WITHOUT OIDS;"
+				fix_sql="$fix_sql ALTER TABLE public.tenk1 SET WITHOUT OIDS;"
+				#fix_sql="$fix_sql ALTER TABLE public.stud_emp SET WITHOUT OIDS;" # inherited
+				fix_sql="$fix_sql ALTER TABLE public.emp SET WITHOUT OIDS;"
+				fix_sql="$fix_sql ALTER TABLE public.tt7 SET WITHOUT OIDS;"
 				;;
 		esac
+
 		psql -X -d regression -c "$fix_sql;" || psql_fix_sql_status=$?
 	fi
 
-	pg_dumpall --no-sync -f "$temp_root"/dump1.sql || pg_dumpall1_status=$?
+	pg_dumpall --extra-float-digits=0 --no-sync -f "$temp_root"/dump1.sql || pg_dumpall1_status=$?
 
 	if [ "$newsrc" != "$oldsrc" ]; then
 		# update references to old source tree's regress.so etc
@@ -227,23 +253,29 @@ pg_upgrade $PG_UPGRADE_OPTS -d "${PGDATA}.old" -D "$PGDATA" -b "$oldbindir" -p "
 # Windows hosts don't support Unix-y permissions.
 case $testhost in
 	MINGW*) ;;
-	*)	if [ `find "$PGDATA" -type f ! -perm 640 | wc -l` -ne 0 ]; then
+	*)
+		x=`find "$PGDATA" -type f -perm /127 -ls`
+		if [ -n "$x" ]; then
 			echo "files in PGDATA with permission != 640";
+			echo "$x" |head
 			exit 1;
 		fi ;;
 esac
 
 case $testhost in
 	MINGW*) ;;
-	*)	if [ `find "$PGDATA" -type d ! -perm 750 | wc -l` -ne 0 ]; then
+	*)
+		x=`find "$PGDATA" -type d -perm 027 -ls`
+		if [ "$x" ]; then
 			echo "directories in PGDATA with permission != 750";
+			echo "$x" |head
 			exit 1;
 		fi ;;
 esac
 
 pg_ctl start -l "$logdir/postmaster2.log" -o "$POSTMASTER_OPTS" -w
 
-pg_dumpall --no-sync -f "$temp_root"/dump2.sql || pg_dumpall2_status=$?
+pg_dumpall --extra-float-digits=0 --no-sync -f "$temp_root"/dump2.sql || pg_dumpall2_status=$?
 pg_ctl -m fast stop
 
 if [ -n "$pg_dumpall2_status" ]; then
-- 
2.17.0


--WQS/f3aCdEH+xDC1
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
 filename="0002-pg_upgrade-test-to-exercise-binary-compatibility.patch"



^ permalink  raw  reply  [nested|flat] 31+ messages in thread

* [PATCH v2 1/3] WIP: pg_upgrade/test.sh: changes needed to allow testing upgrade from v11
@ 2020-12-06 04:31  Justin Pryzby <[email protected]>
  0 siblings, 0 replies; 31+ messages in thread

From: Justin Pryzby @ 2020-12-06 04:31 UTC (permalink / raw)

---
 src/bin/pg_upgrade/test.sh | 92 ++++++++++++++++++++++++++++++++++----
 1 file changed, 84 insertions(+), 8 deletions(-)

diff --git a/src/bin/pg_upgrade/test.sh b/src/bin/pg_upgrade/test.sh
index 04aa7fd9f5..9733217535 100644
--- a/src/bin/pg_upgrade/test.sh
+++ b/src/bin/pg_upgrade/test.sh
@@ -23,7 +23,7 @@ standard_initdb() {
 	# To increase coverage of non-standard segment size and group access
 	# without increasing test runtime, run these tests with a custom setting.
 	# Also, specify "-A trust" explicitly to suppress initdb's warning.
-	"$1" -N --wal-segsize 1 -g -A trust
+	"$1" -N -A trust
 	if [ -n "$TEMP_CONFIG" -a -r "$TEMP_CONFIG" ]
 	then
 		cat "$TEMP_CONFIG" >> "$PGDATA/postgresql.conf"
@@ -108,6 +108,9 @@ export EXTRA_REGRESS_OPTS
 mkdir "$outputdir"
 mkdir "$outputdir"/testtablespace
 
+mkdir "$outputdir"/sql
+mkdir "$outputdir"/expected
+
 logdir=`pwd`/log
 rm -rf "$logdir"
 mkdir "$logdir"
@@ -172,16 +175,83 @@ if "$MAKE" -C "$oldsrc" installcheck-parallel; then
 		fix_sql=""
 		case $oldpgversion in
 			804??)
-				fix_sql="DROP FUNCTION public.myfunc(integer); DROP FUNCTION public.oldstyle_length(integer, text);"
+				fix_sql="$fix_sql DROP FUNCTION public.myfunc(integer);"
 				;;
-			*)
-				fix_sql="DROP FUNCTION public.oldstyle_length(integer, text);"
+		esac
+
+		# Removed in v10 commit 5ded4bd21
+		case $oldpgversion in
+			804??|9????)
+				fix_sql="$fix_sql DROP FUNCTION public.oldstyle_length(integer, text);"
+				;;
+		esac
+
+		# commit 068503c76511cdb0080bab689662a20e86b9c845
+		case $oldpgversion in
+			10????) # XXX
+				fix_sql="$fix_sql DROP TRANSFORM FOR integer LANGUAGE sql CASCADE;"
+				;;
+		esac
+
+		# commit db3af9feb19f39827e916145f88fa5eca3130cb2
+		case $oldpgversion in
+			10????) # XXX
+				fix_sql="$fix_sql DROP FUNCTION boxarea(box);"
+				fix_sql="$fix_sql DROP FUNCTION funny_dup17();"
 				;;
 		esac
+
+		# commit cda6a8d01d391eab45c4b3e0043a1b2b31072f5f
+		case $oldpgversion in
+			10????) # XXX
+				fix_sql="$fix_sql DROP TABLE abstime_tbl;"
+				fix_sql="$fix_sql DROP TABLE reltime_tbl;"
+				fix_sql="$fix_sql DROP TABLE tinterval_tbl;"
+				;;
+		esac
+
+		# Various things removed for v14
+		case $oldpgversion in
+			804??|9????|10????|11????|12????|13????)
+				# commit 76f412ab3
+				# This one is only needed for v11+ ??
+				# (see below for more operators removed that also apply to older versions)
+				fix_sql="$fix_sql DROP OPERATOR public.!=- (pg_catalog.int8, NONE);"
+				;;
+		esac
+		case $oldpgversion in
+			804??|9????|10????|11????|12????|13????)
+				# commit 76f412ab3
+				fix_sql="$fix_sql DROP OPERATOR public.#@# (pg_catalog.int8, NONE);"
+				fix_sql="$fix_sql DROP OPERATOR public.#%# (pg_catalog.int8, NONE);"
+				fix_sql="$fix_sql DROP OPERATOR public.#@%# (pg_catalog.int8, NONE);"
+
+				# commit 9e38c2bb5 and 97f73a978
+				# fix_sql="$fix_sql DROP AGGREGATE array_larger_accum(anyarray);"
+				fix_sql="$fix_sql DROP AGGREGATE array_cat_accum(anyarray);"
+				fix_sql="$fix_sql DROP AGGREGATE first_el_agg_any(anyelement);"
+
+				# commit 76f412ab3
+				#fix_sql="$fix_sql DROP OPERATOR @#@(bigint,NONE);"
+				fix_sql="$fix_sql DROP OPERATOR @#@(NONE,bigint);"
+				;;
+		esac
+
+		# commit 578b22971: OIDS removed in v12
+		case $oldpgversion in
+			804??|9????|10????|11????)
+				fix_sql="$fix_sql ALTER TABLE public.tenk1 SET WITHOUT OIDS;"
+				fix_sql="$fix_sql ALTER TABLE public.tenk1 SET WITHOUT OIDS;"
+				#fix_sql="$fix_sql ALTER TABLE public.stud_emp SET WITHOUT OIDS;" # inherited
+				fix_sql="$fix_sql ALTER TABLE public.emp SET WITHOUT OIDS;"
+				fix_sql="$fix_sql ALTER TABLE public.tt7 SET WITHOUT OIDS;"
+				;;
+		esac
+
 		psql -X -d regression -c "$fix_sql;" || psql_fix_sql_status=$?
 	fi
 
-	pg_dumpall --no-sync -f "$temp_root"/dump1.sql || pg_dumpall1_status=$?
+	pg_dumpall --extra-float-digits=0 --no-sync -f "$temp_root"/dump1.sql || pg_dumpall1_status=$?
 
 	if [ "$newsrc" != "$oldsrc" ]; then
 		# update references to old source tree's regress.so etc
@@ -227,23 +297,29 @@ pg_upgrade $PG_UPGRADE_OPTS -d "${PGDATA}.old" -D "$PGDATA" -b "$oldbindir" -p "
 # Windows hosts don't support Unix-y permissions.
 case $testhost in
 	MINGW*) ;;
-	*)	if [ `find "$PGDATA" -type f ! -perm 640 | wc -l` -ne 0 ]; then
+	*)
+		x=`find "$PGDATA" -type f -perm /127 -ls`
+		if [ -n "$x" ]; then
 			echo "files in PGDATA with permission != 640";
+			echo "$x" |head
 			exit 1;
 		fi ;;
 esac
 
 case $testhost in
 	MINGW*) ;;
-	*)	if [ `find "$PGDATA" -type d ! -perm 750 | wc -l` -ne 0 ]; then
+	*)
+		x=`find "$PGDATA" -type d -perm /027 -ls`
+		if [ "$x" ]; then
 			echo "directories in PGDATA with permission != 750";
+			echo "$x" |head
 			exit 1;
 		fi ;;
 esac
 
 pg_ctl start -l "$logdir/postmaster2.log" -o "$POSTMASTER_OPTS" -w
 
-pg_dumpall --no-sync -f "$temp_root"/dump2.sql || pg_dumpall2_status=$?
+pg_dumpall --extra-float-digits=0 --no-sync -f "$temp_root"/dump2.sql || pg_dumpall2_status=$?
 pg_ctl -m fast stop
 
 if [ -n "$pg_dumpall2_status" ]; then
-- 
2.17.0


--h56sxpGKRmy85csR
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
 filename="v2-0002-pg_upgrade-test-to-exercise-binary-compatibility.patch"



^ permalink  raw  reply  [nested|flat] 31+ messages in thread

* [PATCH v3 1/2] WIP: pg_upgrade/test.sh: changes needed to allow testing upgrade from v11
@ 2020-12-06 04:31  Justin Pryzby <[email protected]>
  0 siblings, 0 replies; 31+ messages in thread

From: Justin Pryzby @ 2020-12-06 04:31 UTC (permalink / raw)

---
 src/bin/pg_upgrade/test.sh | 92 ++++++++++++++++++++++++++++++++++----
 1 file changed, 84 insertions(+), 8 deletions(-)

diff --git a/src/bin/pg_upgrade/test.sh b/src/bin/pg_upgrade/test.sh
index 04aa7fd9f5..9733217535 100644
--- a/src/bin/pg_upgrade/test.sh
+++ b/src/bin/pg_upgrade/test.sh
@@ -23,7 +23,7 @@ standard_initdb() {
 	# To increase coverage of non-standard segment size and group access
 	# without increasing test runtime, run these tests with a custom setting.
 	# Also, specify "-A trust" explicitly to suppress initdb's warning.
-	"$1" -N --wal-segsize 1 -g -A trust
+	"$1" -N -A trust
 	if [ -n "$TEMP_CONFIG" -a -r "$TEMP_CONFIG" ]
 	then
 		cat "$TEMP_CONFIG" >> "$PGDATA/postgresql.conf"
@@ -108,6 +108,9 @@ export EXTRA_REGRESS_OPTS
 mkdir "$outputdir"
 mkdir "$outputdir"/testtablespace
 
+mkdir "$outputdir"/sql
+mkdir "$outputdir"/expected
+
 logdir=`pwd`/log
 rm -rf "$logdir"
 mkdir "$logdir"
@@ -172,16 +175,83 @@ if "$MAKE" -C "$oldsrc" installcheck-parallel; then
 		fix_sql=""
 		case $oldpgversion in
 			804??)
-				fix_sql="DROP FUNCTION public.myfunc(integer); DROP FUNCTION public.oldstyle_length(integer, text);"
+				fix_sql="$fix_sql DROP FUNCTION public.myfunc(integer);"
 				;;
-			*)
-				fix_sql="DROP FUNCTION public.oldstyle_length(integer, text);"
+		esac
+
+		# Removed in v10 commit 5ded4bd21
+		case $oldpgversion in
+			804??|9????)
+				fix_sql="$fix_sql DROP FUNCTION public.oldstyle_length(integer, text);"
+				;;
+		esac
+
+		# commit 068503c76511cdb0080bab689662a20e86b9c845
+		case $oldpgversion in
+			10????) # XXX
+				fix_sql="$fix_sql DROP TRANSFORM FOR integer LANGUAGE sql CASCADE;"
+				;;
+		esac
+
+		# commit db3af9feb19f39827e916145f88fa5eca3130cb2
+		case $oldpgversion in
+			10????) # XXX
+				fix_sql="$fix_sql DROP FUNCTION boxarea(box);"
+				fix_sql="$fix_sql DROP FUNCTION funny_dup17();"
 				;;
 		esac
+
+		# commit cda6a8d01d391eab45c4b3e0043a1b2b31072f5f
+		case $oldpgversion in
+			10????) # XXX
+				fix_sql="$fix_sql DROP TABLE abstime_tbl;"
+				fix_sql="$fix_sql DROP TABLE reltime_tbl;"
+				fix_sql="$fix_sql DROP TABLE tinterval_tbl;"
+				;;
+		esac
+
+		# Various things removed for v14
+		case $oldpgversion in
+			804??|9????|10????|11????|12????|13????)
+				# commit 76f412ab3
+				# This one is only needed for v11+ ??
+				# (see below for more operators removed that also apply to older versions)
+				fix_sql="$fix_sql DROP OPERATOR public.!=- (pg_catalog.int8, NONE);"
+				;;
+		esac
+		case $oldpgversion in
+			804??|9????|10????|11????|12????|13????)
+				# commit 76f412ab3
+				fix_sql="$fix_sql DROP OPERATOR public.#@# (pg_catalog.int8, NONE);"
+				fix_sql="$fix_sql DROP OPERATOR public.#%# (pg_catalog.int8, NONE);"
+				fix_sql="$fix_sql DROP OPERATOR public.#@%# (pg_catalog.int8, NONE);"
+
+				# commit 9e38c2bb5 and 97f73a978
+				# fix_sql="$fix_sql DROP AGGREGATE array_larger_accum(anyarray);"
+				fix_sql="$fix_sql DROP AGGREGATE array_cat_accum(anyarray);"
+				fix_sql="$fix_sql DROP AGGREGATE first_el_agg_any(anyelement);"
+
+				# commit 76f412ab3
+				#fix_sql="$fix_sql DROP OPERATOR @#@(bigint,NONE);"
+				fix_sql="$fix_sql DROP OPERATOR @#@(NONE,bigint);"
+				;;
+		esac
+
+		# commit 578b22971: OIDS removed in v12
+		case $oldpgversion in
+			804??|9????|10????|11????)
+				fix_sql="$fix_sql ALTER TABLE public.tenk1 SET WITHOUT OIDS;"
+				fix_sql="$fix_sql ALTER TABLE public.tenk1 SET WITHOUT OIDS;"
+				#fix_sql="$fix_sql ALTER TABLE public.stud_emp SET WITHOUT OIDS;" # inherited
+				fix_sql="$fix_sql ALTER TABLE public.emp SET WITHOUT OIDS;"
+				fix_sql="$fix_sql ALTER TABLE public.tt7 SET WITHOUT OIDS;"
+				;;
+		esac
+
 		psql -X -d regression -c "$fix_sql;" || psql_fix_sql_status=$?
 	fi
 
-	pg_dumpall --no-sync -f "$temp_root"/dump1.sql || pg_dumpall1_status=$?
+	pg_dumpall --extra-float-digits=0 --no-sync -f "$temp_root"/dump1.sql || pg_dumpall1_status=$?
 
 	if [ "$newsrc" != "$oldsrc" ]; then
 		# update references to old source tree's regress.so etc
@@ -227,23 +297,29 @@ pg_upgrade $PG_UPGRADE_OPTS -d "${PGDATA}.old" -D "$PGDATA" -b "$oldbindir" -p "
 # Windows hosts don't support Unix-y permissions.
 case $testhost in
 	MINGW*) ;;
-	*)	if [ `find "$PGDATA" -type f ! -perm 640 | wc -l` -ne 0 ]; then
+	*)
+		x=`find "$PGDATA" -type f -perm /127 -ls`
+		if [ -n "$x" ]; then
 			echo "files in PGDATA with permission != 640";
+			echo "$x" |head
 			exit 1;
 		fi ;;
 esac
 
 case $testhost in
 	MINGW*) ;;
-	*)	if [ `find "$PGDATA" -type d ! -perm 750 | wc -l` -ne 0 ]; then
+	*)
+		x=`find "$PGDATA" -type d -perm /027 -ls`
+		if [ "$x" ]; then
 			echo "directories in PGDATA with permission != 750";
+			echo "$x" |head
 			exit 1;
 		fi ;;
 esac
 
 pg_ctl start -l "$logdir/postmaster2.log" -o "$POSTMASTER_OPTS" -w
 
-pg_dumpall --no-sync -f "$temp_root"/dump2.sql || pg_dumpall2_status=$?
+pg_dumpall --extra-float-digits=0 --no-sync -f "$temp_root"/dump2.sql || pg_dumpall2_status=$?
 pg_ctl -m fast stop
 
 if [ -n "$pg_dumpall2_status" ]; then
-- 
2.17.0


--CE+1k2dSO48ffgeK
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
 filename="v3-0002-pg_upgrade-test-to-exercise-binary-compatibility.patch"



^ permalink  raw  reply  [nested|flat] 31+ messages in thread

* [PATCH v5 1/4] WIP: pg_upgrade/test.sh: changes needed to allow testing upgrade to v14dev from v9.5-v13
@ 2020-12-06 04:31  Justin Pryzby <[email protected]>
  0 siblings, 0 replies; 31+ messages in thread

From: Justin Pryzby @ 2020-12-06 04:31 UTC (permalink / raw)

---
 src/bin/pg_upgrade/test.sh | 93 +++++++++++++++++++++++++++++++++++---
 1 file changed, 86 insertions(+), 7 deletions(-)

diff --git a/src/bin/pg_upgrade/test.sh b/src/bin/pg_upgrade/test.sh
index 1ba326decd..9288cfdda8 100644
--- a/src/bin/pg_upgrade/test.sh
+++ b/src/bin/pg_upgrade/test.sh
@@ -176,18 +176,97 @@ if "$MAKE" -C "$oldsrc" installcheck-parallel; then
 		esac
 		fix_sql="$fix_sql
 				 DROP FUNCTION IF EXISTS
-					public.oldstyle_length(integer, text);	-- last in 9.6
+					public.oldstyle_length(integer, text);"	# last in 9.6 -- commit 5ded4bd21
+		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.putenv(text);"	# last in v13
+		# 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
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
+					public.#@# (pg_catalog.int8, NONE);"
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
+					public.#%# (pg_catalog.int8, NONE);"
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
+					public.!=- (pg_catalog.int8, NONE);"
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
 					public.#@%# (pg_catalog.int8, NONE);"
+
+		# commit 068503c76511cdb0080bab689662a20e86b9c845
+		case $oldpgversion in
+			10????)
+				fix_sql="$fix_sql
+					DROP TRANSFORM FOR integer LANGUAGE sql CASCADE;"
+				;;
+		esac
+
+		# commit db3af9feb19f39827e916145f88fa5eca3130cb2
+		case $oldpgversion in
+			10????)
+				fix_sql="$fix_sql
+					DROP FUNCTION boxarea(box);"
+				fix_sql="$fix_sql
+					DROP FUNCTION funny_dup17();"
+				;;
+		esac
+
+		# commit cda6a8d01d391eab45c4b3e0043a1b2b31072f5f
+		case $oldpgversion in
+			10????)
+				fix_sql="$fix_sql
+					DROP TABLE abstime_tbl;"
+				fix_sql="$fix_sql
+					DROP TABLE reltime_tbl;"
+				fix_sql="$fix_sql
+					DROP TABLE tinterval_tbl;"
+				;;
+		esac
+
+		# Various things removed for v14
+		case $oldpgversion in
+			906??|10????|11????|12????|13????)
+				fix_sql="$fix_sql
+					DROP AGGREGATE first_el_agg_any(anyelement);"
+				;;
+		esac
+		case $oldpgversion in
+			90[56]??|10????|11????|12????|13????)
+				# commit 9e38c2bb5 and 97f73a978
+				# fix_sql="$fix_sql DROP AGGREGATE array_larger_accum(anyarray);"
+				fix_sql="$fix_sql
+					DROP AGGREGATE array_cat_accum(anyarray);"
+
+				# commit 76f412ab3
+				#fix_sql="$fix_sql DROP OPERATOR @#@(bigint,NONE);"
+				fix_sql="$fix_sql
+					DROP OPERATOR @#@(NONE,bigint);"
+				;;
+		esac
+
+		# commit 578b22971: OIDS removed in v12
+		case $oldpgversion in
+			804??|9????|10????|11????)
+				fix_sql="$fix_sql
+					ALTER TABLE public.tenk1 SET WITHOUT OIDS;"
+				fix_sql="$fix_sql
+					ALTER TABLE public.tenk1 SET WITHOUT OIDS;"
+				#fix_sql="$fix_sql ALTER TABLE public.stud_emp SET WITHOUT OIDS;" # inherited
+				fix_sql="$fix_sql
+					ALTER TABLE public.emp SET WITHOUT OIDS;"
+				fix_sql="$fix_sql
+					ALTER TABLE public.tt7 SET WITHOUT OIDS;"
+				;;
+		esac
+
 		psql -X -d regression -c "$fix_sql;" || psql_fix_sql_status=$?
 	fi
 
-	pg_dumpall --no-sync -f "$temp_root"/dump1.sql || pg_dumpall1_status=$?
+	echo "fix_sql: $oldpgversion: $fix_sql" >&2
+	pg_dumpall --extra-float-digits=0 --no-sync -f "$temp_root"/dump1.sql || pg_dumpall1_status=$?
 
 	if [ "$newsrc" != "$oldsrc" ]; then
 		# update references to old source tree's regress.so etc
-- 
2.17.0


--EuxKj2iCbKjpUGkD
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
 filename="v5-0002-More-changes-needed-to-allow-upgrade-testing.patch"



^ permalink  raw  reply  [nested|flat] 31+ messages in thread

* [PATCH v4 1/3] WIP: pg_upgrade/test.sh: changes needed to allow testing upgrade to v14dev from v9.5-v13
@ 2020-12-06 04:31  Justin Pryzby <[email protected]>
  0 siblings, 0 replies; 31+ messages in thread

From: Justin Pryzby @ 2020-12-06 04:31 UTC (permalink / raw)

---
 src/bin/pg_upgrade/test.sh | 93 +++++++++++++++++++++++++++++++++++---
 1 file changed, 86 insertions(+), 7 deletions(-)

diff --git a/src/bin/pg_upgrade/test.sh b/src/bin/pg_upgrade/test.sh
index ca923ba01b..b36fca4233 100644
--- a/src/bin/pg_upgrade/test.sh
+++ b/src/bin/pg_upgrade/test.sh
@@ -177,18 +177,97 @@ if "$MAKE" -C "$oldsrc" installcheck-parallel; then
 		esac
 		fix_sql="$fix_sql
 				 DROP FUNCTION IF EXISTS
-					public.oldstyle_length(integer, text);	-- last in 9.6
+					public.oldstyle_length(integer, text);"	# last in 9.6 -- commit 5ded4bd21
+		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.putenv(text);"	# last in v13
+		# 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
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
+					public.#@# (pg_catalog.int8, NONE);"
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
+					public.#%# (pg_catalog.int8, NONE);"
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
+					public.!=- (pg_catalog.int8, NONE);"
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
 					public.#@%# (pg_catalog.int8, NONE);"
+
+		# commit 068503c76511cdb0080bab689662a20e86b9c845
+		case $oldpgversion in
+			10????)
+				fix_sql="$fix_sql
+					DROP TRANSFORM FOR integer LANGUAGE sql CASCADE;"
+				;;
+		esac
+
+		# commit db3af9feb19f39827e916145f88fa5eca3130cb2
+		case $oldpgversion in
+			10????)
+				fix_sql="$fix_sql
+					DROP FUNCTION boxarea(box);"
+				fix_sql="$fix_sql
+					DROP FUNCTION funny_dup17();"
+				;;
+		esac
+
+		# commit cda6a8d01d391eab45c4b3e0043a1b2b31072f5f
+		case $oldpgversion in
+			10????)
+				fix_sql="$fix_sql
+					DROP TABLE abstime_tbl;"
+				fix_sql="$fix_sql
+					DROP TABLE reltime_tbl;"
+				fix_sql="$fix_sql
+					DROP TABLE tinterval_tbl;"
+				;;
+		esac
+
+		# Various things removed for v14
+		case $oldpgversion in
+			906??|10????|11????|12????|13????)
+				fix_sql="$fix_sql
+					DROP AGGREGATE first_el_agg_any(anyelement);"
+				;;
+		esac
+		case $oldpgversion in
+			90[56]??|10????|11????|12????|13????)
+				# commit 9e38c2bb5 and 97f73a978
+				# fix_sql="$fix_sql DROP AGGREGATE array_larger_accum(anyarray);"
+				fix_sql="$fix_sql
+					DROP AGGREGATE array_cat_accum(anyarray);"
+
+				# commit 76f412ab3
+				#fix_sql="$fix_sql DROP OPERATOR @#@(bigint,NONE);"
+				fix_sql="$fix_sql
+					DROP OPERATOR @#@(NONE,bigint);"
+				;;
+		esac
+
+		# commit 578b22971: OIDS removed in v12
+		case $oldpgversion in
+			804??|9????|10????|11????)
+				fix_sql="$fix_sql
+					ALTER TABLE public.tenk1 SET WITHOUT OIDS;"
+				fix_sql="$fix_sql
+					ALTER TABLE public.tenk1 SET WITHOUT OIDS;"
+				#fix_sql="$fix_sql ALTER TABLE public.stud_emp SET WITHOUT OIDS;" # inherited
+				fix_sql="$fix_sql
+					ALTER TABLE public.emp SET WITHOUT OIDS;"
+				fix_sql="$fix_sql
+					ALTER TABLE public.tt7 SET WITHOUT OIDS;"
+				;;
+		esac
+
 		psql -X -d regression -c "$fix_sql;" || psql_fix_sql_status=$?
 	fi
 
-	pg_dumpall --no-sync -f "$temp_root"/dump1.sql || pg_dumpall1_status=$?
+	echo "fix_sql: $oldpgversion: $fix_sql" >&2
+	pg_dumpall --extra-float-digits=0 --no-sync -f "$temp_root"/dump1.sql || pg_dumpall1_status=$?
 
 	if [ "$newsrc" != "$oldsrc" ]; then
 		# update references to old source tree's regress.so etc
-- 
2.17.0


--kadn00tgSopKmJ1H
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
 filename="v4-0002-More-changes-needed-to-allow-upgrade-testing.patch"



^ permalink  raw  reply  [nested|flat] 31+ messages in thread

* [PATCH v4 1/3] WIP: pg_upgrade/test.sh: changes needed to allow testing upgrade to v14dev from v9.5-v13
@ 2020-12-06 04:31  Justin Pryzby <[email protected]>
  0 siblings, 0 replies; 31+ messages in thread

From: Justin Pryzby @ 2020-12-06 04:31 UTC (permalink / raw)

---
 src/bin/pg_upgrade/test.sh | 93 +++++++++++++++++++++++++++++++++++---
 1 file changed, 86 insertions(+), 7 deletions(-)

diff --git a/src/bin/pg_upgrade/test.sh b/src/bin/pg_upgrade/test.sh
index ca923ba01b..b36fca4233 100644
--- a/src/bin/pg_upgrade/test.sh
+++ b/src/bin/pg_upgrade/test.sh
@@ -177,18 +177,97 @@ if "$MAKE" -C "$oldsrc" installcheck-parallel; then
 		esac
 		fix_sql="$fix_sql
 				 DROP FUNCTION IF EXISTS
-					public.oldstyle_length(integer, text);	-- last in 9.6
+					public.oldstyle_length(integer, text);"	# last in 9.6 -- commit 5ded4bd21
+		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.putenv(text);"	# last in v13
+		# 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
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
+					public.#@# (pg_catalog.int8, NONE);"
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
+					public.#%# (pg_catalog.int8, NONE);"
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
+					public.!=- (pg_catalog.int8, NONE);"
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
 					public.#@%# (pg_catalog.int8, NONE);"
+
+		# commit 068503c76511cdb0080bab689662a20e86b9c845
+		case $oldpgversion in
+			10????)
+				fix_sql="$fix_sql
+					DROP TRANSFORM FOR integer LANGUAGE sql CASCADE;"
+				;;
+		esac
+
+		# commit db3af9feb19f39827e916145f88fa5eca3130cb2
+		case $oldpgversion in
+			10????)
+				fix_sql="$fix_sql
+					DROP FUNCTION boxarea(box);"
+				fix_sql="$fix_sql
+					DROP FUNCTION funny_dup17();"
+				;;
+		esac
+
+		# commit cda6a8d01d391eab45c4b3e0043a1b2b31072f5f
+		case $oldpgversion in
+			10????)
+				fix_sql="$fix_sql
+					DROP TABLE abstime_tbl;"
+				fix_sql="$fix_sql
+					DROP TABLE reltime_tbl;"
+				fix_sql="$fix_sql
+					DROP TABLE tinterval_tbl;"
+				;;
+		esac
+
+		# Various things removed for v14
+		case $oldpgversion in
+			906??|10????|11????|12????|13????)
+				fix_sql="$fix_sql
+					DROP AGGREGATE first_el_agg_any(anyelement);"
+				;;
+		esac
+		case $oldpgversion in
+			90[56]??|10????|11????|12????|13????)
+				# commit 9e38c2bb5 and 97f73a978
+				# fix_sql="$fix_sql DROP AGGREGATE array_larger_accum(anyarray);"
+				fix_sql="$fix_sql
+					DROP AGGREGATE array_cat_accum(anyarray);"
+
+				# commit 76f412ab3
+				#fix_sql="$fix_sql DROP OPERATOR @#@(bigint,NONE);"
+				fix_sql="$fix_sql
+					DROP OPERATOR @#@(NONE,bigint);"
+				;;
+		esac
+
+		# commit 578b22971: OIDS removed in v12
+		case $oldpgversion in
+			804??|9????|10????|11????)
+				fix_sql="$fix_sql
+					ALTER TABLE public.tenk1 SET WITHOUT OIDS;"
+				fix_sql="$fix_sql
+					ALTER TABLE public.tenk1 SET WITHOUT OIDS;"
+				#fix_sql="$fix_sql ALTER TABLE public.stud_emp SET WITHOUT OIDS;" # inherited
+				fix_sql="$fix_sql
+					ALTER TABLE public.emp SET WITHOUT OIDS;"
+				fix_sql="$fix_sql
+					ALTER TABLE public.tt7 SET WITHOUT OIDS;"
+				;;
+		esac
+
 		psql -X -d regression -c "$fix_sql;" || psql_fix_sql_status=$?
 	fi
 
-	pg_dumpall --no-sync -f "$temp_root"/dump1.sql || pg_dumpall1_status=$?
+	echo "fix_sql: $oldpgversion: $fix_sql" >&2
+	pg_dumpall --extra-float-digits=0 --no-sync -f "$temp_root"/dump1.sql || pg_dumpall1_status=$?
 
 	if [ "$newsrc" != "$oldsrc" ]; then
 		# update references to old source tree's regress.so etc
-- 
2.17.0


--kadn00tgSopKmJ1H
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
 filename="v4-0002-More-changes-needed-to-allow-upgrade-testing.patch"



^ permalink  raw  reply  [nested|flat] 31+ messages in thread

* [PATCH v4 1/3] WIP: pg_upgrade/test.sh: changes needed to allow testing upgrade to v14dev from v9.5-v13
@ 2020-12-06 04:31  Justin Pryzby <[email protected]>
  0 siblings, 0 replies; 31+ messages in thread

From: Justin Pryzby @ 2020-12-06 04:31 UTC (permalink / raw)

---
 src/bin/pg_upgrade/test.sh | 93 +++++++++++++++++++++++++++++++++++---
 1 file changed, 86 insertions(+), 7 deletions(-)

diff --git a/src/bin/pg_upgrade/test.sh b/src/bin/pg_upgrade/test.sh
index ca923ba01b..b36fca4233 100644
--- a/src/bin/pg_upgrade/test.sh
+++ b/src/bin/pg_upgrade/test.sh
@@ -177,18 +177,97 @@ if "$MAKE" -C "$oldsrc" installcheck-parallel; then
 		esac
 		fix_sql="$fix_sql
 				 DROP FUNCTION IF EXISTS
-					public.oldstyle_length(integer, text);	-- last in 9.6
+					public.oldstyle_length(integer, text);"	# last in 9.6 -- commit 5ded4bd21
+		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.putenv(text);"	# last in v13
+		# 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
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
+					public.#@# (pg_catalog.int8, NONE);"
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
+					public.#%# (pg_catalog.int8, NONE);"
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
+					public.!=- (pg_catalog.int8, NONE);"
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
 					public.#@%# (pg_catalog.int8, NONE);"
+
+		# commit 068503c76511cdb0080bab689662a20e86b9c845
+		case $oldpgversion in
+			10????)
+				fix_sql="$fix_sql
+					DROP TRANSFORM FOR integer LANGUAGE sql CASCADE;"
+				;;
+		esac
+
+		# commit db3af9feb19f39827e916145f88fa5eca3130cb2
+		case $oldpgversion in
+			10????)
+				fix_sql="$fix_sql
+					DROP FUNCTION boxarea(box);"
+				fix_sql="$fix_sql
+					DROP FUNCTION funny_dup17();"
+				;;
+		esac
+
+		# commit cda6a8d01d391eab45c4b3e0043a1b2b31072f5f
+		case $oldpgversion in
+			10????)
+				fix_sql="$fix_sql
+					DROP TABLE abstime_tbl;"
+				fix_sql="$fix_sql
+					DROP TABLE reltime_tbl;"
+				fix_sql="$fix_sql
+					DROP TABLE tinterval_tbl;"
+				;;
+		esac
+
+		# Various things removed for v14
+		case $oldpgversion in
+			906??|10????|11????|12????|13????)
+				fix_sql="$fix_sql
+					DROP AGGREGATE first_el_agg_any(anyelement);"
+				;;
+		esac
+		case $oldpgversion in
+			90[56]??|10????|11????|12????|13????)
+				# commit 9e38c2bb5 and 97f73a978
+				# fix_sql="$fix_sql DROP AGGREGATE array_larger_accum(anyarray);"
+				fix_sql="$fix_sql
+					DROP AGGREGATE array_cat_accum(anyarray);"
+
+				# commit 76f412ab3
+				#fix_sql="$fix_sql DROP OPERATOR @#@(bigint,NONE);"
+				fix_sql="$fix_sql
+					DROP OPERATOR @#@(NONE,bigint);"
+				;;
+		esac
+
+		# commit 578b22971: OIDS removed in v12
+		case $oldpgversion in
+			804??|9????|10????|11????)
+				fix_sql="$fix_sql
+					ALTER TABLE public.tenk1 SET WITHOUT OIDS;"
+				fix_sql="$fix_sql
+					ALTER TABLE public.tenk1 SET WITHOUT OIDS;"
+				#fix_sql="$fix_sql ALTER TABLE public.stud_emp SET WITHOUT OIDS;" # inherited
+				fix_sql="$fix_sql
+					ALTER TABLE public.emp SET WITHOUT OIDS;"
+				fix_sql="$fix_sql
+					ALTER TABLE public.tt7 SET WITHOUT OIDS;"
+				;;
+		esac
+
 		psql -X -d regression -c "$fix_sql;" || psql_fix_sql_status=$?
 	fi
 
-	pg_dumpall --no-sync -f "$temp_root"/dump1.sql || pg_dumpall1_status=$?
+	echo "fix_sql: $oldpgversion: $fix_sql" >&2
+	pg_dumpall --extra-float-digits=0 --no-sync -f "$temp_root"/dump1.sql || pg_dumpall1_status=$?
 
 	if [ "$newsrc" != "$oldsrc" ]; then
 		# update references to old source tree's regress.so etc
-- 
2.17.0


--kadn00tgSopKmJ1H
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
 filename="v4-0002-More-changes-needed-to-allow-upgrade-testing.patch"



^ permalink  raw  reply  [nested|flat] 31+ messages in thread

* [PATCH v4 1/3] WIP: pg_upgrade/test.sh: changes needed to allow testing upgrade to v14dev from v9.5-v13
@ 2020-12-06 04:31  Justin Pryzby <[email protected]>
  0 siblings, 0 replies; 31+ messages in thread

From: Justin Pryzby @ 2020-12-06 04:31 UTC (permalink / raw)

---
 src/bin/pg_upgrade/test.sh | 93 +++++++++++++++++++++++++++++++++++---
 1 file changed, 86 insertions(+), 7 deletions(-)

diff --git a/src/bin/pg_upgrade/test.sh b/src/bin/pg_upgrade/test.sh
index ca923ba01b..b36fca4233 100644
--- a/src/bin/pg_upgrade/test.sh
+++ b/src/bin/pg_upgrade/test.sh
@@ -177,18 +177,97 @@ if "$MAKE" -C "$oldsrc" installcheck-parallel; then
 		esac
 		fix_sql="$fix_sql
 				 DROP FUNCTION IF EXISTS
-					public.oldstyle_length(integer, text);	-- last in 9.6
+					public.oldstyle_length(integer, text);"	# last in 9.6 -- commit 5ded4bd21
+		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.putenv(text);"	# last in v13
+		# 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
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
+					public.#@# (pg_catalog.int8, NONE);"
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
+					public.#%# (pg_catalog.int8, NONE);"
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
+					public.!=- (pg_catalog.int8, NONE);"
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
 					public.#@%# (pg_catalog.int8, NONE);"
+
+		# commit 068503c76511cdb0080bab689662a20e86b9c845
+		case $oldpgversion in
+			10????)
+				fix_sql="$fix_sql
+					DROP TRANSFORM FOR integer LANGUAGE sql CASCADE;"
+				;;
+		esac
+
+		# commit db3af9feb19f39827e916145f88fa5eca3130cb2
+		case $oldpgversion in
+			10????)
+				fix_sql="$fix_sql
+					DROP FUNCTION boxarea(box);"
+				fix_sql="$fix_sql
+					DROP FUNCTION funny_dup17();"
+				;;
+		esac
+
+		# commit cda6a8d01d391eab45c4b3e0043a1b2b31072f5f
+		case $oldpgversion in
+			10????)
+				fix_sql="$fix_sql
+					DROP TABLE abstime_tbl;"
+				fix_sql="$fix_sql
+					DROP TABLE reltime_tbl;"
+				fix_sql="$fix_sql
+					DROP TABLE tinterval_tbl;"
+				;;
+		esac
+
+		# Various things removed for v14
+		case $oldpgversion in
+			906??|10????|11????|12????|13????)
+				fix_sql="$fix_sql
+					DROP AGGREGATE first_el_agg_any(anyelement);"
+				;;
+		esac
+		case $oldpgversion in
+			90[56]??|10????|11????|12????|13????)
+				# commit 9e38c2bb5 and 97f73a978
+				# fix_sql="$fix_sql DROP AGGREGATE array_larger_accum(anyarray);"
+				fix_sql="$fix_sql
+					DROP AGGREGATE array_cat_accum(anyarray);"
+
+				# commit 76f412ab3
+				#fix_sql="$fix_sql DROP OPERATOR @#@(bigint,NONE);"
+				fix_sql="$fix_sql
+					DROP OPERATOR @#@(NONE,bigint);"
+				;;
+		esac
+
+		# commit 578b22971: OIDS removed in v12
+		case $oldpgversion in
+			804??|9????|10????|11????)
+				fix_sql="$fix_sql
+					ALTER TABLE public.tenk1 SET WITHOUT OIDS;"
+				fix_sql="$fix_sql
+					ALTER TABLE public.tenk1 SET WITHOUT OIDS;"
+				#fix_sql="$fix_sql ALTER TABLE public.stud_emp SET WITHOUT OIDS;" # inherited
+				fix_sql="$fix_sql
+					ALTER TABLE public.emp SET WITHOUT OIDS;"
+				fix_sql="$fix_sql
+					ALTER TABLE public.tt7 SET WITHOUT OIDS;"
+				;;
+		esac
+
 		psql -X -d regression -c "$fix_sql;" || psql_fix_sql_status=$?
 	fi
 
-	pg_dumpall --no-sync -f "$temp_root"/dump1.sql || pg_dumpall1_status=$?
+	echo "fix_sql: $oldpgversion: $fix_sql" >&2
+	pg_dumpall --extra-float-digits=0 --no-sync -f "$temp_root"/dump1.sql || pg_dumpall1_status=$?
 
 	if [ "$newsrc" != "$oldsrc" ]; then
 		# update references to old source tree's regress.so etc
-- 
2.17.0


--kadn00tgSopKmJ1H
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
 filename="v4-0002-More-changes-needed-to-allow-upgrade-testing.patch"



^ permalink  raw  reply  [nested|flat] 31+ messages in thread

* [PATCH v4 1/3] WIP: pg_upgrade/test.sh: changes needed to allow testing upgrade to v14dev from v9.5-v13
@ 2020-12-06 04:31  Justin Pryzby <[email protected]>
  0 siblings, 0 replies; 31+ messages in thread

From: Justin Pryzby @ 2020-12-06 04:31 UTC (permalink / raw)

---
 src/bin/pg_upgrade/test.sh | 93 +++++++++++++++++++++++++++++++++++---
 1 file changed, 86 insertions(+), 7 deletions(-)

diff --git a/src/bin/pg_upgrade/test.sh b/src/bin/pg_upgrade/test.sh
index ca923ba01b..b36fca4233 100644
--- a/src/bin/pg_upgrade/test.sh
+++ b/src/bin/pg_upgrade/test.sh
@@ -177,18 +177,97 @@ if "$MAKE" -C "$oldsrc" installcheck-parallel; then
 		esac
 		fix_sql="$fix_sql
 				 DROP FUNCTION IF EXISTS
-					public.oldstyle_length(integer, text);	-- last in 9.6
+					public.oldstyle_length(integer, text);"	# last in 9.6 -- commit 5ded4bd21
+		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.putenv(text);"	# last in v13
+		# 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
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
+					public.#@# (pg_catalog.int8, NONE);"
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
+					public.#%# (pg_catalog.int8, NONE);"
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
+					public.!=- (pg_catalog.int8, NONE);"
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
 					public.#@%# (pg_catalog.int8, NONE);"
+
+		# commit 068503c76511cdb0080bab689662a20e86b9c845
+		case $oldpgversion in
+			10????)
+				fix_sql="$fix_sql
+					DROP TRANSFORM FOR integer LANGUAGE sql CASCADE;"
+				;;
+		esac
+
+		# commit db3af9feb19f39827e916145f88fa5eca3130cb2
+		case $oldpgversion in
+			10????)
+				fix_sql="$fix_sql
+					DROP FUNCTION boxarea(box);"
+				fix_sql="$fix_sql
+					DROP FUNCTION funny_dup17();"
+				;;
+		esac
+
+		# commit cda6a8d01d391eab45c4b3e0043a1b2b31072f5f
+		case $oldpgversion in
+			10????)
+				fix_sql="$fix_sql
+					DROP TABLE abstime_tbl;"
+				fix_sql="$fix_sql
+					DROP TABLE reltime_tbl;"
+				fix_sql="$fix_sql
+					DROP TABLE tinterval_tbl;"
+				;;
+		esac
+
+		# Various things removed for v14
+		case $oldpgversion in
+			906??|10????|11????|12????|13????)
+				fix_sql="$fix_sql
+					DROP AGGREGATE first_el_agg_any(anyelement);"
+				;;
+		esac
+		case $oldpgversion in
+			90[56]??|10????|11????|12????|13????)
+				# commit 9e38c2bb5 and 97f73a978
+				# fix_sql="$fix_sql DROP AGGREGATE array_larger_accum(anyarray);"
+				fix_sql="$fix_sql
+					DROP AGGREGATE array_cat_accum(anyarray);"
+
+				# commit 76f412ab3
+				#fix_sql="$fix_sql DROP OPERATOR @#@(bigint,NONE);"
+				fix_sql="$fix_sql
+					DROP OPERATOR @#@(NONE,bigint);"
+				;;
+		esac
+
+		# commit 578b22971: OIDS removed in v12
+		case $oldpgversion in
+			804??|9????|10????|11????)
+				fix_sql="$fix_sql
+					ALTER TABLE public.tenk1 SET WITHOUT OIDS;"
+				fix_sql="$fix_sql
+					ALTER TABLE public.tenk1 SET WITHOUT OIDS;"
+				#fix_sql="$fix_sql ALTER TABLE public.stud_emp SET WITHOUT OIDS;" # inherited
+				fix_sql="$fix_sql
+					ALTER TABLE public.emp SET WITHOUT OIDS;"
+				fix_sql="$fix_sql
+					ALTER TABLE public.tt7 SET WITHOUT OIDS;"
+				;;
+		esac
+
 		psql -X -d regression -c "$fix_sql;" || psql_fix_sql_status=$?
 	fi
 
-	pg_dumpall --no-sync -f "$temp_root"/dump1.sql || pg_dumpall1_status=$?
+	echo "fix_sql: $oldpgversion: $fix_sql" >&2
+	pg_dumpall --extra-float-digits=0 --no-sync -f "$temp_root"/dump1.sql || pg_dumpall1_status=$?
 
 	if [ "$newsrc" != "$oldsrc" ]; then
 		# update references to old source tree's regress.so etc
-- 
2.17.0


--kadn00tgSopKmJ1H
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
 filename="v4-0002-More-changes-needed-to-allow-upgrade-testing.patch"



^ permalink  raw  reply  [nested|flat] 31+ messages in thread

* [PATCH v4 1/3] WIP: pg_upgrade/test.sh: changes needed to allow testing upgrade to v14dev from v9.5-v13
@ 2020-12-06 04:31  Justin Pryzby <[email protected]>
  0 siblings, 0 replies; 31+ messages in thread

From: Justin Pryzby @ 2020-12-06 04:31 UTC (permalink / raw)

---
 src/bin/pg_upgrade/test.sh | 93 +++++++++++++++++++++++++++++++++++---
 1 file changed, 86 insertions(+), 7 deletions(-)

diff --git a/src/bin/pg_upgrade/test.sh b/src/bin/pg_upgrade/test.sh
index ca923ba01b..b36fca4233 100644
--- a/src/bin/pg_upgrade/test.sh
+++ b/src/bin/pg_upgrade/test.sh
@@ -177,18 +177,97 @@ if "$MAKE" -C "$oldsrc" installcheck-parallel; then
 		esac
 		fix_sql="$fix_sql
 				 DROP FUNCTION IF EXISTS
-					public.oldstyle_length(integer, text);	-- last in 9.6
+					public.oldstyle_length(integer, text);"	# last in 9.6 -- commit 5ded4bd21
+		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.putenv(text);"	# last in v13
+		# 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
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
+					public.#@# (pg_catalog.int8, NONE);"
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
+					public.#%# (pg_catalog.int8, NONE);"
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
+					public.!=- (pg_catalog.int8, NONE);"
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
 					public.#@%# (pg_catalog.int8, NONE);"
+
+		# commit 068503c76511cdb0080bab689662a20e86b9c845
+		case $oldpgversion in
+			10????)
+				fix_sql="$fix_sql
+					DROP TRANSFORM FOR integer LANGUAGE sql CASCADE;"
+				;;
+		esac
+
+		# commit db3af9feb19f39827e916145f88fa5eca3130cb2
+		case $oldpgversion in
+			10????)
+				fix_sql="$fix_sql
+					DROP FUNCTION boxarea(box);"
+				fix_sql="$fix_sql
+					DROP FUNCTION funny_dup17();"
+				;;
+		esac
+
+		# commit cda6a8d01d391eab45c4b3e0043a1b2b31072f5f
+		case $oldpgversion in
+			10????)
+				fix_sql="$fix_sql
+					DROP TABLE abstime_tbl;"
+				fix_sql="$fix_sql
+					DROP TABLE reltime_tbl;"
+				fix_sql="$fix_sql
+					DROP TABLE tinterval_tbl;"
+				;;
+		esac
+
+		# Various things removed for v14
+		case $oldpgversion in
+			906??|10????|11????|12????|13????)
+				fix_sql="$fix_sql
+					DROP AGGREGATE first_el_agg_any(anyelement);"
+				;;
+		esac
+		case $oldpgversion in
+			90[56]??|10????|11????|12????|13????)
+				# commit 9e38c2bb5 and 97f73a978
+				# fix_sql="$fix_sql DROP AGGREGATE array_larger_accum(anyarray);"
+				fix_sql="$fix_sql
+					DROP AGGREGATE array_cat_accum(anyarray);"
+
+				# commit 76f412ab3
+				#fix_sql="$fix_sql DROP OPERATOR @#@(bigint,NONE);"
+				fix_sql="$fix_sql
+					DROP OPERATOR @#@(NONE,bigint);"
+				;;
+		esac
+
+		# commit 578b22971: OIDS removed in v12
+		case $oldpgversion in
+			804??|9????|10????|11????)
+				fix_sql="$fix_sql
+					ALTER TABLE public.tenk1 SET WITHOUT OIDS;"
+				fix_sql="$fix_sql
+					ALTER TABLE public.tenk1 SET WITHOUT OIDS;"
+				#fix_sql="$fix_sql ALTER TABLE public.stud_emp SET WITHOUT OIDS;" # inherited
+				fix_sql="$fix_sql
+					ALTER TABLE public.emp SET WITHOUT OIDS;"
+				fix_sql="$fix_sql
+					ALTER TABLE public.tt7 SET WITHOUT OIDS;"
+				;;
+		esac
+
 		psql -X -d regression -c "$fix_sql;" || psql_fix_sql_status=$?
 	fi
 
-	pg_dumpall --no-sync -f "$temp_root"/dump1.sql || pg_dumpall1_status=$?
+	echo "fix_sql: $oldpgversion: $fix_sql" >&2
+	pg_dumpall --extra-float-digits=0 --no-sync -f "$temp_root"/dump1.sql || pg_dumpall1_status=$?
 
 	if [ "$newsrc" != "$oldsrc" ]; then
 		# update references to old source tree's regress.so etc
-- 
2.17.0


--kadn00tgSopKmJ1H
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
 filename="v4-0002-More-changes-needed-to-allow-upgrade-testing.patch"



^ permalink  raw  reply  [nested|flat] 31+ messages in thread

* [PATCH v4 1/3] WIP: pg_upgrade/test.sh: changes needed to allow testing upgrade to v14dev from v9.5-v13
@ 2020-12-06 04:31  Justin Pryzby <[email protected]>
  0 siblings, 0 replies; 31+ messages in thread

From: Justin Pryzby @ 2020-12-06 04:31 UTC (permalink / raw)

---
 src/bin/pg_upgrade/test.sh | 93 +++++++++++++++++++++++++++++++++++---
 1 file changed, 86 insertions(+), 7 deletions(-)

diff --git a/src/bin/pg_upgrade/test.sh b/src/bin/pg_upgrade/test.sh
index ca923ba01b..b36fca4233 100644
--- a/src/bin/pg_upgrade/test.sh
+++ b/src/bin/pg_upgrade/test.sh
@@ -177,18 +177,97 @@ if "$MAKE" -C "$oldsrc" installcheck-parallel; then
 		esac
 		fix_sql="$fix_sql
 				 DROP FUNCTION IF EXISTS
-					public.oldstyle_length(integer, text);	-- last in 9.6
+					public.oldstyle_length(integer, text);"	# last in 9.6 -- commit 5ded4bd21
+		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.putenv(text);"	# last in v13
+		# 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
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
+					public.#@# (pg_catalog.int8, NONE);"
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
+					public.#%# (pg_catalog.int8, NONE);"
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
+					public.!=- (pg_catalog.int8, NONE);"
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
 					public.#@%# (pg_catalog.int8, NONE);"
+
+		# commit 068503c76511cdb0080bab689662a20e86b9c845
+		case $oldpgversion in
+			10????)
+				fix_sql="$fix_sql
+					DROP TRANSFORM FOR integer LANGUAGE sql CASCADE;"
+				;;
+		esac
+
+		# commit db3af9feb19f39827e916145f88fa5eca3130cb2
+		case $oldpgversion in
+			10????)
+				fix_sql="$fix_sql
+					DROP FUNCTION boxarea(box);"
+				fix_sql="$fix_sql
+					DROP FUNCTION funny_dup17();"
+				;;
+		esac
+
+		# commit cda6a8d01d391eab45c4b3e0043a1b2b31072f5f
+		case $oldpgversion in
+			10????)
+				fix_sql="$fix_sql
+					DROP TABLE abstime_tbl;"
+				fix_sql="$fix_sql
+					DROP TABLE reltime_tbl;"
+				fix_sql="$fix_sql
+					DROP TABLE tinterval_tbl;"
+				;;
+		esac
+
+		# Various things removed for v14
+		case $oldpgversion in
+			906??|10????|11????|12????|13????)
+				fix_sql="$fix_sql
+					DROP AGGREGATE first_el_agg_any(anyelement);"
+				;;
+		esac
+		case $oldpgversion in
+			90[56]??|10????|11????|12????|13????)
+				# commit 9e38c2bb5 and 97f73a978
+				# fix_sql="$fix_sql DROP AGGREGATE array_larger_accum(anyarray);"
+				fix_sql="$fix_sql
+					DROP AGGREGATE array_cat_accum(anyarray);"
+
+				# commit 76f412ab3
+				#fix_sql="$fix_sql DROP OPERATOR @#@(bigint,NONE);"
+				fix_sql="$fix_sql
+					DROP OPERATOR @#@(NONE,bigint);"
+				;;
+		esac
+
+		# commit 578b22971: OIDS removed in v12
+		case $oldpgversion in
+			804??|9????|10????|11????)
+				fix_sql="$fix_sql
+					ALTER TABLE public.tenk1 SET WITHOUT OIDS;"
+				fix_sql="$fix_sql
+					ALTER TABLE public.tenk1 SET WITHOUT OIDS;"
+				#fix_sql="$fix_sql ALTER TABLE public.stud_emp SET WITHOUT OIDS;" # inherited
+				fix_sql="$fix_sql
+					ALTER TABLE public.emp SET WITHOUT OIDS;"
+				fix_sql="$fix_sql
+					ALTER TABLE public.tt7 SET WITHOUT OIDS;"
+				;;
+		esac
+
 		psql -X -d regression -c "$fix_sql;" || psql_fix_sql_status=$?
 	fi
 
-	pg_dumpall --no-sync -f "$temp_root"/dump1.sql || pg_dumpall1_status=$?
+	echo "fix_sql: $oldpgversion: $fix_sql" >&2
+	pg_dumpall --extra-float-digits=0 --no-sync -f "$temp_root"/dump1.sql || pg_dumpall1_status=$?
 
 	if [ "$newsrc" != "$oldsrc" ]; then
 		# update references to old source tree's regress.so etc
-- 
2.17.0


--kadn00tgSopKmJ1H
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
 filename="v4-0002-More-changes-needed-to-allow-upgrade-testing.patch"



^ permalink  raw  reply  [nested|flat] 31+ messages in thread

* [PATCH v4 1/3] WIP: pg_upgrade/test.sh: changes needed to allow testing upgrade to v14dev from v9.5-v13
@ 2020-12-06 04:31  Justin Pryzby <[email protected]>
  0 siblings, 0 replies; 31+ messages in thread

From: Justin Pryzby @ 2020-12-06 04:31 UTC (permalink / raw)

---
 src/bin/pg_upgrade/test.sh | 93 +++++++++++++++++++++++++++++++++++---
 1 file changed, 86 insertions(+), 7 deletions(-)

diff --git a/src/bin/pg_upgrade/test.sh b/src/bin/pg_upgrade/test.sh
index ca923ba01b..b36fca4233 100644
--- a/src/bin/pg_upgrade/test.sh
+++ b/src/bin/pg_upgrade/test.sh
@@ -177,18 +177,97 @@ if "$MAKE" -C "$oldsrc" installcheck-parallel; then
 		esac
 		fix_sql="$fix_sql
 				 DROP FUNCTION IF EXISTS
-					public.oldstyle_length(integer, text);	-- last in 9.6
+					public.oldstyle_length(integer, text);"	# last in 9.6 -- commit 5ded4bd21
+		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.putenv(text);"	# last in v13
+		# 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
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
+					public.#@# (pg_catalog.int8, NONE);"
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
+					public.#%# (pg_catalog.int8, NONE);"
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
+					public.!=- (pg_catalog.int8, NONE);"
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
 					public.#@%# (pg_catalog.int8, NONE);"
+
+		# commit 068503c76511cdb0080bab689662a20e86b9c845
+		case $oldpgversion in
+			10????)
+				fix_sql="$fix_sql
+					DROP TRANSFORM FOR integer LANGUAGE sql CASCADE;"
+				;;
+		esac
+
+		# commit db3af9feb19f39827e916145f88fa5eca3130cb2
+		case $oldpgversion in
+			10????)
+				fix_sql="$fix_sql
+					DROP FUNCTION boxarea(box);"
+				fix_sql="$fix_sql
+					DROP FUNCTION funny_dup17();"
+				;;
+		esac
+
+		# commit cda6a8d01d391eab45c4b3e0043a1b2b31072f5f
+		case $oldpgversion in
+			10????)
+				fix_sql="$fix_sql
+					DROP TABLE abstime_tbl;"
+				fix_sql="$fix_sql
+					DROP TABLE reltime_tbl;"
+				fix_sql="$fix_sql
+					DROP TABLE tinterval_tbl;"
+				;;
+		esac
+
+		# Various things removed for v14
+		case $oldpgversion in
+			906??|10????|11????|12????|13????)
+				fix_sql="$fix_sql
+					DROP AGGREGATE first_el_agg_any(anyelement);"
+				;;
+		esac
+		case $oldpgversion in
+			90[56]??|10????|11????|12????|13????)
+				# commit 9e38c2bb5 and 97f73a978
+				# fix_sql="$fix_sql DROP AGGREGATE array_larger_accum(anyarray);"
+				fix_sql="$fix_sql
+					DROP AGGREGATE array_cat_accum(anyarray);"
+
+				# commit 76f412ab3
+				#fix_sql="$fix_sql DROP OPERATOR @#@(bigint,NONE);"
+				fix_sql="$fix_sql
+					DROP OPERATOR @#@(NONE,bigint);"
+				;;
+		esac
+
+		# commit 578b22971: OIDS removed in v12
+		case $oldpgversion in
+			804??|9????|10????|11????)
+				fix_sql="$fix_sql
+					ALTER TABLE public.tenk1 SET WITHOUT OIDS;"
+				fix_sql="$fix_sql
+					ALTER TABLE public.tenk1 SET WITHOUT OIDS;"
+				#fix_sql="$fix_sql ALTER TABLE public.stud_emp SET WITHOUT OIDS;" # inherited
+				fix_sql="$fix_sql
+					ALTER TABLE public.emp SET WITHOUT OIDS;"
+				fix_sql="$fix_sql
+					ALTER TABLE public.tt7 SET WITHOUT OIDS;"
+				;;
+		esac
+
 		psql -X -d regression -c "$fix_sql;" || psql_fix_sql_status=$?
 	fi
 
-	pg_dumpall --no-sync -f "$temp_root"/dump1.sql || pg_dumpall1_status=$?
+	echo "fix_sql: $oldpgversion: $fix_sql" >&2
+	pg_dumpall --extra-float-digits=0 --no-sync -f "$temp_root"/dump1.sql || pg_dumpall1_status=$?
 
 	if [ "$newsrc" != "$oldsrc" ]; then
 		# update references to old source tree's regress.so etc
-- 
2.17.0


--kadn00tgSopKmJ1H
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
 filename="v4-0002-More-changes-needed-to-allow-upgrade-testing.patch"



^ permalink  raw  reply  [nested|flat] 31+ messages in thread

* [PATCH v4 1/3] WIP: pg_upgrade/test.sh: changes needed to allow testing upgrade to v14dev from v9.5-v13
@ 2020-12-06 04:31  Justin Pryzby <[email protected]>
  0 siblings, 0 replies; 31+ messages in thread

From: Justin Pryzby @ 2020-12-06 04:31 UTC (permalink / raw)

---
 src/bin/pg_upgrade/test.sh | 93 +++++++++++++++++++++++++++++++++++---
 1 file changed, 86 insertions(+), 7 deletions(-)

diff --git a/src/bin/pg_upgrade/test.sh b/src/bin/pg_upgrade/test.sh
index ca923ba01b..b36fca4233 100644
--- a/src/bin/pg_upgrade/test.sh
+++ b/src/bin/pg_upgrade/test.sh
@@ -177,18 +177,97 @@ if "$MAKE" -C "$oldsrc" installcheck-parallel; then
 		esac
 		fix_sql="$fix_sql
 				 DROP FUNCTION IF EXISTS
-					public.oldstyle_length(integer, text);	-- last in 9.6
+					public.oldstyle_length(integer, text);"	# last in 9.6 -- commit 5ded4bd21
+		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.putenv(text);"	# last in v13
+		# 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
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
+					public.#@# (pg_catalog.int8, NONE);"
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
+					public.#%# (pg_catalog.int8, NONE);"
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
+					public.!=- (pg_catalog.int8, NONE);"
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
 					public.#@%# (pg_catalog.int8, NONE);"
+
+		# commit 068503c76511cdb0080bab689662a20e86b9c845
+		case $oldpgversion in
+			10????)
+				fix_sql="$fix_sql
+					DROP TRANSFORM FOR integer LANGUAGE sql CASCADE;"
+				;;
+		esac
+
+		# commit db3af9feb19f39827e916145f88fa5eca3130cb2
+		case $oldpgversion in
+			10????)
+				fix_sql="$fix_sql
+					DROP FUNCTION boxarea(box);"
+				fix_sql="$fix_sql
+					DROP FUNCTION funny_dup17();"
+				;;
+		esac
+
+		# commit cda6a8d01d391eab45c4b3e0043a1b2b31072f5f
+		case $oldpgversion in
+			10????)
+				fix_sql="$fix_sql
+					DROP TABLE abstime_tbl;"
+				fix_sql="$fix_sql
+					DROP TABLE reltime_tbl;"
+				fix_sql="$fix_sql
+					DROP TABLE tinterval_tbl;"
+				;;
+		esac
+
+		# Various things removed for v14
+		case $oldpgversion in
+			906??|10????|11????|12????|13????)
+				fix_sql="$fix_sql
+					DROP AGGREGATE first_el_agg_any(anyelement);"
+				;;
+		esac
+		case $oldpgversion in
+			90[56]??|10????|11????|12????|13????)
+				# commit 9e38c2bb5 and 97f73a978
+				# fix_sql="$fix_sql DROP AGGREGATE array_larger_accum(anyarray);"
+				fix_sql="$fix_sql
+					DROP AGGREGATE array_cat_accum(anyarray);"
+
+				# commit 76f412ab3
+				#fix_sql="$fix_sql DROP OPERATOR @#@(bigint,NONE);"
+				fix_sql="$fix_sql
+					DROP OPERATOR @#@(NONE,bigint);"
+				;;
+		esac
+
+		# commit 578b22971: OIDS removed in v12
+		case $oldpgversion in
+			804??|9????|10????|11????)
+				fix_sql="$fix_sql
+					ALTER TABLE public.tenk1 SET WITHOUT OIDS;"
+				fix_sql="$fix_sql
+					ALTER TABLE public.tenk1 SET WITHOUT OIDS;"
+				#fix_sql="$fix_sql ALTER TABLE public.stud_emp SET WITHOUT OIDS;" # inherited
+				fix_sql="$fix_sql
+					ALTER TABLE public.emp SET WITHOUT OIDS;"
+				fix_sql="$fix_sql
+					ALTER TABLE public.tt7 SET WITHOUT OIDS;"
+				;;
+		esac
+
 		psql -X -d regression -c "$fix_sql;" || psql_fix_sql_status=$?
 	fi
 
-	pg_dumpall --no-sync -f "$temp_root"/dump1.sql || pg_dumpall1_status=$?
+	echo "fix_sql: $oldpgversion: $fix_sql" >&2
+	pg_dumpall --extra-float-digits=0 --no-sync -f "$temp_root"/dump1.sql || pg_dumpall1_status=$?
 
 	if [ "$newsrc" != "$oldsrc" ]; then
 		# update references to old source tree's regress.so etc
-- 
2.17.0


--kadn00tgSopKmJ1H
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
 filename="v4-0002-More-changes-needed-to-allow-upgrade-testing.patch"



^ permalink  raw  reply  [nested|flat] 31+ messages in thread

* [PATCH v4 1/3] WIP: pg_upgrade/test.sh: changes needed to allow testing upgrade to v14dev from v9.5-v13
@ 2020-12-06 04:31  Justin Pryzby <[email protected]>
  0 siblings, 0 replies; 31+ messages in thread

From: Justin Pryzby @ 2020-12-06 04:31 UTC (permalink / raw)

---
 src/bin/pg_upgrade/test.sh | 93 +++++++++++++++++++++++++++++++++++---
 1 file changed, 86 insertions(+), 7 deletions(-)

diff --git a/src/bin/pg_upgrade/test.sh b/src/bin/pg_upgrade/test.sh
index ca923ba01b..b36fca4233 100644
--- a/src/bin/pg_upgrade/test.sh
+++ b/src/bin/pg_upgrade/test.sh
@@ -177,18 +177,97 @@ if "$MAKE" -C "$oldsrc" installcheck-parallel; then
 		esac
 		fix_sql="$fix_sql
 				 DROP FUNCTION IF EXISTS
-					public.oldstyle_length(integer, text);	-- last in 9.6
+					public.oldstyle_length(integer, text);"	# last in 9.6 -- commit 5ded4bd21
+		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.putenv(text);"	# last in v13
+		# 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
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
+					public.#@# (pg_catalog.int8, NONE);"
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
+					public.#%# (pg_catalog.int8, NONE);"
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
+					public.!=- (pg_catalog.int8, NONE);"
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
 					public.#@%# (pg_catalog.int8, NONE);"
+
+		# commit 068503c76511cdb0080bab689662a20e86b9c845
+		case $oldpgversion in
+			10????)
+				fix_sql="$fix_sql
+					DROP TRANSFORM FOR integer LANGUAGE sql CASCADE;"
+				;;
+		esac
+
+		# commit db3af9feb19f39827e916145f88fa5eca3130cb2
+		case $oldpgversion in
+			10????)
+				fix_sql="$fix_sql
+					DROP FUNCTION boxarea(box);"
+				fix_sql="$fix_sql
+					DROP FUNCTION funny_dup17();"
+				;;
+		esac
+
+		# commit cda6a8d01d391eab45c4b3e0043a1b2b31072f5f
+		case $oldpgversion in
+			10????)
+				fix_sql="$fix_sql
+					DROP TABLE abstime_tbl;"
+				fix_sql="$fix_sql
+					DROP TABLE reltime_tbl;"
+				fix_sql="$fix_sql
+					DROP TABLE tinterval_tbl;"
+				;;
+		esac
+
+		# Various things removed for v14
+		case $oldpgversion in
+			906??|10????|11????|12????|13????)
+				fix_sql="$fix_sql
+					DROP AGGREGATE first_el_agg_any(anyelement);"
+				;;
+		esac
+		case $oldpgversion in
+			90[56]??|10????|11????|12????|13????)
+				# commit 9e38c2bb5 and 97f73a978
+				# fix_sql="$fix_sql DROP AGGREGATE array_larger_accum(anyarray);"
+				fix_sql="$fix_sql
+					DROP AGGREGATE array_cat_accum(anyarray);"
+
+				# commit 76f412ab3
+				#fix_sql="$fix_sql DROP OPERATOR @#@(bigint,NONE);"
+				fix_sql="$fix_sql
+					DROP OPERATOR @#@(NONE,bigint);"
+				;;
+		esac
+
+		# commit 578b22971: OIDS removed in v12
+		case $oldpgversion in
+			804??|9????|10????|11????)
+				fix_sql="$fix_sql
+					ALTER TABLE public.tenk1 SET WITHOUT OIDS;"
+				fix_sql="$fix_sql
+					ALTER TABLE public.tenk1 SET WITHOUT OIDS;"
+				#fix_sql="$fix_sql ALTER TABLE public.stud_emp SET WITHOUT OIDS;" # inherited
+				fix_sql="$fix_sql
+					ALTER TABLE public.emp SET WITHOUT OIDS;"
+				fix_sql="$fix_sql
+					ALTER TABLE public.tt7 SET WITHOUT OIDS;"
+				;;
+		esac
+
 		psql -X -d regression -c "$fix_sql;" || psql_fix_sql_status=$?
 	fi
 
-	pg_dumpall --no-sync -f "$temp_root"/dump1.sql || pg_dumpall1_status=$?
+	echo "fix_sql: $oldpgversion: $fix_sql" >&2
+	pg_dumpall --extra-float-digits=0 --no-sync -f "$temp_root"/dump1.sql || pg_dumpall1_status=$?
 
 	if [ "$newsrc" != "$oldsrc" ]; then
 		# update references to old source tree's regress.so etc
-- 
2.17.0


--kadn00tgSopKmJ1H
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
 filename="v4-0002-More-changes-needed-to-allow-upgrade-testing.patch"



^ permalink  raw  reply  [nested|flat] 31+ messages in thread

* [PATCH v4 1/3] WIP: pg_upgrade/test.sh: changes needed to allow testing upgrade to v14dev from v9.5-v13
@ 2020-12-06 04:31  Justin Pryzby <[email protected]>
  0 siblings, 0 replies; 31+ messages in thread

From: Justin Pryzby @ 2020-12-06 04:31 UTC (permalink / raw)

---
 src/bin/pg_upgrade/test.sh | 93 +++++++++++++++++++++++++++++++++++---
 1 file changed, 86 insertions(+), 7 deletions(-)

diff --git a/src/bin/pg_upgrade/test.sh b/src/bin/pg_upgrade/test.sh
index ca923ba01b..b36fca4233 100644
--- a/src/bin/pg_upgrade/test.sh
+++ b/src/bin/pg_upgrade/test.sh
@@ -177,18 +177,97 @@ if "$MAKE" -C "$oldsrc" installcheck-parallel; then
 		esac
 		fix_sql="$fix_sql
 				 DROP FUNCTION IF EXISTS
-					public.oldstyle_length(integer, text);	-- last in 9.6
+					public.oldstyle_length(integer, text);"	# last in 9.6 -- commit 5ded4bd21
+		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.putenv(text);"	# last in v13
+		# 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
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
+					public.#@# (pg_catalog.int8, NONE);"
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
+					public.#%# (pg_catalog.int8, NONE);"
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
+					public.!=- (pg_catalog.int8, NONE);"
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
 					public.#@%# (pg_catalog.int8, NONE);"
+
+		# commit 068503c76511cdb0080bab689662a20e86b9c845
+		case $oldpgversion in
+			10????)
+				fix_sql="$fix_sql
+					DROP TRANSFORM FOR integer LANGUAGE sql CASCADE;"
+				;;
+		esac
+
+		# commit db3af9feb19f39827e916145f88fa5eca3130cb2
+		case $oldpgversion in
+			10????)
+				fix_sql="$fix_sql
+					DROP FUNCTION boxarea(box);"
+				fix_sql="$fix_sql
+					DROP FUNCTION funny_dup17();"
+				;;
+		esac
+
+		# commit cda6a8d01d391eab45c4b3e0043a1b2b31072f5f
+		case $oldpgversion in
+			10????)
+				fix_sql="$fix_sql
+					DROP TABLE abstime_tbl;"
+				fix_sql="$fix_sql
+					DROP TABLE reltime_tbl;"
+				fix_sql="$fix_sql
+					DROP TABLE tinterval_tbl;"
+				;;
+		esac
+
+		# Various things removed for v14
+		case $oldpgversion in
+			906??|10????|11????|12????|13????)
+				fix_sql="$fix_sql
+					DROP AGGREGATE first_el_agg_any(anyelement);"
+				;;
+		esac
+		case $oldpgversion in
+			90[56]??|10????|11????|12????|13????)
+				# commit 9e38c2bb5 and 97f73a978
+				# fix_sql="$fix_sql DROP AGGREGATE array_larger_accum(anyarray);"
+				fix_sql="$fix_sql
+					DROP AGGREGATE array_cat_accum(anyarray);"
+
+				# commit 76f412ab3
+				#fix_sql="$fix_sql DROP OPERATOR @#@(bigint,NONE);"
+				fix_sql="$fix_sql
+					DROP OPERATOR @#@(NONE,bigint);"
+				;;
+		esac
+
+		# commit 578b22971: OIDS removed in v12
+		case $oldpgversion in
+			804??|9????|10????|11????)
+				fix_sql="$fix_sql
+					ALTER TABLE public.tenk1 SET WITHOUT OIDS;"
+				fix_sql="$fix_sql
+					ALTER TABLE public.tenk1 SET WITHOUT OIDS;"
+				#fix_sql="$fix_sql ALTER TABLE public.stud_emp SET WITHOUT OIDS;" # inherited
+				fix_sql="$fix_sql
+					ALTER TABLE public.emp SET WITHOUT OIDS;"
+				fix_sql="$fix_sql
+					ALTER TABLE public.tt7 SET WITHOUT OIDS;"
+				;;
+		esac
+
 		psql -X -d regression -c "$fix_sql;" || psql_fix_sql_status=$?
 	fi
 
-	pg_dumpall --no-sync -f "$temp_root"/dump1.sql || pg_dumpall1_status=$?
+	echo "fix_sql: $oldpgversion: $fix_sql" >&2
+	pg_dumpall --extra-float-digits=0 --no-sync -f "$temp_root"/dump1.sql || pg_dumpall1_status=$?
 
 	if [ "$newsrc" != "$oldsrc" ]; then
 		# update references to old source tree's regress.so etc
-- 
2.17.0


--kadn00tgSopKmJ1H
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
 filename="v4-0002-More-changes-needed-to-allow-upgrade-testing.patch"



^ permalink  raw  reply  [nested|flat] 31+ messages in thread

* [PATCH v4 1/3] WIP: pg_upgrade/test.sh: changes needed to allow testing upgrade to v14dev from v9.5-v13
@ 2020-12-06 04:31  Justin Pryzby <[email protected]>
  0 siblings, 0 replies; 31+ messages in thread

From: Justin Pryzby @ 2020-12-06 04:31 UTC (permalink / raw)

---
 src/bin/pg_upgrade/test.sh | 93 +++++++++++++++++++++++++++++++++++---
 1 file changed, 86 insertions(+), 7 deletions(-)

diff --git a/src/bin/pg_upgrade/test.sh b/src/bin/pg_upgrade/test.sh
index ca923ba01b..b36fca4233 100644
--- a/src/bin/pg_upgrade/test.sh
+++ b/src/bin/pg_upgrade/test.sh
@@ -177,18 +177,97 @@ if "$MAKE" -C "$oldsrc" installcheck-parallel; then
 		esac
 		fix_sql="$fix_sql
 				 DROP FUNCTION IF EXISTS
-					public.oldstyle_length(integer, text);	-- last in 9.6
+					public.oldstyle_length(integer, text);"	# last in 9.6 -- commit 5ded4bd21
+		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.putenv(text);"	# last in v13
+		# 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
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
+					public.#@# (pg_catalog.int8, NONE);"
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
+					public.#%# (pg_catalog.int8, NONE);"
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
+					public.!=- (pg_catalog.int8, NONE);"
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
 					public.#@%# (pg_catalog.int8, NONE);"
+
+		# commit 068503c76511cdb0080bab689662a20e86b9c845
+		case $oldpgversion in
+			10????)
+				fix_sql="$fix_sql
+					DROP TRANSFORM FOR integer LANGUAGE sql CASCADE;"
+				;;
+		esac
+
+		# commit db3af9feb19f39827e916145f88fa5eca3130cb2
+		case $oldpgversion in
+			10????)
+				fix_sql="$fix_sql
+					DROP FUNCTION boxarea(box);"
+				fix_sql="$fix_sql
+					DROP FUNCTION funny_dup17();"
+				;;
+		esac
+
+		# commit cda6a8d01d391eab45c4b3e0043a1b2b31072f5f
+		case $oldpgversion in
+			10????)
+				fix_sql="$fix_sql
+					DROP TABLE abstime_tbl;"
+				fix_sql="$fix_sql
+					DROP TABLE reltime_tbl;"
+				fix_sql="$fix_sql
+					DROP TABLE tinterval_tbl;"
+				;;
+		esac
+
+		# Various things removed for v14
+		case $oldpgversion in
+			906??|10????|11????|12????|13????)
+				fix_sql="$fix_sql
+					DROP AGGREGATE first_el_agg_any(anyelement);"
+				;;
+		esac
+		case $oldpgversion in
+			90[56]??|10????|11????|12????|13????)
+				# commit 9e38c2bb5 and 97f73a978
+				# fix_sql="$fix_sql DROP AGGREGATE array_larger_accum(anyarray);"
+				fix_sql="$fix_sql
+					DROP AGGREGATE array_cat_accum(anyarray);"
+
+				# commit 76f412ab3
+				#fix_sql="$fix_sql DROP OPERATOR @#@(bigint,NONE);"
+				fix_sql="$fix_sql
+					DROP OPERATOR @#@(NONE,bigint);"
+				;;
+		esac
+
+		# commit 578b22971: OIDS removed in v12
+		case $oldpgversion in
+			804??|9????|10????|11????)
+				fix_sql="$fix_sql
+					ALTER TABLE public.tenk1 SET WITHOUT OIDS;"
+				fix_sql="$fix_sql
+					ALTER TABLE public.tenk1 SET WITHOUT OIDS;"
+				#fix_sql="$fix_sql ALTER TABLE public.stud_emp SET WITHOUT OIDS;" # inherited
+				fix_sql="$fix_sql
+					ALTER TABLE public.emp SET WITHOUT OIDS;"
+				fix_sql="$fix_sql
+					ALTER TABLE public.tt7 SET WITHOUT OIDS;"
+				;;
+		esac
+
 		psql -X -d regression -c "$fix_sql;" || psql_fix_sql_status=$?
 	fi
 
-	pg_dumpall --no-sync -f "$temp_root"/dump1.sql || pg_dumpall1_status=$?
+	echo "fix_sql: $oldpgversion: $fix_sql" >&2
+	pg_dumpall --extra-float-digits=0 --no-sync -f "$temp_root"/dump1.sql || pg_dumpall1_status=$?
 
 	if [ "$newsrc" != "$oldsrc" ]; then
 		# update references to old source tree's regress.so etc
-- 
2.17.0


--kadn00tgSopKmJ1H
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
 filename="v4-0002-More-changes-needed-to-allow-upgrade-testing.patch"



^ permalink  raw  reply  [nested|flat] 31+ messages in thread

* [PATCH v4 1/3] WIP: pg_upgrade/test.sh: changes needed to allow testing upgrade to v14dev from v9.5-v13
@ 2020-12-06 04:31  Justin Pryzby <[email protected]>
  0 siblings, 0 replies; 31+ messages in thread

From: Justin Pryzby @ 2020-12-06 04:31 UTC (permalink / raw)

---
 src/bin/pg_upgrade/test.sh | 93 +++++++++++++++++++++++++++++++++++---
 1 file changed, 86 insertions(+), 7 deletions(-)

diff --git a/src/bin/pg_upgrade/test.sh b/src/bin/pg_upgrade/test.sh
index ca923ba01b..b36fca4233 100644
--- a/src/bin/pg_upgrade/test.sh
+++ b/src/bin/pg_upgrade/test.sh
@@ -177,18 +177,97 @@ if "$MAKE" -C "$oldsrc" installcheck-parallel; then
 		esac
 		fix_sql="$fix_sql
 				 DROP FUNCTION IF EXISTS
-					public.oldstyle_length(integer, text);	-- last in 9.6
+					public.oldstyle_length(integer, text);"	# last in 9.6 -- commit 5ded4bd21
+		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.putenv(text);"	# last in v13
+		# 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
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
+					public.#@# (pg_catalog.int8, NONE);"
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
+					public.#%# (pg_catalog.int8, NONE);"
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
+					public.!=- (pg_catalog.int8, NONE);"
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
 					public.#@%# (pg_catalog.int8, NONE);"
+
+		# commit 068503c76511cdb0080bab689662a20e86b9c845
+		case $oldpgversion in
+			10????)
+				fix_sql="$fix_sql
+					DROP TRANSFORM FOR integer LANGUAGE sql CASCADE;"
+				;;
+		esac
+
+		# commit db3af9feb19f39827e916145f88fa5eca3130cb2
+		case $oldpgversion in
+			10????)
+				fix_sql="$fix_sql
+					DROP FUNCTION boxarea(box);"
+				fix_sql="$fix_sql
+					DROP FUNCTION funny_dup17();"
+				;;
+		esac
+
+		# commit cda6a8d01d391eab45c4b3e0043a1b2b31072f5f
+		case $oldpgversion in
+			10????)
+				fix_sql="$fix_sql
+					DROP TABLE abstime_tbl;"
+				fix_sql="$fix_sql
+					DROP TABLE reltime_tbl;"
+				fix_sql="$fix_sql
+					DROP TABLE tinterval_tbl;"
+				;;
+		esac
+
+		# Various things removed for v14
+		case $oldpgversion in
+			906??|10????|11????|12????|13????)
+				fix_sql="$fix_sql
+					DROP AGGREGATE first_el_agg_any(anyelement);"
+				;;
+		esac
+		case $oldpgversion in
+			90[56]??|10????|11????|12????|13????)
+				# commit 9e38c2bb5 and 97f73a978
+				# fix_sql="$fix_sql DROP AGGREGATE array_larger_accum(anyarray);"
+				fix_sql="$fix_sql
+					DROP AGGREGATE array_cat_accum(anyarray);"
+
+				# commit 76f412ab3
+				#fix_sql="$fix_sql DROP OPERATOR @#@(bigint,NONE);"
+				fix_sql="$fix_sql
+					DROP OPERATOR @#@(NONE,bigint);"
+				;;
+		esac
+
+		# commit 578b22971: OIDS removed in v12
+		case $oldpgversion in
+			804??|9????|10????|11????)
+				fix_sql="$fix_sql
+					ALTER TABLE public.tenk1 SET WITHOUT OIDS;"
+				fix_sql="$fix_sql
+					ALTER TABLE public.tenk1 SET WITHOUT OIDS;"
+				#fix_sql="$fix_sql ALTER TABLE public.stud_emp SET WITHOUT OIDS;" # inherited
+				fix_sql="$fix_sql
+					ALTER TABLE public.emp SET WITHOUT OIDS;"
+				fix_sql="$fix_sql
+					ALTER TABLE public.tt7 SET WITHOUT OIDS;"
+				;;
+		esac
+
 		psql -X -d regression -c "$fix_sql;" || psql_fix_sql_status=$?
 	fi
 
-	pg_dumpall --no-sync -f "$temp_root"/dump1.sql || pg_dumpall1_status=$?
+	echo "fix_sql: $oldpgversion: $fix_sql" >&2
+	pg_dumpall --extra-float-digits=0 --no-sync -f "$temp_root"/dump1.sql || pg_dumpall1_status=$?
 
 	if [ "$newsrc" != "$oldsrc" ]; then
 		# update references to old source tree's regress.so etc
-- 
2.17.0


--kadn00tgSopKmJ1H
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
 filename="v4-0002-More-changes-needed-to-allow-upgrade-testing.patch"



^ permalink  raw  reply  [nested|flat] 31+ messages in thread

* [PATCH v4 1/3] WIP: pg_upgrade/test.sh: changes needed to allow testing upgrade to v14dev from v9.5-v13
@ 2020-12-06 04:31  Justin Pryzby <[email protected]>
  0 siblings, 0 replies; 31+ messages in thread

From: Justin Pryzby @ 2020-12-06 04:31 UTC (permalink / raw)

---
 src/bin/pg_upgrade/test.sh | 93 +++++++++++++++++++++++++++++++++++---
 1 file changed, 86 insertions(+), 7 deletions(-)

diff --git a/src/bin/pg_upgrade/test.sh b/src/bin/pg_upgrade/test.sh
index ca923ba01b..b36fca4233 100644
--- a/src/bin/pg_upgrade/test.sh
+++ b/src/bin/pg_upgrade/test.sh
@@ -177,18 +177,97 @@ if "$MAKE" -C "$oldsrc" installcheck-parallel; then
 		esac
 		fix_sql="$fix_sql
 				 DROP FUNCTION IF EXISTS
-					public.oldstyle_length(integer, text);	-- last in 9.6
+					public.oldstyle_length(integer, text);"	# last in 9.6 -- commit 5ded4bd21
+		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.putenv(text);"	# last in v13
+		# 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
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
+					public.#@# (pg_catalog.int8, NONE);"
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
+					public.#%# (pg_catalog.int8, NONE);"
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
+					public.!=- (pg_catalog.int8, NONE);"
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
 					public.#@%# (pg_catalog.int8, NONE);"
+
+		# commit 068503c76511cdb0080bab689662a20e86b9c845
+		case $oldpgversion in
+			10????)
+				fix_sql="$fix_sql
+					DROP TRANSFORM FOR integer LANGUAGE sql CASCADE;"
+				;;
+		esac
+
+		# commit db3af9feb19f39827e916145f88fa5eca3130cb2
+		case $oldpgversion in
+			10????)
+				fix_sql="$fix_sql
+					DROP FUNCTION boxarea(box);"
+				fix_sql="$fix_sql
+					DROP FUNCTION funny_dup17();"
+				;;
+		esac
+
+		# commit cda6a8d01d391eab45c4b3e0043a1b2b31072f5f
+		case $oldpgversion in
+			10????)
+				fix_sql="$fix_sql
+					DROP TABLE abstime_tbl;"
+				fix_sql="$fix_sql
+					DROP TABLE reltime_tbl;"
+				fix_sql="$fix_sql
+					DROP TABLE tinterval_tbl;"
+				;;
+		esac
+
+		# Various things removed for v14
+		case $oldpgversion in
+			906??|10????|11????|12????|13????)
+				fix_sql="$fix_sql
+					DROP AGGREGATE first_el_agg_any(anyelement);"
+				;;
+		esac
+		case $oldpgversion in
+			90[56]??|10????|11????|12????|13????)
+				# commit 9e38c2bb5 and 97f73a978
+				# fix_sql="$fix_sql DROP AGGREGATE array_larger_accum(anyarray);"
+				fix_sql="$fix_sql
+					DROP AGGREGATE array_cat_accum(anyarray);"
+
+				# commit 76f412ab3
+				#fix_sql="$fix_sql DROP OPERATOR @#@(bigint,NONE);"
+				fix_sql="$fix_sql
+					DROP OPERATOR @#@(NONE,bigint);"
+				;;
+		esac
+
+		# commit 578b22971: OIDS removed in v12
+		case $oldpgversion in
+			804??|9????|10????|11????)
+				fix_sql="$fix_sql
+					ALTER TABLE public.tenk1 SET WITHOUT OIDS;"
+				fix_sql="$fix_sql
+					ALTER TABLE public.tenk1 SET WITHOUT OIDS;"
+				#fix_sql="$fix_sql ALTER TABLE public.stud_emp SET WITHOUT OIDS;" # inherited
+				fix_sql="$fix_sql
+					ALTER TABLE public.emp SET WITHOUT OIDS;"
+				fix_sql="$fix_sql
+					ALTER TABLE public.tt7 SET WITHOUT OIDS;"
+				;;
+		esac
+
 		psql -X -d regression -c "$fix_sql;" || psql_fix_sql_status=$?
 	fi
 
-	pg_dumpall --no-sync -f "$temp_root"/dump1.sql || pg_dumpall1_status=$?
+	echo "fix_sql: $oldpgversion: $fix_sql" >&2
+	pg_dumpall --extra-float-digits=0 --no-sync -f "$temp_root"/dump1.sql || pg_dumpall1_status=$?
 
 	if [ "$newsrc" != "$oldsrc" ]; then
 		# update references to old source tree's regress.so etc
-- 
2.17.0


--kadn00tgSopKmJ1H
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
 filename="v4-0002-More-changes-needed-to-allow-upgrade-testing.patch"



^ permalink  raw  reply  [nested|flat] 31+ messages in thread

* [PATCH v4 1/3] WIP: pg_upgrade/test.sh: changes needed to allow testing upgrade to v14dev from v9.5-v13
@ 2020-12-06 04:31  Justin Pryzby <[email protected]>
  0 siblings, 0 replies; 31+ messages in thread

From: Justin Pryzby @ 2020-12-06 04:31 UTC (permalink / raw)

---
 src/bin/pg_upgrade/test.sh | 93 +++++++++++++++++++++++++++++++++++---
 1 file changed, 86 insertions(+), 7 deletions(-)

diff --git a/src/bin/pg_upgrade/test.sh b/src/bin/pg_upgrade/test.sh
index ca923ba01b..b36fca4233 100644
--- a/src/bin/pg_upgrade/test.sh
+++ b/src/bin/pg_upgrade/test.sh
@@ -177,18 +177,97 @@ if "$MAKE" -C "$oldsrc" installcheck-parallel; then
 		esac
 		fix_sql="$fix_sql
 				 DROP FUNCTION IF EXISTS
-					public.oldstyle_length(integer, text);	-- last in 9.6
+					public.oldstyle_length(integer, text);"	# last in 9.6 -- commit 5ded4bd21
+		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.putenv(text);"	# last in v13
+		# 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
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
+					public.#@# (pg_catalog.int8, NONE);"
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
+					public.#%# (pg_catalog.int8, NONE);"
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
+					public.!=- (pg_catalog.int8, NONE);"
+		fix_sql="$fix_sql
+				 DROP OPERATOR IF EXISTS
 					public.#@%# (pg_catalog.int8, NONE);"
+
+		# commit 068503c76511cdb0080bab689662a20e86b9c845
+		case $oldpgversion in
+			10????)
+				fix_sql="$fix_sql
+					DROP TRANSFORM FOR integer LANGUAGE sql CASCADE;"
+				;;
+		esac
+
+		# commit db3af9feb19f39827e916145f88fa5eca3130cb2
+		case $oldpgversion in
+			10????)
+				fix_sql="$fix_sql
+					DROP FUNCTION boxarea(box);"
+				fix_sql="$fix_sql
+					DROP FUNCTION funny_dup17();"
+				;;
+		esac
+
+		# commit cda6a8d01d391eab45c4b3e0043a1b2b31072f5f
+		case $oldpgversion in
+			10????)
+				fix_sql="$fix_sql
+					DROP TABLE abstime_tbl;"
+				fix_sql="$fix_sql
+					DROP TABLE reltime_tbl;"
+				fix_sql="$fix_sql
+					DROP TABLE tinterval_tbl;"
+				;;
+		esac
+
+		# Various things removed for v14
+		case $oldpgversion in
+			906??|10????|11????|12????|13????)
+				fix_sql="$fix_sql
+					DROP AGGREGATE first_el_agg_any(anyelement);"
+				;;
+		esac
+		case $oldpgversion in
+			90[56]??|10????|11????|12????|13????)
+				# commit 9e38c2bb5 and 97f73a978
+				# fix_sql="$fix_sql DROP AGGREGATE array_larger_accum(anyarray);"
+				fix_sql="$fix_sql
+					DROP AGGREGATE array_cat_accum(anyarray);"
+
+				# commit 76f412ab3
+				#fix_sql="$fix_sql DROP OPERATOR @#@(bigint,NONE);"
+				fix_sql="$fix_sql
+					DROP OPERATOR @#@(NONE,bigint);"
+				;;
+		esac
+
+		# commit 578b22971: OIDS removed in v12
+		case $oldpgversion in
+			804??|9????|10????|11????)
+				fix_sql="$fix_sql
+					ALTER TABLE public.tenk1 SET WITHOUT OIDS;"
+				fix_sql="$fix_sql
+					ALTER TABLE public.tenk1 SET WITHOUT OIDS;"
+				#fix_sql="$fix_sql ALTER TABLE public.stud_emp SET WITHOUT OIDS;" # inherited
+				fix_sql="$fix_sql
+					ALTER TABLE public.emp SET WITHOUT OIDS;"
+				fix_sql="$fix_sql
+					ALTER TABLE public.tt7 SET WITHOUT OIDS;"
+				;;
+		esac
+
 		psql -X -d regression -c "$fix_sql;" || psql_fix_sql_status=$?
 	fi
 
-	pg_dumpall --no-sync -f "$temp_root"/dump1.sql || pg_dumpall1_status=$?
+	echo "fix_sql: $oldpgversion: $fix_sql" >&2
+	pg_dumpall --extra-float-digits=0 --no-sync -f "$temp_root"/dump1.sql || pg_dumpall1_status=$?
 
 	if [ "$newsrc" != "$oldsrc" ]; then
 		# update references to old source tree's regress.so etc
-- 
2.17.0


--kadn00tgSopKmJ1H
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
 filename="v4-0002-More-changes-needed-to-allow-upgrade-testing.patch"



^ permalink  raw  reply  [nested|flat] 31+ messages in thread

* Optimize IS DISTINCT FROM with non-nullable inputs
@ 2026-01-23 13:40  Richard Guo <[email protected]>
  0 siblings, 1 reply; 31+ messages in thread

From: Richard Guo @ 2026-01-23 13:40 UTC (permalink / raw)
  To: Pg Hackers <[email protected]>

Unlike ordinary comparison operators, the IS [NOT] DISTINCT FROM
predicate treats NULL as a normal data value rather than "unknown".
For non-null inputs, its semantics are identical to standard
operators: IS DISTINCT FROM is equivalent to <>, and IS NOT DISTINCT
FROM is equivalent to =.

Currently, the planner simplifies DistinctExpr only if all inputs are
constants.  I'm thinking that maybe we can optimize cases where inputs
are non-constant but proven to be non-nullable, by converting "x IS
DISTINCT FROM y" to "x <> y".  This representation exposes the
comparison to the planner as a standard operator.  If the clause is
negated (e.g. IS NOT DISTINCT FROM), the resulting "=" operator can
allow the planner to use index scans, merge joins, hash joins, and
EC-based qual deductions.

Attached is a draft patch for this optimization.

I'm kind of concerned about whether there are edge cases where this
transformation is not safe, specifically regarding "rowtype" inputs.
Any feedback would be appreciated.

- Richard


Attachments:

  [application/octet-stream] v1-0001-Optimize-IS-DISTINCT-FROM-with-non-nullable-input.patch (7.6K, ../../CAMbWs49BMAOWvkdSHxpUDnniqJcEcGq3_8dd_5wTR4xrQY8urA@mail.gmail.com/2-v1-0001-Optimize-IS-DISTINCT-FROM-with-non-nullable-input.patch)
  download | inline diff:
From 94c0d93d13d9428c5f987fb67a03365fcc3a9fea Mon Sep 17 00:00:00 2001
From: Richard Guo <[email protected]>
Date: Fri, 23 Jan 2026 12:11:42 +0900
Subject: [PATCH v1] Optimize IS DISTINCT FROM with non-nullable inputs

The IS DISTINCT FROM construct compares values acting as though NULL
were a normal data value, rather than "unknown".  Semantically, "x IS
DISTINCT FROM y" yields true if the values differ or if exactly one is
NULL, and false if they are equal or both NULL.  Unlike ordinary
comparison operators, it never returns NULL.

Previously, the planner only simplified this construct if all inputs
were constants, folding it to a constant boolean result.  This patch
extends the optimization to cases where inputs are non-constant but
proven to be non-nullable.  Specifically, "x IS DISTINCT FROM NULL"
folds to constant TRUE if "x" is known to be non-nullable.  For cases
where both inputs are guaranteed not to be NULL, the expression
becomes semantically equivalent to "x <> y", and the DistinctExpr is
converted into an inequality OpExpr.

This transformation provides several benefits.  It exposes the
comparison to the planner as a standard operator, allowing the use of
partial indexes and constraint exclusion.  Furthermore, if the clause
is negated (e.g. "IS NOT DISTINCT FROM"), it simplifies to an equality
operator.  This enables the planner to generate better plans using
index scans, merge joins, hash joins, and EC-based qual deductions.
---
 .../postgres_fdw/expected/postgres_fdw.out    |  8 +--
 contrib/postgres_fdw/sql/postgres_fdw.sql     |  2 +-
 src/backend/optimizer/util/clauses.c          | 60 ++++++++++++++++++-
 3 files changed, 63 insertions(+), 7 deletions(-)

diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 6066510c7c0..7cad5e67d09 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -698,12 +698,12 @@ EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c1 = -c1;          -- Op
    Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" = (- "C 1")))
 (3 rows)
 
-EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE (c3 IS NOT NULL) IS DISTINCT FROM (c3 IS NOT NULL); -- DistinctExpr
-                                                              QUERY PLAN                                                              
---------------------------------------------------------------------------------------------------------------------------------------
+EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c3 IS DISTINCT FROM c3; -- DistinctExpr
+                                                QUERY PLAN                                                
+----------------------------------------------------------------------------------------------------------
  Foreign Scan on public.ft1 t1
    Output: c1, c2, c3, c4, c5, c6, c7, c8
-   Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (((c3 IS NOT NULL) IS DISTINCT FROM (c3 IS NOT NULL)))
+   Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE ((c3 IS DISTINCT FROM c3))
 (3 rows)
 
 EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c1 = ANY(ARRAY[c2, 1, c1 + 0]); -- ScalarArrayOpExpr
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 4f7ab2ed0ac..eff25bd2baa 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -340,7 +340,7 @@ EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c3 IS NULL;        -- Nu
 EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c3 IS NOT NULL;    -- NullTest
 EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE round(abs(c1), 0) = 1; -- FuncExpr
 EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c1 = -c1;          -- OpExpr(l)
-EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE (c3 IS NOT NULL) IS DISTINCT FROM (c3 IS NOT NULL); -- DistinctExpr
+EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c3 IS DISTINCT FROM c3; -- DistinctExpr
 EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c1 = ANY(ARRAY[c2, 1, c1 + 0]); -- ScalarArrayOpExpr
 EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c1 = (ARRAY[c1,c2,3])[1]; -- SubscriptingRef
 EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c6 = E'foo''s\\bar';  -- check special chars
diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index 39d35827c35..4d58a164d3a 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -2708,6 +2708,7 @@ eval_const_expressions_mutator(Node *node,
 				bool		has_null_input = false;
 				bool		all_null_input = true;
 				bool		has_nonconst_input = false;
+				bool		has_nullable_nonconst = false;
 				Expr	   *simple;
 				DistinctExpr *newexpr;
 
@@ -2724,7 +2725,8 @@ eval_const_expressions_mutator(Node *node,
 				/*
 				 * We must do our own check for NULLs because DistinctExpr has
 				 * different results for NULL input than the underlying
-				 * operator does.
+				 * operator does.  We also check if any non-constant input is
+				 * potentially nullable.
 				 */
 				foreach(arg, args)
 				{
@@ -2734,12 +2736,24 @@ eval_const_expressions_mutator(Node *node,
 						all_null_input &= ((Const *) lfirst(arg))->constisnull;
 					}
 					else
+					{
 						has_nonconst_input = true;
+						all_null_input = false;
+
+						if (!has_nullable_nonconst &&
+							!expr_is_nonnullable(context->root,
+												 (Expr *) lfirst(arg), false))
+							has_nullable_nonconst = true;
+					}
 				}
 
-				/* all constants? then can optimize this out */
 				if (!has_nonconst_input)
 				{
+					/*
+					 * All inputs are constants.  We can optimize this out
+					 * completely.
+					 */
+
 					/* all nulls? then not distinct */
 					if (all_null_input)
 						return makeBoolConst(false, false);
@@ -2784,6 +2798,48 @@ eval_const_expressions_mutator(Node *node,
 						return (Node *) csimple;
 					}
 				}
+				else if (!has_nullable_nonconst)
+				{
+					/*
+					 * There are non-constant inputs, but since all of them
+					 * are proven non-nullable, "IS DISTINCT FROM" semantics
+					 * are much simpler.
+					 */
+
+					OpExpr	   *eqexpr;
+
+					/*
+					 * If one input is an explicit NULL constant, and the
+					 * other is a non-nullable expression, the result is
+					 * always TRUE.
+					 */
+					if (has_null_input)
+						return makeBoolConst(true, false);
+
+					/*
+					 * Otherwise, both inputs are known non-nullable.  In this
+					 * case, "IS DISTINCT FROM" is equivalent to the standard
+					 * inequality operator (usually "<>").  We convert this to
+					 * an OpExpr, which is a more efficient representation for
+					 * the planner.  It can enable the use of partial indexes
+					 * and constraint exclusion.  Furthermore, if the clause
+					 * is negated (e.g. "IS NOT DISTINCT FROM"), the resulting
+					 * "=" operator can allow the planner to use index scans,
+					 * merge joins, hash joins, and EC-based qual deductions.
+					 */
+					eqexpr = makeNode(OpExpr);
+					eqexpr->opno = expr->opno;
+					eqexpr->opfuncid = expr->opfuncid;
+					eqexpr->opresulttype = BOOLOID;
+					eqexpr->opretset = expr->opretset;
+					eqexpr->opcollid = expr->opcollid;
+					eqexpr->inputcollid = expr->inputcollid;
+					eqexpr->args = args;
+					eqexpr->location = expr->location;
+
+					return eval_const_expressions_mutator(negate_clause((Node *) eqexpr),
+														  context);
+				}
 
 				/*
 				 * The expression cannot be simplified any further, so build
-- 
2.39.5 (Apple Git-154)



^ permalink  raw  reply  [nested|flat] 31+ messages in thread

* Re: Optimize IS DISTINCT FROM with non-nullable inputs
@ 2026-01-26 08:00  Richard Guo <[email protected]>
  parent: Richard Guo <[email protected]>
  0 siblings, 1 reply; 31+ messages in thread

From: Richard Guo @ 2026-01-26 08:00 UTC (permalink / raw)
  To: Pg Hackers <[email protected]>

On Fri, Jan 23, 2026 at 10:40 PM Richard Guo <[email protected]> wrote:
> I'm kind of concerned about whether there are edge cases where this
> transformation is not safe, specifically regarding "rowtype" inputs.

After a second thought, I think this should be safe even for "rowtype"
inputs.  The executor (ExecInterpExpr) evaluates a DistinctExpr with
non-null inputs by simply applying the underlying equality function
and then negating the result.  Also, record_eq explicitly treats two
NULLs as equal.  Therefore, in this case, converting IS DISTINCT FROM
to the inequality operator in the planner produces the exact same
behavior as the executor.

Also, I think we can play this same trick with BooleanTest (IS [NOT]
TRUE/FALSE/UNKNOWN).  A BooleanTest treats a NULL input as the logical
value "unknown".  If we know that the input can not be NULL, we can
simplify it directly to a boolean expression or a constant.  Please
see 0002.

- Richard


Attachments:

  [application/octet-stream] v2-0001-Optimize-IS-DISTINCT-FROM-with-non-nullable-input.patch (14.4K, ../../CAMbWs4_vQDaJ3fGQt+RD3+CeasJHB0j9pCjUpS00=dgYe=bW_w@mail.gmail.com/2-v2-0001-Optimize-IS-DISTINCT-FROM-with-non-nullable-input.patch)
  download | inline diff:
From 8cf43e78d8497585122caa2e01a8932e59829669 Mon Sep 17 00:00:00 2001
From: Richard Guo <[email protected]>
Date: Fri, 23 Jan 2026 12:11:42 +0900
Subject: [PATCH v2 1/2] Optimize IS DISTINCT FROM with non-nullable inputs

The IS DISTINCT FROM construct compares values acting as though NULL
were a normal data value, rather than "unknown".  Semantically, "x IS
DISTINCT FROM y" yields true if the values differ or if exactly one is
NULL, and false if they are equal or both NULL.  Unlike ordinary
comparison operators, it never returns NULL.

Previously, the planner only simplified this construct if all inputs
were constants, folding it to a constant boolean result.  This patch
extends the optimization to cases where inputs are non-constant but
proven to be non-nullable.  Specifically, "x IS DISTINCT FROM NULL"
folds to constant TRUE if "x" is known to be non-nullable.  For cases
where both inputs are guaranteed not to be NULL, the expression
becomes semantically equivalent to "x <> y", and the DistinctExpr is
converted into an inequality OpExpr.

This transformation provides several benefits.  It converts the
comparison into a standard operator, allowing the use of partial
indexes and constraint exclusion.  Furthermore, if the clause is
negated (i.e., "IS NOT DISTINCT FROM"), it simplifies to an equality
operator.  This enables the planner to generate better plans using
index scans, merge joins, hash joins, and EC-based qual deduction.
---
 .../postgres_fdw/expected/postgres_fdw.out    |   8 +-
 contrib/postgres_fdw/sql/postgres_fdw.sql     |   2 +-
 src/backend/optimizer/util/clauses.c          |  60 +++++++-
 src/test/regress/expected/predicate.out       | 136 ++++++++++++++++++
 src/test/regress/sql/predicate.sql            |  61 ++++++++
 5 files changed, 260 insertions(+), 7 deletions(-)

diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 6066510c7c0..7cad5e67d09 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -698,12 +698,12 @@ EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c1 = -c1;          -- Op
    Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" = (- "C 1")))
 (3 rows)
 
-EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE (c3 IS NOT NULL) IS DISTINCT FROM (c3 IS NOT NULL); -- DistinctExpr
-                                                              QUERY PLAN                                                              
---------------------------------------------------------------------------------------------------------------------------------------
+EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c3 IS DISTINCT FROM c3; -- DistinctExpr
+                                                QUERY PLAN                                                
+----------------------------------------------------------------------------------------------------------
  Foreign Scan on public.ft1 t1
    Output: c1, c2, c3, c4, c5, c6, c7, c8
-   Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (((c3 IS NOT NULL) IS DISTINCT FROM (c3 IS NOT NULL)))
+   Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE ((c3 IS DISTINCT FROM c3))
 (3 rows)
 
 EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c1 = ANY(ARRAY[c2, 1, c1 + 0]); -- ScalarArrayOpExpr
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 4f7ab2ed0ac..eff25bd2baa 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -340,7 +340,7 @@ EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c3 IS NULL;        -- Nu
 EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c3 IS NOT NULL;    -- NullTest
 EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE round(abs(c1), 0) = 1; -- FuncExpr
 EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c1 = -c1;          -- OpExpr(l)
-EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE (c3 IS NOT NULL) IS DISTINCT FROM (c3 IS NOT NULL); -- DistinctExpr
+EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c3 IS DISTINCT FROM c3; -- DistinctExpr
 EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c1 = ANY(ARRAY[c2, 1, c1 + 0]); -- ScalarArrayOpExpr
 EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c1 = (ARRAY[c1,c2,3])[1]; -- SubscriptingRef
 EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c6 = E'foo''s\\bar';  -- check special chars
diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index 39d35827c35..3bb13a459f0 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -2708,6 +2708,7 @@ eval_const_expressions_mutator(Node *node,
 				bool		has_null_input = false;
 				bool		all_null_input = true;
 				bool		has_nonconst_input = false;
+				bool		has_nullable_nonconst = false;
 				Expr	   *simple;
 				DistinctExpr *newexpr;
 
@@ -2724,7 +2725,8 @@ eval_const_expressions_mutator(Node *node,
 				/*
 				 * We must do our own check for NULLs because DistinctExpr has
 				 * different results for NULL input than the underlying
-				 * operator does.
+				 * operator does.  We also check if any non-constant input is
+				 * potentially nullable.
 				 */
 				foreach(arg, args)
 				{
@@ -2734,12 +2736,24 @@ eval_const_expressions_mutator(Node *node,
 						all_null_input &= ((Const *) lfirst(arg))->constisnull;
 					}
 					else
+					{
 						has_nonconst_input = true;
+						all_null_input = false;
+
+						if (!has_nullable_nonconst &&
+							!expr_is_nonnullable(context->root,
+												 (Expr *) lfirst(arg), false))
+							has_nullable_nonconst = true;
+					}
 				}
 
-				/* all constants? then can optimize this out */
 				if (!has_nonconst_input)
 				{
+					/*
+					 * All inputs are constants.  We can optimize this out
+					 * completely.
+					 */
+
 					/* all nulls? then not distinct */
 					if (all_null_input)
 						return makeBoolConst(false, false);
@@ -2784,6 +2798,48 @@ eval_const_expressions_mutator(Node *node,
 						return (Node *) csimple;
 					}
 				}
+				else if (!has_nullable_nonconst)
+				{
+					/*
+					 * There are non-constant inputs, but since all of them
+					 * are proven non-nullable, "IS DISTINCT FROM" semantics
+					 * are much simpler.
+					 */
+
+					OpExpr	   *eqexpr;
+
+					/*
+					 * If one input is an explicit NULL constant, and the
+					 * other is a non-nullable expression, the result is
+					 * always TRUE.
+					 */
+					if (has_null_input)
+						return makeBoolConst(true, false);
+
+					/*
+					 * Otherwise, both inputs are known non-nullable.  In this
+					 * case, "IS DISTINCT FROM" is equivalent to the standard
+					 * inequality operator (usually "<>").  We convert this to
+					 * an OpExpr, which is a more efficient representation for
+					 * the planner.  It can enable the use of partial indexes
+					 * and constraint exclusion.  Furthermore, if the clause
+					 * is negated (ie, "IS NOT DISTINCT FROM"), the resulting
+					 * "=" operator can allow the planner to use index scans,
+					 * merge joins, hash joins, and EC-based qual deductions.
+					 */
+					eqexpr = makeNode(OpExpr);
+					eqexpr->opno = expr->opno;
+					eqexpr->opfuncid = expr->opfuncid;
+					eqexpr->opresulttype = BOOLOID;
+					eqexpr->opretset = expr->opretset;
+					eqexpr->opcollid = expr->opcollid;
+					eqexpr->inputcollid = expr->inputcollid;
+					eqexpr->args = args;
+					eqexpr->location = expr->location;
+
+					return eval_const_expressions_mutator(negate_clause((Node *) eqexpr),
+														  context);
+				}
 
 				/*
 				 * The expression cannot be simplified any further, so build
diff --git a/src/test/regress/expected/predicate.out b/src/test/regress/expected/predicate.out
index 8ff1172008e..c1205ae353a 100644
--- a/src/test/regress/expected/predicate.out
+++ b/src/test/regress/expected/predicate.out
@@ -632,3 +632,139 @@ SELECT * FROM pred_tab WHERE (a::oid) IS NULL;
 (3 rows)
 
 DROP TABLE pred_tab;
+--
+-- Test optimization of IS [NOT] DISTINCT FROM on non-nullable inputs
+--
+CREATE TYPE dist_row_t AS (a int, b int);
+CREATE TABLE dist_tab (id int, val_nn int NOT NULL, val_null int, row_nn dist_row_t NOT NULL);
+INSERT INTO dist_tab VALUES (1, 10, 10, ROW(1, 1));
+INSERT INTO dist_tab VALUES (2, 20, NULL, ROW(2, 2));
+INSERT INTO dist_tab VALUES (3, 30, 30, ROW(1, NULL));
+CREATE INDEX dist_tab_nn_idx ON dist_tab (val_nn);
+ANALYZE dist_tab;
+-- Ensure that the predicate folds to constant TRUE
+EXPLAIN(COSTS OFF)
+SELECT id FROM dist_tab WHERE val_nn IS DISTINCT FROM NULL;
+      QUERY PLAN      
+----------------------
+ Seq Scan on dist_tab
+(1 row)
+
+SELECT id FROM dist_tab WHERE val_nn IS DISTINCT FROM NULL;
+ id 
+----
+  1
+  2
+  3
+(3 rows)
+
+-- Ensure that the predicate folds to constant FALSE
+EXPLAIN(COSTS OFF)
+SELECT id FROM dist_tab WHERE val_nn IS NOT DISTINCT FROM NULL;
+          QUERY PLAN          
+------------------------------
+ Result
+   Replaces: Scan on dist_tab
+   One-Time Filter: false
+(3 rows)
+
+SELECT id FROM dist_tab WHERE val_nn IS NOT DISTINCT FROM NULL;
+ id 
+----
+(0 rows)
+
+-- Ensure that the predicate is converted to an inequality operator
+EXPLAIN (COSTS OFF)
+SELECT id FROM dist_tab WHERE val_nn IS DISTINCT FROM 10;
+        QUERY PLAN        
+--------------------------
+ Seq Scan on dist_tab
+   Filter: (val_nn <> 10)
+(2 rows)
+
+SELECT id FROM dist_tab WHERE val_nn IS DISTINCT FROM 10;
+ id 
+----
+  2
+  3
+(2 rows)
+
+-- Ensure that the predicate is converted to an equality operator, and thus can
+-- use index scan
+SET enable_seqscan TO off;
+EXPLAIN (COSTS OFF)
+SELECT id FROM dist_tab WHERE val_nn IS NOT DISTINCT FROM 10;
+                  QUERY PLAN                  
+----------------------------------------------
+ Index Scan using dist_tab_nn_idx on dist_tab
+   Index Cond: (val_nn = 10)
+(2 rows)
+
+SELECT id FROM dist_tab WHERE val_nn IS NOT DISTINCT FROM 10;
+ id 
+----
+  1
+(1 row)
+
+RESET enable_seqscan;
+-- Ensure that the predicate is preserved as "IS DISTINCT FROM"
+EXPLAIN (COSTS OFF)
+SELECT id FROM dist_tab WHERE val_null IS DISTINCT FROM 20;
+                QUERY PLAN                
+------------------------------------------
+ Seq Scan on dist_tab
+   Filter: (val_null IS DISTINCT FROM 20)
+(2 rows)
+
+SELECT id FROM dist_tab WHERE val_null IS DISTINCT FROM 20;
+ id 
+----
+  1
+  2
+  3
+(3 rows)
+
+-- Safety check for rowtypes
+-- Ensure that the predicate is converted to an inequality operator
+EXPLAIN (COSTS OFF)
+SELECT id FROM dist_tab WHERE row_nn IS DISTINCT FROM ROW(1, 5)::dist_row_t;
+                QUERY PLAN                 
+-------------------------------------------
+ Seq Scan on dist_tab
+   Filter: (row_nn <> '(1,5)'::dist_row_t)
+(2 rows)
+
+-- ... and that all 3 rows are returned
+SELECT id FROM dist_tab WHERE row_nn IS DISTINCT FROM ROW(1, 5)::dist_row_t;
+ id 
+----
+  1
+  2
+  3
+(3 rows)
+
+-- Ensure that the predicate is converted to an equality operator, and thus
+-- mergejoinable or hashjoinable
+SET enable_nestloop TO off;
+EXPLAIN (COSTS OFF)
+SELECT * FROM dist_tab t1 JOIN dist_tab t2 ON t1.val_nn IS NOT DISTINCT FROM t2.val_nn;
+              QUERY PLAN              
+--------------------------------------
+ Hash Join
+   Hash Cond: (t1.val_nn = t2.val_nn)
+   ->  Seq Scan on dist_tab t1
+   ->  Hash
+         ->  Seq Scan on dist_tab t2
+(5 rows)
+
+SELECT * FROM dist_tab t1 JOIN dist_tab t2 ON t1.val_nn IS NOT DISTINCT FROM t2.val_nn;
+ id | val_nn | val_null | row_nn | id | val_nn | val_null | row_nn 
+----+--------+----------+--------+----+--------+----------+--------
+  1 |     10 |       10 | (1,1)  |  1 |     10 |       10 | (1,1)
+  2 |     20 |          | (2,2)  |  2 |     20 |          | (2,2)
+  3 |     30 |       30 | (1,)   |  3 |     30 |       30 | (1,)
+(3 rows)
+
+RESET enable_nestloop;
+DROP TABLE dist_tab;
+DROP TYPE dist_row_t;
diff --git a/src/test/regress/sql/predicate.sql b/src/test/regress/sql/predicate.sql
index db72b11bb22..537b99bbcd5 100644
--- a/src/test/regress/sql/predicate.sql
+++ b/src/test/regress/sql/predicate.sql
@@ -308,3 +308,64 @@ EXPLAIN (COSTS OFF)
 SELECT * FROM pred_tab WHERE (a::oid) IS NULL;
 
 DROP TABLE pred_tab;
+
+--
+-- Test optimization of IS [NOT] DISTINCT FROM on non-nullable inputs
+--
+
+CREATE TYPE dist_row_t AS (a int, b int);
+CREATE TABLE dist_tab (id int, val_nn int NOT NULL, val_null int, row_nn dist_row_t NOT NULL);
+
+INSERT INTO dist_tab VALUES (1, 10, 10, ROW(1, 1));
+INSERT INTO dist_tab VALUES (2, 20, NULL, ROW(2, 2));
+INSERT INTO dist_tab VALUES (3, 30, 30, ROW(1, NULL));
+
+CREATE INDEX dist_tab_nn_idx ON dist_tab (val_nn);
+
+ANALYZE dist_tab;
+
+-- Ensure that the predicate folds to constant TRUE
+EXPLAIN(COSTS OFF)
+SELECT id FROM dist_tab WHERE val_nn IS DISTINCT FROM NULL;
+SELECT id FROM dist_tab WHERE val_nn IS DISTINCT FROM NULL;
+
+-- Ensure that the predicate folds to constant FALSE
+EXPLAIN(COSTS OFF)
+SELECT id FROM dist_tab WHERE val_nn IS NOT DISTINCT FROM NULL;
+SELECT id FROM dist_tab WHERE val_nn IS NOT DISTINCT FROM NULL;
+
+-- Ensure that the predicate is converted to an inequality operator
+EXPLAIN (COSTS OFF)
+SELECT id FROM dist_tab WHERE val_nn IS DISTINCT FROM 10;
+SELECT id FROM dist_tab WHERE val_nn IS DISTINCT FROM 10;
+
+-- Ensure that the predicate is converted to an equality operator, and thus can
+-- use index scan
+SET enable_seqscan TO off;
+EXPLAIN (COSTS OFF)
+SELECT id FROM dist_tab WHERE val_nn IS NOT DISTINCT FROM 10;
+SELECT id FROM dist_tab WHERE val_nn IS NOT DISTINCT FROM 10;
+RESET enable_seqscan;
+
+-- Ensure that the predicate is preserved as "IS DISTINCT FROM"
+EXPLAIN (COSTS OFF)
+SELECT id FROM dist_tab WHERE val_null IS DISTINCT FROM 20;
+SELECT id FROM dist_tab WHERE val_null IS DISTINCT FROM 20;
+
+-- Safety check for rowtypes
+-- Ensure that the predicate is converted to an inequality operator
+EXPLAIN (COSTS OFF)
+SELECT id FROM dist_tab WHERE row_nn IS DISTINCT FROM ROW(1, 5)::dist_row_t;
+-- ... and that all 3 rows are returned
+SELECT id FROM dist_tab WHERE row_nn IS DISTINCT FROM ROW(1, 5)::dist_row_t;
+
+-- Ensure that the predicate is converted to an equality operator, and thus
+-- mergejoinable or hashjoinable
+SET enable_nestloop TO off;
+EXPLAIN (COSTS OFF)
+SELECT * FROM dist_tab t1 JOIN dist_tab t2 ON t1.val_nn IS NOT DISTINCT FROM t2.val_nn;
+SELECT * FROM dist_tab t1 JOIN dist_tab t2 ON t1.val_nn IS NOT DISTINCT FROM t2.val_nn;
+RESET enable_nestloop;
+
+DROP TABLE dist_tab;
+DROP TYPE dist_row_t;
-- 
2.39.5 (Apple Git-154)



  [application/octet-stream] v2-0002-Optimize-BooleanTest-with-non-nullable-input.patch (7.3K, ../../CAMbWs4_vQDaJ3fGQt+RD3+CeasJHB0j9pCjUpS00=dgYe=bW_w@mail.gmail.com/3-v2-0002-Optimize-BooleanTest-with-non-nullable-input.patch)
  download | inline diff:
From 70068fd47b2ef57cb629bc076ae920e10f420101 Mon Sep 17 00:00:00 2001
From: Richard Guo <[email protected]>
Date: Mon, 26 Jan 2026 12:54:36 +0900
Subject: [PATCH v2 2/2] Optimize BooleanTest with non-nullable input

The BooleanTest construct (IS [NOT] TRUE/FALSE/UNKNOWN) treats a NULL
input as the logical value "unknown".  However, when the input is
proven to be non-nullable, this special handling becomes redundant.
In such cases, the construct can be simplified directly to a boolean
expression or a constant.
---
 src/backend/optimizer/util/clauses.c    |  31 ++++++
 src/test/regress/expected/predicate.out | 119 ++++++++++++++++++++++++
 src/test/regress/sql/predicate.sql      |  54 +++++++++++
 3 files changed, 204 insertions(+)

diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index 3bb13a459f0..b5d2f30dd2f 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -3689,6 +3689,9 @@ eval_const_expressions_mutator(Node *node,
 													 context);
 				if (arg && IsA(arg, Const))
 				{
+					/*
+					 * If arg is Const, simplify to constant.
+					 */
 					Const	   *carg = (Const *) arg;
 					bool		result;
 
@@ -3725,6 +3728,34 @@ eval_const_expressions_mutator(Node *node,
 
 					return makeBoolConst(result, false);
 				}
+				if (arg && expr_is_nonnullable(context->root, (Expr *) arg, false))
+				{
+					/*
+					 * If arg is proven non-nullable, simplify to boolean
+					 * expression or constant.
+					 */
+					switch (btest->booltesttype)
+					{
+						case IS_TRUE:
+						case IS_NOT_FALSE:
+							return arg;
+
+						case IS_FALSE:
+						case IS_NOT_TRUE:
+							return (Node *) make_notclause((Expr *) arg);
+
+						case IS_UNKNOWN:
+							return makeBoolConst(false, false);
+
+						case IS_NOT_UNKNOWN:
+							return makeBoolConst(true, false);
+
+						default:
+							elog(ERROR, "unrecognized booltesttype: %d",
+								 (int) btest->booltesttype);
+							break;
+					}
+				}
 
 				newbtest = makeNode(BooleanTest);
 				newbtest->arg = (Expr *) arg;
diff --git a/src/test/regress/expected/predicate.out b/src/test/regress/expected/predicate.out
index c1205ae353a..b9855397c5f 100644
--- a/src/test/regress/expected/predicate.out
+++ b/src/test/regress/expected/predicate.out
@@ -768,3 +768,122 @@ SELECT * FROM dist_tab t1 JOIN dist_tab t2 ON t1.val_nn IS NOT DISTINCT FROM t2.
 RESET enable_nestloop;
 DROP TABLE dist_tab;
 DROP TYPE dist_row_t;
+--
+-- Test optimization of BooleanTest (IS [NOT] TRUE/FALSE/UNKNOWN) on
+-- non-nullable input
+--
+CREATE TABLE bool_tab (id int, flag_nn boolean NOT NULL, flag_null boolean);
+INSERT INTO bool_tab VALUES (1, true,  true);
+INSERT INTO bool_tab VALUES (2, false, NULL);
+CREATE INDEX bool_tab_nn_idx ON bool_tab (flag_nn);
+ANALYZE bool_tab;
+-- Ensure that the predicate folds to constant FALSE
+EXPLAIN (COSTS OFF)
+SELECT id FROM bool_tab WHERE flag_nn IS UNKNOWN;
+          QUERY PLAN          
+------------------------------
+ Result
+   Replaces: Scan on bool_tab
+   One-Time Filter: false
+(3 rows)
+
+SELECT id FROM bool_tab WHERE flag_nn IS UNKNOWN;
+ id 
+----
+(0 rows)
+
+-- Ensure that the predicate folds to constant TRUE
+EXPLAIN (COSTS OFF)
+SELECT id FROM bool_tab WHERE flag_nn IS NOT UNKNOWN;
+      QUERY PLAN      
+----------------------
+ Seq Scan on bool_tab
+(1 row)
+
+SELECT id FROM bool_tab WHERE flag_nn IS NOT UNKNOWN;
+ id 
+----
+  1
+  2
+(2 rows)
+
+-- Ensure that the predicate folds to flag_nn
+EXPLAIN (COSTS OFF)
+SELECT id FROM bool_tab WHERE flag_nn IS TRUE;
+      QUERY PLAN      
+----------------------
+ Seq Scan on bool_tab
+   Filter: flag_nn
+(2 rows)
+
+SELECT id FROM bool_tab WHERE flag_nn IS TRUE;
+ id 
+----
+  1
+(1 row)
+
+-- Ensure that the predicate folds to flag_nn, and thus can use index scan
+SET enable_seqscan TO off;
+EXPLAIN (COSTS OFF)
+SELECT id FROM bool_tab WHERE flag_nn IS NOT FALSE;
+                  QUERY PLAN                  
+----------------------------------------------
+ Index Scan using bool_tab_nn_idx on bool_tab
+   Index Cond: (flag_nn = true)
+(2 rows)
+
+SELECT id FROM bool_tab WHERE flag_nn IS NOT FALSE;
+ id 
+----
+  1
+(1 row)
+
+RESET enable_seqscan;
+-- Ensure that the predicate folds to not flag_nn
+EXPLAIN (COSTS OFF)
+SELECT id FROM bool_tab WHERE flag_nn IS FALSE;
+       QUERY PLAN        
+-------------------------
+ Seq Scan on bool_tab
+   Filter: (NOT flag_nn)
+(2 rows)
+
+SELECT id FROM bool_tab WHERE flag_nn IS FALSE;
+ id 
+----
+  2
+(1 row)
+
+-- Ensure that the predicate folds to not flag_nn, and thus can use index scan
+SET enable_seqscan TO off;
+EXPLAIN (COSTS OFF)
+SELECT id FROM bool_tab WHERE flag_nn IS NOT TRUE;
+                  QUERY PLAN                  
+----------------------------------------------
+ Index Scan using bool_tab_nn_idx on bool_tab
+   Index Cond: (flag_nn = false)
+(2 rows)
+
+SELECT id FROM bool_tab WHERE flag_nn IS NOT TRUE;
+ id 
+----
+  2
+(1 row)
+
+RESET enable_seqscan;
+-- Ensure that the predicate is preserved as a BooleanTest
+EXPLAIN (COSTS OFF)
+SELECT id FROM bool_tab WHERE flag_null IS UNKNOWN;
+            QUERY PLAN            
+----------------------------------
+ Seq Scan on bool_tab
+   Filter: (flag_null IS UNKNOWN)
+(2 rows)
+
+SELECT id FROM bool_tab WHERE flag_null IS UNKNOWN;
+ id 
+----
+  2
+(1 row)
+
+DROP TABLE bool_tab;
diff --git a/src/test/regress/sql/predicate.sql b/src/test/regress/sql/predicate.sql
index 537b99bbcd5..4829ced0ff6 100644
--- a/src/test/regress/sql/predicate.sql
+++ b/src/test/regress/sql/predicate.sql
@@ -369,3 +369,57 @@ RESET enable_nestloop;
 
 DROP TABLE dist_tab;
 DROP TYPE dist_row_t;
+
+--
+-- Test optimization of BooleanTest (IS [NOT] TRUE/FALSE/UNKNOWN) on
+-- non-nullable input
+--
+CREATE TABLE bool_tab (id int, flag_nn boolean NOT NULL, flag_null boolean);
+
+INSERT INTO bool_tab VALUES (1, true,  true);
+INSERT INTO bool_tab VALUES (2, false, NULL);
+
+CREATE INDEX bool_tab_nn_idx ON bool_tab (flag_nn);
+
+ANALYZE bool_tab;
+
+-- Ensure that the predicate folds to constant FALSE
+EXPLAIN (COSTS OFF)
+SELECT id FROM bool_tab WHERE flag_nn IS UNKNOWN;
+SELECT id FROM bool_tab WHERE flag_nn IS UNKNOWN;
+
+-- Ensure that the predicate folds to constant TRUE
+EXPLAIN (COSTS OFF)
+SELECT id FROM bool_tab WHERE flag_nn IS NOT UNKNOWN;
+SELECT id FROM bool_tab WHERE flag_nn IS NOT UNKNOWN;
+
+-- Ensure that the predicate folds to flag_nn
+EXPLAIN (COSTS OFF)
+SELECT id FROM bool_tab WHERE flag_nn IS TRUE;
+SELECT id FROM bool_tab WHERE flag_nn IS TRUE;
+
+-- Ensure that the predicate folds to flag_nn, and thus can use index scan
+SET enable_seqscan TO off;
+EXPLAIN (COSTS OFF)
+SELECT id FROM bool_tab WHERE flag_nn IS NOT FALSE;
+SELECT id FROM bool_tab WHERE flag_nn IS NOT FALSE;
+RESET enable_seqscan;
+
+-- Ensure that the predicate folds to not flag_nn
+EXPLAIN (COSTS OFF)
+SELECT id FROM bool_tab WHERE flag_nn IS FALSE;
+SELECT id FROM bool_tab WHERE flag_nn IS FALSE;
+
+-- Ensure that the predicate folds to not flag_nn, and thus can use index scan
+SET enable_seqscan TO off;
+EXPLAIN (COSTS OFF)
+SELECT id FROM bool_tab WHERE flag_nn IS NOT TRUE;
+SELECT id FROM bool_tab WHERE flag_nn IS NOT TRUE;
+RESET enable_seqscan;
+
+-- Ensure that the predicate is preserved as a BooleanTest
+EXPLAIN (COSTS OFF)
+SELECT id FROM bool_tab WHERE flag_null IS UNKNOWN;
+SELECT id FROM bool_tab WHERE flag_null IS UNKNOWN;
+
+DROP TABLE bool_tab;
-- 
2.39.5 (Apple Git-154)



^ permalink  raw  reply  [nested|flat] 31+ messages in thread

* Re: Optimize IS DISTINCT FROM with non-nullable inputs
@ 2026-01-27 02:32  Tender Wang <[email protected]>
  parent: Richard Guo <[email protected]>
  0 siblings, 1 reply; 31+ messages in thread

From: Tender Wang @ 2026-01-27 02:32 UTC (permalink / raw)
  To: Richard Guo <[email protected]>; +Cc: Pg Hackers <[email protected]>

Hi,

Richard Guo <[email protected]> 于2026年1月26日周一 16:00写道：
>
> On Fri, Jan 23, 2026 at 10:40 PM Richard Guo <[email protected]> wrote:
> > I'm kind of concerned about whether there are edge cases where this
> > transformation is not safe, specifically regarding "rowtype" inputs.
>
> After a second thought, I think this should be safe even for "rowtype"
> inputs.  The executor (ExecInterpExpr) evaluates a DistinctExpr with
> non-null inputs by simply applying the underlying equality function
> and then negating the result.  Also, record_eq explicitly treats two
> NULLs as equal.  Therefore, in this case, converting IS DISTINCT FROM
> to the inequality operator in the planner produces the exact same
> behavior as the executor.
>
> Also, I think we can play this same trick with BooleanTest (IS [NOT]
> TRUE/FALSE/UNKNOWN).  A BooleanTest treats a NULL input as the logical
> value "unknown".  If we know that the input can not be NULL, we can
> simplify it directly to a boolean expression or a constant.  Please
> see 0002.
>
> - Richard

Interesting optimization. I look through the v2-0001 patch. In the
commit message, it says:
...
 This patch extends the optimization to cases where inputs are non-constant but
 proven to be non-nullable.  Specifically, "x IS DISTINCT FROM NULL"
 folds to constant TRUE if "x" is known to be non-nullable.
...

But I found that the case "x IS DISTINCT FROM NULL"  is converted to
NullTest in transformAExprDistinct().
It will be optimized in the "case T_NullTest:" not by this patch.
The test case can't cover the following codes:
+                                       /*
+                                        * If one input is an explicit
NULL constant, and the
+                                        * other is a non-nullable
expression, the result is
+                                        * always TRUE.
+                                        */
+                                       if (has_null_input)
+                                               return
makeBoolConst(true, false);

And I adjusted a little above code to check if all regression tests
can enter above if,
if (has_null_input)
{
     assert(0);
      return makeBoolConst(true, false);
}
All tests pass; no crashes occurred.


-- 
Thanks,
Tender Wang






^ permalink  raw  reply  [nested|flat] 31+ messages in thread

* Re: Optimize IS DISTINCT FROM with non-nullable inputs
@ 2026-01-27 07:10  Richard Guo <[email protected]>
  parent: Tender Wang <[email protected]>
  0 siblings, 1 reply; 31+ messages in thread

From: Richard Guo @ 2026-01-27 07:10 UTC (permalink / raw)
  To: Tender Wang <[email protected]>; +Cc: Pg Hackers <[email protected]>

On Tue, Jan 27, 2026 at 11:32 AM Tender Wang <[email protected]> wrote:
> Interesting optimization. I look through the v2-0001 patch. In the
> commit message, it says:
> ...
>  This patch extends the optimization to cases where inputs are non-constant but
>  proven to be non-nullable.  Specifically, "x IS DISTINCT FROM NULL"
>  folds to constant TRUE if "x" is known to be non-nullable.
> ...
>
> But I found that the case "x IS DISTINCT FROM NULL"  is converted to
> NullTest in transformAExprDistinct().
> It will be optimized in the "case T_NullTest:" not by this patch.

Well, while it's true that the parser would do this transformation for
"literal" NULLs, here we are talking more about "calculated" NULLs.
Consider "not_null_col IS DISTINCT FROM (1 + NULL)".

Another case is custom plans for prepared statements (e.g., WHERE
not_null_col IS DISTINCT FROM $1), which will inject NULL constants
directly into the DistinctExpr during planning, and the parser does
not have a chance to transform it into a NullTest.

> The test case can't cover the following codes:
> +                                       /*
> +                                        * If one input is an explicit
> NULL constant, and the
> +                                        * other is a non-nullable
> expression, the result is
> +                                        * always TRUE.
> +                                        */
> +                                       if (has_null_input)
> +                                               return
> makeBoolConst(true, false);

Right.  I'll need to adjust the test cases to cover this.

- Richard






^ permalink  raw  reply  [nested|flat] 31+ messages in thread

* Re: Optimize IS DISTINCT FROM with non-nullable inputs
@ 2026-01-28 01:59  Tender Wang <[email protected]>
  parent: Richard Guo <[email protected]>
  0 siblings, 0 replies; 31+ messages in thread

From: Tender Wang @ 2026-01-28 01:59 UTC (permalink / raw)
  To: Richard Guo <[email protected]>; +Cc: Pg Hackers <[email protected]>

Richard Guo <[email protected]> 于2026年1月27日周二 15:10写道：
>
> On Tue, Jan 27, 2026 at 11:32 AM Tender Wang <[email protected]> wrote:
> > Interesting optimization. I look through the v2-0001 patch. In the
> > commit message, it says:
> > ...
> >  This patch extends the optimization to cases where inputs are non-constant but
> >  proven to be non-nullable.  Specifically, "x IS DISTINCT FROM NULL"
> >  folds to constant TRUE if "x" is known to be non-nullable.
> > ...
> >
> > But I found that the case "x IS DISTINCT FROM NULL"  is converted to
> > NullTest in transformAExprDistinct().
> > It will be optimized in the "case T_NullTest:" not by this patch.
>
> Well, while it's true that the parser would do this transformation for
> "literal" NULLs, here we are talking more about "calculated" NULLs.
> Consider "not_null_col IS DISTINCT FROM (1 + NULL)".

Got it.

I looked through the v2-0002 patch. LGTM.


-- 
Thanks,
Tender Wang






^ permalink  raw  reply  [nested|flat] 31+ messages in thread

end of thread, other threads:[~2026-01-28 01:59 UTC | newest]

Thread overview: 31+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-12-06 04:31 [PATCH v4 1/3] WIP: pg_upgrade/test.sh: changes needed to allow testing upgrade to v14dev from v9.5-v13 Justin Pryzby <[email protected]>
2020-12-06 04:31 [PATCH v4 1/3] WIP: pg_upgrade/test.sh: changes needed to allow testing upgrade to v14dev from v9.5-v13 Justin Pryzby <[email protected]>
2020-12-06 04:31 [PATCH v4 1/3] WIP: pg_upgrade/test.sh: changes needed to allow testing upgrade to v14dev from v9.5-v13 Justin Pryzby <[email protected]>
2020-12-06 04:31 [PATCH v4 1/3] WIP: pg_upgrade/test.sh: changes needed to allow testing upgrade to v14dev from v9.5-v13 Justin Pryzby <[email protected]>
2020-12-06 04:31 [PATCH v4 1/3] WIP: pg_upgrade/test.sh: changes needed to allow testing upgrade to v14dev from v9.5-v13 Justin Pryzby <[email protected]>
2020-12-06 04:31 [PATCH v4 1/3] WIP: pg_upgrade/test.sh: changes needed to allow testing upgrade to v14dev from v9.5-v13 Justin Pryzby <[email protected]>
2020-12-06 04:31 [PATCH v4 1/3] WIP: pg_upgrade/test.sh: changes needed to allow testing upgrade to v14dev from v9.5-v13 Justin Pryzby <[email protected]>
2020-12-06 04:31 [PATCH v5 1/4] WIP: pg_upgrade/test.sh: changes needed to allow testing upgrade to v14dev from v9.5-v13 Justin Pryzby <[email protected]>
2020-12-06 04:31 [PATCH v4 1/3] WIP: pg_upgrade/test.sh: changes needed to allow testing upgrade to v14dev from v9.5-v13 Justin Pryzby <[email protected]>
2020-12-06 04:31 [PATCH v4 1/3] WIP: pg_upgrade/test.sh: changes needed to allow testing upgrade to v14dev from v9.5-v13 Justin Pryzby <[email protected]>
2020-12-06 04:31 [PATCH v5 1/4] WIP: pg_upgrade/test.sh: changes needed to allow testing upgrade to v14dev from v9.5-v13 Justin Pryzby <[email protected]>
2020-12-06 04:31 [PATCH v4 1/3] WIP: pg_upgrade/test.sh: changes needed to allow testing upgrade to v14dev from v9.5-v13 Justin Pryzby <[email protected]>
2020-12-06 04:31 [PATCH v4 1/3] WIP: pg_upgrade/test.sh: changes needed to allow testing upgrade to v14dev from v9.5-v13 Justin Pryzby <[email protected]>
2020-12-06 04:31 [PATCH v4 1/3] WIP: pg_upgrade/test.sh: changes needed to allow testing upgrade to v14dev from v9.5-v13 Justin Pryzby <[email protected]>
2020-12-06 04:31 [PATCH v4 1/3] WIP: pg_upgrade/test.sh: changes needed to allow testing upgrade to v14dev from v9.5-v13 Justin Pryzby <[email protected]>
2020-12-06 04:31 [PATCH v4 1/3] WIP: pg_upgrade/test.sh: changes needed to allow testing upgrade to v14dev from v9.5-v13 Justin Pryzby <[email protected]>
2020-12-06 04:31 [PATCH v4 1/3] WIP: pg_upgrade/test.sh: changes needed to allow testing upgrade to v14dev from v9.5-v13 Justin Pryzby <[email protected]>
2020-12-06 04:31 [PATCH v2 1/3] WIP: pg_upgrade/test.sh: changes needed to allow testing upgrade from v11 Justin Pryzby <[email protected]>
2020-12-06 04:31 [PATCH v3 1/2] WIP: pg_upgrade/test.sh: changes needed to allow testing upgrade from v11 Justin Pryzby <[email protected]>
2020-12-06 04:31 [PATCH v4 1/3] WIP: pg_upgrade/test.sh: changes needed to allow testing upgrade to v14dev from v9.5-v13 Justin Pryzby <[email protected]>
2020-12-06 04:31 [PATCH v4 1/3] WIP: pg_upgrade/test.sh: changes needed to allow testing upgrade to v14dev from v9.5-v13 Justin Pryzby <[email protected]>
2020-12-06 04:31 [PATCH v4 1/3] WIP: pg_upgrade/test.sh: changes needed to allow testing upgrade to v14dev from v9.5-v13 Justin Pryzby <[email protected]>
2020-12-06 04:31 [PATCH v4 1/3] WIP: pg_upgrade/test.sh: changes needed to allow testing upgrade to v14dev from v9.5-v13 Justin Pryzby <[email protected]>
2020-12-06 04:31 [PATCH 1/2] WIP: pg_upgrade/test.sh: changes needed to allow testing upgrade from v11 Justin Pryzby <[email protected]>
2020-12-06 04:31 [PATCH v4 1/3] WIP: pg_upgrade/test.sh: changes needed to allow testing upgrade to v14dev from v9.5-v13 Justin Pryzby <[email protected]>
2020-12-06 04:31 [PATCH v4 1/3] WIP: pg_upgrade/test.sh: changes needed to allow testing upgrade to v14dev from v9.5-v13 Justin Pryzby <[email protected]>
2026-01-23 13:40 Optimize IS DISTINCT FROM with non-nullable inputs Richard Guo <[email protected]>
2026-01-26 08:00 ` Re: Optimize IS DISTINCT FROM with non-nullable inputs Richard Guo <[email protected]>
2026-01-27 02:32   ` Re: Optimize IS DISTINCT FROM with non-nullable inputs Tender Wang <[email protected]>
2026-01-27 07:10     ` Re: Optimize IS DISTINCT FROM with non-nullable inputs Richard Guo <[email protected]>
2026-01-28 01:59       ` Re: Optimize IS DISTINCT FROM with non-nullable inputs Tender Wang <[email protected]>

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox