From: Justin Pryzby <pryzby@telsasoft.com>
To: Alvaro Herrera <alvherre@alvh.no-ip.org>
Cc: Andrew Dunstan <andrew@dunslane.net>
Cc: pgsql-hackers@lists.postgresql.org
Subject: Re: pgsql: Fix restore of not-null constraints with inheritance
Date: Sat, 20 Apr 2024 20:25:48 -0500
Message-ID: <ZiRrHLZzwfukSf-v@pryzbyj2023> (raw)
In-Reply-To: <202404191159.cakeaydac4sq@alvherre.pgsql>
References: <3a642a59-e3c7-4050-95dd-af3b5412e5f7@dunslane.net>
<202404191159.cakeaydac4sq@alvherre.pgsql>
On Fri, Apr 19, 2024 at 01:59:31PM +0200, Alvaro Herrera wrote:
> BTW because of a concern from Justin that the NO INHERIT stuff will
> cause errors in old server versions, I started to wonder if it wouldn't
> be better to add these constraints in a separate line for compatibility,
> so for example in the table above it'd be
>
> CREATE TABLE public.rule_and_refint_t2 (
> id2a integer,
> id2c integer
> );
> ALTER TABLE public.rule_and_refint_t2 ADD CONSTRAINT pgdump_throwaway_notnull_0 NOT NULL id2a NO INHERIT;
> ALTER TABLE public.rule_and_refint_t2 ADD CONSTRAINT pgdump_throwaway_notnull_1 NOT NULL id2c NO INHERIT;
>
> which might be less problematic in terms of compatibility: you still end
> up having the table, it's only the ALTER TABLE that would error out.
Under pg_restore -d, those would all be run in a single transactional
command, so it would *still* fail to create the table...
It seems like the workaround to restore into an old server version would
be to run:
| pg_restore -f- |sed 's/ NO INHERIT//' |psql
Putting them on separate lines makes that a tiny bit better, since you
could do:
| pg_restore -f- |sed '/^ALTER TABLE .* ADD CONSTRAINT .* NOT NULL/{ s/ NO INHERIT// }' |psql
But I'm not sure whether that's enough of an improvement to warrant the
effort.
--
Justin
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: pryzby@telsasoft.com, alvherre@alvh.no-ip.org, andrew@dunslane.net, pgsql-hackers@lists.postgresql.org
Subject: Re: pgsql: Fix restore of not-null constraints with inheritance
In-Reply-To: <ZiRrHLZzwfukSf-v@pryzbyj2023>
* 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