Skip site navigation (1) Skip section navigation (2)

Re: Possible savepoint bug

From: Michael Paesold <mpaesold(at)gmx(dot)at>
To: Rod Taylor <pg(at)rbt(dot)ca>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, PostgreSQL Development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Possible savepoint bug
Date: 2005-12-27 15:42:03
Message-ID: 43B160CB.5030804@gmx.at (view raw or flat)
Thread:
Lists: pgsql-hackers
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

In response to

Responses

pgsql-hackers by date

Next:From: Jim C. NasbyDate: 2005-12-27 17:07:29
Subject: Re: [Bizgres-general] WAL bypass for INSERT, UPDATE and
Previous:From: Martijn van OosterhoutDate: 2005-12-27 13:29:46
Subject: Proposed COLLATE implementation

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group