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>


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

On Sun, 16 Nov 2003, Joel Jacobson wrote:

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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

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.com>:
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Joel Jacobson wrote:
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


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

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>:


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

On Sun, 16 Nov 2003, Joel Jacobson wrote:

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.

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Joel Jacobson wrote:


http://archives.postgresql.org/pgsql...4/msg00407.php


I decreased the vacuum frequency :-(


Regards
Gaetano Mendola


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Stephan Szabo <sszabo (AT) megazone (DOT) bigpanda.com> writes:
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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match