Skip site navigation (1) Skip section navigation (2)

Re: [BUG?] strange behavior in ALTER TABLE ... RENAME TO on inherited columns

From: KaiGai Kohei <kaigai(at)ak(dot)jp(dot)nec(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Robert Haas <robertmhaas(at)gmail(dot)com>, KaiGai Kohei <kaigai(at)kaigai(dot)gr(dot)jp>, "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>, Thom Brown <thombrown(at)gmail(dot)com>, Alvaro Herrera <alvherre(at)commandprompt(dot)com>
Subject: Re: [BUG?] strange behavior in ALTER TABLE ... RENAME TO on inherited columns
Date: 2010-01-04 09:55:16
Message-ID: 4B41BB04.2070609@ak.jp.nec.com (view raw or flat)
Thread:
Lists: pgsql-hackers
>> The method I suggested would allow the
>> necessary information to be extracted during the initial search for
>> child tables, which we have to do anyway.
> 
> find_all_inheritors() returns a clean list which does not contain
> duplicated OID of the inherited relation, so it seems to me we need
> to change the function prototype but it affects other parts, or to add
> a new function which also returns number of duplications, not only OIDs.
> 
> Or, we can call find_inheritance_children() in renameatt() as if
> find_all_inheritors() doing except for list_concat_unique_oid().

The attached patch modified the condition to prevent renaming.

It computes an expected inhcount for each child relations on the initial
call of the renameatt() for the parent relation.
The find_all_inheritors_with_inhcount() returns OID of the inherited
relations and the expected inhcoundt. If a child relation has diamond
inheritance tree, it has its expected inhcount larger than 1.

This patch raises an error, if pg_attribute.inhcount is larger than
the expected inhcount. It can be happen when the attribute to be
renamed is merged from any other unrelated relations in the child
relations.

See the example:

  postgres=# CREATE TABLE t1 (a int);
  CREATE TABLE
  postgres=# CREATE TABLE t2 (b int) inherits (t1);
  CREATE TABLE
  postgres=# CREATE TABLE t3 (c int) inherits (t1);
  CREATE TABLE
  postgres=# CREATE TABLE t4 (d int) inherits (t2, t3);
  NOTICE:  merging multiple inherited definitions of column "a"
  CREATE TABLE

  postgres=# ALTER TABLE t1 RENAME a TO x;
  ALTER TABLE
  postgres=# \d t4
        Table "public.t4"
   Column |  Type   | Modifiers
  --------+---------+-----------
   x      | integer |
   b      | integer |
   c      | integer |
   d      | integer |
  Inherits: t2,
            t3

We can rename a of t1, t2, t3 and t4 correctly, although t4.a has inherited from
multiple relations.

  postgres=# CREATE TABLE s1 (x int);
  CREATE TABLE
  postgres=# CREATE TABLE t5 (e int) inherits (t2, t3, s1);
  NOTICE:  merging multiple inherited definitions of column "x"
  NOTICE:  merging multiple inherited definitions of column "x"
  CREATE TABLE
  postgres=# ALTER TABLE t1 RENAME x TO y;
  ERROR:  cannot rename multiple inherited column "x"

But, the new t5 prevent to rename "x" to "y", because t5.x is also inherited from
the s1 and merged. So, its inhcount is 3 larger than expected inhcount (=2).

  postgres=# SELECT attname, attinhcount FROM pg_attribute where attname='x' and attrelid='t5'::regclass;
   attname | attinhcount
  ---------+-------------
   x       |           3
  (1 row)

Thanks,
-- 
OSS Platform Development Division, NEC
KaiGai Kohei <kaigai(at)ak(dot)jp(dot)nec(dot)com>

Attachment: pgsql-fix-inherit-rename.3.patch
Description: application/octect-stream (9.1 KB)

In response to

Responses

pgsql-hackers by date

Next:From: Leonardo FDate: 2010-01-04 10:17:08
Subject: I: TODO: Allow substring/replace() to get/set bit values
Previous:From: Scara MaccaiDate: 2010-01-04 09:30:04
Subject: TODO: Allow substring/replace() to get/set bit values

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group