PSCS Wiki

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
======Running database updates within a transaction======

This is a trick which database experts use, because they have learned (often the hard way) that a simple mistype can trash a database...

Before running any database query which can alter the database (UPDATE, DELETE, INSERT, DROP, CREATE, ALTER) begin a database transaction using the ''BEGIN'' command;

Then, run your update query.

Then, check the results.

Then, either use the ''ROLLBACK'' command if you aren't happy with the results, or use the ''COMMIT'' command if you are happy with the results.

For instance, if you are wanting to delete a widget from the database you could just use the command ''DELETE FROM widgets WHERE id=27;''. The problem is that it's not uncommon for people to just forget to type the 'WHERE id=27' part, and manage to delete all their widgets.

So, to play safe, you would issue the commands:

  BEGIN;
  DELETE FROM widgets WHERE id=27;

Now, you can check the results of the query. The PostgreSQL **psql** program will display the number of rows deleted from the database. So if the response says ''DELETE 1'', you know you have just deleted one row, so you can type ''COMMIT;'' to commit the transaction. However, if the response says ''DELETE 57632'' rather than panicking as you would do if you weren't doing this in a transaction, you can simply type ''ROLLBACK;'' to undo what you have just done.

After doing the update query, you can also issue SELECT queries or other update queries within the same transaction to check database data or perform more updates. (Note that if you have an error, you will have to ROLLBACK and start again). 

Certain other database actions may be queued while you are in the transaction, so try not to make it last too long.
PSCS Wiki

User Tools

Site Tools

Page Tools
