Re: Delay locking partitions during query execution

On Tue, Mar 5, 2019 at 8:04 PM David Rowley
<david(dot)rowley(at)2ndquadrant(dot)com> wrote:
> Actually, I'm not sure it could work at all. It does not seem very
> safe to lookup a partition's parent without actually holding a lock on
> the partition and we can't lock the partition and then lock each
> parent in turn as that's the exact opposite locking order that we do
> when querying a partitioned table, so could result in deadlocking.
>
> Many moons ago in the 0002 patch in [1] I proposed to change the way
> we store partition hierarchies which involved ditching bool
> pg_class.relispartition in favour of Oid pg_class.relpartitionparent.
> That would make parent lookups pretty fast, but... given the above it
> seems like we couldn't do this at all.

One thing that is both kinda nice and kinda strange about our
heavyweight lock manager is that it has no idea what it is locking.
If you say "please lock OID 12345" for me, it does, but it doesn't
know anything about the relationship between that OID and any other
thing you might want to lock. Compare that to what Gray and Reuter
describe in "Transaction Processing: Concepts and Techniques", Section
7.8, Granular Locking. There, there is an idea that the set of
possible locks forms a tree, and that locking a node of the tree is
tantamount to locking all of its descendents. Such a concept would be
useful here: you could take e.g. AccessShareLock on the root of the
partitioning hierarchy and that would in effect give you that lock
mode on every partition, but without needing to make a separate entry
for each partition lock.

Sadly, implementing such a thing for PostgreSQL seems extremely
challenging. We'd somehow have to build up in shared memory an idea
of what the locking hierarchy was, and then update it as DDL happens,
and drop entries that aren't interesting any more so we don't run out
of shared memory. Performance and concurrency would be really hard
problems, and assumptions about the current locking model are baked
into code in MANY parts of the system, so finding everything that
needed to be (or could be) changed would probably be extremely
challenging. But if you could make it all work we might well end up
in a better state than we are today. However, I'm leaving this
problem for a time when I have six months or a year to do nothing
else, because I'm pretty sure that's what it would take.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company