Re: Transaction locks on first insert into partitioned table partition

Hi David,

It should be obvious that I don't want any sessions to be able to
inadvertently be taking locks on the master table.
I could construct a situation where this would produce a deadlock, but this
will be based on poor code quality.

Lets say that I have transaction A inserting into a newly attached
partition partition_table_2019_01_01 inadvertently taking a access shared
lock on the master_table (because of the relcache update).
At some point in transaction A I encounter data which should be inserted
into a non-existing partition_table_2019_01_02 (infer the meaning of the
dates in the table), so in that same transaction I decide to create and
attach that table.
Unfortunately another transaction B (completely unrelated to transaction A,
even in a different session) has had the same idea, and has already been
granted a access exclusive lock on that table prior to creating the table,
but is now missing a lock on the master_table in order to complete the
attach.
Now we have deadlock and someone will need to perform a rollback.

Of course this example is a bad one, because I should not decide to create
and attach a new partition table in the middle of transaction A, but it
does show how my deadlock could happen - "almost" out of my control.

I've tried doing the dummy insert as part of the transaction that creates
and attaches the partition table, but it does not update the relcache entry
or at least it had no affect.
I still end up taking the shared access lock on the master_table on next
insert or deletion. What does work is doing the dummy insert in another
transaction, and I don't even have to commit it, I can just do a rollback.

It feels like a bug to me, that the relcache entry update leaves my
transaction with an access shared lock.
Are there no way to enforce the cache update during the attach, when I
already have the lock on the master_table?

Regards, Martin.

On Sat, Jun 8, 2019 at 8:49 AM David Rowley <david(dot)rowley(at)2ndquadrant(dot)com>
wrote:

> On Fri, 7 Jun 2019 at 19:06, Martin Lund Askøe
> <martinlundaskoe(at)gmail(dot)com> wrote:
> > Do you know if there is any way to force update the relcache entry
> during my 'attach'-transaction?
>
> You could just do a dummy insert after the ATTACH PARTITION
> transaction ends and before the transaction where you start inserting
> the data. Something like:
>
> insert into partition_table_2019_01_01 (id, some_info, e_timestamp)
> select 1,'',now() where 1=0;
>
> That'll build and cache the partition bound in the partitions relcache
> entry.
>
> I'm curious to know why you have concerns about the AccessShareLock on
> the partitioned table being held during the insert. Can you explain
> that?
>
> --
> David Rowley http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Training & Services
>