Re: Transaction locks on first insert into partitioned table partition

On Fri, 7 Jun 2019 at 03:31, Martin Lund Askøe
<martinlundaskoe(at)gmail(dot)com> wrote:
>
> I'm experiencing something I cannot explain with regards to partitioned tables.
> I create a partitioned table and create a partition on it in one transaction.
> I then insert a record into the partition, somehow taking a lock on the master table.
> Then I rollback. I then insert a record again, but this time I do not take a lock on the master table.
>
> Why is this happening?

Attaching the partition to the partitioned table causes a relcache
invalidation (basically an internal cache that each session maintains
to quickly access relation metadata). When you open a relation for the
first time after its cache entry was invalidated, the data must be
reloaded. This requires locking the relation to ensure nobody drops it
out from under us. For this particular case, we must look up the
parent partitioned table's details in order to determine if the tuple
you're inserting is suitable for that partition. The code in question
is in generate_partition_qual(). Since the partition bound is then
stored in the partition's relcache entry, and not the partitioned
table then we've no need to open the parent again on subsequent
inserts that go directly to the partition. If you attached another
partition or did something else like add a column, then this would
invalidate the entry again and you'd see the lock for the duration of
the transaction.

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services