CODEWITHSUNDEEP
sqlpostgresqlconstraints
Damon Snyder
Damon Snyder

Reputation: 1372

How do you ensure that a table is only updated when a new modified_at is greater than the current modified_at in PostgreSQL?

I have a situation where I want to deny an update to a table if the current modified_at column is more recent than the one specified in the update. I attempted to do this with a CHECK constraint, but it has no effect.

Here is an example:

CREATE TABLE test (
    id SERIAL PRIMARY KEY,
    value INTEGER NOT NULL DEFAULT 0,
    modified_at timestamptz DEFAULT NOW()
);                  

ALTER TABLE test ADD CHECK (modified_at >= modified_at);

INSERT INTO test (id, value) VALUES (1, 1);
INSERT 0 1
SELECT * FROM test;
 id | value |          modified_at          
----+-------+-------------------------------
  1 |     1 | 2013-05-30 14:34:37.234456-07

UPDATE test
    SET value = 2, modified_at = NOW() - INTERVAL '1 day'                                                                              
WHERE id = 1;
UPDATE 1
SELECT * FROM test;
 id | value |          modified_at          
----+-------+-------------------------------
  1 |     2 | 2013-05-29 14:35:41.337543-07

This doesn't appear to work as expected. Intuitively I could see this being a problem. How does the planner know that the left hand side should be the new value and the right hand side should be the old?

Knowing this doesn't work, how should this constraint be enforced?

Upvotes: 0

Views: 163

Answers (2)

NewK
NewK

Reputation: 353

You will have to check old modified_date against new modified_date of the updated row, and you can do this using triggers. Set the trigger to run on each row before update and create a function that deals with it, choosing if you want to keep the old modified_date or if you don't want to perform any update at all.

The trigger can be done like this:

CREATE TRIGGER trigger_test
  BEFORE INSERT OR UPDATE
  ON test
  FOR EACH ROW
  EXECUTE PROCEDURE fn_trigger_test();

And the function like this:

CREATE OR REPLACE FUNCTION fn_trigger_test()
  RETURNS trigger AS
$BODY$
  DECLARE
BEGIN
IF (TG_OP = 'UPDATE') THEN
    IF NEW.modified_at<OLD.modified_at THEN

        RAISE EXCEPTION 'Date_modified older than previous';
        /* or to keep the old modified date:
             NEW.modifed_at=OLD.modified_at;
         RETURN NEW; */
    ELSE
        RETURN NEW;
    END IF;
END IF; 

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

Upvotes: 3

Mike Sherrill &#39;Cat Recall&#39;
Mike Sherrill &#39;Cat Recall&#39;

Reputation: 95642

As far as CHECK constraints are concerned, there's no such thing as new values and old values.

PostgreSQL triggers have access to new data. Another fairly common approach is to revoke permissions on the tables, and require all access to take place through stored procedures. Stored procedures can have access to new data, passed through parameters, and stored procs can check values in tables, update additional tables for auditing, etc. See CREATE FUNCTION and plpgsql.

Upvotes: 2

Related Questions