Re: Transaction locks on first insert into partitioned table partition

Hi David.

Thank you for your reply.

Do you know if there is any way to force update the relcache entry during
my 'attach'-transaction?
After attaching a new partition and committing that transaction, I would
like to be able to start a data insert transaction (potentially long
running) on the partition without blocking other transactions from
attaching another partition, as I would by taking an AccessShareLock on the
master table.

So,
Attach partition A -> transaction 1
Insert into partition A -> transaction 2
Attach partition B -> transaction 3 (should not be blocked by transaction 2)

I can think of workarounds, but they aren't pretty.

Regards, Martin.

On Fri, Jun 7, 2019 at 3:11 AM David Rowley <david(dot)rowley(at)2ndquadrant(dot)com>
wrote:

>
> 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
>