Re: index corruption on composite primary key indexes

Just to follow up -- I did find the error in the pgsql logs after all,
although it does not contain any additional information. i.e.: PSTERROR:
duplicate key value violates unique constraint

-----Original Message-----
From: Ng, Stan
Sent: Tuesday, December 14, 2010 10:42 AM
To: 'Craig Ringer'
Cc: pgsql-bugs(at)postgresql(dot)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 retry logic that will
continue to attempt the delta application forever, with about a 5 to 10
minute wait. This will fail every time until a manual REINDEX command is
run on the composite primary key index in-between retry attempts. I've
also backed up the data to another table w/ the same indices and applied
the delta data manually without any problems while this error was
happening.

Oddly enough, even with full logging on, the pgsql logs don't contain
anything about the duplicate key error or suspect index. Perhaps another
important piece of information is that we're doing all this in Java
using the JDBC drivers, so we're seeing this as part of our stacktrace.
i.e.
org.postgresql.util.PSQLException: ERROR: duplicate key value violates
unique constraint "vehicleusediccgradiuscache_i00"
at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecu
torImpl.java:2102)
at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImp
l.java:1835)
at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:
257)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Stateme
nt.java:500)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdb
c2Statement.java:388)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Stateme
nt.java:381)
...

The index looks like this:
CREATE UNIQUE INDEX vehicleusediccgradiuscache_i00
ON vehicleusediccgradiuscache
USING btree
(datasetid, lower(postalcode::text), productgroupid,
vehicleclassgroupidic, vehicleusedidic, vehicleuseddisplaypriority,
vehicleusedsearchradius);

A single delta is calculated on the master server, which is pulled by
each client server. Each client puts the delta data into temp tables
(one each for delete, update, and insert, applied in that order), and
modifies the data via SQL somewhat like the following:

insert into
VehicleUsedICCGRadiusCache(DatasetId,PostalCode,ProductGroupId,VehicleCl
assGroupIdIC,VehicleUsedIdIC,VehicleUsedDisplayPriority,VehicleUsedSearc
hRadius,"VehicleUsedPrice.average","VehicleUsedPrice.min","VehicleUsedPr
ice.max","VehicleUsedIntId.distinct_count") select
t1.DatasetId,t1.PostalCode,t1.ProductGroupId,t1.VehicleClassGroupIdIC,t1
.VehicleUsedIdIC,t1.VehicleUsedDisplayPriority,t1.VehicleUsedSearchRadiu
s,t1."VehicleUsedPrice.average",t1."VehicleUsedPrice.min",t1."VehicleUse
dPrice.max",t1."VehicleUsedIntId.distinct_count" from t1
left outer join VehicleUsedICCGRadiusCache on
VehicleUsedICCGRadiusCache.VehicleUsedSearchRadius=t1.VehicleUsedSearchR
adius and VehicleUsedICCGRadiusCache.ProductGroupId=t1.ProductGroupId
and
VehicleUsedICCGRadiusCache.VehicleUsedDisplayPriority=t1.VehicleUsedDisp
layPriority and
VehicleUsedICCGRadiusCache.VehicleUsedIdIC=t1.VehicleUsedIdIC and
VehicleUsedICCGRadiusCache.DatasetId=t1.DatasetId and
VehicleUsedICCGRadiusCache.PostalCode=t1.PostalCode and
VehicleUsedICCGRadiusCache.VehicleClassGroupIdIC=t1.VehicleClassGroupIdI
C
where VehicleUsedICCGRadiusCache.VehicleUsedSearchRadius is null

-----Original Message-----
From: Craig Ringer [mailto:craig(at)postnewspapers(dot)com(dot)au]
Sent: Tuesday, December 14, 2010 4:47 AM
To: Ng, Stan
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: [BUGS] index corruption on composite primary key indexes

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; you haven't shown any evidence of

index corruption, and you're most likely hitting concurrency problems in

your code.

--
Craig Ringer