cancel
Showing results for 
Search instead for 
Did you mean: 

Automating table partition

SOLVED
Highlighted
Level 2 Adventurer

Automating table partition

How can I automate the  table partition having partition key of 15 days every month.

Is there any solution in PostgreSQL which can be used.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Moderator

Re: Automating table partition

Hi Yashnaresh,

As mentioned, on "https://www.postgresql.org/docs/current/static/ddl-inherit.html".

 

All check constraints and not-null constraints on a parent table are automatically inherited by its children. Other types of constraints (unique, primary key, and foreign key constraints) are not inherited.
 
However as a workaround you can alter the table and add the required constraint.
 
For Example:
 
CREATE OR REPLACE FUNCTION new_partition_creator() RETURNS trigger AS
  $BODY$
    DECLARE
      partition_date TEXT;
      partition TEXT;
      partition_day int;
      startdate date;
      enddate date;
    BEGIN
      partition_day := to_char(NEW.logdate,'DD');
      partition_date := to_char(NEW.logdate,'YYYY_MM');

         IF partition_day < 15 THEN
      partition := TG_RELNAME || '_' || partition_date || '_p1';
      startdate := to_char(NEW.logdate,'YYYY-MM-01');
      enddate := date_trunc('MONTH', NEW.logdate) + INTERVAL '1 MONTH - 1 day';
      ELSE
      partition := TG_RELNAME || '_' || partition_date || '_p2';
      startdate := to_char(NEW.logdate,'YYYY-MM-15');
      enddate := date_trunc('MONTH', NEW.logdate) + INTERVAL '1 MONTH - 1 day';
      END IF;
      
      IF NOT EXISTS(SELECT relname FROM pg_class WHERE relname=partition) THEN
        RAISE NOTICE 'A partition has been created %',partition;
        EXECUTE 'CREATE TABLE ' || partition || ' ( CHECK ( logdate >= DATE ''' || startdate || '''  AND logdate <=  DATE ''' ||  enddate || ''' )) INHERITS (' || TG_RELNAME || ');';
        EXECUTE 'CREATE INDEX ' || partition || '_logdate ON '  || partition || '(logdate)';
        EXECUTE 'ALTER TABLE ' || partition || ' add primary key(city_id);';       
        END IF;
        EXECUTE 'INSERT INTO ' || partition || ' SELECT(' || TG_RELNAME || ' ' || quote_literal(NEW) || ').* RETURNING city_id;';
      RETURN NULL;
    END;
  $BODY$
LANGUAGE plpgsql VOLATILE
COST 100;


postgres=# insert into measurement values(1,'2017-11-12',10,11);
NOTICE:  A partition has been created measurement_2017_11_p1
INSERT 0 0
postgres=# \d+ measurement_2017_11_p1
                            Table "public.measurement_2017_11_p1"
  Column   |  Type   | Collation | Nullable | Default | Storage | Stats target | Description
-----------+---------+-----------+----------+---------+---------+--------------+-------------
 city_id   | integer |           | not null |         | plain   |              |
 logdate   | date    |           | not null |         | plain   |              |
 peaktemp  | integer |           |          |         | plain   |              |
 unitsales | integer |           |          |         | plain   |              |
Indexes:
    "measurement_2017_11_p1_pkey" PRIMARY KEY, btree (city_id)
    "measurement_2017_11_p1_logdate" btree (logdate)
Check constraints:
    "measurement_2017_11_p1_logdate_check" CHECK (logdate >= '2017-11-01'::date AND logdate <= '2017-11-30'::date)
Inherits: measurement

postgres=#
postgres=# insert into measurement values(1,'2017-11-12',10,11);
ERROR:  duplicate key value violates unique constraint "measurement_2017_11_p1_pkey"
DETAIL:  Key (city_id)=(1) already exists.
CONTEXT:  SQL statement "INSERT INTO measurement_2017_11_p1 SELECT(measurement '(1,2017-11-12,10,11)').* RETURNING city_id;"
PL/pgSQL function new_partition_creator() line 28 at EXECUTE
postgres=#

Regarding "RETURNING",

Sometimes it is useful to obtain data from modified or newly added rows while they are being manipulated. The INSERT, UPDATE, and DELETE commands all have an optional RETURNING clause that supports this. Use of RETURNING avoids performing an extra database query to collect the data, and is especially valuable when it would otherwise be difficult to identify the modified rows reliably.

The allowed contents of a RETURNING clause are the same as a SELECT command's output list. It can contain column names of the command's target table, or value expressions using those columns. A common shorthand is RETURNING *, which selects all columns of the target table in order. In an INSERT, the data available to RETURNING is the row as it was inserted.

In provided sample case we have returned city_id which is getting inserted.

For Example,
postgres=# insert into test values(5) returning id;
 id
----
  5
(1 row)

INSERT 0 1
postgres=#

In this example query will return the "id" which is getting inserted in table.

Hope this helps for your requirement.

View solution in original post

22 REPLIES 22
Highlighted
Level 2 Adventurer

Re: Automating table partition

Please share if anyone is having suggestions to write a script for 15 day automatic partition in postreSQL


@yashnaresh wrote:

How can I automate the  table partition having partition key of 15 days every month.

Is there any solution in PostgreSQL which can be used.


 

Highlighted
Moderator

Re: Automating table partition

Hi Yashnaresh,

Hope you are doing good !!

As per your initial email it seems that you are looking for the script which will maintain first 15 days data in one table say "p1" and remaining days data in another partition.

Please check the below script which can be helpful for,

1> Creating automatic partion depends on the date range of insert command
2> In script i have also mentioned that how we can add index on the required column's.
3> Data from date range from 1st to 14th will be added in partition "p1" and remaining will be added in partition "p2".
 
Sample Script :

CREATE TABLE measurement (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
);

CREATE OR REPLACE FUNCTION new_partition_creator() RETURNS trigger AS
  $BODY$
    DECLARE
      partition_date TEXT;
      partition TEXT;
      partition_day int;
      startdate TEXT;
      enddate TEXT;
    BEGIN
      partition_day := to_char(NEW.logdate,'DD');
      partition_date := to_char(NEW.logdate,'YYYY_MM');

         IF partition_day < 15 THEN
      partition := TG_RELNAME || '_' || partition_date || '_p1';
      startdate := to_char(NEW.logdate,'YYYY-MM-01');
      enddate := to_char(NEW.logdate,'YYYY-MM-15');
      ELSE
      partition := TG_RELNAME || '_' || partition_date || '_p2';
      startdate := to_char(NEW.logdate,'YYYY-MM-15');
      enddate := to_char(NEW.logdate,'YYYY-MM-31');
      END IF;
      
      IF NOT EXISTS(SELECT relname FROM pg_class WHERE relname=partition) THEN
        RAISE NOTICE 'A partition has been created %',partition;
        EXECUTE 'CREATE TABLE ' || partition || ' ( CHECK ( logdate >= DATE ''' || startdate || '''  AND logdate <  DATE ''' ||  enddate || ''' )) INHERITS (' || TG_RELNAME || ');';
        EXECUTE 'CREATE INDEX ' || partition || '_logdate ON '  || partition || '(logdate)';
        END IF;
        EXECUTE 'INSERT INTO ' || partition || ' SELECT(' || TG_RELNAME || ' ' || quote_literal(NEW) || ').* RETURNING city_id;';
      RETURN NULL;
    END;
  $BODY$
LANGUAGE plpgsql VOLATILE
COST 100;


CREATE TRIGGER testing_partition_insert_trigger BEFORE INSERT ON measurement FOR EACH ROW EXECUTE PROCEDURE new_partition_creator();


postgres=# insert into measurement values(1,'2017-10-11',10,10);
NOTICE:  A partition has been created measurement_2017_10_p1
INSERT 0 0
postgres=# insert into measurement values(1,'2017-10-11',10,10);
INSERT 0 0
postgres=# select * from measurement_2017_10_p1 ;
 city_id |  logdate   | peaktemp | unitsales
---------+------------+----------+-----------
       1 | 2017-10-11 |       10 |        10
       1 | 2017-10-11 |       10 |        10
(2 rows)


postgres=# insert into measurement values(1,'2017-10-15',10,11);
NOTICE:  A partition has been created measurement_2017_10_p2
INSERT 0 0
postgres=#
postgres=# select * from measurement_2017_10_p2;
 city_id |  logdate   | peaktemp | unitsales
---------+------------+----------+-----------
       1 | 2017-10-15 |       10 |        11
(1 row)

Highlighted
Level 2 Adventurer

Re: Automating table partition

Thanks !! Ranjeet for the reply .

Getting below err while inserting for nov.

 

insert into measurement values(1,'2017-11-15',10,10);

 

ERROR: date/time field value out of range: "2017-11-31"
CONTEXT: SQL statement "CREATE TABLE measurement_2017_11_p2 ( CHECK ( logdate >= DATE '2017-11-15' AND logdate < DATE '2017-11-31' )) INHERITS (measurement);"
PL/pgSQL function new_partition_creator() line 24 at EXECUTE statement
SQL state: 22008

 

 

 

Highlighted
Moderator

Re: Automating table partition

Hi Yashnaresh,

This is failing because of the month end date is not validated. Please execute below updated function and try recreating the child partitions with updated check constraints,


CREATE OR REPLACE FUNCTION new_partition_creator() RETURNS trigger AS
  $BODY$
    DECLARE
      partition_date TEXT;
      partition TEXT;
      partition_day int;
      startdate date;
      enddate date;
    BEGIN
      partition_day := to_char(NEW.logdate,'DD');
      partition_date := to_char(NEW.logdate,'YYYY_MM');

         IF partition_day < 15 THEN
      partition := TG_RELNAME || '_' || partition_date || '_p1';
      startdate := to_char(NEW.logdate,'YYYY-MM-01');
      enddate := date_trunc('MONTH', NEW.logdate) + INTERVAL '1 MONTH - 1 day';
      ELSE
      partition := TG_RELNAME || '_' || partition_date || '_p2';
      startdate := to_char(NEW.logdate,'YYYY-MM-15');
      enddate := date_trunc('MONTH', NEW.logdate) + INTERVAL '1 MONTH - 1 day';
      END IF;
      
      IF NOT EXISTS(SELECT relname FROM pg_class WHERE relname=partition) THEN
        RAISE NOTICE 'A partition has been created %',partition;
        EXECUTE 'CREATE TABLE ' || partition || ' ( CHECK ( logdate >= DATE ''' || startdate || '''  AND logdate <=  DATE ''' ||  enddate || ''' )) INHERITS (' || TG_RELNAME || ');';
        EXECUTE 'CREATE INDEX ' || partition || '_logdate ON '  || partition || '(logdate)';
        END IF;
        EXECUTE 'INSERT INTO ' || partition || ' SELECT(' || TG_RELNAME || ' ' || quote_literal(NEW) || ').* RETURNING city_id;';
      RETURN NULL;
    END;
  $BODY$
LANGUAGE plpgsql VOLATILE
COST 100;


postgres=# insert into measurement values(1,'2017-11-30',10,11);
NOTICE:  A partition has been created measurement_2017_11_p2
INSERT 0 0

Note: Make sure to drop the previously created child partitions with wrong check constraints.

Kindly let us know if you still face any issue.                                        

Highlighted
Level 2 Adventurer

Re: Automating table partition

Thanks !! Ranjeet,

 

Further, how to include primary key in this ,I am getting syntax err

 

EXECUTE 'CREATE TABLE ' || partition || ' ( CHECK ( request_time >= DATE ''' || startdate || '''  AND request_time <=  DATE ''' ||  enddate || ''')) ||' PRIMARY KEY (unique_id)||  INHERITS (' || TG_RELNAME || ');';

 

ERROR: syntax error at or near "PRIMARY"
LINE 25: ...equest_time <= DATE ''' || enddate || ''')) ||' PRIMARY KE...
^
SQL state: 42601

 

 

Highlighted
Level 2 Adventurer

Re: Automating table partition

Also what is the function of RETURNING city_id;'

Highlighted
Moderator

Re: Automating table partition

Hi Yashnaresh,

As mentioned, on "https://www.postgresql.org/docs/current/static/ddl-inherit.html".

 

All check constraints and not-null constraints on a parent table are automatically inherited by its children. Other types of constraints (unique, primary key, and foreign key constraints) are not inherited.
 
However as a workaround you can alter the table and add the required constraint.
 
For Example:
 
CREATE OR REPLACE FUNCTION new_partition_creator() RETURNS trigger AS
  $BODY$
    DECLARE
      partition_date TEXT;
      partition TEXT;
      partition_day int;
      startdate date;
      enddate date;
    BEGIN
      partition_day := to_char(NEW.logdate,'DD');
      partition_date := to_char(NEW.logdate,'YYYY_MM');

         IF partition_day < 15 THEN
      partition := TG_RELNAME || '_' || partition_date || '_p1';
      startdate := to_char(NEW.logdate,'YYYY-MM-01');
      enddate := date_trunc('MONTH', NEW.logdate) + INTERVAL '1 MONTH - 1 day';
      ELSE
      partition := TG_RELNAME || '_' || partition_date || '_p2';
      startdate := to_char(NEW.logdate,'YYYY-MM-15');
      enddate := date_trunc('MONTH', NEW.logdate) + INTERVAL '1 MONTH - 1 day';
      END IF;
      
      IF NOT EXISTS(SELECT relname FROM pg_class WHERE relname=partition) THEN
        RAISE NOTICE 'A partition has been created %',partition;
        EXECUTE 'CREATE TABLE ' || partition || ' ( CHECK ( logdate >= DATE ''' || startdate || '''  AND logdate <=  DATE ''' ||  enddate || ''' )) INHERITS (' || TG_RELNAME || ');';
        EXECUTE 'CREATE INDEX ' || partition || '_logdate ON '  || partition || '(logdate)';
        EXECUTE 'ALTER TABLE ' || partition || ' add primary key(city_id);';       
        END IF;
        EXECUTE 'INSERT INTO ' || partition || ' SELECT(' || TG_RELNAME || ' ' || quote_literal(NEW) || ').* RETURNING city_id;';
      RETURN NULL;
    END;
  $BODY$
LANGUAGE plpgsql VOLATILE
COST 100;


postgres=# insert into measurement values(1,'2017-11-12',10,11);
NOTICE:  A partition has been created measurement_2017_11_p1
INSERT 0 0
postgres=# \d+ measurement_2017_11_p1
                            Table "public.measurement_2017_11_p1"
  Column   |  Type   | Collation | Nullable | Default | Storage | Stats target | Description
-----------+---------+-----------+----------+---------+---------+--------------+-------------
 city_id   | integer |           | not null |         | plain   |              |
 logdate   | date    |           | not null |         | plain   |              |
 peaktemp  | integer |           |          |         | plain   |              |
 unitsales | integer |           |          |         | plain   |              |
Indexes:
    "measurement_2017_11_p1_pkey" PRIMARY KEY, btree (city_id)
    "measurement_2017_11_p1_logdate" btree (logdate)
Check constraints:
    "measurement_2017_11_p1_logdate_check" CHECK (logdate >= '2017-11-01'::date AND logdate <= '2017-11-30'::date)
Inherits: measurement

postgres=#
postgres=# insert into measurement values(1,'2017-11-12',10,11);
ERROR:  duplicate key value violates unique constraint "measurement_2017_11_p1_pkey"
DETAIL:  Key (city_id)=(1) already exists.
CONTEXT:  SQL statement "INSERT INTO measurement_2017_11_p1 SELECT(measurement '(1,2017-11-12,10,11)').* RETURNING city_id;"
PL/pgSQL function new_partition_creator() line 28 at EXECUTE
postgres=#

Regarding "RETURNING",

Sometimes it is useful to obtain data from modified or newly added rows while they are being manipulated. The INSERT, UPDATE, and DELETE commands all have an optional RETURNING clause that supports this. Use of RETURNING avoids performing an extra database query to collect the data, and is especially valuable when it would otherwise be difficult to identify the modified rows reliably.

The allowed contents of a RETURNING clause are the same as a SELECT command's output list. It can contain column names of the command's target table, or value expressions using those columns. A common shorthand is RETURNING *, which selects all columns of the target table in order. In an INSERT, the data available to RETURNING is the row as it was inserted.

In provided sample case we have returned city_id which is getting inserted.

For Example,
postgres=# insert into test values(5) returning id;
 id
----
  5
(1 row)

INSERT 0 1
postgres=#

In this example query will return the "id" which is getting inserted in table.

Hope this helps for your requirement.

View solution in original post

Highlighted
Level 2 Adventurer

Re: Automating table partition

Thanks !! Ranjeet ,you rock !!

Highlighted
Level 2 Adventurer

Re: Automating table partition

Hi Ranjeet,

 

This solution works when there is not sequence used but when using a sequence it gives below error from application, though the manual insert query works. Have any suggestion ?

 

javax.persistence.PersistenceException: org.hibernate.HibernateException: The database returned no natively generated identity value

 

Thanks in advance.

 

Tags (1)
Highlighted
Moderator

Re: Automating table partition

Hi Yashnaresh,  
 
IDENTITY ID generator with a table column which is not properly configured. It should be an auto_increment column for the IDENTITY generator to work. Else, the database doesn't return any generated ID.

You need to make you table's primary column auto increment.

How to solve?

1) Fixing this is as simple as making sure that the Primary Key Column, of the table you are working with, has Auto Increment set.

2) In such scenario, you can create the primary key column with the data type as BIGSERIAL which can auto increment the primary key value.
For Example,
CREATE TABLE measurement (
    city_id         bigserial primary key,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
);



postgres@postgres  \d+ measurement
                                                 Table "public.measurement"
  Column   |  Type   |                           Modifiers                           | Storage | Stats target | Description
-----------+---------+---------------------------------------------------------------+---------+--------------+-------------
 city_id   | bigint  | not null default nextval('measurement_city_id_seq'::regclass) | plain   |              |
 logdate   | date    | not null                                                      | plain   |              |
 peaktemp  | integer |                                                               | plain   |              |
 unitsales | integer |                                                               | plain   |              |
Indexes:
    "measurement_pkey" PRIMARY KEY, btree (city_id)

postgres@postgres  

Kindly let me know if you still face any issue.

Highlighted
Level 2 Adventurer

Re: Automating table partition

Dear All,

 

After creating function for automation of table partitioning, how can we do the automation for drop child table's which are older than 7 days without any downtime ?

 

EDB Team Member

Re: Automating table partition

Hi Yashnaresh,

 

As per your latest comment you want the automation of child tables which are older than 7 days. I have prepared the plpgsql function for it.

 

1) Create the show_partitions view by executing the attached sql file show_partitions.sql

 

postgres=# \i /home/postgres/show_partitions.sql

 

Please replace the parent.relname in the script to parent table name, I have kept it “measurement”.

 

2) Create the drop_partition function using the attached sql file drop_partition.sql

 

postgres=# \i /home/postgres/drop_partition.sql

 

3) Execute the function drop_partition

 

postgres=# select drop_partition();

 

Could you please test this scripts and let me know in case of any issues/queries.

 

Regards,

Sudhir

Highlighted
Level 2 Adventurer

Re: Automating table partition

Dear All,

 

I facing some challenges while creating below function, can some one help me on this

 

CREATE OR REPLACE FUNCTION new_partition_creator() RETURNS trigger AS
$BODY$
DECLARE
partition_date TEXT;
partition TEXT;
partition_day int;
startdate date;
enddate date;
BEGIN
partition_day := to_char(NEW.logdate,'DD');
partition_date := to_char(NEW.logdate,'YYYY_MM');

IF partition_day < 15 THEN
partition := TG_RELNAME || '_' || partition_date || '_p1';
startdate := to_char(NEW.logdate,'YYYY-MM-01');
enddate := date_trunc('MONTH', NEW.logdate) + INTERVAL '1 MONTH - 1 day';
ELSE
partition := TG_RELNAME || '_' || partition_date || '_p2';
startdate := to_char(NEW.logdate,'YYYY-MM-15');
enddate := date_trunc('MONTH', NEW.logdate) + INTERVAL '1 MONTH - 1 day';
END IF;

IF NOT EXISTS(SELECT relname FROM pg_class WHERE relname=partition) THEN
RAISE NOTICE 'A partition has been created %',partition;
EXECUTE 'CREATE TABLE ' || partition || ' ( CHECK ( logdate >= DATE ''' || startdate || ''' AND logdate <= DATE ''' || enddate || ''' )) INHERITS (' || TG_RELNAME || ');';
EXECUTE 'CREATE INDEX ' || partition || '_logdate ON ' || partition || '(logdate)';
EXECUTE 'ALTER TABLE ' || partition || ' add primary key(city_id);';
END IF;
EXECUTE 'INSERT INTO ' || partition || ' SELECT(' || TG_RELNAME || ' ' || quote_literal(NEW) || ').* RETURNING city_id;';
RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;


ERROR: syntax error at or near ")"
LINE 23: ...S(SELECT relname FROM pg_class WHERE relname=partition) THEN

 

Regards,

Moin

Highlighted
EDB Team Member

Re: Automating table partition

Hi Moin,

 

This could be due to some extra characters added while copying the function definition.

 

Could you please use the attached sql file attached and execute it from psql prompt or through psql -f option.

 

postgres=# \i /home/postgres/partition.sql 

CREATE FUNCTION

 

Regards,

Sudhir

Highlighted
Level 2 Adventurer

Re: Automating table partition

Thanks Sudhir,

 

I can able to create function in PostgreSQL Standard 9.4 but while doing same in PPAS 9.4 i am getting that same error.

 

Regards,

Moin

 

 

Highlighted
EDB Team Member

Re: Automating table partition

Hi Moin,

 

We have used the 'partition' as variable in the function. As it is a keyword in the PPAS, it will give you the error.

 

Please try giving any other variable name for it, for e.g. I used partition1 and it creating the function in PPAS successfully.

 

Please test it out and let me know if you face any issues.

 

Regards,

Sudhir

Highlighted
Level 2 Adventurer

Re: Automating table partition

Dear Team,

 

How to automate dropping of child tables after the retention period of 7 days ? 

 

Regards,

Moin

 

Highlighted
EDB Team Member

Re: Automating table partition

Hi Moin,

 

PostgreSQL Function would be wise call for your requirement.

 

Function can have below path to drop the child partition table with respective to the provided Master table and retention policy. And then you can call the function by setting it in the crontab.

 

1. Function should select the child tables with respective to the provided Master table.
2. Soon after receiving child table list. Un-bound the child table depends on the retention policy. (ex. 7 days)
3. And then drop the table after validating the data if it is old enough.

 

Note: Also, we would suggest to test this test case in your Test enviornment, before, trying it on the Prod.

 

Hope this helps.

Highlighted
Level 2 Adventurer

Re: Automating table partition

Dear Team,

 

Thanks for your input and suggestion !

I am still learning of postgresql development like creating functions etc, it will be greate help for me if some one share the function to drop the child tables older than the 3 day and also need a function to drop the child tables older than 12 months.

 

Some of the Queries i have related with partitioning:

 

1. If we go for the Automation of table partitioning will there be any lock on whole table ? do i have to take downtime for table creation ?

2. While droping the child table's do i have to take the downtime and will there any lock on the whole table as well ?

3. Can i used alter table <<tablename>> no inherit option while dropping child tables ?

4. is any view to see the no of child tables we have ?

 

Your valuable suggestion on this will greatly helps us.

 

Regards,

Moin

 

Highlighted
Level 3 Adventurer

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

Highlighted
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

Highlighted
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