My database implements partitioning using inheritance and constraint exclusion,
as described in the fine manual. Each partition holds data for a single day,
and there are well over 300 child partitions.
The exception mentioned below notwithstanding, the technique works exceedingly
well and is a real life saver when you have half a billion rows and climbing.
While a query was running on the partition for today a maintenance job was run
that summarizes data in a different partition using the technique described in
section 5.9.3. - "Managing Partitions" of the manual, with a twist.
Basically, it goes like this:
1. A select into query is run which summarizes the data from a partition into
a table outside the inheritance hierarchy, which is then indexed.
2. Then
a. a transaction is begun,
b. the original partition is dropped,
c. the new table renamed to the original partition's name,
d. the new table's unique index is renamed,
e. the appropriate check constraint is added,
f. select privilege is granted, and
g. the transaction is committed.
So far so good, the maintenance job works fine and is quite speedy. The
problem occurs when a select query is started prior to the sequence of events
above, those steps complete, and an error is thrown saying "PGRES_FATAL_ERROR -
ERROR: could not open relation with OID 64412". Of course, seconds later the
query runs fine, and there is no object with OID 64412 in the database.
It seems to me that removing and inserting partitions into the inheritance
hierarchy may not be a consistent and/or isolated action with respect to
queries that may be using the inheritance hierarchy. To be fair, this sequence
of events doesn't normally happen in daily processing, but the behavior is not
what I expected, nor is it mentioned in the manual. Am I missing or
misinterpreting something?
Thanks in advance for your advice!
Bob Lunney
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
