10 ms·

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 wou

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#XACT-SERIALIZABLE https://www.postgresql.org/docs/current/transaction-iso.html...

jasonhansel 5y ago
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.
- lucian1900 5y ago
  Not only is that slow, but it raises errors programs aren’t currently handing. It would be incompatible to change.
  - belter 5y ago
    As opposed to the current default, that also raises errors programmers aren’t currently handing :-)
    - lucian1900 5y ago
      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 5y ago
  ANSI default is READ COMMITTED I believe.
- stubish 5y ago
  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 5y ago
    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.
deleted 5y ago
[deleted]

jasonhansel 5y ago

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.

lucian1900 5y ago
Not only is that slow, but it raises errors programs aren’t currently handing. It would be incompatible to change.
- belter 5y ago
  As opposed to the current default, that also raises errors programmers aren’t currently handing :-)
  - lucian1900 5y ago
    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 5y ago
ANSI default is READ COMMITTED I believe.
stubish 5y ago
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 5y ago
  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.

deleted 5y ago

[deleted]