Thread: Order of evaluation in triggers for checks on inherited table partitions

I am trying to create a trigger on updates to a table that is
partitioned.  The child tables are partitioned by month and include
checks on a timestamp field.  I want the trigger on the updates to
call a function that replaces the update entirely.  In order to do
this my trigger deletes the record from the parent table (which
deletes it from the appropriate child table) and then inserts into the
appropriate child table and returns NULL (thus skipping the actual
update). However when I try to update an existing record with a
timestamp that would place it in a child table different from the
child table it is in I get an error due to the check on the child
table it is currently in.  My best guess as to what is happening is
that the trigger is evaluating the check before it evaluates the
trigger function and thus cannot tell that the update to the original
table should never take place.  I have included an example below.  The
error that results is "new row for relation "t_foo_2011_6" violates
check constraint "t_foo_2011_6_f_timestamp_check""

My questions:
Is the order of evaluation for the trigger causing this error?
If not what is?
Is there another way to update a record in a child table that would
move it to another child table before the update and skip the
evaluation of the check constraints on the current table?

Example code follows:

CREATE SCHEMA some_schema;
CREATE SCHEMA some_schema_children;

--master table
CREATE TABLE some_schema.t_foo (f_id_foo serial,f_timestamp timestamp,f_text varchar(30)
);

