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.konoplev@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
On Wed, Aug 29, 2012 at 12:26 AM, Moshe Jacobson <moshe@xxxxxxxxxxxx> wrote:It means that when you DEFINE columns in the inherited table they will
> 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
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.
When LIKE table_name is specified in the definition of a table it
> means, but the descendant tables were created using "like tb_audit_event" to
> inherit the columns.
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
Moshe Jacobson
Nead Werx, Inc. | Senior Systems Engineer
Nead Werx, Inc. | Senior Systems Engineer
2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339
moshe@xxxxxxxxxxxx | www.neadwerx.com
moshe@xxxxxxxxxxxx | www.neadwerx.com
