On Wed, Aug 29, 2012 at 12:26 AM, Moshe Jacobson <moshe@xxxxxxxxxxxx> wrote: > 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 It means that when you DEFINE columns in the inherited table they will be independent from the parent table. So anything you do with the columns in the parent table will not affect such columns in the inherited one. > means, but the descendant tables were created using "like tb_audit_event" to > inherit the columns. When LIKE table_name is specified in the definition of a table it means that all the columns will be DEFINED in the new table. You should not specify LIKE table_name in the definition of the inherited tables to make the columns to be INHERITED instead of DEFINED. Here you will find a very good explanation of the inheritance http://www.postgresql.org/docs/9.1/interactive/ddl-inherit.html > 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 > -- Sergey Konoplev a database architect, software developer at PostgreSQL-Consulting.com http://www.postgresql-consulting.com Jabber: gray.ru@xxxxxxxxx Skype: gray-hemp Phone: +79160686204 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
