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.
