duplicated values on primary key field on reindex

From: "Weerts, Jan" <j(dot)weerts(at)i-views(dot)de>
To: "Pgsql-General (E-Mail)" <pgsql-general(at)postgresql(dot)org>
Subject: duplicated values on primary key field on reindex
Date: 2006-07-06 21:36:11
Message-ID: 46C7D0FDF98FDC429E6E57D8DF3CF77B9629@ivsrv03.i-views.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi all!

This was 8.1.3 and now is 8.1.4 running on Debian Sarge, locally
compiled without any fancy options.

Since Tuesday we experience strange diconnects from our database
used as a source code respository for the developers.
First indicators of trouble are log lines like
:ERROR: invalid memory alloc request size 2003127913

Later on follow some disconnects due to process termination
:LOG: server process (PID 8276) was terminated by signal 11
:LOG: terminating any other active server processes
:WARNING: terminating connection because of crash of another
server process
:DETAIL: The postmaster has commanded this server process to
roll back the current transaction and exit, because another
server process exited abnormally and possibly corrupted
shared memory.
:HINT: In a moment you should be able to reconnect to the
database and repeat your command.

Trying to dump the DB results in an error for one table named
tw_blob (given by the source code management tool). The table is:
Table "public.tw_blob"
Column | Type | Modifiers
------------+---------+-----------
primarykey | integer | not null
blobtype | integer |
blobdata | bytea |
Indexes:
"tw_blob_pkey" PRIMARY KEY, btree (primarykey)

After some crawling I found a row to be damaged
# select * from tw_blob order by primarykey limit 1 offset 1636022;
primarykey | blobtype | blobdata
------------+----------+---------
1637694 | 2 | dXBkYXRl
(1 row)
(last field is truncated for the sake of this email)

trying to read this and the next row
# select * from tw_blob order by primarykey limit 2 offset 1636022;
ERROR: invalid memory alloc request size 2003127913

The next query is answered in two different flavors
# select primarykey from tw_blob order by primarykey limit 2 offset 1636022;

First answer:
primarykey
------------
1636694
1636695
(2 rows)

Second answer:
primarykey
------------
1637694
216305
(2 rows)

While the first answer seems much more valid (the primarkey is
an artificially created number), the second answer seems to
be the one being presented for all further invocations of the
above query.

I noted, that the second row does not fit the "order by" clause,
so I tried a reindex of the db, but that led to a duplicate
value error:
# reindex index tw_blob_pkey;
ERROR: could not create unique index
DETAIL: Table contains duplicated values.

Now that is something I don't understand at all.

Since the backup for said server went postal too long ago
unnoticed, I would prefer a "repair" solution. Any ideas?

TIA
Jan

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Scott Marlowe 2006-07-06 22:16:27 Re: duplicated values on primary key field on reindex
Previous Message David Fetter 2006-07-06 21:24:59 Re: [BUGS] BUG #2517: Trouble with cx_Oracle and Plpython