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.

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 ?

 

Highlighted
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

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

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