Обсуждение: 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
От
dominik@dominikdorn.com
Дата:
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
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