Обсуждение: BUG #8577: pg_dump custom format exported dump can't be imported again

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