Re: [BUGS] BUG #2379: Duplicate pkeys in table

#1

Philip Warner wrote:

Quote:

We have an intermittent bug that occurs on a table which is updated several
times per second. The bug occurs every few days/weeks. It is usually
preceeded by a "tuple concurrently updated" messages, but I could not swear
it is always preceeded by it.

The result of the bug is demonstrated by:

select id,count(*) from xxx group by id having count(*)>1;
id | count
-------+-------
24613 | 6
(1 row)

Please do a

SELECT xmin, xmax, cmin, cmax FROM xxx where id = 24613;

if you still have that particular manifestation.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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

#2

On Thu, Apr 06, 2006 at 08:12:31AM -0400, Alvaro Herrera wrote:

Quote:

Please do a

SELECT xmin, xmax, cmin, cmax FROM xxx where id = 24613;

if you still have that particular manifestation.

Also, you'll probably need to set enable_indexscan to off prior to
running the above query.

--
Michael Fuhr

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

#3

Michael Fuhr wrote:

Quote:

On Thu, Apr 06, 2006 at 08:12:31AM -0400, Alvaro Herrera wrote:

Please do a

SELECT xmin, xmax, cmin, cmax FROM xxx where id = 24613;

# set enable_indexscan=off;

# SELECT xmin, xmax, cmin, cmax FROM xxx where id = 24613;
xmin | xmax | cmin | cmax
----------+----------+------+----------
32902771 | 0 | 20 | 32902872
32902771 | 0 | 20 | 32902872
32902771 | 0 | 20 | 32902872
32902771 | 0 | 20 | 32902872
32902771 | 0 | 20 | 32902872
32902771 | 33048159 | 20 | 20
(6 rows)

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

#4

Philip Warner <pjw (AT) rhyme (DOT) com.au> writes:

Quote:

# set enable_indexscan=off;
# SELECT xmin, xmax, cmin, cmax FROM xxx where id = 24613;
xmin | xmax | cmin | cmax
----------+----------+------+----------
32902771 | 0 | 20 | 32902872
32902771 | 0 | 20 | 32902872
32902771 | 0 | 20 | 32902872
32902771 | 0 | 20 | 32902872
32902771 | 0 | 20 | 32902872
32902771 | 33048159 | 20 | 20
(6 rows)

For completeness, could we also see ctid in that query?

regards, tom lane

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

#5

Philip Warner wrote:

Quote:

# set enable_indexscan=off;
# SELECT xmin, xmax, cmin, cmax FROM xxx where id = 24613;
xmin | xmax | cmin | cmax
----------+----------+------+----------
32902771 | 0 | 20 | 32902872
32902771 | 0 | 20 | 32902872
32902771 | 0 | 20 | 32902872
32902771 | 0 | 20 | 32902872
32902771 | 0 | 20 | 32902872
32902771 | 33048159 | 20 | 20
(6 rows)

Ugh.

Do the triggers involved have EXCEPTION clauses? (I assume they are
written in PL/pgSQL -- are there any in other languages?)

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

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

#6

Tom Lane wrote:

Quote:

For completeness, could we also see ctid in that query?
mail=# set enable_indexscan=off;

mail=# SELECT xmin, xmax, cmin, cmax,ctid FROM xxx where id = 24613;
xmin | xmax | cmin | cmax | ctid
----------+----------+------+----------+---------
32902771 | 0 | 20 | 32902872 | (0,7)
32902771 | 0 | 20 | 32902872 | (2,27)
32902771 | 0 | 20 | 32902872 | (58,27)
32902771 | 0 | 20 | 32902872 | (60,28)
32902771 | 0 | 20 | 32902872 | (69,3)
32902771 | 33048159 | 20 | 20 | (72,27)
(6 rows)

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

#7

Alvaro Herrera wrote:

Quote:

Do the triggers involved have EXCEPTION clauses? (I assume they are
written in PL/pgSQL -- are there any in other languages?)
Triggers that update this table are in pl/pgsql, and can *raise*

exceptions (using RAISE) if that is what you mean. They do not handle
them -- is that even possible on pl/pgsql?

Other triggers (for slony) are written in plpgsql and C, and I know some
of the pl/pgsql triggers raise exceptions.

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

