Postgres and Postgres Plus tips and tricks
Ashnik’s DB Consultant, Sameer blogs and how! Picked up from his personal blogs, this one’s particularly edifying and useful. Check it out –
Easy To Attach a Partition in PPAS 9.2
EnterpriseDB offers an easy way to ADD a partition CREATE TABLE employees (empno numeric(4,0), ename varchar(10), job varchar(9), hiredate timestamp ) PARTITION BY LIST (job) (PARTITION emp_mgmt VALUES (‘MANAGER’) tablespace tbsp_1, PARTITION emp_sales VALUES (‘SALESMAN’) tablespace tbsp_2, PARTITION emp_ops VALUES (‘CLERK’) tablespace tbsp_3 ); alter table employees add partition emp_president values(‘PRESIDENT’); and then you can insert data in this partition. Many a time you may want to attach a partition from an existing table. Suppose I have a table called employees_emp_president and I want to attach the table as a new partition to employees, given that the structure is same.
Now you want to add a new partition called emp_president and want to add data from employees_emp_president to it.
A conventional way is to add a new partition emp_president and then insert into employees_emp_president select * from employees_emp_president_temp;
But will that be the best way to do it for this huge no of records?
Let’s try another approach:
Completes in 2 seconds. Wow!!! Let’s see what is the effect of this.
Well I see there is a new child table. Let’s see it’s structure (I am particularly interested in indexes).
So the table structure of our new partition is same as the old table plus it holds the indexes as well. Now let’s confirm if it copied the data (well 2sec was too fast wasn’t it?).
Now let’s just test how much time an insert into..select * from would have taken:
Well, you what you will be doing the next time you need to attach a partition. oh! I forgot about attach_table_to_partition. It’s not an in-built function. I wrote it to ease my pain: CREATE OR REPLACE FUNCTION attach_table_to_partition(
tablename varchar(50),
new_partition_name varchar(50), partition_clause varchar(100),
existing_table_name varchar(50)
)RETURNS VOID AS $$ BEGIN execute ‘alter table ‘|| tablename || ‘ add partition ‘ || new_partition_name || ‘ ‘ || partition_clause; execute ‘alter table ‘|| tablename || ‘ exchange partition ‘ || new_partition_name || ‘ with table ‘ || existing_table_name;
execute ‘drop table ‘ || existing_table_name; end; $$ LANGUAGE plpgsql;
