Re: Exclusively locking parent tables while disinheriting children.

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.

Thom