cancel
Showing results for 
Search instead for 
Did you mean: 

Automating table partition

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

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

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

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

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

 

 

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

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

 

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.

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