CREATE OR REPLACE FUNCTION some_schema.foo_insert_trigger()
RETURNS TRIGGER AS $$
DECLAREv_tablename varchar(13);v_month integer;v_year integer;
BEGINv_month:=(SELECT date_part from date_part('month', NEW.f_timestamp));v_year:=(SELECT date_part from
date_part('year',NEW.f_timestamp));v_tablename:='t_foo_'||v_year||'_'||v_month;IF ((SELECT COUNT(*) FROM pg_tables
WHEREtablename = v_tablename AND
 
schemaname='some_schema_children')=0)THEN    IF (v_month=12)    THEN        EXECUTE 'CREATE TABLE
some_schema_children.'||v_tablename||'
(CHECK(f_timestamp>='''||v_year||'-'||v_month||'-01'||''' AND
f_timestamp < '''||v_year+1||'-01-01'')) INHERITS
(some_schema.t_foo);CREATE INDEX
some_schema_children_'||v_tablename||'_timestamp ON
some_schema_children.'||v_tablename||' (f_timestamp)';    ELSE        EXECUTE 'CREATE TABLE
some_schema_children.'||v_tablename||'
(CHECK(f_timestamp>='''||v_year||'-'||v_month||'-01'||''' AND
f_timestamp < '''||v_year||'-'||v_month+1||'-01'')) INHERITS
(some_schema.t_foo);CREATE INDEX
some_schema_children_'||v_tablename||'_timestamp ON
some_schema_children.'||v_tablename||' (f_timestamp)';    END IF;END IF;
EXECUTE 'INSERT INTO some_schema_children.'||v_tablename||' VALUES
('||NEW.f_id_foo||','''||NEW.f_timestamp||''','''||NEW.f_text||''')';RETURN NULL;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER some_schema_insert_foo_triggerBEFORE INSERT ON some_schema.t_fooFOR EACH ROW EXECUTE PROCEDURE
some_schema.foo_insert_trigger();

CREATE OR REPLACE FUNCTION some_schema.foo_update_trigger()
RETURNS TRIGGER AS $$
DECLAREv_tablename varchar(13);v_month integer;v_year integer;
BEGINv_month:=(SELECT date_part from date_part('month', NEW.f_timestamp));v_year:=(SELECT date_part from
date_part('year',NEW.f_timestamp));v_tablename:='t_foo_'||v_year||'_'||v_month;IF ((SELECT COUNT(*) FROM pg_tables
WHEREtablename = v_tablename AND
 
schemaname='some_schema_children')=0)THEN    IF (v_month=12)    THEN        EXECUTE 'CREATE TABLE
some_schema_children.'||v_tablename||'
(CHECK(f_timestamp>='''||v_year||'-'||v_month||'-01'||''' AND
f_timestamp < '''||v_year+1||'-01-01'')) INHERITS
(some_schema.t_foo);CREATE INDEX
some_schema_children_'||v_tablename||'_timestamp ON
some_schema_children.'||v_tablename||' (f_timestamp)';    ELSE        EXECUTE 'CREATE TABLE
some_schema_children.'||v_tablename||'
(CHECK(f_timestamp>='''||v_year||'-'||v_month||'-01'||''' AND
f_timestamp < '''||v_year||'-'||v_month+1||'-01'')) INHERITS
(some_schema.t_foo);CREATE INDEX
some_schema_children_'||v_tablename||'_timestamp ON
some_schema_children.'||v_tablename||' (f_timestamp)';    END IF;END IF;
EXECUTE 'DELETE FROM some_schema.t_foo WHERE f_id_foo='||NEW.f_id_foo;EXECUTE 'INSERT INTO
some_schema_children.'||v_tablename||'VALUES
 
('||NEW.f_id_foo||','''||NEW.f_timestamp||''','''||NEW.f_text||''')';RETURN NULL;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER some_schema_update_foo_triggerBEFORE UPDATE ON some_schema.t_fooFOR EACH ROW EXECUTE PROCEDURE
some_schema.foo_update_trigger();

INSERT INTO some_schema.t_foo (f_timestamp, f_text) VALUES
('2011-06-01', 'test');
UPDATE some_schema.t_foo SET f_timestamp='2011-09-01' WHERE
f_timestamp='2011-06-01';

On Fri, 27 May 2011 12:28:51 -0700
Kevin Crain <kevin.crain1@gmail.com> wrote:

>Is the order of evaluation for the trigger causing this error?

Are you aware that triggers are executed in alphabetical order?

I simply used RAISE to check the order of execution of my triggers:

RAISE NOTICE '% % % %: received %', TG_TABLE_NAME, TG_NAME, TG_WHEN,
TG_OP, NEW;

On 2011-05-27, Kevin Crain <kevin.crain1@gmail.com> wrote:
> I am trying to create a trigger on updates to a table that is
> partitioned.  The child tables are partitioned by month and include
> checks on a timestamp field. 

> However when I try to update an existing record with a
> timestamp that would place it in a child table different from the
> child table it is in I get an error due to the check on the child
> table it is currently in.  My best guess as to what is happening is
> that the trigger is evaluating the check before it evaluates the
> trigger function and thus cannot tell that the update to the original
> table should never take place.  I have included an example below.  The
> error that results is "new row for relation "t_foo_2011_6" violates
> check constraint "t_foo_2011_6_f_timestamp_check""

the problem is the check is running before the trigger.
perhaps you can use a rule instead of a trigger?

-- 
⚂⚃ 100% natural

Can procedural languages be used in rules?  I didn't see any examples
in the documentation that suggested something like this could be done
using rules.

--Kevin Crain

On Mon, May 30, 2011 at 2:21 AM, Jasen Betts <jasen@xnet.co.nz> wrote:
> On 2011-05-27, Kevin Crain <kevin.crain1@gmail.com> wrote:
>> I am trying to create a trigger on updates to a table that is
>> partitioned.  The child tables are partitioned by month and include
>> checks on a timestamp field.
>
>> However when I try to update an existing record with a
>> timestamp that would place it in a child table different from the
>> child table it is in I get an error due to the check on the child
>> table it is currently in.  My best guess as to what is happening is
>> that the trigger is evaluating the check before it evaluates the
>> trigger function and thus cannot tell that the update to the original
>> table should never take place.  I have included an example below.  The
>> error that results is "new row for relation "t_foo_2011_6" violates
>> check constraint "t_foo_2011_6_f_timestamp_check""
>
> the problem is the check is running before the trigger.
> perhaps you can use a rule instead of a trigger?
>
> --
> ⚂⚃ 100% natural
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>

Okay, I figured out what is going on.  Even though I was running the
update on the master table the trigger was not being applied because
it was actually being ran using the child table where the record to be
updated resided.  So the trigger function was being skipped and it was
running as an ordinary update, hence the error.  In order to get this
to work I had to add a trigger for each child table as well to call my
update function trigger.

--Kevin Crain

On Tue, May 31, 2011 at 6:40 AM, Kevin Crain <kevin.crain1@gmail.com> wrote:
> Can procedural languages be used in rules?  I didn't see any examples
> in the documentation that suggested something like this could be done
> using rules.
>
> --Kevin Crain
>
> On Mon, May 30, 2011 at 2:21 AM, Jasen Betts <jasen@xnet.co.nz> wrote:
>> On 2011-05-27, Kevin Crain <kevin.crain1@gmail.com> wrote:
>>> I am trying to create a trigger on updates to a table that is
>>> partitioned.  The child tables are partitioned by month and include
>>> checks on a timestamp field.
>>
>>> However when I try to update an existing record with a
>>> timestamp that would place it in a child table different from the
>>> child table it is in I get an error due to the check on the child
>>> table it is currently in.  My best guess as to what is happening is
>>> that the trigger is evaluating the check before it evaluates the
>>> trigger function and thus cannot tell that the update to the original
>>> table should never take place.  I have included an example below.  The
>>> error that results is "new row for relation "t_foo_2011_6" violates
>>> check constraint "t_foo_2011_6_f_timestamp_check""
>>
>> the problem is the check is running before the trigger.
>> perhaps you can use a rule instead of a trigger?
>>
>> --
>> ⚂⚃ 100% natural
>>
>>
>> --
>> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-sql
>>
>