Your audit log is probably lying to you. Postgres 18 fixes it in one statement.

In the given scenario, there are several key facts related to the way audit logs work in PostgreSQL and how the upcoming version 18 improves upon it:

1. Traditionally, audit logging involved multiple statements: a read operation, a write operation, and then an insert into the audit log table with the before and after values. This approach led to issues when multiple workers read the same balance, wrote their own new balance, and logged the operation, resulting in lost updates and inconsistent audit logs.

2. In PostgreSQL 18, a new approach was introduced to address this problem. By using the `RETURNING` clause within an UPDATE statement, you can retrieve the old and new values in a single statement, eliminating the gap between the read and write operations. This ensures that the audit log accurately reflects the changes made.

3. The new syntax allows you to specify the row before and after the update directly in the `RETURNING` clause. For example, `UPDATE accounts SET balance = balance - 10 WHERE id = 1 RETURNING old.balance AS was, new.balance AS now;` returns the old balance as "was" and the new balance as "now" in a single operation.

4. This change not only resolves the lost update problem but also provides a more efficient and comprehensive way to generate audit logs. With a single statement, the application can retrieve the necessary information and insert it into the audit log table without the need for additional triggers or complex logic.

5. Furthermore, the `RETURNING` clause can be used in combination with other statements like `INSERT` or `UPSERT`. When using an `UPSERT` statement, which inserts a row if it doesn't exist or updates it if it does, the `RETURNING` clause can indicate whether a row was inserted or updated. This provides valuable information about the outcome of the operation.

6. The article highlights a practical example where ten concurrent workers each perform a withdrawal from a single account. Before PostgreSQL 18, the audit log would often be inconsistent, with multiple workers logging the same withdrawal. However, with the new single statement approach, the audit log accurately reflects the sequence of updates, ensuring data integrity and consistency.

In summary, PostgreSQL 18 introduces a significant improvement in handling audit logs by allowing you to retrieve old and new values in a single statement using the `RETURNING` clause. This eliminates the lost update problem and provides a more reliable and efficient way to generate accurate audit logs, ensuring that the system's changes can be tracked and audited correctly.