Andrew Dunstan <and...@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
