Duplicate primary key record

On Sun, 16 Nov 2003, Joel Jacobson wrote:

Please enter a FULL description of your problem:
------------------------------------------------
I have simple table with a primary key.
Somehow two records with the SAME primary key has managed to get into the
table.
This should as far as I know be impossible.
I should mention that my Postgres daemon crashed two times today when I was
increasing its memory usage setting.
I guess this could have something to do with the problem.

Table
"public.userbalances"
Column | Type |
Modifiers
------------------+---------------+--------------------------------------------------------------------------------------------------
userid | integer | not null
balance | numeric(12,2) | not null
reservedbalance | numeric(12,2) | not null
modificationdate | integer | not null default (date_part('epoch'::text,
('now'::text)::timestamp(6) with time zone))::integer
Indexes: userbalances_pkey primary key btree (userid)
Foreign Key constraints: $1 FOREIGN KEY (userid) REFERENCES users(userid) ON
UPDATE NO ACTION ON DELETE NO ACTION
Triggers: autostamp

pbs=> select * from userbalances where userid = 1002024;
userid | balance | reservedbalance | modificationdate
---------+----------+-----------------+------------------
1002024 | 10000.00 | 154.02 | 1068947725
1002024 | 10000.00 | 727.57 | 1068949964
(2 rows)

Hmm, what does select oid,xmin,xmax,* where userid=1002024; give?

Hi Stephan,

Thanks for a quick reply.

pbs=# SELECT oid,xmin,xmax,* FROM UserBalances WHERE UserID = 1002024;
oid | xmin | xmax | userid | balance | reservedbalance |
modificationdate
---------+-----------+-----------+---------+----------+-----------------+------------------
2080463 | 248152344 | 248307452 | 1002024 | 10000.00 | 154.02 |
1068947725
2080463 | 248274508 | 248307402 | 1002024 | 10000.00 | 727.57 |
1068949964
(2 rows)

Please let me know if there is anything else that I can test.

Best regards,

Joel Jacobson

Citerar Stephan Szabo <sszabo@megazone.bigpanda.com>:

Joel Jacobson wrote:

Hi Stephan,

Thanks for a quick reply.

pbs=# SELECT oid,xmin,xmax,* FROM UserBalances WHERE UserID = 1002024;
oid | xmin | xmax | userid | balance | reservedbalance |
modificationdate
---------+-----------+-----------+---------+----------+-----------------+------------------
2080463 | 248152344 | 248307452 | 1002024 | 10000.00 | 154.02 |
1068947725
2080463 | 248274508 | 248307402 | 1002024 | 10000.00 | 727.57 |
1068949964
(2 rows)

Please let me know if there is anything else that I can test.

This happen a few time to me too and I solved not running anymore
each night a REINDEX on the table. I posted this problem in the
past ...

Do you scedule reindex on this table very often ?

Regards
Gaetano Mendola

No, I have never run REINDEX on any table.
However, I have done many VACUUM FULL ANALYZE on the complete database.

Citerar Gaetano Mendola <mendola@bigfoot.com>:

On Sun, 16 Nov 2003, Joel Jacobson wrote:

Hi Stephan,

Thanks for a quick reply.

pbs=# SELECT oid,xmin,xmax,* FROM UserBalances WHERE UserID = 1002024;
oid | xmin | xmax | userid | balance | reservedbalance |
modificationdate
---------+-----------+-----------+---------+----------+-----------------+------------------
2080463 | 248152344 | 248307452 | 1002024 | 10000.00 | 154.02 |
1068947725
2080463 | 248274508 | 248307402 | 1002024 | 10000.00 | 727.57 |
1068949964
(2 rows)

Hmm, in this case it looks to me like you literally have two versions of
the same row rather than two different rows with duplicate primary key
values. I'm not really sure what would have caused that, but I think it's
come up before (but I can't remember the discussion), so you might want to
check the archives.

Joel Jacobson wrote:

No, I have never run REINDEX on any table.
However, I have done many VACUUM FULL ANALYZE on the complete database.

Yes, once I had this also due to a vacuum:

http://archives.postgresql.org/pgsql-admin/2003-04/msg00407.php

I decreased the vacuum frequency :-(

Regards
Gaetano Mendola

Stephan Szabo <sszabo@megazone.bigpanda.com> writes:

On Sun, 16 Nov 2003, Joel Jacobson wrote:

oid | xmin | xmax | userid | balance | reservedbalance |
modificationdate
---------+-----------+-----------+---------+----------+-----------------+------------------
2080463 | 248152344 | 248307452 | 1002024 | 10000.00 | 154.02 |
1068947725
2080463 | 248274508 | 248307402 | 1002024 | 10000.00 | 727.57 |
1068949964
(2 rows)

Hmm, in this case it looks to me like you literally have two versions of
the same row rather than two different rows with duplicate primary key
values.

Yeah, given that the OIDs are the same, it seems certain that this is
the result of a partially applied UPDATE (ie, new row version committed,
old row version not deleted). In theory that can't happen ...

One way that it could happen is if you have a disk drive that lies about
write-complete (most IDE drives will do so out-of-the-box). If you had
a system crash shortly after the UPDATE in question, it could be that
Postgres thought the two parts of the update were both down to disk,
when in reality only one had made it to the platter.

regards, tom lane