Transactions in Postgres

#database #postgres

In databases, a transaction is a single unit of logic -- they can potentially consist of multiple operations. An example is transferring a balance from a bank account to another. The transaction needs an amount removed from the first account and then added to the other. You wouldn't want an amount removed from the account and a program failing and the amount not ever adding to the second account. This is referred to as atomicity in databases.

In Postgres, implicit to every single statement is the idea that the statement occurs in a transaction. If we write two simple UPDATE statements, they might look something like this:

UPDATE table1 SET column1 = 100.00
    WHERE id = 3;

UPDATE table1 SET column1 = 100.00
    WHERE id = 4;

However, when any statement such as these UPDATE statements occur, you can think of it happening within a transaction like this:

BEGIN;
UPDATE table1 SET column1 = 100.00
    WHERE id = 3;
COMMIT;

BEGIN;
UPDATE table1 SET column1 = 100.00
    WHERE id = 4;
COMMIT;

A transaction "block" is the set of queries executed between the BEGIN and COMMIT commands. When you don't specify either of these commands, they implicitly surround every command.

We can override this default behavior to specify different transaction blocks using the BEGIN and COMMIT commands. The idea is that we can perform multiple operations on a database all at once. This is useful for times where if any intermediate operations might fail, we don't want the subsequent commands to execute. The entire "transaction" will not occur.

Below, you can see here we actually specified a BEGIN and COMMIT in this script. As a transaction block now, these two will be "committed" to the database and visible at the same time.

BEGIN;
    UPDATE table1 SET column1 = 100.00
        WHERE id = 3;

    UPDATE table1 SET column1 = 50.00
        WHERE id = 4;
COMMIT;

This can also be useful if an API or other service happens to request data in between the first and second UPDATE statement, and we don't want data to be accessed until all of the data is updated. Obviously the latency is really slow for simple UPDATE statements, but for a longer complicated process, this becomes increasingly important.

Another example: if a table is dropped but you don't want the table unviewable until it's repopulated completely:

BEGIN;
    CREATE TABLE table2 (
       column1 datatype(length) column_contraint,
       column2 datatype(length) column_contraint,
    );
    // add some data into table2

    DROP table table1;

    SELECT * INTO table1 FROM table2;

COMMIT;

Rollbacks

To roll back or undo the change of the last transaction block, you can use the ROLLBACK command. This can be extremely useful because it might be really difficult to traverse individual statements and figure out how you modified your tables. Wrapping up everything in a transaction block and then rolling back makes it easy to just undo everything that was in the block.