Re: pg17 issues with not-null contraints

On Mon, Apr 15, 2024 at 03:47:38PM +0200, Alvaro Herrera wrote:
> On 2024-Apr-15, Justin Pryzby wrote:
> 
> > I think there are other issues related to b0e96f3119 (Catalog not-null
> > constraints) - if I dump a v16 server using v17 tools, the backup can't
> > be restored into the v16 server.  I'm okay ignoring a line or two like
> > 'unrecognized configuration parameter "transaction_timeout", but not
> > 'syntax error at or near "NO"'.
> 
> This doesn't look something that we can handle at all.  The assumption
> is that pg_dump's output is going to be fed to a server that's at least
> the same version.  Running on older versions is just not supported.

You're right - the docs say:

|Also, it is not guaranteed that pg_dump's output can be loaded into a
|server of an older major version — not even if the dump was taken from a
|server of that version

Here's a couple more issues affecting upgrades from v16 to v17.

postgres=# CREATE TABLE a(i int NOT NULL); CREATE TABLE b(i int PRIMARY KEY) INHERITS (a);
pg_restore: error: could not execute query: ERROR:  constraint "pgdump_throwaway_notnull_0" of relation "b" does not
exist

postgres=# CREATE TABLE a(i int CONSTRAINT a NOT NULL PRIMARY KEY); CREATE TABLE b()INHERITS(a); ALTER TABLE b ADD
CONSTRAINTpkb PRIMARY KEY (i);
 
pg_restore: error: could not execute query: ERROR:  cannot drop inherited constraint "pgdump_throwaway_notnull_0" of
relation"b"
 

-- 
Justin