Re: odd deadlock on CREATE TABLE AS SELECT

Oh no, I thought my message was rejected and I reposted it in a
slightly modified form..

2009/11/9 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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