pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedAutomating PostgreSql table partition using triggers
6+ messages / 3 participants
[nested] [flat]
* Automating PostgreSql table partition using triggers
@ 2011-01-27 17:50 Amitabh Kant <amitabhkant@gmail.com>
0 siblings, 3 replies; 6+ messages in thread
From: Amitabh Kant @ 2011-01-27 17:50 UTC (permalink / raw)
To: pgsql-sql
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.
Create Table tbltesting(
testing_id int not null,
testing_name character varying(255));
Create table tbltesting1(check(testing_id = 1)) inherits(tbltesting);
Create table tbltesting2(check(testing_id = 2)) inherits(tbltesting);
CREATE OR REPLACE FUNCTION partition_insert_trigger()
RETURNS TRIGGER AS $$
DECLARE id integer ;
BEGIN
id := NEW.testing_id;
INSERT INTO tbltesting'||id||' VALUES (NEW.*); //Problem line, not
sure what syntax to use here
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
CREATE TRIGGER partition_trigger
BEFORE INSERT ON tbltesting
FOR EACH ROW EXECUTE PROCEDURE partition_insert_trigger();
Creating tables or child tables is not a problem and the trigger works fine
if the function has static definitions. What I am trying to achieve is use
the new testing_id to create a table name for use in the insert statement.
If I am able to use the variable in the table name, I would not have to
re-declare the function each time with modified conditions for each separate
testing_id.
With regards
Amitabh
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Automating PostgreSql table partition using triggers
@ 2011-01-27 17:52 Amitabh Kant <amitabhkant@gmail.com>
parent: Amitabh Kant <amitabhkant@gmail.com>
2 siblings, 0 replies; 6+ messages in thread
From: Amitabh Kant @ 2011-01-27 17:52 UTC (permalink / raw)
To: pgsql-sql
On Thu, Jan 27, 2011 at 11:20 PM, 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.
>
> Create Table tbltesting(
> testing_id int not null,
> testing_name character varying(255));
>
> Create table tbltesting1(check(testing_id = 1)) inherits(tbltesting);
> Create table tbltesting2(check(testing_id = 2)) inherits(tbltesting);
>
> CREATE OR REPLACE FUNCTION partition_insert_trigger()
> RETURNS TRIGGER AS $$
> DECLARE id integer ;
> BEGIN
> id := NEW.testing_id;
>
> INSERT INTO tbltesting'||id||' VALUES (NEW.*); //Problem line, not
> sure what syntax to use here
>
> RETURN NULL;
> END;
> $$
> LANGUAGE plpgsql;
>
>
> CREATE TRIGGER partition_trigger
> BEFORE INSERT ON tbltesting
> FOR EACH ROW EXECUTE PROCEDURE partition_insert_trigger();
>
> Creating tables or child tables is not a problem and the trigger works fine
> if the function has static definitions. What I am trying to achieve is use
> the new testing_id to create a table name for use in the insert statement.
> If I am able to use the variable in the table name, I would not have to
> re-declare the function each time with modified conditions for each separate
> testing_id.
>
>
> With regards
>
> Amitabh
>
>
Forgot to add that I am using 8.4.
With regards
Amitabh
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Automating PostgreSql table partition using triggers
@ 2011-01-27 21:10 Viktor Bojović <viktor.bojovic@gmail.com>
parent: Amitabh Kant <amitabhkant@gmail.com>
2 siblings, 1 reply; 6+ messages in thread
From: Viktor Bojović @ 2011-01-27 21:10 UTC (permalink / raw)
To: Amitabh Kant <amitabhkant@gmail.com>; +Cc: pgsql-sql
when creating dynamic query try to store it completey as string because you
will not be able to change tableName (i havent been doing that for a long
time , so possibly i can be wrong).
to "exec" or "execute" command you have to pass the query as parameter.
it would look like this
declare sql as varchar;
Field2=new. testing_name;
Field1=new.testing_id;
sql:='insert into tableName'||id||' (testing_id,testing_name) values
('||Field1||','||quote_literal(Field2)||')';
exec(sql);
On Thu, Jan 27, 2011 at 6:50 PM, 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.
>
> Create Table tbltesting(
> testing_id int not null,
> testing_name character varying(255));
>
> Create table tbltesting1(check(testing_id = 1)) inherits(tbltesting);
> Create table tbltesting2(check(testing_id = 2)) inherits(tbltesting);
>
> CREATE OR REPLACE FUNCTION partition_insert_trigger()
> RETURNS TRIGGER AS $$
> DECLARE id integer ;
> BEGIN
> id := NEW.testing_id;
>
> INSERT INTO tbltesting'||id||' VALUES (NEW.*); //Problem line, not
> sure what syntax to use here
>
> RETURN NULL;
> END;
> $$
> LANGUAGE plpgsql;
>
>
> CREATE TRIGGER partition_trigger
> BEFORE INSERT ON tbltesting
> FOR EACH ROW EXECUTE PROCEDURE partition_insert_trigger();
>
> Creating tables or child tables is not a problem and the trigger works fine
> if the function has static definitions. What I am trying to achieve is use
> the new testing_id to create a table name for use in the insert statement.
> If I am able to use the variable in the table name, I would not have to
> re-declare the function each time with modified conditions for each separate
> testing_id.
>
>
> With regards
>
> Amitabh
>
>
--
---------------------------------------
Viktor Bojović
---------------------------------------
Wherever I go, Murphy goes with me
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Automating PostgreSql table partition using triggers
@ 2011-01-27 21:27 Scott Marlowe <scott.marlowe@gmail.com>
parent: Amitabh Kant <amitabhkant@gmail.com>
2 siblings, 1 reply; 6+ messages in thread
From: Scott Marlowe @ 2011-01-27 21:27 UTC (permalink / raw)
To: Amitabh Kant <amitabhkant@gmail.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Automating PostgreSql table partition using triggers
@ 2011-01-28 02:41 Amitabh Kant <amitabhkant@gmail.com>
parent: Viktor Bojović <viktor.bojovic@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Amitabh Kant @ 2011-01-28 02:41 UTC (permalink / raw)
To: Viktor Bojović <viktor.bojovic@gmail.com>; +Cc: pgsql-sql
On Fri, Jan 28, 2011 at 2:40 AM, Viktor Bojović <viktor.bojovic@gmail.com>wrote:
> when creating dynamic query try to store it completey as string because you
> will not be able to change tableName (i havent been doing that for a long
> time , so possibly i can be wrong).
> to "exec" or "execute" command you have to pass the query as parameter.
> it would look like this
>
> declare sql as varchar;
> Field2=new. testing_name;
> Field1=new.testing_id;
> sql:='insert into tableName'||id||' (testing_id,testing_name) values
> ('||Field1||','||quote_literal(Field2)||')';
> exec(sql);
>
> On Thu, Jan 27, 2011 at 6:50 PM, 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.
>>
>> Create Table tbltesting(
>> testing_id int not null,
>> testing_name character varying(255));
>>
>> Create table tbltesting1(check(testing_id = 1)) inherits(tbltesting);
>> Create table tbltesting2(check(testing_id = 2)) inherits(tbltesting);
>>
>> CREATE OR REPLACE FUNCTION partition_insert_trigger()
>> RETURNS TRIGGER AS $$
>> DECLARE id integer ;
>> BEGIN
>> id := NEW.testing_id;
>>
>> INSERT INTO tbltesting'||id||' VALUES (NEW.*); //Problem line, not
>> sure what syntax to use here
>>
>> RETURN NULL;
>> END;
>> $$
>> LANGUAGE plpgsql;
>>
>>
>> CREATE TRIGGER partition_trigger
>> BEFORE INSERT ON tbltesting
>> FOR EACH ROW EXECUTE PROCEDURE partition_insert_trigger();
>>
>> Creating tables or child tables is not a problem and the trigger works
>> fine if the function has static definitions. What I am trying to achieve is
>> use the new testing_id to create a table name for use in the insert
>> statement. If I am able to use the variable in the table name, I would not
>> have to re-declare the function each time with modified conditions for each
>> separate testing_id.
>>
>>
>> With regards
>>
>> Amitabh
>>
>> --
> ---------------------------------------
> Viktor Bojović
> ---------------------------------------
> Wherever I go, Murphy goes with me
>
Thanks Victor. This should work out for me.
Amitabh
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Automating PostgreSql table partition using triggers
@ 2011-01-28 02:47 Amitabh Kant <amitabhkant@gmail.com>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Amitabh Kant @ 2011-01-28 02:47 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: pgsql-sql
On Fri, Jan 28, 2011 at 2:57 AM, Scott Marlowe <scott.marlowe@gmail.com>wrote:
> 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.
>
My partitions are based on a foreign key. I would be creating child tables
when a new entry is made in the parent table.
With regards
Amitabh
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2011-01-28 02:47 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-01-27 17:50 Automating PostgreSql table partition using triggers Amitabh Kant <amitabhkant@gmail.com>
2011-01-27 17:52 ` Amitabh Kant <amitabhkant@gmail.com>
2011-01-27 21:10 ` Viktor Bojović <viktor.bojovic@gmail.com>
2011-01-28 02:41 ` Amitabh Kant <amitabhkant@gmail.com>
2011-01-27 21:27 ` Scott Marlowe <scott.marlowe@gmail.com>
2011-01-28 02:47 ` Amitabh Kant <amitabhkant@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox