Thread: v12.4 pg_dump .sql fails to load data via psql
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.
As a logical backup method this should be loadable into a clean database as-is.
Error:
psql:TestCase1.sql:286: ERROR: The schema/table/column cannot be found in a Domain Relation
CONTEXT: PL/pgSQL function frp_pkg_picklist.is_item_valid_for_column(character varying,character varying,character varying,character varying,boolean,boolean) line 19 at RAISE
COPY customer, line 1: "0 SHARED_DO_NOT_USE customer"
This can be remedied by manually modifying the pg_dumpall .sql file as a workaround
- Move the tables referenced in the check constraint function, and place at the beginning of the COPY section
- Disable the (or all) constraints on the affected tables and enable at the end.
- Do not create the (or all) constraints on the affected tables and create them at the end.
All of these require changes to the output file (risk and work), but it should load cleanly without that.
==========
Test Case
----------
I've minimized the situation to a small test-set to show how it works (or doesn't).
See TestCase1.txt
Also see 202110load.txt for initial discovery information.
- Create a fresh PostgreSQL database
- Load instance with TestCase1BuildDB.sql
e.g. psql -f TestCase1BuildDB.sql
- pg_dumpall instance
e.g. pg_dumpall -h0.0.0.0 -p5432 -Upostgres > TestCase1.sql
- Create a fresh PostgreSQL database
- Load databae from TestCase1.sql dumpall
e.g. psql -f TestCase1.sql
should fail with:
... ERROR: The schema/table/column cannot be found in a Domain Relation
... CONTEXT: PL/pgSQL function frp_pkg_picklist.is_item_valid_for_column(character varying,character varying,character varying,character varying,boolean,boolean) line 19 at RAISE
... COPY customer, line 1: "0 SHARED_DO_NOT_USE customer"
... COPY frp.customer (id, formal_name, accounting_period_scope) FROM stdin;
The check constraint on the table calls a function
and that functions makes a decision based on data in the picklist table
which has not been loaded yet, thus it fails the constraint check and does not load rows.
- Jed
Attachment
On Mon, Oct 11, 2021 at 3:35 PM JED WALKER <jedwa@comcast.net> wrote:
This occurs using check constraints that reference a function using data in a table that is loaded later.
As this is not a supported setup, we require that the check expression be immutable, this is not our problem to solve.
David J.
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 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://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-CHECK-CONSTRAINTS
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
On Tue, Oct 12, 2021 at 8:00 AM Jed Walker <jed.walker@icd-tech.com> wrote:
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 ....
For the case where it actually matters we do exactly this. Triggers. For check constraints, assuming you actually apply them to the existing data when they are enabled or created, there is little benefit to be gained compared to just evaluating the rows as they are recorded. That benefit would be strictly performance related in any case - there are no timing issues because check constraints can only reference the table upon which they are created.
I can work around this by changing the function to recognize "table empty" and produce just a warning.
I would suggest you not "work-around this" at all but instead properly define triggers to implement the desired logic. That was the take-away you were intended from reading the documentation section you quoted (note, you should generally check current version documentation, 9.4 is seriously out-of-date).
David J.
P.S. As others have been doing, it is customary to post replies inline (or strictly at the bottom at worst) with the material you are replying to, and to liberally trim away unneeded context. The mailing list thread for the conversation is much easier to consume when people do this.
Thank you. I originally did this with triggers but there was a push to use the constraint method as it is more easily readable when looking at the table structure (constraint makes it clear what is happening, what column it applies to, versus just seeing there are triggers and having to read code.) I have to admit, I concur with the readability thing since converting. Just making the point for reference - obviously there are pros and cons.
Thanks for the reply tips. I think this is the first "bug" I've filed. Hopefully I won't have more, but good to know!