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?

Answer 1

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.