Re: Dropping a column on parent table doesn't propagate to children? - Mailing list pgsql-general

On Wed, Aug 29, 2012 at 12:26 AM, Moshe Jacobson <moshe@neadwerx.com> 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@neadwerx.com | www.neadwerx.com
>

--
Sergey Konoplev

a database architect, software developer at PostgreSQL-Consulting.com
http://www.postgresql-consulting.com

Jabber: gray.ru@gmail.com Skype: gray-hemp Phone: +79160686204