[whitexn--g28h]

Seems like this problem would be solved by a serializable transaction isolation level without the need for any locking. If during the transaction the select would return a different result the transaction will fail and can be retried. A select sum() is part of the example for serializable transaction isolation. https://www.postgresql.org/docs/current/transaction-iso.html...

[jasonhansel]

Exactly. IMHO Postgres (and other SQL DBs) should consider making SERIALIZABLE the default (as is specified in ANSI SQL). Otherwise people try to be clever.

[stubish]

A program using SERIALIZABLE transaction isolation is required to handle the inevitable errors on commit and retry the transaction or roll back the operation. In practice, developers rarely do and you end up with webapps spitting out 50x errors under load and people blaming the database because they didn't read the fine print. Which is why some projects like psycopg which did default to SERIALIZABLE transaction isolation ended up switching to READ COMMITTED as a more sensible default. It is rare that people actually need SERIALIZABLE and they rarely want to pay the performance or development costs.

[skyde]

You say it is rare that people need serializable. But I would say majority of code that handle money need to use explicit lock or serializable otherwise they can easily be exploited by hacker to get thing for free.

[lucian1900]

Not only is that slow, but it raises errors programs aren’t currently handing. It would be incompatible to change.

[belter]

As opposed to the current default, that also raises errors programmers aren’t currently handing :-)

[lucian1900]

The current default has effect anomalies, but doesn't raise serialization errors.

It's possible to write software that relies on READ COMMITTED and behaves correctly.

[hgdfadsfwer]

ANSI default is READ COMMITTED I believe.