Re: Possible savepoint bug

Rod Taylor schrieb:
> On Wed, 2005-11-09 at 14:20 -0500, Tom Lane wrote:
>> Rod Taylor <pg@rbt.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
nullvalue    | 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