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