Thread: pg_dump - wrong order with inheritance
*PostgreSQL version*: "PostgreSQL 9.4.4 on i686-pc-linux-gnu, compiled by gcc
(Debian 4.4.5-8) 4.4.5, 32-bit"*Operating system*: Debian GNU/Linux
6.0*pg_dump*: pg_dump (PostgreSQL) 9.4.4*command*:
/opt/postgresql-9.4.4/bin/pg_dump -U myuser -p 5433 $DBNAME |
/opt/postgresql-9.4.4/bin/psql -p 5433 -U myuser ${DBNAME}_${branch}pg_dump
doesn't dump data in correct order, so I'm not able to properly restore
data. I've used command above and got following errors:
136257:2015-11-23 10:46:55 CET [3107]: [5150-1]
user=myuser,db=copied_db,app=psql,client=[local] ERROR: null value in
column "category_xxx" violates not-null constraint136337:2015-11-23 10:46:55
CET [3107]: [5169-1] user=myuser,db=copied_db,app=psql,client=[local] ERROR:
null value in column "xxx_id" violates not-null constraint139900:2015-11-23
10:46:59 CET [3107]: [6932-1]
user=myuser,db=copied_db,app=psql,client=[local] ERROR: insert or update on
table "xxx01" violates foreign key constraint "..."139975:2015-11-23
10:46:59 CET [3107]: [6982-1]
user=myuser,db=copied_db,app=psql,client=[local] ERROR: insert or update on
table "xxxx09" violates foreign key constraint "..."140026:2015-11-23
10:46:59 CET [3107]: [7016-1]
user=myuser,db=copied_db,app=psql,client=[local] ERROR: insert or update on
table "xxxx22" violates foreign key constraint "..."140041:2015-11-23
10:46:59 CET [3107]: [7026-1]
user=myuser,db=copied_db,app=psql,client=[local] ERROR: insert or update on
table "xxxx11" violates foreign key constraint "ooo"140046:2015-11-23
10:46:59 CET [3107]: [7030-1]
user=myuser,db=copied_db,app=psql,client=[local] ERROR: constraint "ooo"
for table "xxx22" does not exist140056:2015-11-23 10:46:59 CET [3107]:
[7037-1] user=myuser,db=copied_db,app=psql,client=[local] ERROR: insert or
update on table "xxx44" violates foreign key constraint
"..."140086:2015-11-23 10:46:59 CET [3107]: [7057-1]
user=myuser,db=copied_db,app=psql,client=[local] ERROR: insert or update on
table "xxx12" violates foreign key constraint "..."140119:2015-11-23
10:46:59 CET [3107]: [7079-1]
user=myuser,db=copied_db,app=psql,client=[local] ERROR: insert or update on
table "xxx27" violates foreign key constraint "..."
Some tables are inherited. Maybe it's the issue with right order by
inheritance?Maybe I can somehow force another order?
--
View this message in context: http://postgresql.nabble.com/pg-dump-wrong-order-with-inheritance-tp5874794.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.
On Mon, Nov 23, 2015 at 9:27 PM, pinker <pinker@onet.eu> wrote: > Some tables are inherited. Maybe it's the issue with right order by inheritance? Maybe I can somehow force another order? pg_dump is usually able to take care of that automatically and dumps the data in an order that makes sense by looking at the tree dependency of relations. One way that would allow you to bypass this error in any case is to use pg_dump --disable-triggers so as those integrity checks are disabled when loading the data. This will add in the dump the necessary commands to do so, thought reloading the dump will need superuser rights. -- Michael
pinker <pinker@onet.eu> writes:
> *PostgreSQL version*: "PostgreSQL 9.4.4 on i686-pc-linux-gnu, compiled by gcc
> (Debian 4.4.5-8) 4.4.5, 32-bit"*Operating system*: Debian GNU/Linux
> 6.0*pg_dump*: pg_dump (PostgreSQL) 9.4.4*command*:
> /opt/postgresql-9.4.4/bin/pg_dump -U myuser -p 5433 $DBNAME |
> /opt/postgresql-9.4.4/bin/psql -p 5433 -U myuser ${DBNAME}_${branch}pg_dump
> doesn't dump data in correct order, so I'm not able to properly restore
> data.
You have not provided enough information to prove that that's the actual
nature of the problem, much less for someone else to be able to reproduce
it (and thereby maybe fix it, or at least provide useful advice). Please
read
http://www.postgresql.org/docs/9.4/static/bug-reporting.html
and/or
https://wiki.postgresql.org/wiki/Guide_to_reporting_problems
FWIW, the reason I'm doubtful of your wrong-dump-order diagnosis is
that it's pretty hard to see how a not-null violation would arise
that way. The subsequent complaints may only be consequences of
that one.
regards, tom lane
Tom Lane-2 wrote
> FWIW, the reason I'm doubtful of your wrong-dump-order diagnosis is
> that it's pretty hard to see how a not-null violation would arise
> that way. The subsequent complaints may only be consequences of
> that one.
i've just prepared test case and did some debugging. It turned out that the
issue isn't dump order but wrong ddl generated by pg_dump.
<pre>
CREATE TABLE a00
(
id INTEGER NOT NULL,
name TEXT NOT NULL,
CONSTRAINT a00_pkey PRIMARY KEY (id)
)
WITH (
OIDS =FALSE
);
CREATE TABLE a03
(
id INTEGER NOT NULL,
name TEXT NOT NULL,
CONSTRAINT a03_pkey PRIMARY KEY (id)
)
WITH (
OIDS =FALSE
);
ALTER TABLE a03
INHERIT a00;
ALTER TABLE a03
ALTER COLUMN name DROP NOT NULL;
</pre>
and now:
pg_dump testdump | psql testdump02
<pre>
COPY 0
ERROR: null value in column "name" violates not-null constraint
DETAIL: Failing row contains (1, null).
CONTEXT: COPY a03, line 1: "1 \N"
</pre>
so pg_dump is trying to create table a03 this way:
<pre>
CREATE TABLE a03 (
id integer,
name text
)
INHERITS (a00);
</pre>
and don't takes into account that column name has changed.
--
View this message in context: http://postgresql.nabble.com/pg-dump-wrong-order-with-inheritance-tp5874794p5874925.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.
pinker <pinker@onet.eu> writes:
> i've just prepared test case and did some debugging. It turned out that the
> issue isn't dump order but wrong ddl generated by pg_dump.
> CREATE TABLE a00
> (
> id INTEGER NOT NULL,
> name TEXT NOT NULL,
> CONSTRAINT a00_pkey PRIMARY KEY (id)
> )
> WITH (
> OIDS =FALSE
> );
> CREATE TABLE a03
> (
> id INTEGER NOT NULL,
> name TEXT NOT NULL,
> CONSTRAINT a03_pkey PRIMARY KEY (id)
> )
> WITH (
> OIDS =FALSE
> );
> ALTER TABLE a03
> INHERIT a00;
> ALTER TABLE a03
> ALTER COLUMN name DROP NOT NULL;
Ah. This is not pg_dump's fault, because actually what you've got here
is a logically inconsistent database: at this point, it's possible to
select from a00 and see some rows with null name values, which should
not be possible given the declared NOT NULL constraint on a00.name.
This is a known limitation of the backend. It should refuse to let you
DROP NOT NULL in a case where the NOT NULL is an inherited constraint,
as it is here. It would not let you drop a CHECK constraint in an
equivalent situation; but the NOT NULL support is older/more primitive
and currently doesn't do enough bookkeeping to realize that a03's NOT NULL
constraint is inherited from a parent table. Fixing that is on our to-do
list, but it hasn't been very high priority.
regards, tom lane
Tom Lane-2 wrote > Ah. This is not pg_dump's fault, because actually what you've got here > is a logically inconsistent database: at this point, it's possible to > select from a00 and see some rows with null name values, which should > not be possible given the declared NOT NULL constraint on a00.name. > > This is a known limitation of the backend. It should refuse to let you > DROP NOT NULL in a case where the NOT NULL is an inherited constraint, > as it is here. It would not let you drop a CHECK constraint in an > equivalent situation; but the NOT NULL support is older/more primitive > and currently doesn't do enough bookkeeping to realize that a03's NOT NULL > constraint is inherited from a parent table. Fixing that is on our to-do > list, but it hasn't been very high priority. > > regards, tom lane Thank you for your explanations, I have already made changes to my model. -- View this message in context: http://postgresql.nabble.com/pg-dump-wrong-order-with-inheritance-tp5874794p5874935.html Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.
On Wed, Nov 25, 2015 at 1:43 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > This is a known limitation of the backend. It should refuse to let you > DROP NOT NULL in a case where the NOT NULL is an inherited constraint, > as it is here. It would not let you drop a CHECK constraint in an > equivalent situation; but the NOT NULL support is older/more primitive > and currently doesn't do enough bookkeeping to realize that a03's NOT NULL > constraint is inherited from a parent table. Fixing that is on our to-do > list, but it hasn't been very high priority. Just an update for pinker: I have posted a patch here to address the problem in the next major releases, 10.0 presumably: https://www.postgresql.org/message-id/CAB7nPqTPXgX9HiyhhtAgpW7jbA1iskMCSoqXPEEB_KYXYy1E1Q@mail.gmail.com -- Michael