Converting contrib SQL functions to new style

From 35eee2d4dbfc741cb940029a94bde30242b8b6e0 Mon Sep 17 00:00:00 2001
From: Ronan Dunklau <ronan.dunklau@aiven.io>
Date: Mon, 28 Oct 2024 16:25:52 +0100
Subject: [PATCH v2 5/6] Use "new style" SQL function in xml2 extension

Author: Tom Lane
Discussion: https://www.postgresql.org/message-id/3395418.1618352794%40sss.pgh.pa.us
---
 contrib/xml2/Makefile     | 4 +++-
 contrib/xml2/xml2.control | 2 +-
 2 files changed, 4 insertions(+), 2 deletions(-)

diff --git a/contrib/xml2/Makefile b/contrib/xml2/Makefile
index 0d703fe0e8f..8597e9aa9c5 100644
--- a/contrib/xml2/Makefile
+++ b/contrib/xml2/Makefile
@@ -7,7 +7,9 @@ OBJS = \
 	xslt_proc.o
 
 EXTENSION = xml2
-DATA = xml2--1.1.sql xml2--1.0--1.1.sql
+DATA = xml2--1.1.sql \
+	xml2--1.1--1.2.sql \
+	xml2--1.0--1.1.sql
 PGFILEDESC = "xml2 - XPath querying and XSLT"
 
 REGRESS = xml2
diff --git a/contrib/xml2/xml2.control b/contrib/xml2/xml2.control
index ba2c0599a37..b32156c949e 100644
--- a/contrib/xml2/xml2.control
+++ b/contrib/xml2/xml2.control
@@ -1,6 +1,6 @@
 # xml2 extension
 comment = 'XPath querying and XSLT'
-default_version = '1.1'
+default_version = '1.2'
 module_pathname = '$libdir/pgxml'
 # XXX do we still need this to be non-relocatable?
 relocatable = false
-- 
2.47.0

From 0f513936c95b52c8801d8987b3b9cb2c9060d627 Mon Sep 17 00:00:00 2001
From: Ronan Dunklau <ronan.dunklau@aiven.io>
Date: Tue, 29 Oct 2024 07:53:02 +0100
Subject: [PATCH v2 6/6] Use "new style" SQL function in lo extension

---
 contrib/lo/Makefile         | 2 +-
 contrib/lo/lo--1.1--1.2.sql | 9 +++++++++
 contrib/lo/lo.control       | 2 +-
 3 files changed, 11 insertions(+), 2 deletions(-)
 create mode 100644 contrib/lo/lo--1.1--1.2.sql

diff --git a/contrib/lo/Makefile b/contrib/lo/Makefile
index 716893859eb..8cfff63d882 100644
--- a/contrib/lo/Makefile
+++ b/contrib/lo/Makefile
@@ -3,7 +3,7 @@
 MODULES = lo
 
 EXTENSION = lo
-DATA = lo--1.1.sql lo--1.0--1.1.sql
+DATA = lo--1.1.sql lo--1.0--1.1.sql lo--1.1--1.2.sql
 PGFILEDESC = "lo - management for large objects"
 
 REGRESS = lo
