Hi all,
Could anybody explain, what happens first: constraint check or before-trigger execution?
I have a table, partitioned by date:
CREATE TABLE foo
(
unid text NOT NULL,
logtime timestamp with time zone NOT NULL,
size integer,
CONSTRAINT foo_pkey PRIMARY KEY (unid)
);
-- There is an before-insert trigger which works perfectly, creates a new monthly partition if neccessary and inserts new record into the partition.
-- Here is how partitions look like:
CREATE TABLE foo_2018_01
(
CONSTRAINT foo_2018_01_pkey PRIMARY KEY (unid),
CONSTRAINT foo_2018_01_logtime_check CHECK (logtime >= '2018-01-01 00:00:00+00'::timestamp with time zone AND logtime < '2018-02-01 00:00:00+00'::timestamp with time zone)
)
INHERITS (foo);
I cannot change anything in the application, as it's proprietary. So I had to do partitioning myself with a trigger.
Now there's a new problem. It looks like the application sometimes do UPDATEs to the "logtime" column, which I use for partitioning.
So the application can do something like UPDATE foo SET logtime='2017-12-01 00:00:00+00', size=5 WHERE unid='blahblablah', althrough this record had logtime='2018-01-18 00:00:00+00' and was in different partition.
In such case, I can see the error (and transaction aborts):
ERROR: new row for relation "foo_2018_01" violates check constraint "foo_2018_01_logtime_check"
For business logic, it wouldn't be critical if I forbid/undo/replace modification of logtime column. But other columns must be updated by the application when neccessary.
Now I need to ignore new value for "logtime" column for every UPDATE to table "foo".
Here is my idea:
CREATE OR REPLACE FUNCTION logtime_update_trigger() RETURNS trigger AS
$BODY$
BEGIN
IF (NEW.logtime != OLD.logtime) THEN
NEW.logtime := OLD.logtime;
END IF;
RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;
CREATE TRIGGER trg_foo_update BEFORE UPDATE ON foo
FOR EACH ROW EXECUTE PROCEDURE logtime_update_trigger();
Unfortunately, it seems like this trigger is not even being executed and I still get the same error:
ERROR: new row for relation "foo_2018_01" violates check constraint "foo_2018_01_logtime_check"
I suppose that's because contraint check is performed before the trigger is fired? Is there any workarounds here?
I also tried to create a rule:
CREATE OR REPLACE RULE test_rule AS ON UPDATE TO foo
WHERE new.logtime <> old.logtime DO INSTEAD
UPDATE foo SET size = new.size WHERE foo.unid = old.unid AND foo.logtime = old.logtime;
But then I get recursion error:
ERROR: infinite recursion detected in rules for relation "foo"
Possibly because the recursion analysis doesn't take WHERE condition into account.
Any help would be greatly appreciated.
PostgreSQL version: 9.0.1 on CentOS 5 i686.
Best regards, Nikolay Karikh.
