Re: Common case not at all clear

On Sun, Aug 1, 2021 at 11:35 PM Anthony Berglas <anthony(at)berglas(dot)org> wrote:

> I have attached a proposed doc update that makes the problem clearer. I
> think that this is important because if people do not understand it they
> will write buggy code and then blame Postgresql for losing updates, which
> is totally unacceptable. So please do action this. I have tested and
> confirm that the behaviour is as I specify.
>

That really isn't a good solution though...a better one is to modify the
update command to be:

UPDATE products SET quantity_on_hand = qoh - 50 WHERE quantity_on_hand =
qoh;

Or, even better:

SELECT ... last_updated INTO lastupdatetimestamp, ...;

UPDATE products SET quantity_on_hand = qoh - 50 AND last_updated =
lastupdatetimestamp;

Then the application needs to simply check for a zero record update and, if
that happens, decide how it wants to deal with the fact that the data has
changed out from under it.

This is superior to waiting an indeterminate amount of time holding a FOR
UPDATE lock in an open transaction.

I would still expand on the FOR UPDATE option as you suggest.

This is still just discussion though, someone will need to convert this
into a proper doc patch that can be built, added to the commitfest,
reviewed, and ultimately committed. IMO it is not worth going to the
trouble of making this all HTML-friendly as your patch did. Just stick to
plain text discussion in the email body if you aren't going to write a
patch in the sgml source language and present it as a diff.

David J.