diff --git a/contrib/lo/lo--1.1--1.2.sql b/contrib/lo/lo--1.1--1.2.sql
new file mode 100644
index 00000000000..4cad3e689dd
--- /dev/null
+++ b/contrib/lo/lo--1.1--1.2.sql
@@ -0,0 +1,9 @@
+/* contrib/lo/lo--1.1--1.2.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION lo UPDATE TO '1.2'" to load this file. \quit
+
+CREATE OR REPLACE FUNCTION lo_oid(lo) RETURNS pg_catalog.oid
+LANGUAGE SQL STRICT IMMUTABLE PARALLEL SAFE
+RETURN (SELECT $1::pg_catalog.oid);
+
diff --git a/contrib/lo/lo.control b/contrib/lo/lo.control
index f73f8b5fae5..3acb3665a4e 100644
--- a/contrib/lo/lo.control
+++ b/contrib/lo/lo.control
@@ -1,6 +1,6 @@
 # lo extension
 comment = 'Large Object maintenance'
-default_version = '1.1'
+default_version = '1.2'
 module_pathname = '$libdir/lo'
 relocatable = true
 trusted = true
-- 
2.47.0

From 1ba5dfd577a57e845f2e375e56d22c24208107c9 Mon Sep 17 00:00:00 2001
From: Ronan Dunklau <ronan.dunklau@aiven.io>
Date: Mon, 28 Oct 2024 16:24:46 +0100
Subject: [PATCH v2 4/6] Use "new style" SQL function in pg_freespacemap
 extension

Author: Tom Lane
Discussion: https://www.postgresql.org/message-id/3395418.1618352794%40sss.pgh.pa.us
---
 contrib/pg_freespacemap/Makefile                    |  4 +++-
 .../pg_freespacemap/pg_freespacemap--1.2--1.3.sql   | 13 +++++++++++++
 contrib/pg_freespacemap/pg_freespacemap.control     |  2 +-
 3 files changed, 17 insertions(+), 2 deletions(-)
 create mode 100644 contrib/pg_freespacemap/pg_freespacemap--1.2--1.3.sql

diff --git a/contrib/pg_freespacemap/Makefile b/contrib/pg_freespacemap/Makefile
index b48e4b255bc..a986ae73d69 100644
--- a/contrib/pg_freespacemap/Makefile
+++ b/contrib/pg_freespacemap/Makefile
@@ -6,7 +6,9 @@ OBJS = \
 	pg_freespacemap.o
 
 EXTENSION = pg_freespacemap
-DATA = pg_freespacemap--1.1.sql pg_freespacemap--1.1--1.2.sql \
+DATA = pg_freespacemap--1.1.sql \
+	pg_freespacemap--1.2--1.3.sql \
+	pg_freespacemap--1.1--1.2.sql \
 	pg_freespacemap--1.0--1.1.sql
 PGFILEDESC = "pg_freespacemap - monitoring of free space map"
 
diff --git a/contrib/pg_freespacemap/pg_freespacemap--1.2--1.3.sql b/contrib/pg_freespacemap/pg_freespacemap--1.2--1.3.sql
new file mode 100644
index 00000000000..7f92c9e92e3
--- /dev/null
+++ b/contrib/pg_freespacemap/pg_freespacemap--1.2--1.3.sql
@@ -0,0 +1,13 @@
+/* contrib/pg_freespacemap/pg_freespacemap--1.2--1.3.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION pg_freespacemap UPDATE TO '1.3'" to load this file. \quit
+
+CREATE OR REPLACE FUNCTION
+  pg_freespace(rel regclass, blkno OUT bigint, avail OUT int2)
+RETURNS SETOF RECORD
+LANGUAGE SQL PARALLEL SAFE
+BEGIN ATOMIC
+  SELECT blkno, pg_freespace($1, blkno) AS avail
+  FROM generate_series(0, pg_relation_size($1) / current_setting('block_size')::bigint - 1) AS blkno;
+END;
diff --git a/contrib/pg_freespacemap/pg_freespacemap.control b/contrib/pg_freespacemap/pg_freespacemap.control
index ac8fc5050a9..1992320691b 100644
--- a/contrib/pg_freespacemap/pg_freespacemap.control
+++ b/contrib/pg_freespacemap/pg_freespacemap.control
@@ -1,5 +1,5 @@
 # pg_freespacemap extension
 comment = 'examine the free space map (FSM)'
-default_version = '1.2'
+default_version = '1.3'
 module_pathname = '$libdir/pg_freespacemap'
 relocatable = true
-- 
2.47.0

From 87335abb842f3864c18122c78f417f2d0f5024fc Mon Sep 17 00:00:00 2001
From: Ronan Dunklau <ronan.dunklau@aiven.io>
Date: Mon, 28 Oct 2024 16:23:49 +0100
Subject: [PATCH v2 3/6] Use "new style" SQL function in pageinspect extension

Author: Tom Lane
Discussion: https://www.postgresql.org/message-id/3395418.1618352794%40sss.pgh.pa.us
---
 contrib/pageinspect/Makefile                  |  3 +-
 .../pageinspect/pageinspect--1.12--1.13.sql   | 74 +++++++++++++++++++
 contrib/pageinspect/pageinspect.control       |  2 +-
 3 files changed, 77 insertions(+), 2 deletions(-)
 create mode 100644 contrib/pageinspect/pageinspect--1.12--1.13.sql

diff --git a/contrib/pageinspect/Makefile b/contrib/pageinspect/Makefile
index 95e030b3969..9dee7653310 100644
--- a/contrib/pageinspect/Makefile
+++ b/contrib/pageinspect/Makefile
@@ -13,7 +13,8 @@ OBJS = \
 	rawpage.o
 
 EXTENSION = pageinspect
-DATA =  pageinspect--1.11--1.12.sql pageinspect--1.10--1.11.sql \
+DATA =  pageinspect--1.12--1.13.sql \
+	pageinspect--1.11--1.12.sql pageinspect--1.10--1.11.sql \
 	pageinspect--1.9--1.10.sql pageinspect--1.8--1.9.sql \
 	pageinspect--1.7--1.8.sql pageinspect--1.6--1.7.sql \
 	pageinspect--1.5.sql pageinspect--1.5--1.6.sql \
diff --git a/contrib/pageinspect/pageinspect--1.12--1.13.sql b/contrib/pageinspect/pageinspect--1.12--1.13.sql
new file mode 100644
index 00000000000..f444fba58ce
--- /dev/null
+++ b/contrib/pageinspect/pageinspect--1.12--1.13.sql
@@ -0,0 +1,74 @@
+/* contrib/pageinspect/pageinspect--1.12--1.13.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION pageinspect UPDATE TO '1.13'" to load this file. \quit
+
+-- Convert SQL functions to new style
+
+CREATE OR REPLACE FUNCTION heap_page_item_attrs(
+    IN page bytea,
+    IN rel_oid regclass,
+    IN do_detoast bool,
+    OUT lp smallint,
+    OUT lp_off smallint,
+    OUT lp_flags smallint,
+    OUT lp_len smallint,
+    OUT t_xmin xid,
+    OUT t_xmax xid,
+    OUT t_field3 int4,
+    OUT t_ctid tid,
+    OUT t_infomask2 integer,
+    OUT t_infomask integer,
+    OUT t_hoff smallint,
+    OUT t_bits text,
+    OUT t_oid oid,
+    OUT t_attrs bytea[]
+    )
+RETURNS SETOF record
+LANGUAGE SQL PARALLEL SAFE
+BEGIN ATOMIC
+SELECT lp,
+       lp_off,
+       lp_flags,
+       lp_len,
+       t_xmin,
+       t_xmax,
+       t_field3,
+       t_ctid,
+       t_infomask2,
+       t_infomask,
+       t_hoff,
+       t_bits,
+       t_oid,
+       tuple_data_split(
+         rel_oid,
+         t_data,
+         t_infomask,
+         t_infomask2,
+         t_bits,
+         do_detoast)
+         AS t_attrs
+  FROM heap_page_items(page);
+END;
+
+CREATE OR REPLACE FUNCTION heap_page_item_attrs(IN page bytea, IN rel_oid regclass,
+    OUT lp smallint,
+    OUT lp_off smallint,
+    OUT lp_flags smallint,
+    OUT lp_len smallint,
+    OUT t_xmin xid,
+    OUT t_xmax xid,
+    OUT t_field3 int4,
+    OUT t_ctid tid,
+    OUT t_infomask2 integer,
+    OUT t_infomask integer,
+    OUT t_hoff smallint,
+    OUT t_bits text,
+    OUT t_oid oid,
+    OUT t_attrs bytea[]
+    )
+RETURNS SETOF record
+LANGUAGE SQL PARALLEL SAFE
+BEGIN ATOMIC
+SELECT * FROM heap_page_item_attrs(page, rel_oid, false);
+END;
diff --git a/contrib/pageinspect/pageinspect.control b/contrib/pageinspect/pageinspect.control
index b2804e9b128..cfc87feac03 100644
--- a/contrib/pageinspect/pageinspect.control
+++ b/contrib/pageinspect/pageinspect.control
@@ -1,5 +1,5 @@
 # pageinspect extension
 comment = 'inspect the contents of database pages at a low level'
-default_version = '1.12'
+default_version = '1.13'
 module_pathname = '$libdir/pageinspect'
 relocatable = true
-- 
2.47.0

From 51ad1b5f7003fe898b6e15ff5b8db4c71bb81ba6 Mon Sep 17 00:00:00 2001
From: Ronan Dunklau <ronan.dunklau@aiven.io>
Date: Mon, 28 Oct 2024 15:34:45 +0100
Subject: [PATCH v2 2/6] Use "new style" SQL function in earthdistance
 extension.

Since the sql_body is parsed at function definition time, and not when
executing it, the namespace is resolved at that point. This solves a
couple of bugs related to pg_restore failing to locate functions
belonging to an extension once they are inline.

The missing piece from the previous attempt was a syntax to reference
another extension's schema, which has been introduced in PG16. It is
then safe to fully-qualify every object reference either implicitly
because it has just been created, or by the extension schema it belongs
to.

Author: Tom Lane
Discussion: https://www.postgresql.org/message-id/3395418.1618352794%40sss.pgh.pa.us
---
 contrib/earthdistance/Makefile                |  3 +-
 .../earthdistance/earthdistance--1.1--1.2.sql | 58 +++++++++++
 contrib/earthdistance/earthdistance--1.2.sql  | 98 +++++++++++++++++++
 contrib/earthdistance/earthdistance.control   |  2 +-
 4 files changed, 159 insertions(+), 2 deletions(-)
 create mode 100644 contrib/earthdistance/earthdistance--1.1--1.2.sql
 create mode 100644 contrib/earthdistance/earthdistance--1.2.sql

diff --git a/contrib/earthdistance/Makefile b/contrib/earthdistance/Makefile
index f93b7a925a2..0cf3fa379a2 100644
--- a/contrib/earthdistance/Makefile
+++ b/contrib/earthdistance/Makefile
@@ -3,7 +3,8 @@
 MODULES = earthdistance
 
 EXTENSION = earthdistance
-DATA = earthdistance--1.1.sql earthdistance--1.0--1.1.sql
+DATA = earthdistance--1.1.sql earthdistance--1.0--1.1.sql \
+	earthdistance--1.1--1.2.sql
 PGFILEDESC = "earthdistance - calculate distances on the surface of the Earth"
 
 REGRESS = earthdistance
diff --git a/contrib/earthdistance/earthdistance--1.1--1.2.sql b/contrib/earthdistance/earthdistance--1.1--1.2.sql
new file mode 100644
index 00000000000..f4508fed8f8
--- /dev/null
+++ b/contrib/earthdistance/earthdistance--1.1--1.2.sql
@@ -0,0 +1,58 @@
+/* contrib/earthdistance/earthdistance--1.1--1.2.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION earthdistance UPDATE TO '1.2'" to load this file. \quit
+
+CREATE OR REPLACE FUNCTION earth() RETURNS float8
+LANGUAGE SQL IMMUTABLE PARALLEL SAFE
+RETURN '6378168'::float8;
+
+CREATE OR REPLACE FUNCTION sec_to_gc(float8)
+RETURNS float8
+LANGUAGE SQL
+IMMUTABLE STRICT
+PARALLEL SAFE
+RETURN CASE WHEN $1 < 0 THEN 0::float8 WHEN $1/(2*earth()) > 1 THEN pg_catalog.pi()*earth() ELSE 2*earth()*pg_catalog.asin($1/(2*earth())) END;
+
+CREATE OR REPLACE FUNCTION gc_to_sec(float8)
+RETURNS float8
+LANGUAGE SQL
+IMMUTABLE STRICT
+PARALLEL SAFE
+RETURN CASE WHEN $1 < 0 THEN 0::float8 WHEN $1/earth() > pg_catalog.pi() THEN 2*earth() ELSE 2*earth()*pg_catalog.sin($1/(2*earth())) END;
+
+CREATE OR REPLACE FUNCTION ll_to_earth(float8, float8)
+RETURNS earth
+LANGUAGE SQL
+IMMUTABLE STRICT
+PARALLEL SAFE
+RETURN @extschema:cube@.cube(@extschema:cube@.cube(@extschema:cube@.cube(earth()*pg_catalog.cos(radians($1))*pg_catalog.cos(radians($2))),earth()*pg_catalog.cos(radians($1))*pg_catalog.sin(radians($2))),earth()*pg_catalog.sin(radians($1)))::earth;
+
+CREATE OR REPLACE FUNCTION latitude(earth)
+RETURNS float8
+LANGUAGE SQL
+IMMUTABLE STRICT
+PARALLEL SAFE
+RETURN CASE WHEN @extschema:cube@.cube_ll_coord($1, 3)/earth() < -1 THEN -90::float8 WHEN @extschema:cube@.cube_ll_coord($1, 3)/earth() > 1 THEN 90::float8 ELSE pg_catalog.degrees(pg_catalog.asin(@extschema:cube@.cube_ll_coord($1, 3)/earth())) END;
+
+CREATE OR REPLACE FUNCTION longitude(earth)
+RETURNS float8
+LANGUAGE SQL
+IMMUTABLE STRICT
+PARALLEL SAFE
+RETURN pg_catalog.degrees(pg_catalog.atan2(@extschema:cube@.cube_ll_coord($1, 2), @extschema:cube@.cube_ll_coord($1, 1)));
+
+CREATE OR REPLACE FUNCTION earth_distance(earth, earth)
+RETURNS float8
+LANGUAGE SQL
+IMMUTABLE STRICT
+PARALLEL SAFE
+RETURN sec_to_gc(@extschema:cube@.cube_distance($1, $2));
+
+CREATE OR REPLACE FUNCTION earth_box(earth, float8)
+RETURNS @extschema:cube@.cube
+LANGUAGE SQL
+IMMUTABLE STRICT
+PARALLEL SAFE
+RETURN @extschema:cube@.cube_enlarge($1, gc_to_sec($2), 3);
+
diff --git a/contrib/earthdistance/earthdistance--1.2.sql b/contrib/earthdistance/earthdistance--1.2.sql
new file mode 100644
index 00000000000..fac15969ad4
--- /dev/null
+++ b/contrib/earthdistance/earthdistance--1.2.sql
@@ -0,0 +1,98 @@
+/* contrib/earthdistance/earthdistance--1.1.sql */
+
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION earthdistance" to load this file. \quit
+
+-- earth() returns the radius of the earth in meters. This is the only
+-- place you need to change things for the cube base distance functions
+-- in order to use different units (or a better value for the Earth's radius).
+
+CREATE FUNCTION earth() RETURNS float8
+LANGUAGE SQL IMMUTABLE PARALLEL SAFE
+RETURN '6378168'::float8;
+
+-- Astronomers may want to change the earth function so that distances will be
+-- returned in degrees. To do this comment out the above definition and
+-- uncomment the one below. Note that doing this will break the regression
+-- tests.
+--
+-- CREATE FUNCTION earth() RETURNS float8
+-- LANGUAGE SQL IMMUTABLE
+-- AS 'SELECT 180/pi()';
+
+-- Define domain for locations on the surface of the earth using a cube
+-- datatype with constraints. cube provides 3D indexing.
+-- The cube is restricted to be a point, no more than 3 dimensions
+-- (for less than 3 dimensions 0 is assumed for the missing coordinates)
+-- and that the point must be very near the surface of the sphere
+-- centered about the origin with the radius of the earth.
+
+CREATE DOMAIN earth AS $extschema:cube@.cube
+  CONSTRAINT not_point check($extschema:cube@.cube_is_point(value))
+  CONSTRAINT not_3d check($extschema:cube@.cube_dim(value) <= 3)
+  CONSTRAINT on_surface check(abs($extschema:cube@.cube_distance(value, '(0)'::$extschema:cube@.cube) /
+  earth() - '1'::float8) < '10e-7'::float8);
+
+CREATE FUNCTION sec_to_gc(float8)
+RETURNS float8
+LANGUAGE SQL
+IMMUTABLE STRICT
+PARALLEL SAFE
+RETURN CASE WHEN $1 < 0 THEN 0::float8 WHEN $1/(2*earth()) > 1 THEN pg_catalog.pi()*earth() ELSE 2*earth()*pg_catalog.asin($1/(2*earth())) END;
+
+CREATE FUNCTION gc_to_sec(float8)
+RETURNS float8
+LANGUAGE SQL
+IMMUTABLE STRICT
+PARALLEL SAFE
+RETURN CASE WHEN $1 < 0 THEN 0::float8 WHEN $1/earth() > pg_catalog.pi() THEN 2*earth() ELSE 2*earth()*pg_catalog.sin($1/(2*earth())) END;
+
+CREATE FUNCTION ll_to_earth(float8, float8)
+RETURNS earth
+LANGUAGE SQL
+IMMUTABLE STRICT
+PARALLEL SAFE
+RETURN @extschema:cube@.cube(@extschema:cube@.cube(@extschema:cube@.cube(earth()*pg_catalog.cos(radians($1))*pg_catalog.cos(radians($2))),earth()*pg_catalog.cos(radians($1))*pg_catalog.sin(radians($2))),earth()*pg_catalog.sin(radians($1)))::earth;
+
+CREATE FUNCTION latitude(earth)
+RETURNS float8
+LANGUAGE SQL
+IMMUTABLE STRICT
+PARALLEL SAFE
+RETURN CASE WHEN @extschema:cube@.cube_ll_coord($1, 3)/earth() < -1 THEN -90::float8 WHEN @extschema:cube@.cube_ll_coord($1, 3)/earth() > 1 THEN 90::float8 ELSE pg_catalog.degrees(pg_catalog.asin(@extschema:cube@.cube_ll_coord($1, 3)/earth())) END;
+
+CREATE FUNCTION longitude(earth)
+RETURNS float8
+LANGUAGE SQL
+IMMUTABLE STRICT
+PARALLEL SAFE
+RETURN pg_catalog.degrees(pg_catalog.atan2(@extschema:cube@.cube_ll_coord($1, 2), @extschema:cube@.cube_ll_coord($1, 1)));
+
+CREATE FUNCTION earth_distance(earth, earth)
+RETURNS float8
+LANGUAGE SQL
+IMMUTABLE STRICT
+PARALLEL SAFE
+RETURN sec_to_gc(@extschema:cube@.cube_distance($1, $2));
+
+CREATE FUNCTION earth_box(earth, float8)
+RETURNS cube
+LANGUAGE SQL
+IMMUTABLE STRICT
+PARALLEL SAFE
+RETURN @extschema:cube@.cube_enlarge($1, gc_to_sec($2), 3);
+
+--------------- geo_distance
+
+CREATE FUNCTION geo_distance (point, point)
+RETURNS float8
+LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE AS 'MODULE_PATHNAME';
+
+--------------- geo_distance as operator <@>
+
+CREATE OPERATOR <@> (
+  LEFTARG = point,
+  RIGHTARG = point,
+  PROCEDURE = geo_distance,
+  COMMUTATOR = <@>
+);
diff --git a/contrib/earthdistance/earthdistance.control b/contrib/earthdistance/earthdistance.control
index 5816d22cdd9..de2465d487e 100644
--- a/contrib/earthdistance/earthdistance.control
+++ b/contrib/earthdistance/earthdistance.control
@@ -1,6 +1,6 @@
 # earthdistance extension
 comment = 'calculate great-circle distances on the surface of the Earth'
-default_version = '1.1'
+default_version = '1.2'
 module_pathname = '$libdir/earthdistance'
 relocatable = true
 requires = 'cube'
-- 
2.47.0

From ababdd1ffc8ba6034ba693274bea1997a52ec061 Mon Sep 17 00:00:00 2001
From: Ronan Dunklau <ronan.dunklau@aiven.io>
Date: Mon, 28 Oct 2024 16:13:35 +0100
Subject: [PATCH v2 1/6] Use "new style" SQL function in citext extension

Author: Tom Lane
Discussion: https://www.postgresql.org/message-id/3395418.1618352794%40sss.pgh.pa.us
---
 contrib/citext/Makefile             |  1 +
 contrib/citext/citext--1.7--1.8.sql | 60 +++++++++++++++++++++++++++++
 contrib/citext/citext.control       |  2 +-
 3 files changed, 62 insertions(+), 1 deletion(-)
 create mode 100644 contrib/citext/citext--1.7--1.8.sql

diff --git a/contrib/citext/Makefile b/contrib/citext/Makefile
index b9b3713f537..fc990607bf2 100644
--- a/contrib/citext/Makefile
+++ b/contrib/citext/Makefile
@@ -4,6 +4,7 @@ MODULES = citext
 
 EXTENSION = citext
 DATA = citext--1.4.sql \
+	citext--1.7--1.8.sql \
 	citext--1.6--1.7.sql \
 	citext--1.5--1.6.sql \
 	citext--1.4--1.5.sql \
diff --git a/contrib/citext/citext--1.7--1.8.sql b/contrib/citext/citext--1.7--1.8.sql
new file mode 100644
index 00000000000..7c95a9883aa
--- /dev/null
+++ b/contrib/citext/citext--1.7--1.8.sql
@@ -0,0 +1,60 @@
+/* contrib/citext/citext--1.7--1.8.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION citext UPDATE TO '1.8'" to load this file. \quit
+
+CREATE OR REPLACE FUNCTION regexp_match(string citext, pattern citext) RETURNS TEXT[]
+LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE
+RETURN pg_catalog.regexp_match( $1::pg_catalog.text, $2::pg_catalog.text, 'i' );
+
+CREATE OR REPLACE FUNCTION regexp_match(string citext, pattern citext, flags text) RETURNS TEXT[]
+LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE
+RETURN pg_catalog.regexp_match( $1::pg_catalog.text, $2::pg_catalog.text, CASE WHEN pg_catalog.strpos($3, 'c') = 0 THEN  $3 || 'i' ELSE $3 END );
+
+CREATE OR REPLACE FUNCTION regexp_matches(string citext, pattern citext) RETURNS SETOF TEXT[]
+LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE ROWS 1
+RETURN pg_catalog.regexp_matches( $1::pg_catalog.text, $2::pg_catalog.text, 'i' );
+
+CREATE OR REPLACE FUNCTION regexp_matches(string citext, pattern citext, flags text) RETURNS SETOF TEXT[]
+LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE ROWS 10
+RETURN pg_catalog.regexp_matches( $1::pg_catalog.text, $2::pg_catalog.text, CASE WHEN pg_catalog.strpos($3, 'c') = 0 THEN  $3 || 'i' ELSE $3 END );
+
+CREATE OR REPLACE FUNCTION regexp_replace(string citext, pattern citext, replacement text) returns TEXT
+LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE
+RETURN pg_catalog.regexp_replace( $1::pg_catalog.text, $2::pg_catalog.text, $3, 'i');
+
+CREATE OR REPLACE FUNCTION regexp_replace(string citext, pattern citext, replacement text, flags text) returns TEXT
+LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE
+RETURN pg_catalog.regexp_replace( $1::pg_catalog.text, $2::pg_catalog.text, $3, CASE WHEN pg_catalog.strpos($4, 'c') = 0 THEN  $4 || 'i' ELSE $4 END);
+
+CREATE OR REPLACE FUNCTION regexp_split_to_array(string citext, pattern citext) RETURNS TEXT[]
+LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE
+RETURN pg_catalog.regexp_split_to_array( $1::pg_catalog.text, $2::pg_catalog.text, 'i' );
+
+CREATE OR REPLACE FUNCTION regexp_split_to_array(string citext, pattern citext, flags text) RETURNS TEXT[]
+LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE
+RETURN pg_catalog.regexp_split_to_array( $1::pg_catalog.text, $2::pg_catalog.text, CASE WHEN pg_catalog.strpos($3, 'c') = 0 THEN  $3 || 'i' ELSE $3 END );
+
+CREATE OR REPLACE FUNCTION regexp_split_to_table(string citext, pattern citext) RETURNS SETOF TEXT
+LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE
+RETURN pg_catalog.regexp_split_to_table( $1::pg_catalog.text, $2::pg_catalog.text, 'i' );
+
+CREATE OR REPLACE FUNCTION regexp_split_to_table(string citext, pattern citext, flags text) RETURNS SETOF TEXT
+LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE
+RETURN pg_catalog.regexp_split_to_table( $1::pg_catalog.text, $2::pg_catalog.text, CASE WHEN pg_catalog.strpos($3, 'c') = 0 THEN  $3 || 'i' ELSE $3 END );
+
+CREATE OR REPLACE FUNCTION strpos( citext, citext ) RETURNS INT
+LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE
+RETURN pg_catalog.strpos( pg_catalog.lower( $1::pg_catalog.text ), pg_catalog.lower( $2::pg_catalog.text ) );
+
+CREATE OR REPLACE FUNCTION replace( citext, citext, citext ) RETURNS TEXT
+LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE
+RETURN pg_catalog.regexp_replace( $1::pg_catalog.text, pg_catalog.regexp_replace($2::pg_catalog.text, '([^a-zA-Z_0-9])', E'\\\\\\1', 'g'), $3::pg_catalog.text, 'gi' );
+
+CREATE OR REPLACE FUNCTION split_part( citext, citext, int ) RETURNS TEXT
+LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE
+RETURN (pg_catalog.regexp_split_to_array( $1::pg_catalog.text, pg_catalog.regexp_replace($2::pg_catalog.text, '([^a-zA-Z_0-9])', E'\\\\\\1', 'g'), 'i'))[$3];
+
+CREATE OR REPLACE FUNCTION translate( citext, citext, text ) RETURNS TEXT
+LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE
+RETURN pg_catalog.translate( pg_catalog.translate( $1::pg_catalog.text, pg_catalog.lower($2::pg_catalog.text), $3), pg_catalog.upper($2::pg_catalog.text), $3);
diff --git a/contrib/citext/citext.control b/contrib/citext/citext.control
index f82265b3347..2b0f3fa8407 100644
--- a/contrib/citext/citext.control
+++ b/contrib/citext/citext.control
@@ -1,6 +1,6 @@
 # citext extension
 comment = 'data type for case-insensitive character strings'
-default_version = '1.7'
+default_version = '1.8'
 module_pathname = '$libdir/citext'
 relocatable = true
 trusted = true
-- 
2.47.0

From 56c02afacc77b15cf907cdd32795f32235fe39f5 Mon Sep 17 00:00:00 2001
From: Ronan Dunklau <ronan.dunklau@aiven.io>
Date: Mon, 28 Oct 2024 15:34:45 +0100
Subject: [PATCH v3 2/6] Use "new style" SQL function in earthdistance
 extension.

Since the sql_body is parsed at function definition time, and not when
executing it, the namespace is resolved at that point. This solves a
couple of bugs related to pg_restore failing to locate functions
belonging to an extension once they are inline.

The missing piece from the previous attempt was a syntax to reference
another extension's schema, which has been introduced in PG16. It is
then safe to fully-qualify every object reference either implicitly
because it has just been created, or by the extension schema it belongs
to.

Author: Tom Lane
Discussion: https://www.postgresql.org/message-id/3395418.1618352794%40sss.pgh.pa.us
---
 contrib/earthdistance/Makefile                |  3 +-
 .../earthdistance/earthdistance--1.1--1.2.sql | 58 +++++++++++
 contrib/earthdistance/earthdistance--1.2.sql  | 98 +++++++++++++++++++
 contrib/earthdistance/earthdistance.control   |  2 +-
 contrib/earthdistance/meson.build             |  1 +
 5 files changed, 160 insertions(+), 2 deletions(-)
 create mode 100644 contrib/earthdistance/earthdistance--1.1--1.2.sql
 create mode 100644 contrib/earthdistance/earthdistance--1.2.sql

diff --git a/contrib/earthdistance/Makefile b/contrib/earthdistance/Makefile
index f93b7a925a2..0cf3fa379a2 100644
--- a/contrib/earthdistance/Makefile
+++ b/contrib/earthdistance/Makefile
@@ -3,7 +3,8 @@
 MODULES = earthdistance
 
 EXTENSION = earthdistance
-DATA = earthdistance--1.1.sql earthdistance--1.0--1.1.sql
+DATA = earthdistance--1.1.sql earthdistance--1.0--1.1.sql \
+	earthdistance--1.1--1.2.sql
 PGFILEDESC = "earthdistance - calculate distances on the surface of the Earth"
 
 REGRESS = earthdistance
diff --git a/contrib/earthdistance/earthdistance--1.1--1.2.sql b/contrib/earthdistance/earthdistance--1.1--1.2.sql
new file mode 100644
index 00000000000..f4508fed8f8
--- /dev/null
+++ b/contrib/earthdistance/earthdistance--1.1--1.2.sql
@@ -0,0 +1,58 @@
+/* contrib/earthdistance/earthdistance--1.1--1.2.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION earthdistance UPDATE TO '1.2'" to load this file. \quit
+
+CREATE OR REPLACE FUNCTION earth() RETURNS float8
+LANGUAGE SQL IMMUTABLE PARALLEL SAFE
+RETURN '6378168'::float8;
+
+CREATE OR REPLACE FUNCTION sec_to_gc(float8)
+RETURNS float8
+LANGUAGE SQL
+IMMUTABLE STRICT
+PARALLEL SAFE
+RETURN CASE WHEN $1 < 0 THEN 0::float8 WHEN $1/(2*earth()) > 1 THEN pg_catalog.pi()*earth() ELSE 2*earth()*pg_catalog.asin($1/(2*earth())) END;
+
+CREATE OR REPLACE FUNCTION gc_to_sec(float8)
+RETURNS float8
+LANGUAGE SQL
+IMMUTABLE STRICT
+PARALLEL SAFE
+RETURN CASE WHEN $1 < 0 THEN 0::float8 WHEN $1/earth() > pg_catalog.pi() THEN 2*earth() ELSE 2*earth()*pg_catalog.sin($1/(2*earth())) END;
+
+CREATE OR REPLACE FUNCTION ll_to_earth(float8, float8)
+RETURNS earth
+LANGUAGE SQL
+IMMUTABLE STRICT
+PARALLEL SAFE
+RETURN @extschema:cube@.cube(@extschema:cube@.cube(@extschema:cube@.cube(earth()*pg_catalog.cos(radians($1))*pg_catalog.cos(radians($2))),earth()*pg_catalog.cos(radians($1))*pg_catalog.sin(radians($2))),earth()*pg_catalog.sin(radians($1)))::earth;
+
+CREATE OR REPLACE FUNCTION latitude(earth)
+RETURNS float8
+LANGUAGE SQL
+IMMUTABLE STRICT
+PARALLEL SAFE
+RETURN CASE WHEN @extschema:cube@.cube_ll_coord($1, 3)/earth() < -1 THEN -90::float8 WHEN @extschema:cube@.cube_ll_coord($1, 3)/earth() > 1 THEN 90::float8 ELSE pg_catalog.degrees(pg_catalog.asin(@extschema:cube@.cube_ll_coord($1, 3)/earth())) END;
+
+CREATE OR REPLACE FUNCTION longitude(earth)
+RETURNS float8
+LANGUAGE SQL
+IMMUTABLE STRICT
+PARALLEL SAFE
+RETURN pg_catalog.degrees(pg_catalog.atan2(@extschema:cube@.cube_ll_coord($1, 2), @extschema:cube@.cube_ll_coord($1, 1)));
+
+CREATE OR REPLACE FUNCTION earth_distance(earth, earth)
+RETURNS float8
+LANGUAGE SQL
+IMMUTABLE STRICT
+PARALLEL SAFE
+RETURN sec_to_gc(@extschema:cube@.cube_distance($1, $2));
+
+CREATE OR REPLACE FUNCTION earth_box(earth, float8)
+RETURNS @extschema:cube@.cube
+LANGUAGE SQL
+IMMUTABLE STRICT
+PARALLEL SAFE
+RETURN @extschema:cube@.cube_enlarge($1, gc_to_sec($2), 3);
+
diff --git a/contrib/earthdistance/earthdistance--1.2.sql b/contrib/earthdistance/earthdistance--1.2.sql
new file mode 100644
index 00000000000..fac15969ad4
--- /dev/null
+++ b/contrib/earthdistance/earthdistance--1.2.sql
@@ -0,0 +1,98 @@
+/* contrib/earthdistance/earthdistance--1.1.sql */
+
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION earthdistance" to load this file. \quit
+
+-- earth() returns the radius of the earth in meters. This is the only
+-- place you need to change things for the cube base distance functions
+-- in order to use different units (or a better value for the Earth's radius).
+
+CREATE FUNCTION earth() RETURNS float8
+LANGUAGE SQL IMMUTABLE PARALLEL SAFE
+RETURN '6378168'::float8;
+
+-- Astronomers may want to change the earth function so that distances will be
+-- returned in degrees. To do this comment out the above definition and
+-- uncomment the one below. Note that doing this will break the regression
+-- tests.
+--
+-- CREATE FUNCTION earth() RETURNS float8
+-- LANGUAGE SQL IMMUTABLE
+-- AS 'SELECT 180/pi()';
+
+-- Define domain for locations on the surface of the earth using a cube
+-- datatype with constraints. cube provides 3D indexing.
+-- The cube is restricted to be a point, no more than 3 dimensions
+-- (for less than 3 dimensions 0 is assumed for the missing coordinates)
+-- and that the point must be very near the surface of the sphere
+-- centered about the origin with the radius of the earth.
+
+CREATE DOMAIN earth AS $extschema:cube@.cube
+  CONSTRAINT not_point check($extschema:cube@.cube_is_point(value))
+  CONSTRAINT not_3d check($extschema:cube@.cube_dim(value) <= 3)
+  CONSTRAINT on_surface check(abs($extschema:cube@.cube_distance(value, '(0)'::$extschema:cube@.cube) /
+  earth() - '1'::float8) < '10e-7'::float8);
+
+CREATE FUNCTION sec_to_gc(float8)
+RETURNS float8
+LANGUAGE SQL
+IMMUTABLE STRICT
+PARALLEL SAFE
+RETURN CASE WHEN $1 < 0 THEN 0::float8 WHEN $1/(2*earth()) > 1 THEN pg_catalog.pi()*earth() ELSE 2*earth()*pg_catalog.asin($1/(2*earth())) END;
+
+CREATE FUNCTION gc_to_sec(float8)
+RETURNS float8
+LANGUAGE SQL
+IMMUTABLE STRICT
+PARALLEL SAFE
+RETURN CASE WHEN $1 < 0 THEN 0::float8 WHEN $1/earth() > pg_catalog.pi() THEN 2*earth() ELSE 2*earth()*pg_catalog.sin($1/(2*earth())) END;
+
+CREATE FUNCTION ll_to_earth(float8, float8)
+RETURNS earth
+LANGUAGE SQL
+IMMUTABLE STRICT
+PARALLEL SAFE
+RETURN @extschema:cube@.cube(@extschema:cube@.cube(@extschema:cube@.cube(earth()*pg_catalog.cos(radians($1))*pg_catalog.cos(radians($2))),earth()*pg_catalog.cos(radians($1))*pg_catalog.sin(radians($2))),earth()*pg_catalog.sin(radians($1)))::earth;
+
+CREATE FUNCTION latitude(earth)
+RETURNS float8
+LANGUAGE SQL
+IMMUTABLE STRICT
+PARALLEL SAFE
+RETURN CASE WHEN @extschema:cube@.cube_ll_coord($1, 3)/earth() < -1 THEN -90::float8 WHEN @extschema:cube@.cube_ll_coord($1, 3)/earth() > 1 THEN 90::float8 ELSE pg_catalog.degrees(pg_catalog.asin(@extschema:cube@.cube_ll_coord($1, 3)/earth())) END;
+
+CREATE FUNCTION longitude(earth)
+RETURNS float8
+LANGUAGE SQL
+IMMUTABLE STRICT
+PARALLEL SAFE
+RETURN pg_catalog.degrees(pg_catalog.atan2(@extschema:cube@.cube_ll_coord($1, 2), @extschema:cube@.cube_ll_coord($1, 1)));
+
+CREATE FUNCTION earth_distance(earth, earth)
+RETURNS float8
+LANGUAGE SQL
+IMMUTABLE STRICT
+PARALLEL SAFE
+RETURN sec_to_gc(@extschema:cube@.cube_distance($1, $2));
+
+CREATE FUNCTION earth_box(earth, float8)
+RETURNS cube
+LANGUAGE SQL
+IMMUTABLE STRICT
+PARALLEL SAFE
+RETURN @extschema:cube@.cube_enlarge($1, gc_to_sec($2), 3);
+
+--------------- geo_distance
+
+CREATE FUNCTION geo_distance (point, point)
+RETURNS float8
+LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE AS 'MODULE_PATHNAME';
+
+--------------- geo_distance as operator <@>
+
+CREATE OPERATOR <@> (
+  LEFTARG = point,
+  RIGHTARG = point,
+  PROCEDURE = geo_distance,
+  COMMUTATOR = <@>
+);
diff --git a/contrib/earthdistance/earthdistance.control b/contrib/earthdistance/earthdistance.control
index 5816d22cdd9..de2465d487e 100644
--- a/contrib/earthdistance/earthdistance.control
+++ b/contrib/earthdistance/earthdistance.control
@@ -1,6 +1,6 @@
 # earthdistance extension
 comment = 'calculate great-circle distances on the surface of the Earth'
-default_version = '1.1'
+default_version = '1.2'
 module_pathname = '$libdir/earthdistance'
 relocatable = true
 requires = 'cube'
diff --git a/contrib/earthdistance/meson.build b/contrib/earthdistance/meson.build
index 4e3c538f7aa..be63bf577c3 100644
--- a/contrib/earthdistance/meson.build
+++ b/contrib/earthdistance/meson.build
@@ -20,6 +20,7 @@ install_data(
   'earthdistance.control',
   'earthdistance--1.0--1.1.sql',
   'earthdistance--1.1.sql',
+  'earthdistance--1.1--1.2.sql',
   kwargs: contrib_data_args,
 )
 
-- 
2.47.0

From 826ee3f30a3bc46340709e721ed8be3c3d4afbde Mon Sep 17 00:00:00 2001
From: Ronan Dunklau <ronan.dunklau@aiven.io>
Date: Mon, 28 Oct 2024 16:23:49 +0100
Subject: [PATCH v3 3/6] Use "new style" SQL function in pageinspect extension

Author: Tom Lane
Discussion: https://www.postgresql.org/message-id/3395418.1618352794%40sss.pgh.pa.us
---
 contrib/pageinspect/Makefile                  |  3 +-
 contrib/pageinspect/meson.build               |  1 +
 .../pageinspect/pageinspect--1.12--1.13.sql   | 74 +++++++++++++++++++
 contrib/pageinspect/pageinspect.control       |  2 +-
 4 files changed, 78 insertions(+), 2 deletions(-)
 create mode 100644 contrib/pageinspect/pageinspect--1.12--1.13.sql

diff --git a/contrib/pageinspect/Makefile b/contrib/pageinspect/Makefile
index 95e030b3969..9dee7653310 100644
--- a/contrib/pageinspect/Makefile
+++ b/contrib/pageinspect/Makefile
@@ -13,7 +13,8 @@ OBJS = \
 	rawpage.o
 
 EXTENSION = pageinspect
-DATA =  pageinspect--1.11--1.12.sql pageinspect--1.10--1.11.sql \
+DATA =  pageinspect--1.12--1.13.sql \
+	pageinspect--1.11--1.12.sql pageinspect--1.10--1.11.sql \
 	pageinspect--1.9--1.10.sql pageinspect--1.8--1.9.sql \
 	pageinspect--1.7--1.8.sql pageinspect--1.6--1.7.sql \
 	pageinspect--1.5.sql pageinspect--1.5--1.6.sql \
diff --git a/contrib/pageinspect/meson.build b/contrib/pageinspect/meson.build
index 27a007c1df3..689b9d1dba2 100644
--- a/contrib/pageinspect/meson.build
+++ b/contrib/pageinspect/meson.build
@@ -37,6 +37,7 @@ install_data(
   'pageinspect--1.9--1.10.sql',
   'pageinspect--1.10--1.11.sql',
   'pageinspect--1.11--1.12.sql',
+  'pageinspect--1.12--1.13.sql',
   'pageinspect.control',
   kwargs: contrib_data_args,
 )
diff --git a/contrib/pageinspect/pageinspect--1.12--1.13.sql b/contrib/pageinspect/pageinspect--1.12--1.13.sql
new file mode 100644
index 00000000000..f444fba58ce
--- /dev/null
+++ b/contrib/pageinspect/pageinspect--1.12--1.13.sql
@@ -0,0 +1,74 @@
+/* contrib/pageinspect/pageinspect--1.12--1.13.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION pageinspect UPDATE TO '1.13'" to load this file. \quit
+
+-- Convert SQL functions to new style
+
+CREATE OR REPLACE FUNCTION heap_page_item_attrs(
+    IN page bytea,
+    IN rel_oid regclass,
+    IN do_detoast bool,
+    OUT lp smallint,
+    OUT lp_off smallint,
+    OUT lp_flags smallint,
+    OUT lp_len smallint,
+    OUT t_xmin xid,
+    OUT t_xmax xid,
+    OUT t_field3 int4,
+    OUT t_ctid tid,
+    OUT t_infomask2 integer,
+    OUT t_infomask integer,
+    OUT t_hoff smallint,
+    OUT t_bits text,
+    OUT t_oid oid,
+    OUT t_attrs bytea[]
+    )
+RETURNS SETOF record
+LANGUAGE SQL PARALLEL SAFE
+BEGIN ATOMIC
+SELECT lp,
+       lp_off,
+       lp_flags,
+       lp_len,
+       t_xmin,
+       t_xmax,
+       t_field3,
+       t_ctid,
+       t_infomask2,
+       t_infomask,
+       t_hoff,
+       t_bits,
+       t_oid,
+       tuple_data_split(
+         rel_oid,
+         t_data,
+         t_infomask,
+         t_infomask2,
+         t_bits,
+         do_detoast)
+         AS t_attrs
+  FROM heap_page_items(page);
+END;
+
+CREATE OR REPLACE FUNCTION heap_page_item_attrs(IN page bytea, IN rel_oid regclass,
+    OUT lp smallint,
+    OUT lp_off smallint,
+    OUT lp_flags smallint,
+    OUT lp_len smallint,
+    OUT t_xmin xid,
+    OUT t_xmax xid,
+    OUT t_field3 int4,
+    OUT t_ctid tid,
+    OUT t_infomask2 integer,
+    OUT t_infomask integer,
+    OUT t_hoff smallint,
+    OUT t_bits text,
+    OUT t_oid oid,
+    OUT t_attrs bytea[]
+    )
+RETURNS SETOF record
+LANGUAGE SQL PARALLEL SAFE
+BEGIN ATOMIC
+SELECT * FROM heap_page_item_attrs(page, rel_oid, false);
+END;
diff --git a/contrib/pageinspect/pageinspect.control b/contrib/pageinspect/pageinspect.control
index b2804e9b128..cfc87feac03 100644
--- a/contrib/pageinspect/pageinspect.control
+++ b/contrib/pageinspect/pageinspect.control
@@ -1,5 +1,5 @@
 # pageinspect extension
 comment = 'inspect the contents of database pages at a low level'
-default_version = '1.12'
+default_version = '1.13'
 module_pathname = '$libdir/pageinspect'
 relocatable = true
-- 
2.47.0

From 4f4ec3900622f753ccff773571c46e71d6e797a2 Mon Sep 17 00:00:00 2001
From: Ronan Dunklau <ronan.dunklau@aiven.io>
Date: Mon, 28 Oct 2024 16:24:46 +0100
Subject: [PATCH v3 4/6] Use "new style" SQL function in pg_freespacemap
 extension

Author: Tom Lane
Discussion: https://www.postgresql.org/message-id/3395418.1618352794%40sss.pgh.pa.us
---
 contrib/pg_freespacemap/Makefile                    |  4 +++-
 contrib/pg_freespacemap/meson.build                 |  1 +
 .../pg_freespacemap/pg_freespacemap--1.2--1.3.sql   | 13 +++++++++++++
 contrib/pg_freespacemap/pg_freespacemap.control     |  2 +-
 4 files changed, 18 insertions(+), 2 deletions(-)
 create mode 100644 contrib/pg_freespacemap/pg_freespacemap--1.2--1.3.sql

diff --git a/contrib/pg_freespacemap/Makefile b/contrib/pg_freespacemap/Makefile
index b48e4b255bc..a986ae73d69 100644
--- a/contrib/pg_freespacemap/Makefile
+++ b/contrib/pg_freespacemap/Makefile
@@ -6,7 +6,9 @@ OBJS = \
 	pg_freespacemap.o
 
 EXTENSION = pg_freespacemap
-DATA = pg_freespacemap--1.1.sql pg_freespacemap--1.1--1.2.sql \
+DATA = pg_freespacemap--1.1.sql \
+	pg_freespacemap--1.2--1.3.sql \
+	pg_freespacemap--1.1--1.2.sql \
 	pg_freespacemap--1.0--1.1.sql
 PGFILEDESC = "pg_freespacemap - monitoring of free space map"
 
diff --git a/contrib/pg_freespacemap/meson.build b/contrib/pg_freespacemap/meson.build
index af5d2b8c27c..42ff1e74cc3 100644
--- a/contrib/pg_freespacemap/meson.build
+++ b/contrib/pg_freespacemap/meson.build
@@ -20,6 +20,7 @@ install_data(
   'pg_freespacemap--1.0--1.1.sql',
   'pg_freespacemap--1.1--1.2.sql',
   'pg_freespacemap--1.1.sql',
+  'pg_freespacemap--1.2--1.3.sql',
   'pg_freespacemap.control',
   kwargs: contrib_data_args,
 )
diff --git a/contrib/pg_freespacemap/pg_freespacemap--1.2--1.3.sql b/contrib/pg_freespacemap/pg_freespacemap--1.2--1.3.sql
new file mode 100644
index 00000000000..7f92c9e92e3
--- /dev/null
+++ b/contrib/pg_freespacemap/pg_freespacemap--1.2--1.3.sql
@@ -0,0 +1,13 @@
+/* contrib/pg_freespacemap/pg_freespacemap--1.2--1.3.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION pg_freespacemap UPDATE TO '1.3'" to load this file. \quit
+
+CREATE OR REPLACE FUNCTION
+  pg_freespace(rel regclass, blkno OUT bigint, avail OUT int2)
+RETURNS SETOF RECORD
+LANGUAGE SQL PARALLEL SAFE
+BEGIN ATOMIC
+  SELECT blkno, pg_freespace($1, blkno) AS avail
+  FROM generate_series(0, pg_relation_size($1) / current_setting('block_size')::bigint - 1) AS blkno;
+END;
diff --git a/contrib/pg_freespacemap/pg_freespacemap.control b/contrib/pg_freespacemap/pg_freespacemap.control
index ac8fc5050a9..1992320691b 100644
--- a/contrib/pg_freespacemap/pg_freespacemap.control
+++ b/contrib/pg_freespacemap/pg_freespacemap.control
@@ -1,5 +1,5 @@
 # pg_freespacemap extension
 comment = 'examine the free space map (FSM)'
-default_version = '1.2'
+default_version = '1.3'
 module_pathname = '$libdir/pg_freespacemap'
 relocatable = true
-- 
2.47.0

From 82057ef832c9a982809bcc4b31c685f94d8f684d Mon Sep 17 00:00:00 2001
From: Ronan Dunklau <ronan.dunklau@aiven.io>
Date: Mon, 28 Oct 2024 16:25:52 +0100
Subject: [PATCH v3 5/6] Use "new style" SQL function in xml2 extension

Author: Tom Lane
Discussion: https://www.postgresql.org/message-id/3395418.1618352794%40sss.pgh.pa.us
---
 contrib/xml2/Makefile           |  4 +++-
 contrib/xml2/meson.build        |  1 +
 contrib/xml2/xml2--1.1--1.2.sql | 18 ++++++++++++++++++
 contrib/xml2/xml2.control       |  2 +-
 4 files changed, 23 insertions(+), 2 deletions(-)
 create mode 100644 contrib/xml2/xml2--1.1--1.2.sql

diff --git a/contrib/xml2/Makefile b/contrib/xml2/Makefile
index 0d703fe0e8f..8597e9aa9c5 100644
--- a/contrib/xml2/Makefile
+++ b/contrib/xml2/Makefile
@@ -7,7 +7,9 @@ OBJS = \
 	xslt_proc.o
 
 EXTENSION = xml2
-DATA = xml2--1.1.sql xml2--1.0--1.1.sql
+DATA = xml2--1.1.sql \
+	xml2--1.1--1.2.sql \
+	xml2--1.0--1.1.sql
 PGFILEDESC = "xml2 - XPath querying and XSLT"
 
 REGRESS = xml2
diff --git a/contrib/xml2/meson.build b/contrib/xml2/meson.build
index 5e80e17f824..32d9ab53cbd 100644
--- a/contrib/xml2/meson.build
+++ b/contrib/xml2/meson.build
@@ -27,6 +27,7 @@ contrib_targets += xml2
 install_data(
   'xml2--1.0--1.1.sql',
   'xml2--1.1.sql',
+  'xml2--1.1--1.2.sql',
   'xml2.control',
   kwargs: contrib_data_args,
 )
diff --git a/contrib/xml2/xml2--1.1--1.2.sql b/contrib/xml2/xml2--1.1--1.2.sql
new file mode 100644
index 00000000000..12d00064e8f
--- /dev/null
+++ b/contrib/xml2/xml2--1.1--1.2.sql
@@ -0,0 +1,18 @@
+/* contrib/xml2/xml2--1.1--1.2.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION xml2 UPDATE TO '1.2'" to load this file. \quit
+
+CREATE OR REPLACE FUNCTION xpath_list(text,text) RETURNS text
+LANGUAGE SQL STRICT IMMUTABLE PARALLEL SAFE
+RETURN xpath_list($1,$2,',');
+
+CREATE OR REPLACE FUNCTION xpath_nodeset(text,text)
+RETURNS text
+LANGUAGE SQL STRICT IMMUTABLE PARALLEL SAFE
+RETURN xpath_nodeset($1,$2,'','');
+
+CREATE OR REPLACE FUNCTION xpath_nodeset(text,text,text)
+RETURNS text
+LANGUAGE SQL STRICT IMMUTABLE PARALLEL SAFE
+RETURN xpath_nodeset($1,$2,'',$3);
diff --git a/contrib/xml2/xml2.control b/contrib/xml2/xml2.control
index ba2c0599a37..b32156c949e 100644
--- a/contrib/xml2/xml2.control
+++ b/contrib/xml2/xml2.control
@@ -1,6 +1,6 @@
 # xml2 extension
 comment = 'XPath querying and XSLT'
-default_version = '1.1'
+default_version = '1.2'
 module_pathname = '$libdir/pgxml'
 # XXX do we still need this to be non-relocatable?
 relocatable = false
-- 
2.47.0

From f00c5ef637ab216fac707081959abff1926ebeb6 Mon Sep 17 00:00:00 2001
From: Ronan Dunklau <ronan.dunklau@aiven.io>
Date: Tue, 29 Oct 2024 07:53:02 +0100
Subject: [PATCH v3 6/6] Use "new style" SQL function in lo extension

---
 contrib/lo/Makefile         | 2 +-
 contrib/lo/lo--1.1--1.2.sql | 9 +++++++++
 contrib/lo/lo.control       | 2 +-
 contrib/lo/meson.build      | 1 +
 4 files changed, 12 insertions(+), 2 deletions(-)
 create mode 100644 contrib/lo/lo--1.1--1.2.sql

diff --git a/contrib/lo/Makefile b/contrib/lo/Makefile
index 716893859eb..8cfff63d882 100644
--- a/contrib/lo/Makefile
+++ b/contrib/lo/Makefile
@@ -3,7 +3,7 @@
 MODULES = lo
 
 EXTENSION = lo
-DATA = lo--1.1.sql lo--1.0--1.1.sql
+DATA = lo--1.1.sql lo--1.0--1.1.sql lo--1.1--1.2.sql
 PGFILEDESC = "lo - management for large objects"
 
 REGRESS = lo
diff --git a/contrib/lo/lo--1.1--1.2.sql b/contrib/lo/lo--1.1--1.2.sql
new file mode 100644
index 00000000000..4cad3e689dd
--- /dev/null
+++ b/contrib/lo/lo--1.1--1.2.sql
@@ -0,0 +1,9 @@
+/* contrib/lo/lo--1.1--1.2.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION lo UPDATE TO '1.2'" to load this file. \quit
+
+CREATE OR REPLACE FUNCTION lo_oid(lo) RETURNS pg_catalog.oid
+LANGUAGE SQL STRICT IMMUTABLE PARALLEL SAFE
+RETURN (SELECT $1::pg_catalog.oid);
+
diff --git a/contrib/lo/lo.control b/contrib/lo/lo.control
index f73f8b5fae5..3acb3665a4e 100644
--- a/contrib/lo/lo.control
+++ b/contrib/lo/lo.control
@@ -1,6 +1,6 @@
 # lo extension
 comment = 'Large Object maintenance'
-default_version = '1.1'
+default_version = '1.2'
 module_pathname = '$libdir/lo'
 relocatable = true
 trusted = true
diff --git a/contrib/lo/meson.build b/contrib/lo/meson.build
index 4cc72b64578..1443a260b27 100644
--- a/contrib/lo/meson.build
+++ b/contrib/lo/meson.build
@@ -20,6 +20,7 @@ install_data(
   'lo.control',
   'lo--1.0--1.1.sql',
   'lo--1.1.sql',
+  'lo--1.1--1.2.sql',
   kwargs: contrib_data_args,
 )
 
-- 
2.47.0

From 96e7794f49384107b92649f4314e7e1f27ad4b75 Mon Sep 17 00:00:00 2001
From: Ronan Dunklau <ronan.dunklau@aiven.io>
Date: Mon, 28 Oct 2024 16:13:35 +0100
Subject: [PATCH v3 1/6] Use "new style" SQL function in citext extension

Author: Tom Lane
Discussion: https://www.postgresql.org/message-id/3395418.1618352794%40sss.pgh.pa.us
---
 contrib/citext/Makefile             |  1 +
 contrib/citext/citext--1.7--1.8.sql | 60 +++++++++++++++++++++++++++++
 contrib/citext/citext.control       |  2 +-
 contrib/citext/meson.build          |  1 +
 4 files changed, 63 insertions(+), 1 deletion(-)
 create mode 100644 contrib/citext/citext--1.7--1.8.sql

diff --git a/contrib/citext/Makefile b/contrib/citext/Makefile
index b9b3713f537..fc990607bf2 100644
--- a/contrib/citext/Makefile
+++ b/contrib/citext/Makefile
@@ -4,6 +4,7 @@ MODULES = citext
 
 EXTENSION = citext
 DATA = citext--1.4.sql \
+	citext--1.7--1.8.sql \
 	citext--1.6--1.7.sql \
 	citext--1.5--1.6.sql \
 	citext--1.4--1.5.sql \
diff --git a/contrib/citext/citext--1.7--1.8.sql b/contrib/citext/citext--1.7--1.8.sql
new file mode 100644
index 00000000000..7c95a9883aa
--- /dev/null
+++ b/contrib/citext/citext--1.7--1.8.sql
@@ -0,0 +1,60 @@
+/* contrib/citext/citext--1.7--1.8.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION citext UPDATE TO '1.8'" to load this file. \quit
+
+CREATE OR REPLACE FUNCTION regexp_match(string citext, pattern citext) RETURNS TEXT[]
+LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE
+RETURN pg_catalog.regexp_match( $1::pg_catalog.text, $2::pg_catalog.text, 'i' );
+
+CREATE OR REPLACE FUNCTION regexp_match(string citext, pattern citext, flags text) RETURNS TEXT[]
+LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE
+RETURN pg_catalog.regexp_match( $1::pg_catalog.text, $2::pg_catalog.text, CASE WHEN pg_catalog.strpos($3, 'c') = 0 THEN  $3 || 'i' ELSE $3 END );
+
+CREATE OR REPLACE FUNCTION regexp_matches(string citext, pattern citext) RETURNS SETOF TEXT[]
+LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE ROWS 1
+RETURN pg_catalog.regexp_matches( $1::pg_catalog.text, $2::pg_catalog.text, 'i' );
+
+CREATE OR REPLACE FUNCTION regexp_matches(string citext, pattern citext, flags text) RETURNS SETOF TEXT[]
+LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE ROWS 10
+RETURN pg_catalog.regexp_matches( $1::pg_catalog.text, $2::pg_catalog.text, CASE WHEN pg_catalog.strpos($3, 'c') = 0 THEN  $3 || 'i' ELSE $3 END );
+
+CREATE OR REPLACE FUNCTION regexp_replace(string citext, pattern citext, replacement text) returns TEXT
+LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE
+RETURN pg_catalog.regexp_replace( $1::pg_catalog.text, $2::pg_catalog.text, $3, 'i');
+
+CREATE OR REPLACE FUNCTION regexp_replace(string citext, pattern citext, replacement text, flags text) returns TEXT
+LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE
+RETURN pg_catalog.regexp_replace( $1::pg_catalog.text, $2::pg_catalog.text, $3, CASE WHEN pg_catalog.strpos($4, 'c') = 0 THEN  $4 || 'i' ELSE $4 END);
+
+CREATE OR REPLACE FUNCTION regexp_split_to_array(string citext, pattern citext) RETURNS TEXT[]
+LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE
+RETURN pg_catalog.regexp_split_to_array( $1::pg_catalog.text, $2::pg_catalog.text, 'i' );
+
+CREATE OR REPLACE FUNCTION regexp_split_to_array(string citext, pattern citext, flags text) RETURNS TEXT[]
+LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE
+RETURN pg_catalog.regexp_split_to_array( $1::pg_catalog.text, $2::pg_catalog.text, CASE WHEN pg_catalog.strpos($3, 'c') = 0 THEN  $3 || 'i' ELSE $3 END );
+
+CREATE OR REPLACE FUNCTION regexp_split_to_table(string citext, pattern citext) RETURNS SETOF TEXT
+LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE
+RETURN pg_catalog.regexp_split_to_table( $1::pg_catalog.text, $2::pg_catalog.text, 'i' );
+
+CREATE OR REPLACE FUNCTION regexp_split_to_table(string citext, pattern citext, flags text) RETURNS SETOF TEXT
+LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE
+RETURN pg_catalog.regexp_split_to_table( $1::pg_catalog.text, $2::pg_catalog.text, CASE WHEN pg_catalog.strpos($3, 'c') = 0 THEN  $3 || 'i' ELSE $3 END );
+
+CREATE OR REPLACE FUNCTION strpos( citext, citext ) RETURNS INT
+LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE
+RETURN pg_catalog.strpos( pg_catalog.lower( $1::pg_catalog.text ), pg_catalog.lower( $2::pg_catalog.text ) );
+
+CREATE OR REPLACE FUNCTION replace( citext, citext, citext ) RETURNS TEXT
+LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE
+RETURN pg_catalog.regexp_replace( $1::pg_catalog.text, pg_catalog.regexp_replace($2::pg_catalog.text, '([^a-zA-Z_0-9])', E'\\\\\\1', 'g'), $3::pg_catalog.text, 'gi' );
+
+CREATE OR REPLACE FUNCTION split_part( citext, citext, int ) RETURNS TEXT
+LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE
+RETURN (pg_catalog.regexp_split_to_array( $1::pg_catalog.text, pg_catalog.regexp_replace($2::pg_catalog.text, '([^a-zA-Z_0-9])', E'\\\\\\1', 'g'), 'i'))[$3];
+
+CREATE OR REPLACE FUNCTION translate( citext, citext, text ) RETURNS TEXT
+LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE
+RETURN pg_catalog.translate( pg_catalog.translate( $1::pg_catalog.text, pg_catalog.lower($2::pg_catalog.text), $3), pg_catalog.upper($2::pg_catalog.text), $3);
diff --git a/contrib/citext/citext.control b/contrib/citext/citext.control
index f82265b3347..2b0f3fa8407 100644
--- a/contrib/citext/citext.control
+++ b/contrib/citext/citext.control
@@ -1,6 +1,6 @@
 # citext extension
 comment = 'data type for case-insensitive character strings'
-default_version = '1.7'
+default_version = '1.8'
 module_pathname = '$libdir/citext'
 relocatable = true
 trusted = true
diff --git a/contrib/citext/meson.build b/contrib/citext/meson.build
index 40cdd0d2f18..ae87445e6a4 100644
--- a/contrib/citext/meson.build
+++ b/contrib/citext/meson.build
@@ -26,6 +26,7 @@ install_data(
   'citext--1.4--1.5.sql',
   'citext--1.5--1.6.sql',
   'citext--1.6--1.7.sql',
+  'citext--1.7--1.8.sql',
   kwargs: contrib_data_args,
 )
 
-- 
2.47.0

From 48f04a556536426fc7022ac33477f1629a31ca51 Mon Sep 17 00:00:00 2001
From: Ronan Dunklau <ronan.dunklau@aiven.io>
Date: Wed, 13 Nov 2024 08:28:37 +0100
Subject: [PATCH v4 1/6] Add tests for xpath_nodeset and xpath_list functions.

---
 contrib/xml2/expected/xml2.out | 30 ++++++++++++++++++++++++++++++
 contrib/xml2/sql/xml2.sql      |  8 ++++++++
 2 files changed, 38 insertions(+)

diff --git a/contrib/xml2/expected/xml2.out b/contrib/xml2/expected/xml2.out
index eba6ae60364..5b292090982 100644
--- a/contrib/xml2/expected/xml2.out
+++ b/contrib/xml2/expected/xml2.out
@@ -222,3 +222,33 @@ $$<xsl:stylesheet version="1.0"
   </xsl:template>
 </xsl:stylesheet>$$);
 ERROR:  failed to apply stylesheet
+SELECT xpath_nodeset(article_xml::text, '/article/author|/article/pages', 'result', 'item') from articles;
+                                   xpath_nodeset                                   
+-----------------------------------------------------------------------------------
+ <result><item><author>test</author></item><item><pages>37</pages></item></result>
+(1 row)
+
+SELECT xpath_nodeset(article_xml::text, '/article/author|/article/pages', 'item_without_toptag') from articles;
+                                                        xpath_nodeset                                                         
+------------------------------------------------------------------------------------------------------------------------------
+ <item_without_toptag><author>test</author></item_without_toptag><item_without_toptag><pages>37</pages></item_without_toptag>
+(1 row)
+
+SELECT xpath_nodeset(article_xml::text, '/article/author|/article/pages') from articles;
+             xpath_nodeset              
+----------------------------------------
+ <author>test</author><pages>37</pages>
+(1 row)
+
+SELECT xpath_list(article_xml::text, '/article/author|/article/pages') from articles;
+ xpath_list 
+------------
+ test,37
+(1 row)
+
+SELECT xpath_list(article_xml::text, '/article/author|/article/pages', '|') from articles;
+ xpath_list 
+------------
+ test|37
+(1 row)
+
diff --git a/contrib/xml2/sql/xml2.sql b/contrib/xml2/sql/xml2.sql
index ac49cfa7c52..5eae992b55b 100644
--- a/contrib/xml2/sql/xml2.sql
+++ b/contrib/xml2/sql/xml2.sql
@@ -137,3 +137,11 @@ $$<xsl:stylesheet version="1.0"
     </sax:output>
   </xsl:template>
 </xsl:stylesheet>$$);
+
+SELECT xpath_nodeset(article_xml::text, '/article/author|/article/pages', 'result', 'item') from articles;
+SELECT xpath_nodeset(article_xml::text, '/article/author|/article/pages', 'item_without_toptag') from articles;
+SELECT xpath_nodeset(article_xml::text, '/article/author|/article/pages') from articles;
+
+SELECT xpath_list(article_xml::text, '/article/author|/article/pages') from articles;
+SELECT xpath_list(article_xml::text, '/article/author|/article/pages', '|') from articles;
+
-- 
2.47.0

From 5f027b8c4021c13c8e97d1dc56fa6548824c90c9 Mon Sep 17 00:00:00 2001
From: Ronan Dunklau <ronan.dunklau@aiven.io>
Date: Wed, 13 Nov 2024 08:45:13 +0100
Subject: [PATCH v4 2/6] Add test for lo_oid function

---
 contrib/lo/expected/lo.out | 6 ++++++
 contrib/lo/sql/lo.sql      | 2 ++
 2 files changed, 8 insertions(+)

diff --git a/contrib/lo/expected/lo.out b/contrib/lo/expected/lo.out
index c63e4b1c704..65798205a5a 100644
--- a/contrib/lo/expected/lo.out
+++ b/contrib/lo/expected/lo.out
@@ -47,4 +47,10 @@ SELECT lo_get(43214);
 DELETE FROM image;
 SELECT lo_get(43214);
 ERROR:  large object 43214 does not exist
+SELECT lo_oid(1::lo);
+ lo_oid 
+--------
+      1
+(1 row)
+
 DROP TABLE image;
diff --git a/contrib/lo/sql/lo.sql b/contrib/lo/sql/lo.sql
index 77039509245..ca36cdb3098 100644
--- a/contrib/lo/sql/lo.sql
+++ b/contrib/lo/sql/lo.sql
@@ -27,4 +27,6 @@ DELETE FROM image;
 
 SELECT lo_get(43214);
 
+SELECT lo_oid(1::lo);
+
 DROP TABLE image;
-- 
2.47.0

From 9c5235cd123eeb55b95b8bfd281dfcc37df197c5 Mon Sep 17 00:00:00 2001
From: Ronan Dunklau <ronan.dunklau@aiven.io>
Date: Mon, 28 Oct 2024 16:13:35 +0100
Subject: [PATCH v4 3/6] Use "new style" SQL function in citext extension

Author: Tom Lane
Discussion: https://www.postgresql.org/message-id/3395418.1618352794%40sss.pgh.pa.us
---
 contrib/citext/Makefile             |  1 +
 contrib/citext/citext--1.7--1.8.sql | 60 +++++++++++++++++++++++++++++
 contrib/citext/citext.control       |  2 +-
 contrib/citext/meson.build          |  1 +
 4 files changed, 63 insertions(+), 1 deletion(-)
 create mode 100644 contrib/citext/citext--1.7--1.8.sql

diff --git a/contrib/citext/Makefile b/contrib/citext/Makefile
index b9b3713f537..fc990607bf2 100644
--- a/contrib/citext/Makefile
+++ b/contrib/citext/Makefile
@@ -4,6 +4,7 @@ MODULES = citext
 
 EXTENSION = citext
 DATA = citext--1.4.sql \
+	citext--1.7--1.8.sql \
 	citext--1.6--1.7.sql \
 	citext--1.5--1.6.sql \
 	citext--1.4--1.5.sql \
diff --git a/contrib/citext/citext--1.7--1.8.sql b/contrib/citext/citext--1.7--1.8.sql
new file mode 100644
index 00000000000..7c95a9883aa
--- /dev/null
+++ b/contrib/citext/citext--1.7--1.8.sql
@@ -0,0 +1,60 @@
+/* contrib/citext/citext--1.7--1.8.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION citext UPDATE TO '1.8'" to load this file. \quit
+
+CREATE OR REPLACE FUNCTION regexp_match(string citext, pattern citext) RETURNS TEXT[]
+LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE
+RETURN pg_catalog.regexp_match( $1::pg_catalog.text, $2::pg_catalog.text, 'i' );
+
+CREATE OR REPLACE FUNCTION regexp_match(string citext, pattern citext, flags text) RETURNS TEXT[]
+LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE
+RETURN pg_catalog.regexp_match( $1::pg_catalog.text, $2::pg_catalog.text, CASE WHEN pg_catalog.strpos($3, 'c') = 0 THEN  $3 || 'i' ELSE $3 END );
+
+CREATE OR REPLACE FUNCTION regexp_matches(string citext, pattern citext) RETURNS SETOF TEXT[]
+LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE ROWS 1
+RETURN pg_catalog.regexp_matches( $1::pg_catalog.text, $2::pg_catalog.text, 'i' );
+
+CREATE OR REPLACE FUNCTION regexp_matches(string citext, pattern citext, flags text) RETURNS SETOF TEXT[]
+LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE ROWS 10
+RETURN pg_catalog.regexp_matches( $1::pg_catalog.text, $2::pg_catalog.text, CASE WHEN pg_catalog.strpos($3, 'c') = 0 THEN  $3 || 'i' ELSE $3 END );
+
+CREATE OR REPLACE FUNCTION regexp_replace(string citext, pattern citext, replacement text) returns TEXT
+LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE
+RETURN pg_catalog.regexp_replace( $1::pg_catalog.text, $2::pg_catalog.text, $3, 'i');
+
+CREATE OR REPLACE FUNCTION regexp_replace(string citext, pattern citext, replacement text, flags text) returns TEXT
+LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE
+RETURN pg_catalog.regexp_replace( $1::pg_catalog.text, $2::pg_catalog.text, $3, CASE WHEN pg_catalog.strpos($4, 'c') = 0 THEN  $4 || 'i' ELSE $4 END);
+
+CREATE OR REPLACE FUNCTION regexp_split_to_array(string citext, pattern citext) RETURNS TEXT[]
+LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE
+RETURN pg_catalog.regexp_split_to_array( $1::pg_catalog.text, $2::pg_catalog.text, 'i' );
+
+CREATE OR REPLACE FUNCTION regexp_split_to_array(string citext, pattern citext, flags text) RETURNS TEXT[]
+LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE
+RETURN pg_catalog.regexp_split_to_array( $1::pg_catalog.text, $2::pg_catalog.text, CASE WHEN pg_catalog.strpos($3, 'c') = 0 THEN  $3 || 'i' ELSE $3 END );
+
+CREATE OR REPLACE FUNCTION regexp_split_to_table(string citext, pattern citext) RETURNS SETOF TEXT
+LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE
+RETURN pg_catalog.regexp_split_to_table( $1::pg_catalog.text, $2::pg_catalog.text, 'i' );
+
+CREATE OR REPLACE FUNCTION regexp_split_to_table(string citext, pattern citext, flags text) RETURNS SETOF TEXT
+LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE
+RETURN pg_catalog.regexp_split_to_table( $1::pg_catalog.text, $2::pg_catalog.text, CASE WHEN pg_catalog.strpos($3, 'c') = 0 THEN  $3 || 'i' ELSE $3 END );
+
+CREATE OR REPLACE FUNCTION strpos( citext, citext ) RETURNS INT
+LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE
+RETURN pg_catalog.strpos( pg_catalog.lower( $1::pg_catalog.text ), pg_catalog.lower( $2::pg_catalog.text ) );
+
+CREATE OR REPLACE FUNCTION replace( citext, citext, citext ) RETURNS TEXT
+LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE
+RETURN pg_catalog.regexp_replace( $1::pg_catalog.text, pg_catalog.regexp_replace($2::pg_catalog.text, '([^a-zA-Z_0-9])', E'\\\\\\1', 'g'), $3::pg_catalog.text, 'gi' );
+
+CREATE OR REPLACE FUNCTION split_part( citext, citext, int ) RETURNS TEXT
+LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE
+RETURN (pg_catalog.regexp_split_to_array( $1::pg_catalog.text, pg_catalog.regexp_replace($2::pg_catalog.text, '([^a-zA-Z_0-9])', E'\\\\\\1', 'g'), 'i'))[$3];
+
+CREATE OR REPLACE FUNCTION translate( citext, citext, text ) RETURNS TEXT
+LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE
+RETURN pg_catalog.translate( pg_catalog.translate( $1::pg_catalog.text, pg_catalog.lower($2::pg_catalog.text), $3), pg_catalog.upper($2::pg_catalog.text), $3);
diff --git a/contrib/citext/citext.control b/contrib/citext/citext.control
index f82265b3347..2b0f3fa8407 100644
--- a/contrib/citext/citext.control
+++ b/contrib/citext/citext.control
@@ -1,6 +1,6 @@
 # citext extension
 comment = 'data type for case-insensitive character strings'
-default_version = '1.7'
+default_version = '1.8'
 module_pathname = '$libdir/citext'
 relocatable = true
 trusted = true
diff --git a/contrib/citext/meson.build b/contrib/citext/meson.build
index 40cdd0d2f18..ae87445e6a4 100644
--- a/contrib/citext/meson.build
+++ b/contrib/citext/meson.build
@@ -26,6 +26,7 @@ install_data(
   'citext--1.4--1.5.sql',
   'citext--1.5--1.6.sql',
   'citext--1.6--1.7.sql',
+  'citext--1.7--1.8.sql',
   kwargs: contrib_data_args,
 )
 
-- 
2.47.0

From a371f0be44a875d688a536fbae9ae2593d58154f Mon Sep 17 00:00:00 2001
From: Ronan Dunklau <ronan.dunklau@aiven.io>
Date: Mon, 28 Oct 2024 15:34:45 +0100
Subject: [PATCH v4 4/6] Use "new style" SQL function in earthdistance
 extension.

Since the sql_body is parsed at function definition time, and not when
executing it, the namespace is resolved at that point. This solves a
couple of bugs related to pg_restore failing to locate functions
belonging to an extension once they are inline.

The missing piece from the previous attempt was a syntax to reference
another extension's schema, which has been introduced in PG16. It is
then safe to fully-qualify every object reference either implicitly
because it has just been created, or by the extension schema it belongs
to.

Author: Tom Lane
Discussion: https://www.postgresql.org/message-id/3395418.1618352794%40sss.pgh.pa.us
---
 contrib/earthdistance/Makefile                |  3 +-
 .../earthdistance/earthdistance--1.1--1.2.sql | 57 +++++++++++
 contrib/earthdistance/earthdistance--1.2.sql  | 98 +++++++++++++++++++
 contrib/earthdistance/earthdistance.control   |  2 +-
 contrib/earthdistance/meson.build             |  1 +
 5 files changed, 159 insertions(+), 2 deletions(-)
 create mode 100644 contrib/earthdistance/earthdistance--1.1--1.2.sql
 create mode 100644 contrib/earthdistance/earthdistance--1.2.sql

diff --git a/contrib/earthdistance/Makefile b/contrib/earthdistance/Makefile
index f93b7a925a2..0cf3fa379a2 100644
--- a/contrib/earthdistance/Makefile
+++ b/contrib/earthdistance/Makefile
@@ -3,7 +3,8 @@
 MODULES = earthdistance
 
 EXTENSION = earthdistance
-DATA = earthdistance--1.1.sql earthdistance--1.0--1.1.sql
+DATA = earthdistance--1.1.sql earthdistance--1.0--1.1.sql \
+	earthdistance--1.1--1.2.sql
 PGFILEDESC = "earthdistance - calculate distances on the surface of the Earth"
 
 REGRESS = earthdistance
diff --git a/contrib/earthdistance/earthdistance--1.1--1.2.sql b/contrib/earthdistance/earthdistance--1.1--1.2.sql
new file mode 100644
index 00000000000..16be60720ae
--- /dev/null
+++ b/contrib/earthdistance/earthdistance--1.1--1.2.sql
@@ -0,0 +1,57 @@
+/* contrib/earthdistance/earthdistance--1.1--1.2.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION earthdistance UPDATE TO '1.2'" to load this file. \quit
+
+CREATE OR REPLACE FUNCTION earth() RETURNS float8
+LANGUAGE SQL IMMUTABLE PARALLEL SAFE
+RETURN '6378168'::float8;
+
+CREATE OR REPLACE FUNCTION sec_to_gc(float8)
+RETURNS float8
+LANGUAGE SQL
+IMMUTABLE STRICT
+PARALLEL SAFE
+RETURN CASE WHEN $1 < 0 THEN 0::float8 WHEN $1/(2*earth()) > 1 THEN pg_catalog.pi()*earth() ELSE 2*earth()*pg_catalog.asin($1/(2*earth())) END;
+
+CREATE OR REPLACE FUNCTION gc_to_sec(float8)
+RETURNS float8
+LANGUAGE SQL
+IMMUTABLE STRICT
+PARALLEL SAFE
+RETURN CASE WHEN $1 < 0 THEN 0::float8 WHEN $1/earth() > pg_catalog.pi() THEN 2*earth() ELSE 2*earth()*pg_catalog.sin($1/(2*earth())) END;
+
+CREATE OR REPLACE FUNCTION ll_to_earth(float8, float8)
+RETURNS earth
+LANGUAGE SQL
+IMMUTABLE STRICT
+PARALLEL SAFE
+RETURN @extschema:cube@.cube(@extschema:cube@.cube(@extschema:cube@.cube(earth()*pg_catalog.cos(radians($1))*pg_catalog.cos(radians($2))),earth()*pg_catalog.cos(radians($1))*pg_catalog.sin(radians($2))),earth()*pg_catalog.sin(radians($1)))::earth;
+
+CREATE OR REPLACE FUNCTION latitude(earth)
+RETURNS float8
+LANGUAGE SQL
+IMMUTABLE STRICT
+PARALLEL SAFE
+RETURN CASE WHEN @extschema:cube@.cube_ll_coord($1, 3)/earth() < -1 THEN -90::float8 WHEN @extschema:cube@.cube_ll_coord($1, 3)/earth() > 1 THEN 90::float8 ELSE pg_catalog.degrees(pg_catalog.asin(@extschema:cube@.cube_ll_coord($1, 3)/earth())) END;
+
+CREATE OR REPLACE FUNCTION longitude(earth)
+RETURNS float8
+LANGUAGE SQL
+IMMUTABLE STRICT
+PARALLEL SAFE
+RETURN pg_catalog.degrees(pg_catalog.atan2(@extschema:cube@.cube_ll_coord($1, 2), @extschema:cube@.cube_ll_coord($1, 1)));
+
+CREATE OR REPLACE FUNCTION earth_distance(earth, earth)
+RETURNS float8
+LANGUAGE SQL
+IMMUTABLE STRICT
+PARALLEL SAFE
+RETURN sec_to_gc(@extschema:cube@.cube_distance($1, $2));
+
+CREATE OR REPLACE FUNCTION earth_box(earth, float8)
+RETURNS @extschema:cube@.cube
+LANGUAGE SQL
+IMMUTABLE STRICT
+PARALLEL SAFE
+RETURN @extschema:cube@.cube_enlarge($1, gc_to_sec($2), 3);
diff --git a/contrib/earthdistance/earthdistance--1.2.sql b/contrib/earthdistance/earthdistance--1.2.sql
new file mode 100644
index 00000000000..fac15969ad4
--- /dev/null
+++ b/contrib/earthdistance/earthdistance--1.2.sql
@@ -0,0 +1,98 @@
+/* contrib/earthdistance/earthdistance--1.1.sql */
+
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION earthdistance" to load this file. \quit
+
+-- earth() returns the radius of the earth in meters. This is the only
+-- place you need to change things for the cube base distance functions
+-- in order to use different units (or a better value for the Earth's radius).
+
+CREATE FUNCTION earth() RETURNS float8
+LANGUAGE SQL IMMUTABLE PARALLEL SAFE
+RETURN '6378168'::float8;
+
+-- Astronomers may want to change the earth function so that distances will be
+-- returned in degrees. To do this comment out the above definition and
+-- uncomment the one below. Note that doing this will break the regression
+-- tests.
+--
+-- CREATE FUNCTION earth() RETURNS float8
+-- LANGUAGE SQL IMMUTABLE
+-- AS 'SELECT 180/pi()';
+
+-- Define domain for locations on the surface of the earth using a cube
+-- datatype with constraints. cube provides 3D indexing.
+-- The cube is restricted to be a point, no more than 3 dimensions
+-- (for less than 3 dimensions 0 is assumed for the missing coordinates)
+-- and that the point must be very near the surface of the sphere
+-- centered about the origin with the radius of the earth.
+
+CREATE DOMAIN earth AS $extschema:cube@.cube
+  CONSTRAINT not_point check($extschema:cube@.cube_is_point(value))
+  CONSTRAINT not_3d check($extschema:cube@.cube_dim(value) <= 3)
+  CONSTRAINT on_surface check(abs($extschema:cube@.cube_distance(value, '(0)'::$extschema:cube@.cube) /
+  earth() - '1'::float8) < '10e-7'::float8);
+
+CREATE FUNCTION sec_to_gc(float8)
+RETURNS float8
+LANGUAGE SQL
+IMMUTABLE STRICT
+PARALLEL SAFE
+RETURN CASE WHEN $1 < 0 THEN 0::float8 WHEN $1/(2*earth()) > 1 THEN pg_catalog.pi()*earth() ELSE 2*earth()*pg_catalog.asin($1/(2*earth())) END;
+
+CREATE FUNCTION gc_to_sec(float8)
+RETURNS float8
+LANGUAGE SQL
+IMMUTABLE STRICT
+PARALLEL SAFE
+RETURN CASE WHEN $1 < 0 THEN 0::float8 WHEN $1/earth() > pg_catalog.pi() THEN 2*earth() ELSE 2*earth()*pg_catalog.sin($1/(2*earth())) END;
+
+CREATE FUNCTION ll_to_earth(float8, float8)
+RETURNS earth
+LANGUAGE SQL
+IMMUTABLE STRICT
+PARALLEL SAFE
+RETURN @extschema:cube@.cube(@extschema:cube@.cube(@extschema:cube@.cube(earth()*pg_catalog.cos(radians($1))*pg_catalog.cos(radians($2))),earth()*pg_catalog.cos(radians($1))*pg_catalog.sin(radians($2))),earth()*pg_catalog.sin(radians($1)))::earth;
+
+CREATE FUNCTION latitude(earth)
+RETURNS float8
+LANGUAGE SQL
+IMMUTABLE STRICT
+PARALLEL SAFE
+RETURN CASE WHEN @extschema:cube@.cube_ll_coord($1, 3)/earth() < -1 THEN -90::float8 WHEN @extschema:cube@.cube_ll_coord($1, 3)/earth() > 1 THEN 90::float8 ELSE pg_catalog.degrees(pg_catalog.asin(@extschema:cube@.cube_ll_coord($1, 3)/earth())) END;
+
+CREATE FUNCTION longitude(earth)
+RETURNS float8
+LANGUAGE SQL
+IMMUTABLE STRICT
+PARALLEL SAFE
+RETURN pg_catalog.degrees(pg_catalog.atan2(@extschema:cube@.cube_ll_coord($1, 2), @extschema:cube@.cube_ll_coord($1, 1)));
+
+CREATE FUNCTION earth_distance(earth, earth)
+RETURNS float8
+LANGUAGE SQL
+IMMUTABLE STRICT
+PARALLEL SAFE
+RETURN sec_to_gc(@extschema:cube@.cube_distance($1, $2));
+
+CREATE FUNCTION earth_box(earth, float8)
+RETURNS cube
+LANGUAGE SQL
+IMMUTABLE STRICT
+PARALLEL SAFE
+RETURN @extschema:cube@.cube_enlarge($1, gc_to_sec($2), 3);
+
+--------------- geo_distance
+
+CREATE FUNCTION geo_distance (point, point)
+RETURNS float8
+LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE AS 'MODULE_PATHNAME';
+
+--------------- geo_distance as operator <@>
+
+CREATE OPERATOR <@> (
+  LEFTARG = point,
+  RIGHTARG = point,
+  PROCEDURE = geo_distance,
+  COMMUTATOR = <@>
+);
diff --git a/contrib/earthdistance/earthdistance.control b/contrib/earthdistance/earthdistance.control
index 5816d22cdd9..de2465d487e 100644
--- a/contrib/earthdistance/earthdistance.control
+++ b/contrib/earthdistance/earthdistance.control
@@ -1,6 +1,6 @@
 # earthdistance extension
 comment = 'calculate great-circle distances on the surface of the Earth'
-default_version = '1.1'
+default_version = '1.2'
 module_pathname = '$libdir/earthdistance'
 relocatable = true
 requires = 'cube'
diff --git a/contrib/earthdistance/meson.build b/contrib/earthdistance/meson.build
index 4e3c538f7aa..be63bf577c3 100644
--- a/contrib/earthdistance/meson.build
+++ b/contrib/earthdistance/meson.build
@@ -20,6 +20,7 @@ install_data(
   'earthdistance.control',
   'earthdistance--1.0--1.1.sql',
   'earthdistance--1.1.sql',
+  'earthdistance--1.1--1.2.sql',
   kwargs: contrib_data_args,
 )
 
-- 
2.47.0

From ca3f87189eaf060a91486b5d2167930ecdf7b541 Mon Sep 17 00:00:00 2001
From: Ronan Dunklau <ronan.dunklau@aiven.io>
Date: Mon, 28 Oct 2024 16:25:52 +0100
Subject: [PATCH v4 5/6] Use "new style" SQL function in xml2 extension

Author: Tom Lane
Discussion: https://www.postgresql.org/message-id/3395418.1618352794%40sss.pgh.pa.us
---
 contrib/xml2/Makefile           |  4 +++-
 contrib/xml2/meson.build        |  1 +
 contrib/xml2/xml2--1.1--1.2.sql | 18 ++++++++++++++++++
 contrib/xml2/xml2.control       |  2 +-
 4 files changed, 23 insertions(+), 2 deletions(-)
 create mode 100644 contrib/xml2/xml2--1.1--1.2.sql

diff --git a/contrib/xml2/Makefile b/contrib/xml2/Makefile
index 0d703fe0e8f..8597e9aa9c5 100644
--- a/contrib/xml2/Makefile
+++ b/contrib/xml2/Makefile
@@ -7,7 +7,9 @@ OBJS = \
 	xslt_proc.o
 
 EXTENSION = xml2
-DATA = xml2--1.1.sql xml2--1.0--1.1.sql
+DATA = xml2--1.1.sql \
+	xml2--1.1--1.2.sql \
+	xml2--1.0--1.1.sql
 PGFILEDESC = "xml2 - XPath querying and XSLT"
 
 REGRESS = xml2
diff --git a/contrib/xml2/meson.build b/contrib/xml2/meson.build
index 5e80e17f824..32d9ab53cbd 100644
--- a/contrib/xml2/meson.build
+++ b/contrib/xml2/meson.build
@@ -27,6 +27,7 @@ contrib_targets += xml2
 install_data(
   'xml2--1.0--1.1.sql',
   'xml2--1.1.sql',
+  'xml2--1.1--1.2.sql',
   'xml2.control',
   kwargs: contrib_data_args,
 )
diff --git a/contrib/xml2/xml2--1.1--1.2.sql b/contrib/xml2/xml2--1.1--1.2.sql
new file mode 100644
index 00000000000..12d00064e8f
--- /dev/null
+++ b/contrib/xml2/xml2--1.1--1.2.sql
@@ -0,0 +1,18 @@
+/* contrib/xml2/xml2--1.1--1.2.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION xml2 UPDATE TO '1.2'" to load this file. \quit
+
+CREATE OR REPLACE FUNCTION xpath_list(text,text) RETURNS text
+LANGUAGE SQL STRICT IMMUTABLE PARALLEL SAFE
+RETURN xpath_list($1,$2,',');
+
+CREATE OR REPLACE FUNCTION xpath_nodeset(text,text)
+RETURNS text
+LANGUAGE SQL STRICT IMMUTABLE PARALLEL SAFE
+RETURN xpath_nodeset($1,$2,'','');
+
+CREATE OR REPLACE FUNCTION xpath_nodeset(text,text,text)
+RETURNS text
+LANGUAGE SQL STRICT IMMUTABLE PARALLEL SAFE
+RETURN xpath_nodeset($1,$2,'',$3);
diff --git a/contrib/xml2/xml2.control b/contrib/xml2/xml2.control
index ba2c0599a37..b32156c949e 100644
--- a/contrib/xml2/xml2.control
+++ b/contrib/xml2/xml2.control
@@ -1,6 +1,6 @@
 # xml2 extension
 comment = 'XPath querying and XSLT'
-default_version = '1.1'
+default_version = '1.2'
 module_pathname = '$libdir/pgxml'
 # XXX do we still need this to be non-relocatable?
 relocatable = false
-- 
2.47.0

From 9230ccad6329745bbd3d789b75b350fb0e3387c0 Mon Sep 17 00:00:00 2001
From: Ronan Dunklau <ronan.dunklau@aiven.io>
Date: Tue, 29 Oct 2024 07:53:02 +0100
Subject: [PATCH v4 6/6] Use "new style" SQL function in lo extension

---
 contrib/lo/Makefile         | 2 +-
 contrib/lo/lo--1.1--1.2.sql | 8 ++++++++
 contrib/lo/lo.control       | 2 +-
 contrib/lo/meson.build      | 1 +
 4 files changed, 11 insertions(+), 2 deletions(-)
 create mode 100644 contrib/lo/lo--1.1--1.2.sql

diff --git a/contrib/lo/Makefile b/contrib/lo/Makefile
index 716893859eb..8cfff63d882 100644
--- a/contrib/lo/Makefile
+++ b/contrib/lo/Makefile
@@ -3,7 +3,7 @@
 MODULES = lo
 
 EXTENSION = lo
-DATA = lo--1.1.sql lo--1.0--1.1.sql
+DATA = lo--1.1.sql lo--1.0--1.1.sql lo--1.1--1.2.sql
 PGFILEDESC = "lo - management for large objects"
 
 REGRESS = lo
diff --git a/contrib/lo/lo--1.1--1.2.sql b/contrib/lo/lo--1.1--1.2.sql
new file mode 100644
index 00000000000..a480923a3a6
--- /dev/null
+++ b/contrib/lo/lo--1.1--1.2.sql
@@ -0,0 +1,8 @@
+/* contrib/lo/lo--1.1--1.2.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION lo UPDATE TO '1.2'" to load this file. \quit
+
+CREATE OR REPLACE FUNCTION lo_oid(lo) RETURNS pg_catalog.oid
+LANGUAGE SQL STRICT IMMUTABLE PARALLEL SAFE
+RETURN (SELECT $1::pg_catalog.oid);
diff --git a/contrib/lo/lo.control b/contrib/lo/lo.control
index f73f8b5fae5..3acb3665a4e 100644
--- a/contrib/lo/lo.control
+++ b/contrib/lo/lo.control
@@ -1,6 +1,6 @@
 # lo extension
 comment = 'Large Object maintenance'
-default_version = '1.1'
+default_version = '1.2'
 module_pathname = '$libdir/lo'
 relocatable = true
 trusted = true
diff --git a/contrib/lo/meson.build b/contrib/lo/meson.build
index 4cc72b64578..1443a260b27 100644
--- a/contrib/lo/meson.build
+++ b/contrib/lo/meson.build
@@ -20,6 +20,7 @@ install_data(
   'lo.control',
   'lo--1.0--1.1.sql',
   'lo--1.1.sql',
+  'lo--1.1--1.2.sql',
   kwargs: contrib_data_args,
 )
 
-- 
2.47.0

>From 9c5235cd123eeb55b95b8bfd281dfcc37df197c5 Mon Sep 17 00:00:00 2001
From: Ronan Dunklau <ronan.dunklau@aiven.io>
Date: Mon, 28 Oct 2024 16:13:35 +0100
Subject: [PATCH v4 3/6] Use "new style" SQL function in citext extension

Author: Tom Lane
Discussion: https://www.postgresql.org/message-id/3395418.1618352794%40sss.pgh.pa.us
---
 contrib/citext/Makefile             |  1 +
 contrib/citext/citext--1.7--1.8.sql | 60 +++++++++++++++++++++++++++++
 contrib/citext/citext.control       |  2 +-
 contrib/citext/meson.build          |  1 +
 4 files changed, 63 insertions(+), 1 deletion(-)
 create mode 100644 contrib/citext/citext--1.7--1.8.sql

diff --git a/contrib/citext/Makefile b/contrib/citext/Makefile
index b9b3713f537..fc990607bf2 100644
--- a/contrib/citext/Makefile
+++ b/contrib/citext/Makefile
@@ -4,6 +4,7 @@ MODULES = citext
 
 EXTENSION = citext
 DATA = citext--1.4.sql \
+	citext--1.7--1.8.sql \
 	citext--1.6--1.7.sql \
 	citext--1.5--1.6.sql \
 	citext--1.4--1.5.sql \
diff --git a/contrib/citext/citext--1.7--1.8.sql b/contrib/citext/citext--1.7--1.8.sql
new file mode 100644
index 00000000000..7c95a9883aa
--- /dev/null
+++ b/contrib/citext/citext--1.7--1.8.sql
@@ -0,0 +1,60 @@
+/* contrib/citext/citext--1.7--1.8.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION citext UPDATE TO '1.8'" to load this file. \quit
+
+CREATE OR REPLACE FUNCTION regexp_match(string citext, pattern citext) RETURNS TEXT[]
+LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE
+RETURN pg_catalog.regexp_match( $1::pg_catalog.text, $2::pg_catalog.text, 'i' );
+
+CREATE OR REPLACE FUNCTION regexp_match(string citext, pattern citext, flags text) RETURNS TEXT[]
+LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE
+RETURN pg_catalog.regexp_match( $1::pg_catalog.text, $2::pg_catalog.text, CASE WHEN pg_catalog.strpos($3, 'c') = 0 THEN  $3 || 'i' ELSE $3 END );
+
+CREATE OR REPLACE FUNCTION regexp_matches(string citext, pattern citext) RETURNS SETOF TEXT[]
+LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE ROWS 1
+RETURN pg_catalog.regexp_matches( $1::pg_catalog.text, $2::pg_catalog.text, 'i' );
+
+CREATE OR REPLACE FUNCTION regexp_matches(string citext, pattern citext, flags text) RETURNS SETOF TEXT[]
+LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE ROWS 10
+RETURN pg_catalog.regexp_matches( $1::pg_catalog.text, $2::pg_catalog.text, CASE WHEN pg_catalog.strpos($3, 'c') = 0 THEN  $3 || 'i' ELSE $3 END );
+
+CREATE OR REPLACE FUNCTION regexp_replace(string citext, pattern citext, replacement text) returns TEXT
+LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE
+RETURN pg_catalog.regexp_replace( $1::pg_catalog.text, $2::pg_catalog.text, $3, 'i');
+
+CREATE OR REPLACE FUNCTION regexp_replace(string citext, pattern citext, replacement text, flags text) returns TEXT
+LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE
+RETURN pg_catalog.regexp_replace( $1::pg_catalog.text, $2::pg_catalog.text, $3, CASE WHEN pg_catalog.strpos($4, 'c') = 0 THEN  $4 || 'i' ELSE $4 END);
+
+CREATE OR REPLACE FUNCTION regexp_split_to_array(string citext, pattern citext) RETURNS TEXT[]
+LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE
+RETURN pg_catalog.regexp_split_to_array( $1::pg_catalog.text, $2::pg_catalog.text, 'i' );
+
+CREATE OR REPLACE FUNCTION regexp_split_to_array(string citext, pattern citext, flags text) RETURNS TEXT[]
+LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE
+RETURN pg_catalog.regexp_split_to_array( $1::pg_catalog.text, $2::pg_catalog.text, CASE WHEN pg_catalog.strpos($3, 'c') = 0 THEN  $3 || 'i' ELSE $3 END );
+
+CREATE OR REPLACE FUNCTION regexp_split_to_table(string citext, pattern citext) RETURNS SETOF TEXT
+LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE
+RETURN pg_catalog.regexp_split_to_table( $1::pg_catalog.text, $2::pg_catalog.text, 'i' );
+
+CREATE OR REPLACE FUNCTION regexp_split_to_table(string citext, pattern citext, flags text) RETURNS SETOF TEXT
+LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE
+RETURN pg_catalog.regexp_split_to_table( $1::pg_catalog.text, $2::pg_catalog.text, CASE WHEN pg_catalog.strpos($3, 'c') = 0 THEN  $3 || 'i' ELSE $3 END );
+
+CREATE OR REPLACE FUNCTION strpos( citext, citext ) RETURNS INT
+LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE
+RETURN pg_catalog.strpos( pg_catalog.lower( $1::pg_catalog.text ), pg_catalog.lower( $2::pg_catalog.text ) );
+
+CREATE OR REPLACE FUNCTION replace( citext, citext, citext ) RETURNS TEXT
+LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE
+RETURN pg_catalog.regexp_replace( $1::pg_catalog.text, pg_catalog.regexp_replace($2::pg_catalog.text, '([^a-zA-Z_0-9])', E'\\\\\\1', 'g'), $3::pg_catalog.text, 'gi' );
+
+CREATE OR REPLACE FUNCTION split_part( citext, citext, int ) RETURNS TEXT
+LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE
+RETURN (pg_catalog.regexp_split_to_array( $1::pg_catalog.text, pg_catalog.regexp_replace($2::pg_catalog.text, '([^a-zA-Z_0-9])', E'\\\\\\1', 'g'), 'i'))[$3];
+
+CREATE OR REPLACE FUNCTION translate( citext, citext, text ) RETURNS TEXT
+LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE
+RETURN pg_catalog.translate( pg_catalog.translate( $1::pg_catalog.text, pg_catalog.lower($2::pg_catalog.text), $3), pg_catalog.upper($2::pg_catalog.text), $3);
diff --git a/contrib/citext/citext.control b/contrib/citext/citext.control
index f82265b3347..2b0f3fa8407 100644
--- a/contrib/citext/citext.control
+++ b/contrib/citext/citext.control
@@ -1,6 +1,6 @@
 # citext extension
 comment = 'data type for case-insensitive character strings'
-default_version = '1.7'
+default_version = '1.8'
 module_pathname = '$libdir/citext'
 relocatable = true
 trusted = true
diff --git a/contrib/citext/meson.build b/contrib/citext/meson.build
index 40cdd0d2f18..ae87445e6a4 100644
--- a/contrib/citext/meson.build
+++ b/contrib/citext/meson.build
@@ -26,6 +26,7 @@ install_data(
   'citext--1.4--1.5.sql',
   'citext--1.5--1.6.sql',
   'citext--1.6--1.7.sql',
+  'citext--1.7--1.8.sql',
   kwargs: contrib_data_args,
 )
 
-- 
2.47.0

>From ca3f87189eaf060a91486b5d2167930ecdf7b541 Mon Sep 17 00:00:00 2001
From: Ronan Dunklau <ronan.dunklau@aiven.io>
Date: Mon, 28 Oct 2024 16:25:52 +0100
Subject: [PATCH v4 5/6] Use "new style" SQL function in xml2 extension

Author: Tom Lane
Discussion: https://www.postgresql.org/message-id/3395418.1618352794%40sss.pgh.pa.us
---
 contrib/xml2/Makefile           |  4 +++-
 contrib/xml2/meson.build        |  1 +
 contrib/xml2/xml2--1.1--1.2.sql | 18 ++++++++++++++++++
 contrib/xml2/xml2.control       |  2 +-
 4 files changed, 23 insertions(+), 2 deletions(-)
 create mode 100644 contrib/xml2/xml2--1.1--1.2.sql

diff --git a/contrib/xml2/Makefile b/contrib/xml2/Makefile
index 0d703fe0e8f..8597e9aa9c5 100644
--- a/contrib/xml2/Makefile
+++ b/contrib/xml2/Makefile
@@ -7,7 +7,9 @@ OBJS = \
 	xslt_proc.o
 
 EXTENSION = xml2
-DATA = xml2--1.1.sql xml2--1.0--1.1.sql
+DATA = xml2--1.1.sql \
+	xml2--1.1--1.2.sql \
+	xml2--1.0--1.1.sql
 PGFILEDESC = "xml2 - XPath querying and XSLT"
 
 REGRESS = xml2
diff --git a/contrib/xml2/meson.build b/contrib/xml2/meson.build
index 5e80e17f824..32d9ab53cbd 100644
--- a/contrib/xml2/meson.build
+++ b/contrib/xml2/meson.build
@@ -27,6 +27,7 @@ contrib_targets += xml2
 install_data(
   'xml2--1.0--1.1.sql',
   'xml2--1.1.sql',
+  'xml2--1.1--1.2.sql',
   'xml2.control',
   kwargs: contrib_data_args,
 )
diff --git a/contrib/xml2/xml2--1.1--1.2.sql b/contrib/xml2/xml2--1.1--1.2.sql
new file mode 100644
index 00000000000..12d00064e8f
--- /dev/null
+++ b/contrib/xml2/xml2--1.1--1.2.sql
@@ -0,0 +1,18 @@
+/* contrib/xml2/xml2--1.1--1.2.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION xml2 UPDATE TO '1.2'" to load this file. \quit
+
+CREATE OR REPLACE FUNCTION xpath_list(text,text) RETURNS text
+LANGUAGE SQL STRICT IMMUTABLE PARALLEL SAFE
+RETURN xpath_list($1,$2,',');
+
+CREATE OR REPLACE FUNCTION xpath_nodeset(text,text)
+RETURNS text
+LANGUAGE SQL STRICT IMMUTABLE PARALLEL SAFE
+RETURN xpath_nodeset($1,$2,'','');
+
+CREATE OR REPLACE FUNCTION xpath_nodeset(text,text,text)
+RETURNS text
+LANGUAGE SQL STRICT IMMUTABLE PARALLEL SAFE
+RETURN xpath_nodeset($1,$2,'',$3);
diff --git a/contrib/xml2/xml2.control b/contrib/xml2/xml2.control
index ba2c0599a37..b32156c949e 100644
--- a/contrib/xml2/xml2.control
+++ b/contrib/xml2/xml2.control
@@ -1,6 +1,6 @@
 # xml2 extension
 comment = 'XPath querying and XSLT'
-default_version = '1.1'
+default_version = '1.2'
 module_pathname = '$libdir/pgxml'
 # XXX do we still need this to be non-relocatable?
 relocatable = false
-- 
2.47.0

diff --git a/contrib/hstore_plperl/hstore_plperl--1.0.sql b/contrib/hstore_plperl/hstore_plperl--1.0.sql
index af743c8733..2837f3719f 100644
--- a/contrib/hstore_plperl/hstore_plperl--1.0.sql
+++ b/contrib/hstore_plperl/hstore_plperl--1.0.sql
@@ -7,11 +7,11 @@ CREATE FUNCTION hstore_to_plperl(val internal) RETURNS internal
 LANGUAGE C STRICT IMMUTABLE
 AS 'MODULE_PATHNAME';
 
-CREATE FUNCTION plperl_to_hstore(val internal) RETURNS hstore
+CREATE FUNCTION plperl_to_hstore(val internal) RETURNS @extschema:hstore@.hstore
 LANGUAGE C STRICT IMMUTABLE
 AS 'MODULE_PATHNAME';
 
-CREATE TRANSFORM FOR hstore LANGUAGE plperl (
+CREATE TRANSFORM FOR @extschema:hstore@.hstore LANGUAGE plperl (
     FROM SQL WITH FUNCTION hstore_to_plperl(internal),
     TO SQL WITH FUNCTION plperl_to_hstore(internal)
 );
diff --git a/contrib/hstore_plperl/hstore_plperlu--1.0.sql b/contrib/hstore_plperl/hstore_plperlu--1.0.sql
index 7c3bc86eba..7f3119a7b2 100644
--- a/contrib/hstore_plperl/hstore_plperlu--1.0.sql
+++ b/contrib/hstore_plperl/hstore_plperlu--1.0.sql
@@ -7,11 +7,11 @@ CREATE FUNCTION hstore_to_plperlu(val internal) RETURNS internal
 LANGUAGE C STRICT IMMUTABLE
 AS 'MODULE_PATHNAME', 'hstore_to_plperl';
 
-CREATE FUNCTION plperlu_to_hstore(val internal) RETURNS hstore
+CREATE FUNCTION plperlu_to_hstore(val internal) RETURNS @extschema:hstore@.hstore
 LANGUAGE C STRICT IMMUTABLE
 AS 'MODULE_PATHNAME', 'plperl_to_hstore';
 
-CREATE TRANSFORM FOR hstore LANGUAGE plperlu (
+CREATE TRANSFORM FOR @extschema:hstore@.hstore LANGUAGE plperlu (
     FROM SQL WITH FUNCTION hstore_to_plperlu(internal),
     TO SQL WITH FUNCTION plperlu_to_hstore(internal)
 );
diff --git a/contrib/hstore_plpython/hstore_plpython3u--1.0.sql b/contrib/hstore_plpython/hstore_plpython3u--1.0.sql
index 0b410ab183..3508232241 100644
--- a/contrib/hstore_plpython/hstore_plpython3u--1.0.sql
+++ b/contrib/hstore_plpython/hstore_plpython3u--1.0.sql
@@ -7,13 +7,13 @@ CREATE FUNCTION hstore_to_plpython3(val internal) RETURNS internal
 LANGUAGE C STRICT IMMUTABLE
 AS 'MODULE_PATHNAME', 'hstore_to_plpython';
 
-CREATE FUNCTION plpython3_to_hstore(val internal) RETURNS hstore
+CREATE FUNCTION plpython3_to_hstore(val internal) RETURNS @extschema:hstore@.hstore
 LANGUAGE C STRICT IMMUTABLE
 AS 'MODULE_PATHNAME', 'plpython_to_hstore';
 
-CREATE TRANSFORM FOR hstore LANGUAGE plpython3u (
+CREATE TRANSFORM FOR @extschema:hstore@.hstore LANGUAGE plpython3u (
     FROM SQL WITH FUNCTION hstore_to_plpython3(internal),
     TO SQL WITH FUNCTION plpython3_to_hstore(internal)
 );
 
-COMMENT ON TRANSFORM FOR hstore LANGUAGE plpython3u IS 'transform between hstore and Python dict';
+COMMENT ON TRANSFORM FOR @extschema:hstore@.hstore LANGUAGE plpython3u IS 'transform between hstore and Python dict';
diff --git a/contrib/ltree_plpython/ltree_plpython3u--1.0.sql b/contrib/ltree_plpython/ltree_plpython3u--1.0.sql
index 09ada3c7e8..14f73518d6 100644
--- a/contrib/ltree_plpython/ltree_plpython3u--1.0.sql
+++ b/contrib/ltree_plpython/ltree_plpython3u--1.0.sql
@@ -7,6 +7,6 @@ CREATE FUNCTION ltree_to_plpython3(val internal) RETURNS internal
 LANGUAGE C STRICT IMMUTABLE
 AS 'MODULE_PATHNAME', 'ltree_to_plpython';
 
-CREATE TRANSFORM FOR ltree LANGUAGE plpython3u (
+CREATE TRANSFORM FOR @extschema:ltree@.ltree LANGUAGE plpython3u (
     FROM SQL WITH FUNCTION ltree_to_plpython3(internal)
 );
diff --git a/doc/src/sgml/extend.sgml b/doc/src/sgml/extend.sgml
index 218940ee5c..ba492ca27c 100644
--- a/doc/src/sgml/extend.sgml
+++ b/doc/src/sgml/extend.sgml
@@ -1348,15 +1348,11 @@ SELECT * FROM pg_extension_update_paths('<replaceable>extension_name</replaceabl
      </para>
 
      <para>
-      Cross-extension references are extremely difficult to make fully
-      secure, partially because of uncertainty about which schema the other
-      extension is in.  The hazards are reduced if both extensions are
-      installed in the same schema, because then a hostile object cannot be
-      placed ahead of the referenced extension in the installation-time
-      <varname>search_path</varname>.  However, no mechanism currently exists
-      to require that.  For now, best practice is to not mark an extension
-      trusted if it depends on another one, unless that other one is always
-      installed in <literal>pg_catalog</literal>.
+      Secure cross-extension references typically require schema-qualification
+      of the names of the other extension's objects, using the
+      <literal>@extschema:<replaceable>name</replaceable>@</literal>
+      syntax, in addition to careful matching of argument types for functions
+      and operators.
      </para>
     </sect3>
    </sect2>
diff --git a/doc/src/sgml/hstore.sgml b/doc/src/sgml/hstore.sgml
index 7d93e49e91..44325e0bba 100644
--- a/doc/src/sgml/hstore.sgml
+++ b/doc/src/sgml/hstore.sgml
@@ -946,15 +946,6 @@ ALTER TABLE tablename ALTER hstorecol TYPE hstore USING hstorecol || '';
    extension for PL/Python is called <literal>hstore_plpython3u</literal>.
    If you use it, <type>hstore</type> values are mapped to Python dictionaries.
   </para>
-
-  <caution>
-   <para>
-    It is strongly recommended that the transform extensions be installed in
-    the same schema as <filename>hstore</filename>.  Otherwise there are
-    installation-time security hazards if a transform extension's schema
-    contains objects defined by a hostile user.
-   </para>
-  </caution>
  </sect2>
 
  <sect2 id="hstore-authors">
diff --git a/doc/src/sgml/ltree.sgml b/doc/src/sgml/ltree.sgml
index 9584105b03..1c3543303f 100644
--- a/doc/src/sgml/ltree.sgml
+++ b/doc/src/sgml/ltree.sgml
@@ -841,15 +841,6 @@ ltreetest=&gt; SELECT ins_label(path,2,'Space') FROM test WHERE path &lt;@ 'Top.
    creating a function, <type>ltree</type> values are mapped to Python lists.
    (The reverse is currently not supported, however.)
   </para>
-
-  <caution>
-   <para>
-    It is strongly recommended that the transform extension be installed in
-    the same schema as <filename>ltree</filename>.  Otherwise there are
-    installation-time security hazards if a transform extension's schema
-    contains objects defined by a hostile user.
-   </para>
-  </caution>
  </sect2>
 
  <sect2 id="ltree-authors">

diff --git a/contrib/pg_freespacemap/pg_freespacemap--1.2--1.3.sql b/contrib/pg_freespacemap/pg_freespacemap--1.2--1.3.sql
index 7f92c9e92e3..a75c4d8642b 100644
--- a/contrib/pg_freespacemap/pg_freespacemap--1.2--1.3.sql
+++ b/contrib/pg_freespacemap/pg_freespacemap--1.2--1.3.sql
@@ -9,5 +9,5 @@ RETURNS SETOF RECORD
 LANGUAGE SQL PARALLEL SAFE
 BEGIN ATOMIC
   SELECT blkno, pg_freespace($1, blkno) AS avail
-  FROM generate_series(0, pg_relation_size($1) / current_setting('block_size')::bigint - 1) AS blkno;
+  FROM generate_series(0::bigint, pg_relation_size($1) / current_setting('block_size'::text)::bigint - 1::bigint) AS blkno;
 END;