RE: v12.4 pg_dump .sql fails to load data via psql

Ah, OK, I found the reference in the check constraint documentation (should have thought to look there ☹ )    
Pity pg_dumpall doesn't work like other dbs where the constraints are added or enabled after data load (nice
enhancementif done.) That would solve the problem, but if enough people don't have the issue .... 
 
I can work around this by changing the function to recognize "table empty" and produce just a warning.

Thank you for looking and responding even though I missed the Note in the check constraint reference.

https://www.postgresql.org/docs/9.4/ddl-constraints.html
Note: PostgreSQL does not support CHECK constraints that reference table data other than the new or updated row being
checked.While a CHECK constraint that violates this rule may appear to work in simple tests, it cannot guarantee that
thedatabase will not reach a state in which the constraint condition is false (due to subsequent changes of the other
row(s)involved). This would cause a database dump and reload to fail. The reload could fail even when the complete
databasestate is consistent with the constraint, due to rows not being loaded in an order that will satisfy the
constraint.If possible, use UNIQUE, EXCLUDE, or FOREIGN KEY constraints to express cross-row and cross-table
restrictions.

If what you desire is a one-time check against other rows at row insertion, rather than a continuously-maintained
consistencyguarantee, a custom trigger can be used to implement that. (This approach avoids the dump/reload problem
becausepg_dump does not reinstall triggers until after reloading data, so that the check will not be enforced during a
dump/reload.)


- Jed


-----Original Message-----
From: Tom Lane <tgl@sss.pgh.pa.us> 
Sent: Monday, October 11, 2021 16:55
To: JED WALKER <jedwa@comcast.net>
Cc: pgsql-bugs@lists.postgresql.org; Jed Walker <jed.walker@icd-tech.com>
Subject: Re: v12.4 pg_dump .sql fails to load data via psql

[You don't often get email from tgl@sss.pgh.pa.us. Learn why this is important at
http://aka.ms/LearnAboutSenderIdentification.]

JED WALKER <jedwa@comcast.net> writes:
> PostgreSQL v12.4 pg_dumpall output fails to load via "psql -f" load to empty instance/server.
> This occurs using check constraints that reference a function using data in a table that is loaded later.

That's never worked reliably, neither in v12 nor any other version.
The function is a "black box", so the fact that it creates an ordering constraint isn't apparent to pg_dump.

Note that our documentation specifically disclaims correct enforcement of CHECK constraints that reference any mutable
dataother than the row being checked (see NOTEs at [1]).  Even if the particular scenario you describe here happened to
work,there are many other cases where such a constraint could become violated after it was initially checked --- and
Postgreswouldn't notice.  Nor do we have any interest in making it notice.
 

                        regards, tom lane

[1]
https://nam04.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.postgresql.org%2Fdocs%2Fcurrent%2Fddl-constraints.html%23DDL-CONSTRAINTS-CHECK-CONSTRAINTS&data=04%7C01%7Cjed.walker%40icd-tech.com%7C761b0345ebb64a8969d908d98d0a38f5%7Cee3d5ccdf951421a8e1fd14a200c003f%7C0%7C0%7C637695897761660129%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=yw0nypvBImJYoFh1285bOTkVqS2gH78PkAMZoZEwK9k%3D&reserved=0