Re: Partitioned Data and Locking

Ed Behn <ed(dot)behn(at)rockwellcollins(dot)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