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

[Moshe Jacobson <moshe@xxxxxxxxxxxx>]

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@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@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@moshe=>devmain:ises=# alter table tb_audit_event drop column audit_event;

ALTER TABLE

postgres@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@xxxxxxxxxxxx | www.neadwerx.com