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

Thanks Sergey.

I create these inherited tables from the main table, and then move them
into a different schema that is backed up separately from the main schema,
since they are for audit logging, which gets very big.

Questions:
1. If I want the inherited table's columns indexed the same way as the
parent, must I create new indexes on the inherited table?
2. If I move the inherited table to a new schema, will its indexes also be
moved into the new schema?
3. Any difference in behavior regarding check constraints, schemas,
indexes, etc that I should be aware of between inherited tables created
with pure inheritance as opposed to "like <parent_table>"?

Thank you.

On Wed, Aug 29, 2012 at 4:35 AM, Sergey Konoplev <
sergey(dot)konoplev(at)postgresql-consulting(dot)com> wrote:

> On Wed, Aug 29, 2012 at 12:26 AM, Moshe Jacobson <moshe(at)neadwerx(dot)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(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
> >
>
> --
> Sergey Konoplev
>
> a database architect, software developer at PostgreSQL-Consulting.com
> http://www.postgresql-consulting.com
>
> Jabber: gray(dot)ru(at)gmail(dot)com Skype: gray-hemp Phone: +79160686204
>

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