Re: Duplicate primary key record

Your name : Joel Jacobson
Your email address : joel(at)jacobson(dot)be

System Configuration
---------------------
Architecture (example: Intel Pentium) : Intel(R) Pentium(R) III

Operating System (example: Linux 2.0.26 ELF) : Linux 2.4.21

PostgreSQL version (example: PostgreSQL-7.3.3): PostgreSQL-7.3.3

Compiler used (example: gcc 2.95.2) : gcc version 2.95.4 20011002
(Debian prerelease)

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)

pbs=> UPDATE UserBalances SET Balance = 10000, ReservedBalance=0 where userid =
1002024;
ERROR: Cannot insert a duplicate key into unique index userbalances_pkey

I can't understand how two rows with the same primary key can exist in this
table.

I will keep the table in this state if anyone would like to help debugging
this.

Best regards,

Joel Jacobson <joel(at)jacobson(dot)be>

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(at)megazone(dot)bigpanda(dot)com>:
> Hmm, what does select oid,xmin,xmax,* where userid=1002024; give?

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(at)bigfoot(dot)com>:

> 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

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(at)megazone(dot)bigpanda(dot)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