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: 
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](https://www.postgresql.org/docs/11/transaction-iso.html). 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.
The `update`, subqueries, and queries in function calls should all see a consistent view of the data.

From [Chapter 13. Concurrency Control](https://www.postgresql.org/docs/10/mvcc-intro.html).

> 
> 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.