Dropping a column on parent table doesn't propagate to children?

From: Moshe Jacobson <moshe(at)neadwerx(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Dropping a column on parent table doesn't propagate to children?
Date: 2012-08-28 20:26:47
Message-ID: CAJ4CxLmE9gieAqO53-t8nKffXinMgC_BBgwfzkHA-cK7F=Y2wg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have a skeleton table tb_audit_event that has a bunch of descendant
tables.
I want to remove the pk column from this table and all of the descendant
tables, however issuing an ALTER TABLE tb_audit_event DROP COLUMN
audit_event drops the column only from the parent table.

The docs said that the descendant tables' columns would be removed unless
they had had their own definition for that column. I'm not sure what that
means, but the descendant tables were created using "like tb_audit_event"
to inherit the columns.
Any idea why the descendant columns are not dropping along with the parent?

Here are the table descriptions followed by my table alter and check:

postgres(at)zeus=>hera:ises=# \d tb_audit_event

Table "public.tb_audit_event"
Column | Type |
Modifiers
----------------+-----------------------------+---------------------------------------------------------
audit_event | integer | not null default
nextval('sq_pk_audit_event'::regclass)
audit_field | integer | not null
row_pk_val | integer | not null
recorded | timestamp without time zone | not null default now()
entity | integer | not null
row_op | character(1) | not null
op_sequence | integer | not null
transaction_id | bigint | not null
process_id | integer | not null
old_value | text |
new_value | text |
Indexes:
"tb_audit_event_pkey" PRIMARY KEY, btree (audit_event)
"tb_audit_event_recorded_key" btree (recorded)
"tb_audit_event_transaction_id_key" btree (transaction_id)
Check constraints:
"tb_audit_event_row_op_check" CHECK (row_op = ANY (ARRAY['I'::bpchar,
'U'::bpchar, 'D'::bpchar]))
Foreign-key constraints:
"tb_audit_event_audit_field_fkey" FOREIGN KEY (audit_field) REFERENCES
tb_audit_field(audit_field)
"tb_audit_event_entity_fkey" FOREIGN KEY (entity) REFERENCES
tb_entity(entity)
Triggers:
tr_redirect_audit_events BEFORE INSERT ON tb_audit_event FOR EACH ROW
EXECUTE PROCEDURE fn_redirect_audit_events()
Number of child tables: 17 (Use \d+ to list them.)

postgres(at)zeus=>hera:ises=# \d audit_log.tb_audit_event_20120826_0208
Table "audit_log.tb_audit_event_20120826_0208"
Column | Type |
Modifiers
----------------+-----------------------------+---------------------------------------------------------
audit_event | integer | not null default
nextval('sq_pk_audit_event'::regclass)
audit_field | integer | not null
row_pk_val | integer | not null
recorded | timestamp without time zone | not null default now()
entity | integer | not null
row_op | character(1) | not null
op_sequence | integer | not null
transaction_id | bigint | not null
process_id | integer | not null
old_value | text |
new_value | text |
Indexes:
"tb_audit_event_20120826_0208_pkey" PRIMARY KEY, btree (audit_event)
"tb_audit_event_20120826_0208_recorded_idx" btree (recorded)
"tb_audit_event_20120826_0208_transaction_id_idx" btree (transaction_id)
Check constraints:
"tb_audit_event_20120826_0208_recorded_check" CHECK (recorded >=
'2012-08-19 14:57:49.315938'::timestamp without time zone AND recorded <=
'2012-08-26 14:13:04.133753'::timestamp without time zone)
"tb_audit_event_row_op_check" CHECK (row_op = ANY (ARRAY['I'::bpchar,
'U'::bpchar, 'D'::bpchar]))
Inherits: tb_audit_event

postgres(at)moshe=>devmain:ises=# *alter table tb_audit_event drop column
audit_event;*
ALTER TABLE
postgres(at)moshe=>devmain:ises=# *\d audit_log.tb_audit_event_20120826_0208*
Table "audit_log.tb_audit_event_20120826_0208"
Column | Type |
Modifiers
----------------+-----------------------------+---------------------------------------------------------
audit_event | integer | not null default
nextval('sq_pk_audit_event'::regclass)
audit_field | integer | not null
row_pk_val | integer | not null
recorded | timestamp without time zone | not null default now()
entity | integer | not null
row_op | character(1) | not null
op_sequence | integer | not null
transaction_id | bigint | not null
process_id | integer | not null
old_value | text |
new_value | text |
Indexes:
"tb_audit_event_20120826_0208_pkey" PRIMARY KEY, btree (audit_event)
"tb_audit_event_20120826_0208_recorded_idx" btree (recorded)
"tb_audit_event_20120826_0208_transaction_id_idx" btree (transaction_id)
Check constraints:
"tb_audit_event_20120826_0208_recorded_check" CHECK (recorded >=
'2012-08-26 14:26:55.761958'::timestamp without time zone AND rec

orded <= '2012-08-26
14:45:35.989979'::timestamp without time zone)
"tb_audit_event_row_op_check" CHECK (row_op = ANY (ARRAY['I'::bpchar,
'U'::bpchar, 'D'::bpchar]))
Inherits: tb_audit_event

Also, another question about the docs. The syntax for the ALTER TABLE
command starts as follows:

ALTER TABLE [ ONLY ] name [ * ]

What is the asterisk? It is not explained anywhere on that page.

Thanks,
Moshe

--
Moshe Jacobson
Nead Werx, Inc. | Senior Systems Engineer
2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339
moshe(at)neadwerx(dot)com | www.neadwerx.com

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Gavin Flower 2012-08-28 20:38:44 Re: "Need some information about postgresql products and community"
Previous Message Dmitriy Igrishin 2012-08-28 20:25:36 Re: Views versus user-defined functions: formatting, comments, performance, etc.