Обсуждение: odd deadlock on CREATE TABLE AS SELECT
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
"digital.death@gmx.it" <digital.death@gmx.it> writes:
> I hope it's not a bug, but I get a deadlock error in a
> function/transaction with these statements:
It's not a bug. The CREATE TABLE AS SELECT is acquiring a read lock on
table "adc", and then the ALTER TABLE RENAME tries to upgrade that lock
to exclusive. If you've got some other stuff going on with "adc" at
the same time, a deadlock isn't surprising in the least.
You could make the function safe by adding "LOCK TABLE adc" before
the select. However, if the idea is to not hold a strong lock on adc
while the CREATE is going on, this approach isn't going to work :-(
I kinda think you have more bugs than that, btw. If a deadlock is
happening it's probably because some other process also had read lock
on "adc" and is trying to upgrade it, which would strongly suggest
that the other process is trying to modify the contents of "adc",
which would be a Real Bad Thing because it implies that you're losing
data with this. Any changes committed into "adc" after the function
starts are not going to be reflected in the updated version of "adc",
which cannot be what you want.
regards, tom lane
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.death@gmx.it"
<digital.death@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
Oh no, I thought my message was rejected and I reposted it in a slightly modified form.. 2009/11/9 Tom Lane <tgl@sss.pgh.pa.us>: > It's not a bug. The CREATE TABLE AS SELECT is acquiring a read lock on > table "adc", and then the ALTER TABLE RENAME tries to upgrade that lock > to exclusive. If you've got some other stuff going on with "adc" at > the same time, a deadlock isn't surprising in the least. Ok, I have split my function in two pieces, one for CREATE TABLE AS SELECT and one for ALTER TABLE, but: If acquired lock is only a read lock, why can't I SELECT from the adc table while CREATEing TABLE AS SELECT? Nobody is trying to change nothing... > You could make the function safe by adding "LOCK TABLE adc" before > the select. However, if the idea is to not hold a strong lock on adc > while the CREATE is going on, this approach isn't going to work :-( > > I kinda think you have more bugs than that, btw. If a deadlock is > happening it's probably because some other process also had read lock > on "adc" and is trying to upgrade it, which would strongly suggest > that the other process is trying to modify the contents of "adc", > which would be a Real Bad Thing because it implies that you're losing > data with this. Any changes committed into "adc" after the function > starts are not going to be reflected in the updated version of "adc", > which cannot be what you want. Everybody is doing SELECT only on "adc" table, so why deadlocks if there are no write attempts on any row? That's the reason why I posted my message, it seems strange: SELECTs only do read... Thank you for your help
On Mon, Nov 9, 2009 at 9:48 AM, digitaldeath <digital.death@gmx.it> wrote: > Everybody is doing SELECT only on "adc" table, so why deadlocks if there > are no write attempts on any row? Well the deadlock error does include the information about what queries deadlocked. In newer versions it includes the full text of the query, but in older versions you would have to check the pg_stat* views or postgres logs to track them down. -- greg