Postgres broken inheritance?

Question

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;

Can you elaborate on `After that test in child becomes inherited from parent.` - is there documentation stating that it becomes inherited? I can't find any, just that they are "merged". Based solely on observation, it appears that a column explicitly created on a child table will *not* be considered inherited even if the parent table subsequently gets a column of that same name. — 404, Jun 19 '19 at 11:47
Good question. The code from this answer shows parent columns for the particular column https://stackoverflow.com/a/9178440/9521312. And when you'll try to change ```test``` column in child you'll get error about inherited column. So I made a conclusion that child column becomes inherited from parent, maybe it's wrong. — paveldroo, Jun 19 '19 at 11:53
This is a very interesting question. The linked query is based on `pg_inherits` which just has table ids. It gets all columns from that. But there must be more to it. If you create a `parent` table, and a `child` table which inherits `parent`, add columns *only* to `parent`, then drop from `parent`, they are dropped in the child. But if you first do this: `ALTER TABLE child NO INHERIT parent` then `ALTER TABLE child INHERIT parent`, so remove and re-add inheritance, then drop columns in `parent`, none of them are dropped in `child`. So it appears inheritance doesn't work for "merged" columns. — 404, Jun 19 '19 at 12:26
Yes, I hoped to fix these "broken" fields in legacy DB by `NO INHERIT` and `INHERIT` sequence. But with no luck =\ — paveldroo, Jun 19 '19 at 12:31

Laurenz Albe · Accepted Answer · 2019-06-19T13:56:38.103

6

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

edited Jun 19 '19 at 13:56

answered Jun 19 '19 at 12:48

Laurenz Albe

209,280
17
206
263

Great! Searching this attr through DB will show all "broken" fields. Thank you! But edit please your second example and create `test_inherit` fields in order: child first, parent next. Problem is still here. But flag `attislocal` solves my problem "how to find and fix these fields". Thanks again. – paveldroo Jun 19 '19 at 13:07
PS edited code in question for more clear instructions. – paveldroo Jun 19 '19 at 13:14
1

My second example shows how to create the tables in the "regular" fashion so that the columns are inherited. Of course, if you create `child` first, the columns won't be inherited. – Laurenz Albe Jun 19 '19 at 13:24
1

And interesting fact about inherit property of column. If you'll try to edit `test_inherit` column in child which was created before parent, you'll get an error from Postgre `sql> ALTER TABLE child ALTER COLUMN test_inherit TYPE INT [2019-06-19 16:32:21] [42P16] ERROR: cannot alter inherited column "test_inherit"`. So is it legit to call this column as not inherited? Quote from docs about `attislocal`: `Note that a column can be locally defined and inherited simultaneously`. There is no information in docs that cascade dropping inheritance fields doesn't affects locally created attrib. – paveldroo Jun 19 '19 at 13:36
1

Good point. I have edited the answer and improved the description of `attislocal`. – Laurenz Albe Jun 19 '19 at 13:57

Postgres broken inheritance?

1 Answers1