Thread: Automating PostgreSql table partition using triggers

Hi<br /><br />I am trying to write a function which is being called from a trigger used for partitioning a large table.
Thepartitioning is to happen based on an integer field (testing_id). A simplified structure of what I am trying to do
iswritten below.<br /><br />Create Table tbltesting(<br /> testing_id int not null,<br /> testing_name character
varying(255));<br/><br />Create table tbltesting1(check(testing_id = 1)) inherits(tbltesting);<br />Create table
tbltesting2(check(testing_id= 2)) inherits(tbltesting);<br /><br />CREATE OR REPLACE FUNCTION
partition_insert_trigger()<br/>RETURNS TRIGGER AS $$<br />DECLARE id integer ;<br />BEGIN<br />    id :=
NEW.testing_id;<br />    <br />    INSERT INTO tbltesting'||id||' VALUES (NEW.*);   //Problem line, not sure what
syntaxto use here<br /><br />    RETURN NULL;<br />END;<br />$$<br />LANGUAGE plpgsql;<br /><br /><br />CREATE TRIGGER
partition_trigger<br/>    BEFORE INSERT ON tbltesting<br />    FOR EACH ROW EXECUTE PROCEDURE
partition_insert_trigger();<br/><br />Creating tables or child tables is not a problem and the trigger works fine if
thefunction has static definitions. What I am trying to achieve is use the new testing_id to create a table name for
usein the insert statement. If I am able to use the variable in the table name, I would not have to re-declare the
functioneach time with modified conditions for each separate testing_id.<br /><br /><br />With regards<br /><br
/>Amitabh<br/><br /> 

when creating dynamic query try to store it completey as string because you will not be able to change tableName (i
haventbeen doing that for a long time , so possibly i can be wrong).<br />to "exec" or "execute" command you have to
passthe query as parameter.<br /> it would look like this<br /><br />declare sql as varchar;<br />Field2=new.
testing_name;<br/>Field1=new.testing_id;<br />sql:='insert into tableName'||id||' (testing_id,testing_name) values
('||Field1||','||quote_literal(Field2)||')';<br/> exec(sql);<br /><br /><div class="gmail_quote">On Thu, Jan 27, 2011
at6:50 PM, Amitabh Kant <span dir="ltr"><<a href="mailto:amitabhkant@gmail.com">amitabhkant@gmail.com</a>></span>
wrote:<br/><blockquote class="gmail_quote" style="margin: 0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204);
padding-left:1ex;"> Hi<br /><br />I am trying to write a function which is being called from a trigger used for
partitioninga large table. The partitioning is to happen based on an integer field (testing_id). A simplified structure
ofwhat I am trying to do is written below.<br /><br />Create Table tbltesting(<br /> testing_id int not null,<br
/> testing_namecharacter varying(255));<br /><br />Create table tbltesting1(check(testing_id = 1))
inherits(tbltesting);<br/>Create table tbltesting2(check(testing_id = 2)) inherits(tbltesting);<br /><br />CREATE OR
REPLACEFUNCTION partition_insert_trigger()<br />RETURNS TRIGGER AS $$<br />DECLARE id integer ;<br />BEGIN<br />    id
:=NEW.testing_id; <br />    <br />    INSERT INTO tbltesting'||id||' VALUES (NEW.*);   //Problem line, not sure what
syntaxto use here<br /><br />    RETURN NULL;<br />END;<br />$$<br />LANGUAGE plpgsql;<br /><br /><br />CREATE TRIGGER
partition_trigger<br/>    BEFORE INSERT ON tbltesting<br />    FOR EACH ROW EXECUTE PROCEDURE
partition_insert_trigger();<br/><br />Creating tables or child tables is not a problem and the trigger works fine if
thefunction has static definitions. What I am trying to achieve is use the new testing_id to create a table name for
usein the insert statement. If I am able to use the variable in the table name, I would not have to re-declare the
functioneach time with modified conditions for each separate testing_id.<br /><br /><br />With regards<br /><font
color="#888888"><br/>Amitabh<br /><br /></font></blockquote></div><br /><br clear="all" /><br />-- <br
/>---------------------------------------<br/>Viktor Bojović<br />---------------------------------------<br />Wherever
Igo, Murphy goes with me<br /><div id="avg_ls_inline_popup" style="visibility: hidden; left: -5000px; position:
absolute;z-index: 9999; padding: 0px; margin-left: 0px; margin-top: 0px; overflow: hidden; word-wrap: break-word;
color:black; font-size: 10px; text-align: left; line-height: 130%;"></div> 

On Thu, Jan 27, 2011 at 10:50 AM, Amitabh Kant <amitabhkant@gmail.com> wrote:
> Hi
>
> I am trying to write a function which is being called from a trigger used
> for partitioning a large table. The partitioning is to happen based on an
> integer field (testing_id). A simplified structure of what I am trying to do
> is written below.

My advice is to move the table creation from a trigger to a nightly
cron job.  I have one that runs each night that checks for the
existence of the needed tables and creates new ones for up to a month
in advance and emails me the results.  that way if it starts failing
I've got 30 days to get things fixed.  Then another script runs that
creates my new triggers to work with the new partitions.

Too many race conditions and performance issues with using dynamic DDL
to create partitions.