Partitioning constraints vs before-trigger

[Nikolay <nitronick2005@xxxxxxxxx>]

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.