http://archives.postgresql.org

#8

Philip Warner <pjw (AT) rhyme (DOT) com.au> writes:

Quote:

mail=# set enable_indexscan=off;
mail=# SELECT xmin, xmax, cmin, cmax,ctid FROM xxx where id = 24613;
xmin | xmax | cmin | cmax | ctid
----------+----------+------+----------+---------
32902771 | 0 | 20 | 32902872 | (0,7)
32902771 | 0 | 20 | 32902872 | (2,27)
32902771 | 0 | 20 | 32902872 | (58,27)
32902771 | 0 | 20 | 32902872 | (60,28)
32902771 | 0 | 20 | 32902872 | (69,3)
32902771 | 33048159 | 20 | 20 | (72,27)
(6 rows)

The "cmax" values in the first 5 rows are evidently really xvac values,
ie, these have all been moved by VACUUM FULL. (I assume you run VACUUM
FULL regularly on this table?) The thing that is striking though is
that the xmin/cmin values are all the same, indicating that all six
tuples were inserted by the same command. That seems pretty odd. Can
you show us the procedure by which rows are inserted in this table?

Also, the last tuple has either been deleted or locked-for-update by
transaction 33048159; if it were an attempted deletion we'd have to
conclude that 33048159 failed to commit. Do you use SELECT FOR UPDATE
on this table?

BTW, which of these rows is selected by an indexscan-enabled query,
ie, set enable_indexscan=on then repeat same query?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

#9

Tom Lane wrote:

Quote:

Philip Warner <pjw (AT) rhyme (DOT) com.au> writes:

mail=# set enable_indexscan=off;
mail=# SELECT xmin, xmax, cmin, cmax,ctid FROM xxx where id = 24613;
xmin | xmax | cmin | cmax | ctid
----------+----------+------+----------+---------
32902771 | 0 | 20 | 32902872 | (0,7)
32902771 | 0 | 20 | 32902872 | (2,27)
32902771 | 0 | 20 | 32902872 | (58,27)
32902771 | 0 | 20 | 32902872 | (60,28)
32902771 | 0 | 20 | 32902872 | (69,3)
32902771 | 33048159 | 20 | 20 | (72,27)
(6 rows)

The "cmax" values in the first 5 rows are evidently really xvac values,
ie, these have all been moved by VACUUM FULL. (I assume you run VACUUM
FULL regularly on this table?)
Yes, every minute. Table has about 1500 rows and grows *very* fast due

to updates.

Quote:

The thing that is striking though is
that the xmin/cmin values are all the same, indicating that all six
tuples were inserted by the same command. That seems pretty odd. Can
you show us the procedure by which rows are inserted in this table?

The original insertion is probably not relevant (it happened months

ago); there are many places that update the table. And for the specific
row in question, it was probably inserted directly by psql. Other rows
exhibit this problem (less often), were usually inserted by a long pgsql
procedure.

Updates happen regularly from many sources, but the procedure that does
the most updates is a trigger. Do you want to see that?

Quote:

Also, the last tuple has either been deleted or locked-for-update by
transaction 33048159; if it were an attempted deletion we'd have to
conclude that 33048159 failed to commit. Do you use SELECT FOR UPDATE
on this table?

No. But when a new row is added, I do lock the table in exclusive mode:

Lock Table xxx In Exclusive Mode;

The specific row in these examples will never be deleted.

Quote:

BTW, which of these rows is selected by an indexscan-enabled query,
ie, set enable_indexscan=on then repeat same query?

#10

Philip Warner <pjw (AT) rhyme (DOT) com.au> writes:

Quote:

Tom Lane wrote:
The thing that is striking though is
that the xmin/cmin values are all the same, indicating that all six
tuples were inserted by the same command. That seems pretty odd. Can
you show us the procedure by which rows are inserted in this table?

The original insertion is probably not relevant (it happened months
ago); there are many places that update the table.

OK, what I should say is that all these tuples were updated by the same
command.

Quote:

Updates happen regularly from many sources, but the procedure that does
the most updates is a trigger. Do you want to see that?

Please.

Also, if you care to run pg_filedump -i -F over the table, it'd be
interesting to see the complete header info for each of these tuples.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly