postgres update an updatedAt field with RULE

Question

I'm trying to set automaticaly an updatedAt field on postgres

Here my dump file:

CREATE TABLE IF NOT EXISTS "Product" (
  "id" uuid PRIMARY KEY,
  "createdAt" timestamp(3) NOT NULL DEFAULT (CURRENT_TIMESTAMP),
  "updatedAt" timestamp(3) NOT NULL DEFAULT (CURRENT_TIMESTAMP),
  "name" TEXT NOT NUll UNIQUE,
  "type" "ProductType" NOT NULL
);


CREATE RULE set_updatedAt AS ON UPDATE TO "Product"
  DO UPDATE "Product" SET "updatedAt" = NOW() WHERE NEW.id = OLD.id AND NEW."updatedAt" = OLD."updatedAt";

On UPDATE, I'm receiving the error "infinite recursion detected in rules for relation "Product""

I do not understand how it's possible whith this kind of condition "NEW.id = OLD.id AND NEW."updatedAt" = OLD."updatedAt""

Any idea, how can I perform a treatment like that?

version : postgres 10

Thanks

Adrian Klaver · Accepted Answer · 2020-11-26 17:40:05Z

2

First don't use rules they will only give you headaches. Second the reason it failed is an UPDATE is both a DELETE(OLD) and an INSERT(NEW). You logic is saying if the OLD and NEW record equal each other for id and updatedAt then UPDATE updatedAt. When you do the UPDATE the OLD and NEW values will always match as you have not actually updated updatedAt yet. This drops you into the infinite recursion of updating because you updated. If you want to do this use a BEFORE UPDATE trigger and function.

edited Nov 26, 2020 at 17:40

answered Nov 26, 2020 at 17:19

Adrian Klaver

20.4k3 gold badges24 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Thomas Dupont · Accepted Answer · 2020-11-26 17:50:02Z

1

Yes,

I had a doubt on the feasibility. You answer my question. I soluced the issue with trigger

CREATE OR REPLACE FUNCTION set_updatedAt() RETURNS trigger AS
$set_updatedAt$
BEGIN
    IF NEW."updatedAt" = OLD."updatedAt" THEN
        NEW."updatedAt" = NOW();
    END IF;
    RETURN NEW;
END;
$set_updatedAt$ LANGUAGE plpgsql;

CREATE TABLE IF NOT EXISTS "Product" (
  "id" uuid PRIMARY KEY,
  "createdAt" timestamp(3) NOT NULL DEFAULT (CURRENT_TIMESTAMP),
  "updatedAt" timestamp(3) NOT NULL DEFAULT (CURRENT_TIMESTAMP),
  "name" TEXT NOT NUll UNIQUE,
  "type" "ProductType" NOT NULL
);

CREATE TRIGGER table_update
BEFORE UPDATE ON "Product"
FOR EACH ROW EXECUTE PROCEDURE set_updatedAt();

answered Nov 26, 2020 at 17:50

Thomas Dupont

4478 silver badges22 bronze badges

1 Comment

Adrian Klaver Over a year ago

If you just want updatedAt reset every time an UPDATE is done the above can be simplified to NEW."updatedAt" = NOW(); without the IF test.

S-Man · Accepted Answer · 2020-11-26 17:29:57Z

0

When you do an UPDATE, your RULE will be triggered. That makes an UPDATE, which triggers your RULE. That makes an UPDATE, which triggers your RULE...

I am not quite sure, but I guess, that your RULE UPDATE will not be persisted until the operation is finished, as in a normal transaction. So, each new RULE call works on the old, non-persisted data. Therefore, every RULE call checks the same condition as before and this creates an infinite loop.

However: Please think about using RULES, anyway

edited Nov 26, 2020 at 17:29

answered Nov 26, 2020 at 17:20

S-Man

24k9 gold badges51 silver badges78 bronze badges

Collectives™ on Stack Overflow

postgres update an updatedAt field with RULE

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related