Обсуждение: Is this the expected behaviour for DDL-query execution?
Hi,
I am using PG 8.2.11 with psycopg2 2.0.6 (Python) in a multithreaded
application. Each thread keeps its own DB-connection.
The tables in the applications DB are partitioned with help of triggers
for redirection of INSERTS (only triggers for INSERTS, not for DELETE or
UPDATES, we rely on constraint exclusion for that part).
When performing an DROP TABLE query on a partition when one or more
other threads are at the same time performing updates on the very same
partitioned table (UPDATES which does not reference the partition key
column!, i.e. all available partitions has to be checked for the row to
be updated) which the partition are being dropped from I get the
following errors:
pg_log:
2009-05-10 00:00:41.451 CEST> ERROR: could not open relation with OID 24223
2009-05-10 00:00:41.451 CEST> STATEMENT:
UPDATE state_change SET (final_view_time, end_time) =
(33, 8745) WHERE id = 76306866
My applications log:
2009-05-10 00:00:41,394 SystemLog INFO: Dropped partition
table state_change_20090425
After the table has been dropped these errors disappears and everything
is nice and neat until 24 hours later when is time to drop the next
partition etc.
As I understand, it should be perfectly okay to perform DDL-queries at
the same time as performing DML-queries? Or do I have to make sure to
provide exclusive access to the DB while performing DDL-queries? Is
there anything in the PG documentation regarding this, I have looked but
has found nothing of interest so far.
I am planning to add a WHERE clause to the UPDATE statement ithh the
paritioning column, which I guess might solve the immediate problem, but
it would still be nice to be able to perform more general updates
without specifying constraints on the partitioning column.
Best Regards,
Thomas
Thomas Johansson <thomas.johansson@agama.tv> writes:
> When performing an DROP TABLE query on a partition when one or more
> other threads are at the same time performing updates on the very same
> partitioned table (UPDATES which does not reference the partition key
> column!, i.e. all available partitions has to be checked for the row to
> be updated) which the partition are being dropped from I get the
> following errors:
> pg_log:
> 2009-05-10 00:00:41.451 CEST> ERROR: could not open relation with OID 24223
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
> 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
Thomas Johansson <thomas.johansson@agama.tv> writes: > 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" What PG version are you using? In 8.3 it seems to work automatically, although in prior versions you could well have some problems with cached plans not getting invalidated. If it is 8.3 I'd like to see a detailed example. FWIW, we have implemented a trial solution to your original complaint for 8.4: http://archives.postgresql.org/pgsql-committers/2009-05/msg00208.php regards, tom lane
Tom Lane wrote:
> What PG version are you using?
8.2.11
> In 8.3 it seems to work automatically,
> although in prior versions you could well have some problems with cached
> plans not getting invalidated.
Any proposed workaround?
Would SELECTs be affected by this too?
(detaild log message from pg_log
2009-05-15 00:00:17.179 CEST> LOCATION: make_inh_translation_lists,
prepunion.c:992
2009-05-15 00:00:17.179 CEST> STATEMENT:
UPDATE state_change SET (final_view_time, end_time) =
(226, 10528) WHERE id = 91332641 AND time = 10523
2009-05-15 00:00:17.179 CEST> ERROR: XX000: could not find inherited
attribute "id" of relation "state_change_20090430")
> FWIW, we have implemented a trial solution to your original complaint
> for 8.4:
> http://archives.postgresql.org/pgsql-committers/2009-05/msg00208.php
>
Nice :-) Although for now I will need to get this working on 8.2.x.
Does this leave me with UPDATE triggers as the best viable (is it
viable?) solution?
Are there, as mentioned in previous post, some way to simulate the way
the DB behaved when using rules for partitioning?
Best Regards
Thomas
Thomas Johansson <thomas.johansson@agama.tv> writes:
> (detaild log message from pg_log
> 2009-05-15 00:00:17.179 CEST> LOCATION: make_inh_translation_lists,
> prepunion.c:992
> 2009-05-15 00:00:17.179 CEST> STATEMENT:
> UPDATE state_change SET (final_view_time, end_time) =
> (226, 10528) WHERE id = 91332641 AND time = 10523
> 2009-05-15 00:00:17.179 CEST> ERROR: XX000: could not find inherited
> attribute "id" of relation "state_change_20090430")
I'm still curious to see a complete test case for this behavior. AFAICS
the only way you could get that failure after an ALTER NO INHERIT would
be if the planner saw the changes to the child relation but did not see
the removal of the pg_inherits entry; which seems improbable.
regards, tom lane