PostgreSQL — Transaction

What is a transaction in PostgreSQL?

What is a transaction in PostgreSQL?

In PostgreSQL, a transaction is a single unit of work that groups a set of tasks into one cohesive execution. Each transaction begins with a specific operation and concludes when all included tasks are completed. If any task fails, the entire transaction fails, ensuring that it has only two possible outcomes: success or failure.

Upon success, all data modifications made during the transaction are committed and become a permanent part of the database. Conversely, if the transaction encounters errors and must be canceled or rolled back, all data modifications are erased, maintaining the integrity and consistency of the database.

Transaction ensures ACID compliance in PostgreSQL Understanding transactions is essential for effective database management in PostgreSQL. In this article, we’ll explore how to use transactions in your daily SQL operations, making your workflows more reliable and efficient. Here's how to use transaction control commands in your daily SQL operations.

Transaction Control Commands

Figure 1: The mishap

Imagine you want to run:

postgres=> DELETE FROM users WHERE age < 18;

But you accidentally type:

postgres=> DELETE FROM users;
DELETE 1970284

The meme in Figure 1 perfectly captures the horror of this mistake. 😨 This command would erase all user data from the 'users' table, deleting 1,970,284 rows and causing significant disruption. As humans, we’re bound to slip up now and then. However, this doesn’t have to be a disaster if you follow some simple steps when running queries in production. This is where Transaction Control Commands in PostgreSQL come to the rescue 😎 , providing a safety net for running risky queries.

In PostgreSQL, we have three main commands for transaction control:

• BEGIN: Starts a new transaction.

• COMMIT: Saves the changes made in the current transaction to the disk and ends the transaction.

• ROLLBACK: Discards all changes made in the current transaction, leaving the database unchanged.

Here’s a basic outline of how to use a transaction:

BEGIN; 
-- SQL Statement 1 
-- SQL Statement 2
-- ...
-- SQL Statement n 
[COMMIT; if happy else ROLLBACK;]

Now, let’s see this in action with our earlier example.

Start with:

postgres=> BEGIN; 
postgres=*> DELETE FROM users;
DELETE 1970284

**Panic sets in**. But don’t worry, you made this change within a transaction.

To undo the mistake, simply run:

postgres=*> ROLLBACK;

This ends the current transaction and undoes the deletion, saving your day.

To execute the correct command, run:

postgres=> BEGIN; 
postgres=*> DELETE FROM users WHERE age < 18;
DELETE 101

Remember, you can view the results of a transaction only within that transaction session. If a colleague wants to verify the changes before you commit, you’ll need to show them the results from your current psql session.

Once you’re satisfied with the results, finalize the changes with:

postgres=*> COMMIT;

And just like that, you’ve successfully used transaction commands to prevent a major mishap!

Note: If your usual psql prompt looks like postgres=>, it will change to postgres=* when you’re in a transaction and will stay that way until the transaction ends. Keep an eye on this indicator to ensure you don’t forget to start a transaction when running queries in production. Make it a habit to check and avoid costly mistakes!

Conclusion

By understanding and utilizing transaction control commands in PostgreSQL, you can safeguard your database operations against human errors and unintended consequences. Remember to start transactions with BEGIN, verify your changes, and then either COMMIT to save or ROLLBACK to discard them.

Keeping an eye on the psql prompt ensures you’re aware of your transaction status, making your database management more reliable and efficient. Practice these steps regularly to maintain data integrity and avoid costly mistakes in production.