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 $$ DECLARE v_tablename varchar(13); v_month integer; v_year integer; BEGIN v_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 WHERE tablename = 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_trigger BEFORE INSERT ON some_schema.t_foo FOR EACH ROW EXECUTE PROCEDURE some_schema.foo_insert_trigger(); CREATE OR REPLACE FUNCTION some_schema.foo_update_trigger() RETURNS TRIGGER AS $$ DECLARE v_tablename varchar(13); v_month integer; v_year integer; BEGIN v_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 WHERE tablename = 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_trigger BEFORE UPDATE ON some_schema.t_foo FOR 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'; -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql