pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedv12.4 pg_dump .sql fails to load data via psql
5+ messages / 4 participants
[nested] [flat]
* v12.4 pg_dump .sql fails to load data via psql
@ 2021-10-11 22:26 JED WALKER <jedwa@comcast.net>
2021-10-11 22:48 ` Re: v12.4 pg_dump .sql fails to load data via psql David G. Johnston <david.g.johnston@gmail.com>
2021-10-11 22:55 ` Re: v12.4 pg_dump .sql fails to load data via psql Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 5+ messages in thread
From: JED WALKER @ 2021-10-11 22:26 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>; +Cc: Jed@Home <jedwa@comcast.net>; Jed@ICD <jed.walker@icd-tech.com>
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
1. Move the tables referenced in the check constraint function, and place at the beginning of the COPY section
2. Disable the (or all) constraints on the affected tables and enable at the end.
3. 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.
1. Create a fresh PostgreSQL database
1. Load instance with TestCase1BuildDB.sql
e.g. psql -f TestCase1BuildDB.sql
1. pg_dumpall instance
e.g. pg_dumpall -h0.0.0.0 -p5432 -Upostgres > TestCase1.sql
1. Create a fresh PostgreSQL database
1. 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
Attachments:
TestCase1.sql (application/octet-stream, 10.0K) download
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: v12.4 pg_dump .sql fails to load data via psql
2021-10-11 22:26 v12.4 pg_dump .sql fails to load data via psql JED WALKER <jedwa@comcast.net>
@ 2021-10-11 22:48 ` David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: David G. Johnston @ 2021-10-11 22:48 UTC (permalink / raw)
To: JED WALKER <jedwa@comcast.net>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>; Jed@ICD <jed.walker@icd-tech.com>
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.
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: v12.4 pg_dump .sql fails to load data via psql
2021-10-11 22:26 v12.4 pg_dump .sql fails to load data via psql JED WALKER <jedwa@comcast.net>
@ 2021-10-11 22:55 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-10-12 14:27 ` RE: v12.4 pg_dump .sql fails to load data via psql Jed Walker <jed.walker@icd-tech.com>
1 sibling, 1 reply; 5+ messages in thread
From: Tom Lane @ 2021-10-11 22:55 UTC (permalink / raw)
To: JED WALKER <jedwa@comcast.net>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>; Jed@ICD <jed.walker@icd-tech.com>
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* RE: v12.4 pg_dump .sql fails to load data via psql
2021-10-11 22:26 v12.4 pg_dump .sql fails to load data via psql JED WALKER <jedwa@comcast.net>
2021-10-11 22:55 ` Re: v12.4 pg_dump .sql fails to load data via psql Tom Lane <tgl@sss.pgh.pa.us>
@ 2021-10-12 14:27 ` Jed Walker <jed.walker@icd-tech.com>
2021-10-12 15:50 ` Re: v12.4 pg_dump .sql fails to load data via psql David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Jed Walker @ 2021-10-12 14:27 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; JED WALKER <jedwa@comcast.net>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
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@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 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%2Fcurren...
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: v12.4 pg_dump .sql fails to load data via psql
2021-10-11 22:26 v12.4 pg_dump .sql fails to load data via psql JED WALKER <jedwa@comcast.net>
2021-10-11 22:55 ` Re: v12.4 pg_dump .sql fails to load data via psql Tom Lane <tgl@sss.pgh.pa.us>
2021-10-12 14:27 ` RE: v12.4 pg_dump .sql fails to load data via psql Jed Walker <jed.walker@icd-tech.com>
@ 2021-10-12 15:50 ` David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: David G. Johnston @ 2021-10-12 15:50 UTC (permalink / raw)
To: Jed Walker <jed.walker@icd-tech.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; JED WALKER <jedwa@comcast.net>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2021-10-12 15:50 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-10-11 22:26 v12.4 pg_dump .sql fails to load data via psql JED WALKER <jedwa@comcast.net>
2021-10-11 22:48 ` David G. Johnston <david.g.johnston@gmail.com>
2021-10-11 22:55 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-10-12 14:27 ` Jed Walker <jed.walker@icd-tech.com>
2021-10-12 15:50 ` David G. Johnston <david.g.johnston@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox