The Art of PostgreSQL/

...

/

Data Manipulation and Concurrency Control: Update

Data Manipulation and Concurrency Control: Update

Learn how to use update statements for data manipulation and concurrency control.

We'll cover the following...

The SQL update statement

The SQL update statement is used to replace existing values in the database. Its most important aspect lies in its concurrency behavior, as it allows replacing existing values while other users are concurrently working with the database.

In PostgreSQL, all the concurrency features are based on Multiversion Concurrency Control (MVCC), and in the case of the update statement, it means that internally PostgreSQL is doing both an insert of the new data and a delete of the old. PostgreSQL system columns xmin and xmax allow visibility tracking of the rows so that concurrent statements have a consistent snapshot of the server’s dataset at all times.

As row locking is done per tuple in PostgreSQL, an update statement only ever blocks another update, delete, or select for update statement that targets the same row(s).

Assigning names

We created some users without a nickname before, and maybe it’s time to remedy that by assigning them their uname as a nickname for now.

Press + to interact
begin;
   update tweet.users
      set nickname = 'Robin Goodfellow'
    where userid = 17 and uname = 'Puck'
returning users.*;
commit;

Here we pick the 17 ID from the table after a manual lookup. The idea is to show how to update fields in a single tuple from a primary key lookup. In many cases, our application’s code has fetched the id previously and injected it in the update query in much the same way.

And thanks to the returning clause, we get to see what we’ve done:

 userid │ uname │     nickname     │         bio          │ picture 
════════╪═══════╪══════════════════╪══════════════════════╪═════════
     17 │ Puck  │ Robin Goodfellow │ or Robin Goodfellow. │ ¤
(1 row)

As you can see in the previous query, not only do we use the primary key field, but as it is a synthetic key, we also added the real value we’re interested in. Should we have ...