Order of evaluation in triggers for checks on inherited table partitions - Mailing list pgsql-sql

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';