PostgreSQL Transaction

In this tutorial, you will learn how to handle PostgreSQL transactions using the BEGIN, COMMIT, and ROLLBACK statements.

Transaction begins with the start of a transaction in database systems and ends when the transaction ends.

These operations may include creating a database, deleting data, or updating data.

Let’s give an ATM example for better understanding.

You go to the ATM and the moment you insert your card into the ATM to withdraw money from the ATM, a transaction begins on your behalf, this transaction is called “BEGIN” by the database.

When you specify the amount and withdraw the money, your transaction ends, the card is delivered to you, and when the card is delivered to you, the transaction that started in your name ends. This transaction is called “COMMIT” in the database section.

You entered your card’s password, the transaction started in your name, but if you give up the transaction, the transaction that started for you ends and your card is returned to you. This transaction is defined as “ROLLBACK” in the database section.

Every transaction successfully defined in databases starts with begin and ends with commit. This is also called AutoCommit.

When you make a transaction without AUTO COMMIT
(select, insert, update, delete, create, drop, delete….) It starts with begin and ends with commit, and if you want to undo the action, rollback is performed.

Let’s start the transaction with begin in the example below.

When we run the query above, we see that our table has been created by looking at the query result.

Our table has been created, but when we go to the Tables tab and check, we cannot see that our table has been created.

The reason for this is that we opened the transaction with the begin command but did not close it, so it is waiting for a commit or rollback from us.

Let’s end our relevant transaction by committing and check if our table is created.

After committing the table in the window where we created it, we check it by clicking refresh on Tables and see that it has been created.

If a commit was made, this table would be considered as if it had never been created and the transaction would be terminated.

We understand commit and rollback, but what is savepoint?

Let’s say you made three transactions in the savepoint command transaction, but you want to return to the second transaction, you can return to that transaction by typing savepoint_name.

Let’s reinforce it with an example.

Above, we created a table named trial in a single transaction and added the data in this transaction.

After making two inserts, we made a savepoint and added another insert sentence. After that, since we did not want to make the last insert, we came to the save point we created using the “rollback to point” command. When we query, we see that only the first two data have been added.

We saw the data with Select*from, but we still do not have a table named trial. This is because we did not commit, so we returned to the savepoint with rollback, but we need to commit in order for it to be written in the other table creation and insert sentences.

After the commit, our table is created and we see the data added.