Thread: BUG #4244: ALTER TABLE ... NO INHERIT problem

The following bug has been logged online:

Bug reference:      4244
Logged by:          Tatsuhito Kasahara
Email address:      kasahara.tatsuhito@oss.ntt.co.jp
PostgreSQL version: 8.3.3
Operating system:   RedHat Linux 5.2
Description:        ALTER TABLE ... NO INHERIT problem
Details:

Hi.

I performed "NO INHERIT" command to separate a table, then I got following
error.
This problem is reproducible on simple test.

:Session A
  CREATE TABLE p_tbl (id int primary key);
  CREATE TABLE c1_tbl (LIKE p_tbl INCLUDING INDEXES, CHECK (id between 1 and
100)) INHERITS(p_tbl);
  CREATE TABLE c2_tbl (LIKE p_tbl INCLUDING INDEXES, CHECK (id between 101
and 200)) INHERITS(p_tbl);
  INSERT INTO c1_tbl SELECT generate_series(1,100);
  INSERT INTO c2_tbl SELECT generate_series(101,200);
  BEGIN;
  ALTER TABLE c1_tbl NO INHERIT p_tbl;
:Session B
  SELECT * FROM p_tbl;
  (Blocking)
:Session A
  COMMIT;
:Session B (error occures)
  ERROR:  could not find inherited attribute "id" of relation "c1_tbl"
  LOCATION:  make_inh_translation_lists, prepunion.c:996


The comment in tablecmds.c says "AccessShareLock is probably enough".
But it actually needs more strong lock mode ?

ATExecDropInherit(Relation rel, RangeVar *parent)
    /*
     * AccessShareLock on the parent is probably enough, seeing that DROP
     * TABLE doesn't lock parent tables at all.  We need some lock since we'll
     * be inspecting the parent's schema.
     */
    parent_rel = heap_openrv(parent, AccessShareLock);

Best Regards.

"Tatsuhito Kasahara" <kasahara.tatsuhito@oss.ntt.co.jp> writes:
> The comment in tablecmds.c says "AccessShareLock is probably enough".
> But it actually needs more strong lock mode ?

> ATExecDropInherit(Relation rel, RangeVar *parent)
>     /*
>      * AccessShareLock on the parent is probably enough, seeing that DROP
>      * TABLE doesn't lock parent tables at all.  We need some lock since we'll
>      * be inspecting the parent's schema.
>      */
>     parent_rel = heap_openrv(parent, AccessShareLock);

Well, as the comment points out, things are just as bad if you do
"DROP TABLE c1_tbl" instead of ALTER NO INHERIT.

I'm not sure there is a lot we can do about this.  Taking a lock
on the parent table that is strong enough to block a SELECT would
just convert the problem into a deadlock (since a SELECT on the
parent will lock parent before child).  Hardly seems like an
improvement.

            regards, tom lane