Re: Exclusively locking parent tables while disinheriting children.

On 7 August 2015 at 12:34, Thom Brown <thom(at)linux(dot)com> wrote:

>
> On 30 July 2015 at 13:35, Rowan Collins <rowan(dot)collins(at)gmail(dot)com> wrote:
>
>> Hi,
>>
>> When working with partition sets, we're seeing occasional errors of
>> "could not find inherited attribute..." in Select queries. This is
>> apparently caused when an "ALTER TABLE ... NO INHERIT" runs concurrently
>> with another transaction selecting from the relevant child table.
>>
>> I found an old bug report filed against 8.3 back in 2008 [1] I can still
>> reproduce the test case in that report on 9.1.11 and 9.3.5, and it seems to
>> match what we're seeing in production.
>>
>> Tom Lane said at the time that a lock would cause more problems than it
>> solved [2], but when I add an explicit lock statement ("LOCK TABLE ONLY
>> p_tbl, c1_tbl IN ACCESS EXCLUSIVE MODE;") between "BEGIN" and "ALTER
>> TABLE", I get the behaviour I would expect - the SELECT blocks until the
>> transaction is committed, then returns rows from the remaining child table.
>>
>> So what I want to understand is what the risk of adding this lock are -
>> under what circumstances would I expect to see dead locks if I manually
>> added this lock to my partition maintenance functions?
>>
>
> I'm not clear on the problems such a change would present either, but I'm
> probably overlooking the relevant scenario.
>

Has anyone got insight as to what's wrong with exclusively locking a parent
table to disinherit a child table?

Thom