cancel
Showing results for 
Search instead for 
Did you mean: 

Automating table partition

SOLVED
Highlighted
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
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.

8 REPLIES
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.


 

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)

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

 

 

 

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.                                        

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

 

 

Adventurer

Re: Automating table partition

Also what is the function of RETURNING city_id;'

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.

Adventurer

Re: Automating table partition

Thanks !! Ranjeet ,you rock !!