Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux