Partitioned Data and Locking
I've found what to me is a surprising locking behavior when querying
partitioned data as described in section 5.10 of the User's Manual.
I have an empty parent table with a number of child tables containing data.
Each child has a CHECK condition on the relevant column. I am executing a
SELECT query against the parent table with a condition on the column in the
CHECK in the WHERE clause.
I have constraint_exclusion set to partition.
If I run EXPLAIN on the query, I get a result that shows that only the
child tables whose CHECKs are consistent with the WHERE clause are
searched. This is exactly what I expected.
However, when I run the query, AccessShareLocks are obtained by the
transaction for all child tables (and their indices).
Am I misunderstanding something? I seems that these locks shouldn't exist
if the query plan doesn't use most of the child tables.
If this is a bug, perhaps it could be fixed in a future release. I would be
beneficial to my application, as we most often are only writing to one
partition in any given day. If a query against older data is running, the
write transaction could still proceed.
-Ed
--
Ed Behn / Staff Engineer / Airline and Network Services
Information Management Services
2551 Riva Road, Annapolis, MD 21401 USA
Phone: 410-266-4426 / Cell: 240-696-7443
ed.behn@rockwellcollins.com
www.rockwellcollins.com
Ed Behn <ed.behn@rockwellcollins.com> writes:
If I run EXPLAIN on the query, I get a result that shows that only the
child tables whose CHECKs are consistent with the WHERE clause are
searched. This is exactly what I expected.
However, when I run the query, AccessShareLocks are obtained by the
transaction for all child tables (and their indices).
Am I misunderstanding something? I seems that these locks shouldn't exist
if the query plan doesn't use most of the child tables.
Nope, they must exist, because the planner has to examine those tables
to discover that their constraints allow skipping them at execution.
If this is a bug, perhaps it could be fixed in a future release.
This is not a bug. You might argue that we could release a child table's
lock once we've proven that we need not scan that table, but that's
fraught with theoretical and practical difficulties. As one example,
once we've released that lock, someone could change the child's
constraint, invalidating the proof. (Indeed, since AccessShareLock is
such a weak lock, it would more or less require DDL on the child table
for there to be any conflict.)
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general