BUG #8577: pg_dump custom format exported dump can't be imported again

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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

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/

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Dominik Dorn <dominik@dominikdorn.com> wrote:

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.

DELETE FROM lytartist where ctid = '(284,60)';

And how can I prevent inserts like these in the future?

I would start by applying any updates available for the firmware,
OS, storage drivers, and PostgreSQL.  And I would probably schedule
a hardware check for the next suitable maintenance window.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs