Skip site navigation (1) Skip section navigation (2)

Re: Is this the expected behaviour for DDL-query execution?

From: Thomas Johansson <thomas(dot)johansson(at)agama(dot)tv>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: Is this the expected behaviour for DDL-query execution?
Date: 2009-05-14 07:58:35
Message-ID: 4A0BCF2B.5010207@agama.tv (view raw or flat)
Thread:
Lists: pgsql-bugs
> Yeah, this is possible because DROP TABLE does not attempt to acquire
> exclusive lock on the victim table's parent(s).  So if a concurrent
> query operating on the parent had already obtained the victim table's
> OID from pg_inherit, it would get this failure.
>
> It's somewhat annoying but I'm not sure the cure wouldn't be worse than
> the disease.  In particular, a straight attempt to lock the parent would
> result in deadlock failures in exactly the cases where you get this
> error now.
>
> 			regards, tom lane
>   

Thank you very much for the informative answer :-)

 So what would be the best/easiest way to circumvent this behaviour 
while still allowing concurrent queries? I tried to implement a solution 
which I hoped would fix this by first doing NO INHERIT on the partition 
which were to be dropped and then later (an hour later, to be absolutely 
sure that no query were still using the table) dropping the table. 
However this resulted in the following type of problem instead, which I 
guess is just another symptom of the locking strategy described by you 
above?

ProgrammingError: could not find inherited attribute "id" of relation 
"state_change_20090429"

I initially stumbled upon this problem when changing from using rules to 
triggers for table partitioning (for improved scalability). When we were 
using rules this kind of problem did not exist which I suspect is a side 
affect caused by the base table "owning" the partitioning rules?

So I believe my best remaining option is to add UPDATE triggers to the 
base tables, that would help right? Or can the "rules side affect" be 
simulated some way?

I guess that I am not the only one who has stumbled upon this problem? 
Probably the PG manual should mention something about this together with 
a proposed workaround?

Best Regards,
Thomas

In response to

Responses

pgsql-bugs by date

Next:From: Devrim GÜNDÜZDate: 2009-05-14 15:09:51
Subject: Re: POSTGRESQL 8.2.3
Previous:From: Jaime CasanovaDate: 2009-05-14 05:14:35
Subject: Re: POSTGRESQL 8.2.3

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group