Order of evaluation in triggers for checks on inherited table partitions

From: Kevin Crain <kevin(dot)crain1(at)gmail(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Order of evaluation in triggers for checks on inherited table partitions
Date: 2011-05-27 19:28:51
Message-ID: BANLkTi=ZH6_MCYE=F=pevpK1S72jCDGLVA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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 $$
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';

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Tarlika Elisabeth Schmitz 2011-05-27 19:52:42 Re: Order of evaluation in triggers for checks on inherited table partitions
Previous Message Samuel Gendler 2011-05-27 19:28:22 Re: [SQL] Re: 500KB PDF files for postgresql8.3, which is the most efficient way?