Re: Trigger in partitioned table

[Erik Jones <erik(at)myemma(dot)com>]

On Dec 18, 2007, at 5:53 AM, Goboxe wrote:

> Hi,
>
> When adding sets of new child tables to a artitioned table, I need to
> update the triggers with new dates added as new condition. See sample
> below.
>
> My question is, what will happen if the update to the trigger happen
> at the same time as insert operation to the master table?
>
> CREATE OR REPLACE FUNCTION t_agg_billing()
>   RETURNS "trigger" AS
> $BODY$
> DECLARE
> BEGIN
> 	IF (TG_OP = 'INSERT') THEN
> 		IF NEW.CallDate = 20071001 THEN
> 			INSERT INTO z_agg_billing_d_20071001 VALUES ( NEW.* );
> 		ELSEIF NEW.CallDate = 20071002 THEN
> 			INSERT INTO z_agg_billing_d_20071002 VALUES ( NEW.* );
> 		ELSEIF NEW.CallDate = 20071003 THEN
> 			INSERT INTO z_agg_billing_d_20071003 VALUES ( NEW.* );
>                                  ...
>                                  ...
>                                  ...
>
> 		END IF;
> 	END IF;
> 	RETURN NULL;
> END;
> $BODY$
>   LANGUAGE 'plpgsql' VOLATILE;
> ALTER FUNCTION t_agg_billing() OWNER TO sa;

Nothing special.  You'll be updating the function that is called by  
the trigger, not the trigger itself.   So, The function that gets  
called will be the old function version if the CREATE OR REPLACE  
FUNCTION hasn't commited when the INSERT starts.

Btw, if you have a whole lot of those partitions, you may want to  
benchmark just using NEW.CallDate to build a dynamic query to be run  
with EXECUTE, if it's feasible in the speed sense, that'll cut out  
the headache of having to update the trigger function every time you  
add partitions.

Erik Jones

Software Developer | Emma®
erik(at)myemma(dot)com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com