Postgres broken inheritance?

user_9521312 0
Viewed 1532

After using DROP COLUMN column_name on parent table some columns in the child tables are not deleted.

How to reassign columns with this behaviour for correct cascade deleting in future?

How to reproduce: There are two tables: parent and child. Child is inherited from the parent and has same columns. Add new column test in child. Add new column test in parent. After that test in child becomes inherited from parent. Try to drop test from parent - expecting cascade deleting test from child. But it stays.

CREATE TABLE parent (a INT);
CREATE TABLE child () INHERITS (parent);
ALTER TABLE child ADD COLUMN test_inherit VARCHAR;
ALTER TABLE parent ADD COLUMN test_inherit VARCHAR;
ALTER TABLE parent DROP COLUMN test_inherit;
postgresql
1 Answers

What happens here is that the columns on table child are not marked as inherited columns:

CREATE TABLE parent (a INT);
CREATE TABLE child (a INT) INHERITS (parent);
NOTICE:  merging column "a" with inherited definition
ALTER TABLE child ADD COLUMN test_inherit VARCHAR;
ALTER TABLE parent ADD COLUMN test_inherit VARCHAR;
NOTICE:  merging definition of column "test_inherit" for child "child"

SELECT attname, attnum, attislocal
FROM pg_attribute
WHERE attrelid = 'child'::regclass AND attnum > 0;

   attname    | attnum | attislocal 
--------------+--------+------------
 a            |      1 | t
 test_inherit |      2 | t
(2 rows)

attislocal means that it is a column that was defined on child directly, not automatically created because of inheritance from another table.

If you define the child table without any columns, the columns will be inherited columns:

DROP TABLE parent, child;
CREATE TABLE parent (a INT, test_inherit VARCHAR);
CREATE TABLE child () INHERITS (parent);

SELECT attname, attnum, attislocal
FROM pg_attribute
WHERE attrelid = 'child'::regclass AND attnum > 0;

   attname    | attnum | attislocal 
--------------+--------+------------
 a            |      1 | f
 test_inherit |      2 | f
(2 rows)

Only inherited columns are dropped if the column in the inheritance parent are dropped:

ALTER TABLE parent DROP COLUMN test_inherit;

\d child
               Table "laurenz.child"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 a      | integer |           |          | 
Inherits: parent
user_64643080
Related