Thread: BUG #8577: pg_dump custom format exported dump can't be imported again
BUG #8577: pg_dump custom format exported dump can't be imported again
From
dominik@dominikdorn.com
Date:
The following bug has been logged on the website:
Bug reference: 8577
Logged by: Dominik Dorn
Email address: dominik@dominikdorn.com
PostgreSQL version: 9.1.10
Operating system: Ubuntu x64
Description:
Hi,
I ran into an issue trying to restore a custom dump from postgresql 9.1.10
from one machine into postgresql 9.1.10 on my CI machine.
For some reason, pg_dump inserts an entry with null values into the dump
(even for the primary key).
The commands I used are:
pg_dump -Fc -f dump.sql mydatabase (on the source machine)
pg_restore -e -d mydatabase_2013_11_05 dump.sql
The error I get is:
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 3370; 0 61665 TABLE DATA
lytartist lyriks
pg_restore: [archiver (db)] COPY failed for table "lytartist": ERROR: null
value in column "nartistnr" violates not-null constraint
CONTEXT: COPY lytartist, line 21841: "\N \N \N \N \N \N \N \N \N \N \N"
pg_restore: [archiver] worker process failed: exit code 1
the table in question looks like this:
lyriks=> \d lyriks.lytartist
Table "lyriks.lytartist"
Column | Type |
Modifiers
----------------+-----------------------------+---------------------------------------------------------------
nartistnr | integer | not null default
nextval('lytartist_nartistnr_seq'::regclass)
sartist | character varying(250) | not null default
''::character varying
nartistnralias | integer |
nstatusnr | integer | not null default 1660
ntypenr | integer | not null default 510
surl | character varying(250) | not null default
''::character varying
nlabelnr | integer |
nusernr | integer | not null default 0
dnow | timestamp without time zone | not null
ssoundex | character varying(250) |
surlname | character varying(100) |
Of course, querying for the entry with a NULL PK results in no results on
the source machine.
Please help!
Thanks,
Dominik
On 2013-11-05 20:53:32 +0000, dominik@dominikdorn.com wrote: > For some reason, pg_dump inserts an entry with null values into the dump > (even for the primary key). > pg_restore: [archiver (db)] Error while PROCESSING TOC: > pg_restore: [archiver (db)] Error from TOC entry 3370; 0 61665 TABLE DATA > lytartist lyriks > pg_restore: [archiver (db)] COPY failed for table "lytartist": ERROR: null > value in column "nartistnr" violates not-null constraint > CONTEXT: COPY lytartist, line 21841: "\N \N \N \N \N \N \N \N \N \N \N" > pg_restore: [archiver] worker process failed: exit code 1 Hm. That might be caused by on-disk corruption... > Of course, querying for the entry with a NULL PK results in no results on > the source machine. Well, that will probably have used the the index, try it by doing something like: SET enable_indexscan = false; SET enable_bitmapscan = false; SET constraint_exclusion = false; EXPLAIN SELECT ctid, * FROM lytartist WHERE nartistnr IS NULL; SELECT ctid, * FROM lytartist WHERE nartistnr IS NULL; The explain should show a sequential scan, right? Does it now return a row? Greetings, Andres Freund
Hi Andres,
oh, it returns a row!
lyriks=> SET enable_indexscan = false;
SET
lyriks=> SET enable_bitmapscan = false;
SET
lyriks=> SET constraint_exclusion = false;
SET
lyriks=> EXPLAIN SELECT ctid, * FROM lytartist WHERE nartistnr IS NULL;
QUERY PLAN
-------------------------------------------------------------
Seq Scan on lytartist (cost=0.00..1274.20 rows=1 width=68)
Filter: (nartistnr IS NULL)
(2 rows)
lyriks=> SELECT ctid, * FROM lytartist WHERE nartistnr IS NULL;
ctid | nartistnr | sartist | nartistnralias | nstatusnr | ntypenr
| surl | nlabelnr | nusernr | dnow | ssoundex | surlname
----------+-----------+---------+----------------+-----------+---------+------+----------+---------+------+----------+----------
(284,60) | | | | |
| | | | | |
(1 row)
How do I delete it from there there?
lyriks=> DELETE FROM lytartist where ctid = (284,60);
ERROR: operator does not exist: tid = record
LINE 1: DELETE FROM lytartist where ctid = (284,60);
^
HINT: No operator matches the given name and argument type(s). You
might need to add explicit type casts.
And how can I prevent inserts like these in the future?
Thanks!
Dominik
On Tue, Nov 5, 2013 at 10:15 PM, Andres Freund <andres@2ndquadrant.com> wrote:
> On 2013-11-05 20:53:32 +0000, dominik@dominikdorn.com wrote:
>> For some reason, pg_dump inserts an entry with null values into the dump
>> (even for the primary key).
>
>> pg_restore: [archiver (db)] Error while PROCESSING TOC:
>> pg_restore: [archiver (db)] Error from TOC entry 3370; 0 61665 TABLE DATA
>> lytartist lyriks
>> pg_restore: [archiver (db)] COPY failed for table "lytartist": ERROR: null
>> value in column "nartistnr" violates not-null constraint
>> CONTEXT: COPY lytartist, line 21841: "\N \N \N \N \N \N \N \N \N \N
\N"
>> pg_restore: [archiver] worker process failed: exit code 1
>
> Hm. That might be caused by on-disk corruption...
>
>> Of course, querying for the entry with a NULL PK results in no results on
>> the source machine.
>
> Well, that will probably have used the the index, try it by doing
> something like:
> SET enable_indexscan = false;
> SET enable_bitmapscan = false;
> SET constraint_exclusion = false;
> EXPLAIN SELECT ctid, * FROM lytartist WHERE nartistnr IS NULL;
> SELECT ctid, * FROM lytartist WHERE nartistnr IS NULL;
>
> The explain should show a sequential scan, right? Does it now return a row?
>
> Greetings,
>
> Andres Freund
--
Dominik Dorn
http://dominikdorn.com | http://twitter.com/domdorn
XING: https://www.xing.com/profile/Dominik_Dorn
LINKEDIN: http://at.linkedin.com/pub/dominik-dorn/66/b42/bb1/
On 11/5/2013 1:39 PM, Dominik Dorn wrote: > ERROR: operator does not exist: tid = record > LINE 1: DELETE FROM lytartist where ctid = (284,60); try, DELETE FROM lytartist where ctid = '(284,60)'; ? or, for that matter, WHERE nartistnr IS NULL; -- john r pierce 37N 122W somewhere on the middle of the left coast
Re: BUG #8577: pg_dump custom format exported dump can't be imported again
From
Kevin Grittner
Date:
Dominik Dorn <dominik@dominikdorn.com> wrote:=0A=0A> How do I delete it fro= m there there?=0A> lyriks=3D> DELETE FROM lytartist where ctid =3D (284,60)= ;=0A> ERROR:=A0 operator does not exist: tid =3D record=0A> LINE 1: DELETE = FROM lytartist where ctid =3D (284,60);=0A>=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0 ^=0A> HINT:=A0 No operator matches the given name and argum= ent type(s).=0A> You might need to add explicit type casts.=0A=0ADELETE FRO= M lytartist where ctid =3D '(284,60)';=0A=0A> And how can I prevent inserts= like these in the future?=0A=0AI would start by applying any updates avail= able for the firmware,=0AOS, storage drivers, and PostgreSQL.=A0 And I woul= d probably schedule=0Aa hardware check for the next suitable maintenance wi= ndow.=0A=0A--=0AKevin Grittner=0AEDB: http://www.enterprisedb.com=0AThe Ent= erprise PostgreSQL Company