Search Postgresql Archives

duplicated values on primary key field on reindex

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux