CODEWITHSUNDEEP
sqlpostgresql
Thomas Dupont
Thomas Dupont

Reputation: 427

postgres update an updatedAt field with RULE

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

Upvotes: 2

Views: 3941

Answers (3)

Thomas Dupont
Thomas Dupont

Reputation: 427

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();

Upvotes: 1

Adrian Klaver
Adrian Klaver

Reputation: 19664

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.

Upvotes: 2

S-Man
S-Man

Reputation: 23726

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

Upvotes: 0

Related Questions