From: Andrew Dunstan <andrew@dunslane.net>
To: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: pgsql-hackers@lists.postgresql.org
Subject: Re: pgsql: Fix restore of not-null constraints with inheritance
Date: Thu, 18 Apr 2024 15:18:32 -0400
Message-ID: <3a642a59-e3c7-4050-95dd-af3b5412e5f7@dunslane.net> (raw)
In-Reply-To: <202404181539.lh42llaesnv3@alvherre.pgsql>
References: <202404181539.lh42llaesnv3@alvherre.pgsql>
On 2024-04-18 Th 11:39, Alvaro Herrera wrote:
> On 2024-Apr-18, Alvaro Herrera wrote:
>
>> On 2024-Apr-18, Alvaro Herrera wrote:
>>
>>> Lastly, make two changes to pg_dump: 1) do not try to drop a not-null
>>> constraint that's marked as inherited; this allows a dump to restore
>>> with no errors if a table with a PK inherits from another which also has
>>> a PK; 2) avoid giving inherited constraints throwaway names, for the
>>> rare cases where such a constraint survives after the restore.
>> Hmm, this last bit broke pg_upgrade on crake. I'll revert this part,
>> meanwhile I'll be installing 9.2 to see if it can be fixed in a better way.
> Eh, so:
>
> 1. running "make check" on pg_upgrade using an oldinstall pointing to
> 9.2 fails, because PostgreSQL::Test::Cluster doesn't support that
> version -- it only goes back to 9.2. How difficult was it to port it
> back to all these old versions?
It's not that hard to make it go back to 9.2. Here's a version that's a
couple of years old, but it supports versions all the way back to 7.2 :-)
If there's interest I'll work on supporting our official "old" versions
(i.e. 9.2 and up)
>
> 2. running "make check" with an oldinstall pointing to 10 fails, because
> the "invalid database" checks fail:
>
> not ok 7 - invalid database causes failure status (got 0 vs expected 1)
>
> # Failed test 'invalid database causes failure status (got 0 vs expected 1)'
> # at t/002_pg_upgrade.pl line 407.
> not ok 8 - invalid database causes failure stdout /(?^:invalid)/
>
>
> 3. Lastly, even if I put back the code that causes the failures on crake
> and restore from 10 (and ignore those two problems), I cannot reproduce
> the issues it reported. Is crake running some funky code that's not
> what "make check" on pg_upgrade does, perchance?
It's running the buildfarm cross version upgrade module. See
<https://github.com/PGBuildFarm/client-code/blob/main/PGBuild/Modules/TestUpgradeXversion.pm;
It's choking on the change in constraint names between the dump of the
pre-upgrade database and the dump of the post-upgrade database, e.g.
CREATE TABLE public.rule_and_refint_t2 (
- id2a integer CONSTRAINT pgdump_throwaway_notnull_0 NOT NULL NO INHERIT,
- id2c integer CONSTRAINT pgdump_throwaway_notnull_1 NOT NULL NO INHERIT
+ id2a integer CONSTRAINT rule_and_refint_t2_id2a_not_null NOT NULL NO INHERIT,
+ id2c integer CONSTRAINT rule_and_refint_t2_id2c_not_null NOT NULL NO INHERIT
);
look at the dumpdiff-REL9_2_STABLE file for the full list.
I assume that means pg_dump is generating names that pg_upgrade throws
away? That seems ... unfortunate.
There is a perl module at
src/test/perl/PostgreSQL/Test/AdjustUpgrade.pm. This is used to adjust
the dump files before we diff them. Maybe you can remedy the problem by
adding some code in there.
cheers
andrew
--
Andrew Dunstan
EDB:https://www.enterprisedb.comAttachments:
Cluster.pm (application/x-perl, 80.8K) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-committers@postgresql.org
Cc: andrew@dunslane.net, alvherre@alvh.no-ip.org, pgsql-hackers@lists.postgresql.org
Subject: Re: pgsql: Fix restore of not-null constraints with inheritance
In-Reply-To: <3a642a59-e3c7-4050-95dd-af3b5412e5f7@dunslane.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox