Re: [BUGS] index corruption on composite primary key indexes

2010-12-16 Thread Tom Lane
Heikki Linnakangas writes: > On 16.12.2010 16:50, Alvaro Herrera wrote: >> All tuples need to have index pointers, even the dead ones. > To be precise, tuples that are truly dead, ie. not visible to anyone > anymore, don't need index pointers. They usually have index pointers > anyway, because

Re: [BUGS] index corruption on composite primary key indexes

2010-12-16 Thread Heikki Linnakangas
On 16.12.2010 16:50, Alvaro Herrera wrote: Excerpts from Ng, Stan's message of mié dic 15 21:07:57 -0300 2010: Now I just need to get my head wrapped around the corner cases of MVCC + indexes. From a layman's perspective, it seems the index should only operate on the latest version. Perhaps the

Re: [BUGS] index corruption on composite primary key indexes

2010-12-16 Thread Alvaro Herrera
Excerpts from Ng, Stan's message of mié dic 15 21:07:57 -0300 2010: > Now I just need to get my head wrapped around the corner cases of MVCC > + indexes. From a layman's perspective, it seems the index should only > operate on the latest version. Perhaps there's a pgsql configuration > option or h

Re: [BUGS] index corruption on composite primary key indexes

2010-12-15 Thread Ng, Stan
From: Mikael Krantz [mailto:m...@zigamorph.se] Sent: Tuesday, December 14, 2010 4:36 PM To: Ng, Stan Cc: Craig Ringer; pgsql-bugs@postgresql.org Subject: Re: [BUGS] index corruption on composite primary key indexes I'm afraid that I'm a bit out of my depth when it comes to index corruption. I

Re: [BUGS] index corruption on composite primary key indexes

2010-12-14 Thread Mikael Krantz
VehicleUsedICCGRadiusCache.DatasetId=t1.DatasetId and > VehicleUsedICCGRadiusCache.PostalCode=t1.PostalCode and > VehicleUsedICCGRadiusCache.VehicleClassGroupIdIC=t1.VehicleClassGroupIdI > C > where VehicleUsedICCGRadiusCache.VehicleUsedSearchRadius is null > > > > -Original Message

Re: [BUGS] index corruption on composite primary key indexes

2010-12-14 Thread Ng, Stan
inger' Cc: pgsql-bugs@postgresql.org Subject: RE: [BUGS] index corruption on composite primary key indexes Ah, I forgot to mention why I suspected corruption. The delta application is a single threaded, sequential process. Each delta is done within a single transaction. If it fails, there is r

Re: [BUGS] index corruption on composite primary key indexes

2010-12-14 Thread Ng, Stan
icleClassGroupIdIC=t1.VehicleClassGroupIdI C where VehicleUsedICCGRadiusCache.VehicleUsedSearchRadius is null -Original Message- From: Craig Ringer [mailto:cr...@postnewspapers.com.au] Sent: Tuesday, December 14, 2010 4:47 AM To: Ng, Stan Cc: pgsql-bugs@postgresql.org Subject: Re: [BUGS] index cor

Re: [BUGS] index corruption on composite primary key indexes

2010-12-14 Thread Craig Ringer
On 12/14/2010 10:18 AM, Ng, Stan wrote: Some info on the platform I’m using: Please also show your schema and query / queries. If you think it's corruption, provide your postgresql logs too, including any mention of the names of the suspect indexes. Personally I agree with Mikael Krantz; y

Re: [BUGS] index corruption on composite primary key indexes

2010-12-14 Thread Mikael Krantz
This could quite possibly be a correct behaviour. "duplicate key value violates unique constraint" usually happens when you try to insert a row to a table already containing a row with the same value(s) for the key column(s). If you have two connections both trying to insert a new record with the s