Re: Transaction locks on first insert into partitioned table partition

On Sun, 9 Jun 2019 at 11:04, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> =?UTF-8?Q?Martin_Lund_Ask=C3=B8e?= <martinlundaskoe@gmail.com> writes:
> > It feels like a bug to me, that the relcache entry update leaves my
> > transaction with an access shared lock.
>
> It is not a bug.  The fact that the uncommitted insert takes a lock
> preventing any DDL changing the partition structure is actually an
> essential property, because otherwise such DDL could perhaps change
> which partition the inserted row should have gone into.  The DDL
> command would then be responsible for moving rows appropriately ...
> but it can't see an uncommitted row from another session, so it
> would fail to move it.  Result: corrupted data.

If that were the case, we'd already have bugs since the lock on the
parent is only taken when we must obtain the partition bound in
generate_partition_qual(), and that's only going to happen just after
a relcache entry has been invalidated or if it's not been built yet.

The only DDL that could affect what partition the row is meant to go
into is DROP TABLE or DETACH PARTITION on the partition that's being
INSERTed into. In that case, we'll take an AccessExclusiveLock on the
partition itself which would prevent the concurrent INSERT.

To me it sounds very much like Martin is trying to use partitioning in
a way that's not supported. He didn't mention it, by my guess is he's
trying to use a BEFORE INSERT trigger to create a partition that does
not exist. That's a very bad idea and he'll be much better off
creating the partitions before they're required using some job that
runs periodically.  I think this is likely the case since otherwise,
he'd not have a problem with starting a new transaction to perform the
dummy insert to populate the relcache.

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