Date: Sat, 20 Apr 2024 20:25:48 -0500
From: Justin Pryzby <pryzby@telsasoft.com>
To: Alvaro Herrera <alvherre@alvh.no-ip.org>
Cc: Andrew Dunstan <andrew@dunslane.net>,
	pgsql-hackers@lists.postgresql.org
Subject: Re: pgsql: Fix restore of not-null constraints with inheritance
Message-ID: <ZiRrHLZzwfukSf-v@pryzbyj2023>
References: <3a642a59-e3c7-4050-95dd-af3b5412e5f7@dunslane.net>
 <202404191159.cakeaydac4sq@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <202404191159.cakeaydac4sq@alvherre.pgsql>
Archived-At: 
 <https://www.postgresql.org/message-id/ZiRrHLZzwfukSf-v%40pryzbyj2023>
Precedence: bulk

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