odd deadlock on CREATE TABLE AS SELECT

Поиск
Список
Период
Сортировка
От digital.death@gmx.it
Тема odd deadlock on CREATE TABLE AS SELECT
Дата
Msg-id 20091108124013.47d01bcf@maglie.elkos.it
обсуждение исходный текст
Ответы Re: odd deadlock on CREATE TABLE AS SELECT  (Tom Lane <tgl@sss.pgh.pa.us>)
Re: odd deadlock on CREATE TABLE AS SELECT  (Greg Stark <stark@mit.edu>)
Список 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

В списке pgsql-bugs по дате отправления:

Предыдущее
От: Robert Haas
Дата:
Сообщение: Re: BUG #5171: Composite type with array does not translate in plpythonu
Следующее
От: "Viisard"
Дата:
Сообщение: BUG #5172: ecpg - cursor with regexp containing '.*/' fails to compile with gcc