Re: odd deadlock on CREATE TABLE AS SELECT

"digital(dot)death(at)gmx(dot)it" <digital(dot)death(at)gmx(dot)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