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.
