cancel
Showing results for 
Search instead for 
Did you mean: 

Automating table partition

SOLVED
Highlighted
EDB Team Member

Re: Automating table partition

Hi Moin,

 

You can use same drop_partition.sql function attached in mail thread for dropping required child tables, just you need to set the retention period inside function or function can also be modified as parameterized which will accept retention period as input.

 

Please refer below comments for queries you raised

> 1. If we go for the Automation of table partitioning will there be any lock on the whole table ? do i have to take downtime for table creation ?
- While creating a partition table it will not lock parent table hence no downtime will be required.


> 2. While dropping the child table's do i have to take the downtime and will there any lock on the whole table as well ?
- Partitioned table or child table will be considered as the whole table so while dropping the child table, it will lock the child table


> 3. Can i used alter table <<tablename>> no inherit option while dropping child tables ?
- To remove the partition from the partitioned table but retain access to it as a table in its own right: ALTER TABLE <child_table> NO INHERIT <parent_table>;


> 4. is any view to see the no of child tables we have ?
- Postgresql system catalog "pg_inherits" can be used to get list of child tables. SQL can be written something like below:

CREATE VIEW show_partitions AS
SELECT nmsp_parent.nspname AS parent_schema,
parent.relname AS parent,
nmsp_child.nspname AS child_schema,
child.relname AS child
FROM pg_inherits
JOIN pg_class parent ON pg_inherits.inhparent = parent.oid
JOIN pg_class child ON pg_inherits.inhrelid = child.oid
JOIN pg_namespace nmsp_parent ON nmsp_parent.oid = parent.relnamespace
JOIN pg_namespace nmsp_child ON nmsp_child.oid = child.relnamespace
WHERE parent.relname='<parent_table>' ;

Hope this helps you.

 

 

Regards,
Ranjan

Level 2 Adventurer

Re: Automating table partition

Dear Experts,

 

Again back with some more queries related with table partitions:

 

1. How can i create future partitions with shell scripts and without use of any triggers.

2. Can we use pg_partman in PPAS 9.4, is EDB Support for pg_partman ?

 

please kindly share the shell script if you have.

 

Regards,

Moin

EDB Team Member

Re: Automating table partition

Hi Moin,

 

1. How can i create future partitions with shell scripts and without use of any triggers.

You can refer the below link how to create the partitions and you can right a shell script based on your future requirement.

 

https://www.postgresql.org/docs/current/static/ddl-partitioning.html

 

2. Can we use pg_partman in PPAS 9.4, is EDB Support for pg_partman ?

EDB doesn't support pg_partman