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.konop...@postgresql-consulting.com> wrote: > On Wed, Aug 29, 2012 at 12:26 AM, Moshe Jacobson <mo...@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 > > mo...@neadwerx.com | www.neadwerx.com > > > > -- > Sergey Konoplev > > a database architect, software developer at PostgreSQL-Consulting.com > http://www.postgresql-consulting.com > > Jabber: gray...@gmail.com Skype: gray-hemp Phone: +79160686204 > -- Moshe Jacobson Nead Werx, Inc. | Senior Systems Engineer 2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339 mo...@neadwerx.com | www.neadwerx.com
