Returning change in updated value in PostgreSQL

Question

Suppose that I have two tables, payments and payment_events where payments contains an amount, and payment_events contains a log of changes to the amount.

I receive a request to change the amount by 50. I now want to track both the event, and the payment itself:

UPDATE payments SET amount = amount + 50 WHERE id = 1234
INSERT INTO payment_events (payment_id, changed_amount) VALUES (payment_id, 50);

So far this is easy, but what if there is an additional requirement on the amount, for example, there is max_amount column and amount mustn't exceed this value.

UPDATE payments SET amount = LEAST(max_amount, amount + 50) WHERE id = 1234;
INSERT INTO payment_events (payment_id, changed_amount) VALUES (payment_id, 50);

Suddenly the insert into payment_events could be wrong. If amount is 180 and we tried to add 50 then we should only have an amount change of 20, since the max is 200. Of course I could return the new amount:

UPDATE payments SET amount = LEAST(max_amount, amount + 50)
WHERE id = 1234
RETURNING amount;

Given that I know the previous amount I can simply diff them. But since this operation is not atomic, it is prone to race conditions.

Since RETURNING as far as I can tell can only return actual columns, I can't simply use that to return the diff.

So far the only solution I've come up with is to add an otherwise useless column maybe named previous_amount to payments and do something like this:

UPDATE payments SET
  previous_amount = amount,
  amount = LEAST(max_amount, amount + 50)
WHERE id = 1234
RETURNING previous_amount, amount;

But that seems kind of dumb. Is there a better way of doing this?

I'm doing this as part of an application, so the queries are executed from a Ruby/Sequel app.

Laurenz Albe · Accepted Answer · 2019-02-05 13:55:30Z

3

That's a good application for a trigger:

CREATE FUNCTION after_update_trig() RETURNS trigger
   LANGUAGE plpgsql AS
$$BEGIN
   INSERT INTO payment_events (payment_id, changed_amount)
      VALUES (NEW.payment_id, NEW.amount - OLD.amount);
   RETURN NEW;
END;$$;

CREATE TRIGGER after_update_trig
   AFTER UPDATE ON payments FOR EACH ROW
   EXECUTE PROCEDURE after_update_trig();

If that is not feasible for you, you'll have to first get the old values:

BEGIN;
-- FOR UPDATE prevents concurrent modifications by others
SELECT * FROM payments WHERE id = 1234 FOR UPDATE;
UPDATE payments ... RETURNING *;
INSERT INTO payment_events ...;
COMMIT;

edited Feb 5, 2019 at 13:55

answered Feb 5, 2019 at 13:02

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

2 Comments

jnicklas Over a year ago

That's a good solution in this simplified example, in my actual application I have a bunch of other data that I want to put into the payment_events table (for example which user was responsible for the event), and it seems like it'd be tough to make that work with a purely trigger based solution.

Laurenz Albe Over a year ago

Then you have to go the other way. I have extended the answer.

Branko Radovanovic · Accepted Answer · 2023-11-27 16:47:18Z

This answer comes a bit late, but I'm going to submit it anyway because it might be useful to somebody:

with update_payments as
(
    UPDATE payments 
    SET amount = LEAST(max_amount, amount + 50) 
    WHERE id = 1234
    RETURNING *
)
INSERT INTO payment_events (payment_id, changed_amount)
SELECT payments_new.id, payments_new.amount - payments_old.amount
FROM update_payments payments_new
INNER JOIN payments payments_old ON payments_old.id = payments_new.id;

Why does this work? The RETURNING clause will fetch the content of the row after the update (hence payments_new). However, when selecting from the payments table in the INSERT statement's INNER JOIN, we will fetch the content of the row before the update (hence payments_old). The documentation says this about data-modifying statements in WITH:

All the statements are executed with the same snapshot [...], so they cannot “see” one another's effects on the target tables.[1]

So, in this example, the UPDATE and the INSERT do not work as two consecutive DML statements, but rather as a single, read-consistent DML statement. In fact, the only way to fetch row content modified by the same DML statement is through the RETURNING clause. We can therefore access both the old and the new content, which is particularly useful for logging.

[1] https://www.postgresql.org/docs/current/queries-with.html#QUERIES-WITH-MODIFYING

Collectives™ on Stack Overflow

Returning change in updated value in PostgreSQL

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related