ne much-lauded feature of PostgreSQL is transactions. Transactions in a database help prevent accidental data loss or misrepresentation.
For example, let's say you want to delete records from a table. In PostgreSQL the command is:
template1=# DELETE FROM foo;
However, the above command will delete all of the records in the table. This is probably not what you want, andunless you were using transactionsthe only way to get the data back would be from a backup. Using transactions, getting the data back is simple. The command sequence would be:
DELETE FROM foo;
statement causes the database to initiate a transaction, so as soon as you realize that the preceding command failed to include a WHERE
clause, thus deleting the entire table, you could rollback the transaction.
DELETE FROM foo;
There is a drawback to using transactions in the current version of PostgreSQL. If any error occurs in the statements within your transaction, you must
issue a rollback. A rollback is issued by executing the rollback command within the transaction and must be executed before any other commands will be processed by your specific connection. Once a rollback is executed you must begin the transaction again and restate the commands in a manner that will not cause an error. This rule includes both user errors, such as deleting all records in a table, and syntactical errors, such as trying to select from a table that does not exist. For example:
UPDATE foo SET bar = (SELECT count(*) FROM baz));
INSERT INTO foo (column1) SELECT column2 FROM bar;
ERROR: relation "bar" does not exist
CREATE TABLE bar (column1 text, column2 float);
ERROR: current transaction is aborted,
commands ignored until end of transaction block
Because of the error, you will have to rollback and all your current work will be lost. This particular aspect of transactions with PostgreSQL is particularly irritating during testing and debugging.