Re: Possible savepoint bug

Rod Taylor schrieb:
> On Wed, 2005-11-09 at 14:20 -0500, Tom Lane wrote:
>> Rod Taylor <pg(at)rbt(dot)ca> writes:
>>> As you can see, we have duplicates within the table (heap) of a primary
>>> key value. The index itself only references one of these tuples.
>> Can you put together a test case to reproduce this? It doesn't have to
>> fail every time, as long as it fails once in awhile ...
>
> Seems not. I've done millions of iterations of the same type of
> functionality that happens with these structures and haven't produced a
> single case. These are fairly low usage structures, so I think I've done
> about 3 months worth of work, which in production had 20 bad tuples. I
> tried playing with various delays, vacuum schedules, and number of
> parallel processes.

I am seeing a similar unique index bug here...

This is PostgreSQL 8.1.1 on RHEL 3, Intel Xeon (i686).

We don't use SAVEPOINTs and we don't use autovacuum. It's quite unlikely
that the problem is directly related to VACUUM since that is only run
via cron during night hours.

The symptoms are duplicate entries in a unique index.

billing=> \d properties
Table "billing.properties"
Column | Type | Modifiers
----------+-------------------+-----------
language | character(2) | not null
key_name | character varying | not null
value | character varying | not null
Indexes:
"pk_properties" PRIMARY KEY, btree ("language", key_name)
Check constraints:
"tc_properties_key_name" CHECK (key_name::text ~
'^[a-zA-Z][a-zA-Z0-9_.]+$'::text)
"tc_properties_language" CHECK ("language" = 'de'::bpchar OR
"language" = 'en'::bpchar)

billing=> reindex table properties;
ERROR: could not create unique index
DETAIL: Table contains duplicated values.

billing=> select ctid,xmin,xmax,cmin,cmax,language,key_name from
properties where key_name = 'enum.server_task_log.status.keys';
ctid | xmin | xmax | cmin | cmax | language | key_name
---------+--------+------+------+------+----------+----------------------------------
(31,64) | 505433 | 0 | 5 | 0 | de |
enum.server_task_log.status.keys
(31,57) | 505261 | 0 | 7 | 0 | de |
enum.server_task_log.status.keys
(31,56) | 505261 | 0 | 5 | 0 | en |
enum.server_task_log.status.keys
(3 rows)

The state is the effect of only UPDATEs of the rows after a SELECT ...
FOR UPDATE in the same transaction. It happend twice right now but I
deleted the other rows... the table should still contain the data. I
have disabled scheduled vacuums for now.

I could send the index and table files off-list. This is the only
effected table right now. It is not updated frequently but is rather
static. I upgraded to 8.1.1 around Dec 21, there should have been near
zero updates since then until today.

Perhaps it's a problem with multi-column unique indexes?

Best Regards,
Michael Paesold