Re: odd deadlock on CREATE TABLE AS SELECT

It's your "fault" though I don't know how you would know that without
knowing a lot about how postgres handles internal locking.

The problem is that the creat table as select * from adc takes a share
lock on adc then later the rename table upgrades the lock. This is
always a deadlock risk. In this case if you only run this in one
process it might have been safe but it's hard to be certain when there
are other locks involved.

You have two choices. Either start th function with an explicit LOCK
TABLE on adc in access exclusive mode; or break the rename out onto a
second function and commit the transaction after the first function.

--
Greg

On 2009-11-08, at 11:40 AM, "digital(dot)death(at)gmx(dot)it"
<digital(dot)death(at)gmx(dot)it> wrote:

> 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
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs