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 enhancement if 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 the database 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 database state 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 consistency guarantee, a custom trigger can be used to implement that. (This approach avoids the dump/reload problem because pg_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(at)sss(dot)pgh(dot)pa(dot)us>
Sent: Monday, October 11, 2021 16:55
To: JED WALKER <jedwa(at)comcast(dot)net>
Cc: pgsql-bugs(at)lists(dot)postgresql(dot)org; Jed Walker <jed(dot)walker(at)icd-tech(dot)com>
Subject: Re: v12.4 pg_dump .sql fails to load data via psql

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

JED WALKER <jedwa(at)comcast(dot)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 data other 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 Postgres wouldn'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&amp;data=04%7C01%7Cjed.walker%40icd-tech.com%7C761b0345ebb64a8969d908d98d0a38f5%7Cee3d5ccdf951421a8e1fd14a200c003f%7C0%7C0%7C637695897761660129%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&amp;sdata=yw0nypvBImJYoFh1285bOTkVqS2gH78PkAMZoZEwK9k%3D&amp;reserved=0