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

odd deadlock on CREATE TABLE AS SELECT

From: "digital(dot)death(at)gmx(dot)it" <digital(dot)death(at)gmx(dot)it>
To: pgsql-bugs(at)postgresql(dot)org
Subject: odd deadlock on CREATE TABLE AS SELECT
Date: 2009-11-08 11:40:13
Message-ID: 20091108124013.47d01bcf@maglie.elkos.it (view raw or flat)
Thread:
Lists: pgsql-bugs
Hello,

I hope it's not a bug, but I get a deadlock error in a
function/transaction with these statements:

CREATE OR REPLACE FUNCTION cluster_adc_table () RETURNS INTEGER AS $$
  BEGIN
    DROP TABLE IF EXISTS adc_clustered;
    RAISE NOTICE 'start creating clustered table at %s',
        clock_timestamp(); 
    CREATE TABLE adc_clustered AS (
       SELECT * FROM adc ORDER BY somecolumn DESC NULLS LAST); 
    ALTER TABLE adc_clustered ADD PRIMARY KEY (id);
    CREATE INDEX adc_ft_idx ON adc_clustered USING gin(somecol);
    -- then I create other indexes on new table --
    RAISE NOTICE 'finished creating clustered table at %s',
        clock_timestamp();
    ANALYZE adc_clustered;
    ALTER TABLE adc RENAME TO adc_old;
    ALTER TABLE adc_clustered RENAME TO adc;
    RETURN 1;
    
    EXCEPTION
       WHEN DEADLOCK_DETECTED THEN
          RETURN 0;
  END;

I think "adc" table is locked in exclusive mode because I can't select
(it waits for a long long time) and in logs I can see this:

ERROR:  deadlock detected 
DETAIL:  Process 5087 waits for AccessShareLock on relation 63704 of
     database 16385; blocked by process 5095. 
  Process 5095 waits for AccessExclusiveLock on relation 63301 of
     database 16385; blocked by process 5087. 
  Process 5087: SELECT COUNT(adc.datepublished) AS c FROM ad WHERE
     ad.COLUMN1='t' AND adc.COLUMN2<=1 AND ad.FT1 @@
     'word1'::tsquery
  Process 5095: SELECT cluster_adc_table() AS cluster_result 
HINT:  See server log for query details.

Pg version: 8.4beta2, pulled out from CVS trunk on June, but I can't
figure which revision is it, I normally use SVN, whose command I launch
is `grep revision .svn/entries | awk -F\" '{print $2}' `.

Machine is a amd64 Opteron with Debian Linux.

Hope it's my fault and not really a bug. I guess I must give you more
infos, right?

Thank you in advance

D

Responses

pgsql-bugs by date

Next:From: Richard NeillDate: 2009-11-08 18:16:56
Subject: BUG #5174: [minor] directories symlinked into base/ are not recursively removed
Previous:From: ViisardDate: 2009-11-07 18:56:36
Subject: BUG #5172: ecpg - cursor with regexp containing '.*/' fails to compile with gcc

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