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.de...@gmx.it"
<digital.de...@gmx.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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
