Re: Extracting cross-version-upgrade knowledge from buildfarm client

Andrew Dunstan <andrew@dunslane.net> writes:
> On 2023-01-16 Mo 21:58, Tom Lane wrote:
>> I dunno if we want to stretch buildfarm owners' patience with yet
>> another BF client release right now.  On the other hand, I'm antsy
>> to see if we can un-revert 1b4d280ea after doing a little more
>> work in AdjustUpgrade.pm.

> I think the next step is to push the buildfarm client changes, and
> update those three animals to use it, and make sure nothing breaks. I'll
> go and do those things now. Then you should be able to try your unrevert.

It looks like unrevert will require ~130 lines in AdjustUpgrade.pm,
which is not great but not awful either.  I think this is ready to
go once you've vetted your remaining buildfarm animals.

            regards, tom lane

diff --git a/src/test/perl/PostgreSQL/Test/AdjustUpgrade.pm b/src/test/perl/PostgreSQL/Test/AdjustUpgrade.pm
index 7cf4ced392..5bed1d6839 100644
--- a/src/test/perl/PostgreSQL/Test/AdjustUpgrade.pm
+++ b/src/test/perl/PostgreSQL/Test/AdjustUpgrade.pm
@@ -268,6 +268,12 @@ sub adjust_old_dumpfile
     # Version comments will certainly not match.
     $dump =~ s/^-- Dumped from database version.*\n//mg;

+    if ($old_version < 16)
+    {
+        # Fix up some view queries that no longer require table-qualification.
+        $dump = _mash_view_qualifiers($dump);
+    }
+
     if ($old_version >= 14 && $old_version < 16)
     {
         # Fix up some privilege-set discrepancies.
@@ -396,6 +402,133 @@ sub adjust_old_dumpfile
     return $dump;
 }

+
+# Data for _mash_view_qualifiers
+my @_unused_view_qualifiers = (
+    # Present at least since 9.2
+    { obj => 'VIEW public.trigger_test_view',  qual => 'trigger_test' },
+    { obj => 'VIEW public.domview',            qual => 'domtab' },
+    { obj => 'VIEW public.my_property_normal', qual => 'customer' },
+    { obj => 'VIEW public.my_property_secure', qual => 'customer' },
+    { obj => 'VIEW public.pfield_v1',          qual => 'pf' },
+    { obj => 'VIEW public.rtest_v1',           qual => 'rtest_t1' },
+    { obj => 'VIEW public.rtest_vview1',       qual => 'x' },
+    { obj => 'VIEW public.rtest_vview2',       qual => 'rtest_view1' },
+    { obj => 'VIEW public.rtest_vview3',       qual => 'x' },
+    { obj => 'VIEW public.rtest_vview5',       qual => 'rtest_view1' },
+    { obj => 'VIEW public.shoelace_obsolete',  qual => 'shoelace' },
+    { obj => 'VIEW public.shoelace_candelete', qual => 'shoelace_obsolete' },
+    { obj => 'VIEW public.toyemp',             qual => 'emp' },
+    { obj => 'VIEW public.xmlview4',           qual => 'emp' },
+    # Since 9.3 (some of these were removed in 9.6)
+    { obj => 'VIEW public.tv',                 qual => 't' },
+    { obj => 'MATERIALIZED VIEW mvschema.tvm', qual => 'tv' },
+    { obj => 'VIEW public.tvv',                qual => 'tv' },
+    { obj => 'MATERIALIZED VIEW public.tvvm',  qual => 'tvv' },
+    { obj => 'VIEW public.tvvmv',              qual => 'tvvm' },
+    { obj => 'MATERIALIZED VIEW public.bb',    qual => 'tvvmv' },
+    { obj => 'VIEW public.nums',               qual => 'nums' },
+    { obj => 'VIEW public.sums_1_100',         qual => 't' },
+    { obj => 'MATERIALIZED VIEW public.tm',    qual => 't' },
+    { obj => 'MATERIALIZED VIEW public.tmm',   qual => 'tm' },
+    { obj => 'MATERIALIZED VIEW public.tvmm',  qual => 'tvm' },
+    # Since 9.4
+    {
+        obj  => 'MATERIALIZED VIEW public.citext_matview',
+        qual => 'citext_table'
+    },
+    {
+        obj  => 'OR REPLACE VIEW public.key_dependent_view',
+        qual => 'view_base_table'
+    },
+    {
+        obj  => 'OR REPLACE VIEW public.key_dependent_view_no_cols',
+        qual => 'view_base_table'
+    },
+    # Since 9.5
+    {
+        obj  => 'VIEW public.dummy_seclabel_view1',
+        qual => 'dummy_seclabel_tbl2'
+    },
+    { obj => 'VIEW public.vv',                  qual => 'test_tablesample' },
+    { obj => 'VIEW public.test_tablesample_v1', qual => 'test_tablesample' },
+    { obj => 'VIEW public.test_tablesample_v2', qual => 'test_tablesample' },
+    # Since 9.6
+    {
+        obj  => 'MATERIALIZED VIEW public.test_pg_dump_mv1',
+        qual => 'test_pg_dump_t1'
+    },
+    { obj => 'VIEW public.test_pg_dump_v1', qual => 'test_pg_dump_t1' },
+    { obj => 'VIEW public.mvtest_tv',       qual => 'mvtest_t' },
+    {
+        obj  => 'MATERIALIZED VIEW mvtest_mvschema.mvtest_tvm',
+        qual => 'mvtest_tv'
+    },
+    { obj => 'VIEW public.mvtest_tvv',               qual => 'mvtest_tv' },
+    { obj => 'MATERIALIZED VIEW public.mvtest_tvvm', qual => 'mvtest_tvv' },
+    { obj => 'VIEW public.mvtest_tvvmv',             qual => 'mvtest_tvvm' },
+    { obj => 'MATERIALIZED VIEW public.mvtest_bb',   qual => 'mvtest_tvvmv' },
+    { obj => 'MATERIALIZED VIEW public.mvtest_tm',   qual => 'mvtest_t' },
+    { obj => 'MATERIALIZED VIEW public.mvtest_tmm',  qual => 'mvtest_tm' },
+    { obj => 'MATERIALIZED VIEW public.mvtest_tvmm', qual => 'mvtest_tvm' },
+    # Since 10 (some removed in 12)
+    { obj => 'VIEW public.itestv10',      qual => 'itest10' },
+    { obj => 'VIEW public.itestv11',      qual => 'itest11' },
+    { obj => 'VIEW public.xmltableview2', qual => '"xmltable"' },
+    # Since 12
+    {
+        obj  => 'MATERIALIZED VIEW public.tableam_tblmv_heap2',
+        qual => 'tableam_tbl_heap2'
+    },
+    # Since 13
+    { obj => 'VIEW public.limit_thousand_v_1', qual => 'onek' },
+    { obj => 'VIEW public.limit_thousand_v_2', qual => 'onek' },
+    { obj => 'VIEW public.limit_thousand_v_3', qual => 'onek' },
+    { obj => 'VIEW public.limit_thousand_v_4', qual => 'onek' });
+
+# Internal subroutine to remove no-longer-used table qualifiers from
+# CREATE [MATERIALIZED] VIEW commands.  See list of targeted views above.
+sub _mash_view_qualifiers
+{
+    my ($dump) = @_;
+
+    for my $uvq (@_unused_view_qualifiers)
+    {
+        my $leader    = "CREATE $uvq->{obj} ";
+        my $qualifier = $uvq->{qual};
+        # Note: we loop because there are presently some cases where the same
+        # view name appears in multiple databases.  Fortunately, the same
+        # qualifier removal applies or is harmless for each instance ... but
+        # we might want to rename some things to avoid assuming that.
+        my @splitchunks = split $leader, $dump;
+        $dump = shift(@splitchunks);
+        foreach my $chunk (@splitchunks)
+        {
+            my @thischunks = split /;/, $chunk, 2;
+            my $stmt       = shift(@thischunks);
+            my $ostmt      = $stmt;
+
+            # now $stmt is just the body of the CREATE [MATERIALIZED] VIEW
+            $stmt =~ s/$qualifier\.//g;
+
+            $dump .= $leader . $stmt . ';' . $thischunks[0];
+        }
+    }
+
+    # Further hack a few cases where not all occurrences of the qualifier
+    # should be removed.
+    $dump =~ s {^(CREATE VIEW public\.rtest_vview1 .*?)(a\)\)\);)}
+    {$1x.$2}ms;
+    $dump =~ s {^(CREATE VIEW public\.rtest_vview3 .*?)(a\)\)\);)}
+    {$1x.$2}ms;
+    $dump =~
+      s {^(CREATE VIEW public\.shoelace_obsolete .*?)(sl_color\)\)\)\);)}
+    {$1shoelace.$2}ms;
+
+    return $dump;
+}
+
+
 # Internal subroutine to mangle whitespace within view/rule commands.
 # Any consecutive sequence of whitespace is reduced to one space.
 sub _mash_view_whitespace