Fixing or diagnosing Canceled on identification as a pivot, during write - Mailing list pgsql-general

From Andrus
Subject Fixing or diagnosing Canceled on identification as a pivot, during write
Date
Msg-id 93A4AF1A22064772A040F83F846A52BB@dell2
Whole thread Raw
Responses Re: Fixing or diagnosing Canceled on identification as a pivot, during write
List pgsql-general
After switching to PostgreSql 9.1 serializable transaction level for all transactions during posting single document errors
 
40001:ERROR: could not serialize access due to read/write dependencies among transactions
Reason code: Canceled on identification as a pivot, during write.;
 
started to appear in log file.
 
Code which causes them is below.
 
Code involves only single document (in this example id 95162) . Is document is probably not accesed by others.
How to fix or diagnose this error ?
 
tasutud1 is temporary table created in transaction earlier:
 
CREATE TEMP TABLE tasutud1 (dokumnr INTEGER, tasutud NUMERIC(1)) ON COMMIT DROP
 
Other tables are permanent tables updated and accessed by 7 users concurrently.
 
 
Code where exception occurs is :
 
CREATE TEMP TABLE ids(dokumnr INT) ON COMMIT DROP;
INSERT INTO ids VALUES(95162);
analyze ids;UPDATE DOK set
      kinnitatud = TRUE,
      doksumma=CASE WHEN TRUE THEN COALESCE(doksumma.doksumma,0) ELSE 0 END,
      tasumata =CASE WHEN TRUE AND dok.krdokumnr IS NULL and
      dok.doktyyp IN ('G','O') THEN
           doksumma.doksumma-COALESCE(doksumma.tasutud,0) ELSE 0 END
     FROM
(SELECT
    ids.dokumnr,
    SUM( CASE WHEN rid.toode is NULL OR LENGTH(RTRIM(rid.toode))>2 OR toode.grupp<>'S' or
               (STRPOS(toode.klass,'T')!=0 AND STRPOS(toode.klass,'E')=0)
      THEN
        ROUND(COALESCE(rid.hind,0)*CASE WHEN COALESCE(rid.kogus,0)=0 THEN 1 ELSE rid.kogus END*CASE WHEN COALESCE(rid.kogpak,0)=0 THEN 1 ELSE rid.kogpak END,2) ELSE 0 END ) AS doksumma,
    max(tasutud1.tasutud) as tasutud
  FROM ids
  JOIN dok USING(dokumnr)
  JOIN rid USING(dokumnr)
  LEFT JOIN toode USING(toode)
  LEFT JOIN tasutud1 ON tasutud1.dokumnr=ids.dokumnr
WHERE not rid.fifoexpens and not rid.calculrow
  and (not dok.inventuur or rid.kogus<>0 )
GROUP BY 1
) doksumma
left join bilkaib on bilkaib.dokumnr=doksumma.dokumnr and bilkaib.alusdok='LO'
WHERE dok.dokumnr=doksumma.dokumnr
 
Should this code split into multiple commands to find which part causes exception or other idea ?
 
Andrus.

pgsql-general by date:

Previous
From: Feridun türk
Date:
Subject:
Next
From: Edson Richter
Date:
Subject: Re: Compressed binary field