Re: pgsql: Fix restore of not-null constraints with inheritance

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