Generated UUIDs behavior in postgres INSERT rule compared to the UPDATE rule

Question

I have a postgres database with a single table. The primary key of this table is a generated UUID. I am trying to add a logging table to this database such that whenever a row is added or deleted, the logging table gets an entry. My table has the following structure

CREATE TABLE configuration (
     id    uuid NOT NULL DEFAULT uuid_generate_v4(),
     name  text,
     data  json 
);

My logging table has the following structure

CREATE TABLE configuration_log (
    configuration_id         uuid,
    new_configuration_data   json,
    old_configuration_data   json,
    "user"                   text,
    time                     timestamp
);

I have added the following rules:

CREATE OR REPLACE RULE log_configuration_insert AS ON INSERT TO "configuration"
       DO INSERT INTO configuration_log VALUES (
          NEW.id,
          NEW.data,
          '{}',
          current_user,
          current_timestamp
       );

CREATE OR REPLACE RULE log_configuration_update AS ON UPDATE TO "configuration"
      WHERE NEW.data::json::text != OLD.data::json::text
      DO INSERT INTO configuration_log VALUES (
          NEW.id,
          NEW.data,
          OLD.data,
          current_user,
          current_timestamp
      );

Now, if I insert a value in the configuration table, the UUID in the configuration table and the configuration_log table are different. For example, the insert query

INSERT INTO configuration (name, data) 
       VALUES ('test', '{"property1":"value1"}')

The result is this... the UUID is c2b6ca9b-1771-404d-baae-ae2ec69785ac in the configuration table whereas in the configuration_log table the result is this... the UUID id 16109caa-dddc-4959-8054-0b9df6417406

However, the update rule works as expected. So if I write an update query as

UPDATE "configuration" 
    SET "data" = '{"property1":"abcd"}' 
    WHERE "id" = 'c2b6ca9b-1771-404d-baae-ae2ec69785ac';

The configuration_log table gets the correct UUID as seen here i.e. c2b6ca9b-1771-404d-baae-ae2ec69785ac

I am using NEW.id in both the rules so I was expecting the same behavior. Can anyone point out what I might be doing wrong here?

Thanks

Answer 1

This is another good example why rules should be avoided

Quote from the manual:

For any reference to NEW, the target list of the original query is searched for a corresponding entry. If found, that entry's expression replaces the reference.

So NEW.id is replaced with uuid_generate_v4() which explains why you are seeing a different value.

You should rewrite this to a trigger.

---

Btw: using jsonb is preferred over json, then you can also get rid of the (essentially incorrect) cast of the json column to text to compare the content.