Re: Combine pg_walinspect till_end_of_wal functions with others

On Wed, Mar 15, 2023 at 12:27 PM Michael Paquier <michael(at)paquier(dot)xyz> wrote:
>
> On Tue, Mar 14, 2023 at 07:05:20PM -0700, Andres Freund wrote:
> > It's using ICU, but not a specific collation. The build I linked to is WIP
> > hackery to add ICU support to windows CI. Here's the initdb output:
> > https://api.cirrus-ci.com/v1/artifact/task/6288336663347200/testrun/build/testrun/pg_walinspect/regress/log/initdb.log
>
> Hmm. Thanks. At the end, I think that I would be tempted to just
> remove this \dx query and move on. I did not anticipate that this
> ordering would be that much sensitive, and the solution of using a
> COLLATE C at the end of a describe.c query does not sound much
> appealing, either.

-1 for removing \dx+ for pg_walinspect version 1.0, because we wanted
to show the diff of functions along with testing the upgrade path in
the oldextversions.sql. Therefore, I prefer something like [1]:

[1]
diff --git a/contrib/pg_walinspect/sql/oldextversions.sql
b/contrib/pg_walinspect/sql/oldextversions.sql
index 258a009888..32a059c72d 100644
--- a/contrib/pg_walinspect/sql/oldextversions.sql
+++ b/contrib/pg_walinspect/sql/oldextversions.sql
@@ -5,8 +5,17 @@ CREATE EXTENSION pg_walinspect WITH VERSION '1.0';
-- Mask DETAIL messages as these could refer to current LSN positions.
\set VERBOSITY terse

+-- \dx+ will give locale-sensitive results, so we can't use it here.
+CREATE VIEW list_pg_walinspect_objects AS
+ SELECT pg_describe_object(classid, objid, 0) AS "Object description"
+ FROM pg_depend
+ WHERE refclassid = 'pg_extension'::regclass AND
+ refobjid = (SELECT oid FROM pg_extension WHERE
extname = 'pg_walinspect') AND
+ deptype = 'e'
+ ORDER BY pg_describe_object(classid, objid, 0) COLLATE "C";
+
-- List what version 1.0 contains
-\dx+ pg_walinspect
+SELECT * FROM list_pg_walinspect_objects;

-- Make sure checkpoints don't interfere with the test.
SELECT 'init' FROM
pg_create_physical_replication_slot('regress_pg_walinspect_slot',
true, false);
@@ -25,7 +34,7 @@ SELECT COUNT(*) >= 1 AS ok FROM
pg_get_wal_stats_till_end_of_wal('FFFFFFFF/FFFFF
ALTER EXTENSION pg_walinspect UPDATE TO '1.1';

-- List what version 1.1 contains
-\dx+ pg_walinspect
+SELECT * FROM list_pg_walinspect_objects;

SELECT pg_drop_replication_slot('regress_pg_walinspect_slot');

--
Bharath Rupireddy
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com