Can I rely on it that every single update statement in postgresql is atomic?

Question

I read some materials before saying that each update statement in postgresql is atomic.

For example,

update set column_1 = column_1 + 100 where column_2 = 10;

Even though I have multiple processes calling the update simultaneously, I can rely on it that they will happen in sequence because each update is atomic behind the scene and the "read_modify_write" cycle is encapsulated in a bundle.

However, what if the update statement looks like the following:

update set column_1 = myFunction(column_1) where column_2 = 10;

Here, myFunction() is a stored procedure created by me.In this function, I will apply different math operations to column_1 depending on its amount. Something like:

    if(column_1 < 10):
            // do something
    else if (column_1 < 20):
            // do something
    else
            // do something

In this case, when the single update statement contains self-defined function, does it remain atomic?

You can avoid the uncertainty by putting it in a transaction. — Schwern, Feb 28 '19 at 21:02
@Schwern But which block should I transactionize? around the update statement? like BEGIN; update set column_1 = myFunction(column_1) where column_2 = 10; COMMIT;? — Jane.L, Feb 28 '19 at 21:05
@JaneL The part that needs to be atomic. That looks like the `update`, yes. That will include anything done by the `update` such as subqueries and functions. — Schwern, Feb 28 '19 at 21:07
@Schwern I thought about this solution before, but an article worried me. https://blog.2ndquadrant.com/postgresql-anti-patterns-read-modify-write-cycles/ In the ` Don’t transactions prevent this? ` section, it says that even BEGIN;/COMMIT; cannot prevent read-modify-write concurrency races. And I am not sure how that happened. — Jane.L, Feb 28 '19 at 21:15
A single statement is always atomic. It always sees a consistent snapshot of the database of the moment when the statement began - including any function that is used inside the statement or sub-queries that it may use. — , Feb 28 '19 at 21:17
@Jane.L That article has some very good insights, but none of them apply here. You've got a single statement. Your function has no extra queries, it's just doing math. — Schwern, Feb 28 '19 at 21:45
I think the key point is that the `UPDATE` locks the row before reading it. In other words, it behaves like the `SELECT ... FOR UPDATE` solution from the article you referenced. — Nick Barnes, Feb 28 '19 at 22:38

score 4 · Accepted Answer · answered Feb 28 '19 at 21:53

OK, @Schwern's knowledge of Perl may well be world class but as regards PostgreSQL transactions, I can correct him :-)

Every statement in PostgreSQL is executed within a transaction, either an explicit one you BEGIN/COMMIT yourself or an implicit one wrapping the statement. For the duration of a statement you will see a stable view of the entire database.

If you write myFunction as an in-database custom function in pl/pgsql or some such then it too will be in the same transaction as the statement that calls it. If it doesn't run its own queries, just operates on its parameters then you don't need to think any further.

If you are reading from tables within your function then you will need a passing familiarity with transaction isolation levels. In particular, make sure you understand what "read committed" implies about seeing other processes' activities.

The blog article you refer to is discussing performing operations outside of the database. The solution it proposes is exactly what you are asking about - an atomic update.

I'll be the first to admit, I'm not a Postgres expert. Any feedback on my answer? — Schwern, Feb 28 '19 at 22:08

score 0 · Answer 2 · answered Feb 28 '19 at 21:53

The update, subqueries, and queries in function calls should all see a consistent view of the data.

From Chapter 13. Concurrency Control.

Internally, data consistency is maintained by using a multiversion model (Multiversion Concurrency Control, MVCC). This means each SQL statement sees a snapshot of data (a database version) as it was some time ago, regardless of the current state of the underlying data. This prevents statements from viewing inconsistent data produced by concurrent transactions performing updates on the same data rows, providing transaction isolation for each database session.

What that means, I believe, is for each statement Postgres keeps a version of the data. Each statement sees a consistent version of the database for the entirety of its run. That includes sub-queries and function calls.

This doesn't mean you don't have to think about concurrency. It just means that update will see consistent data.

Can I rely on it that every single update statement in postgresql is atomic?

2 Answers2