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, and—unless you were using transactions—the 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:

BEGIN;
       DELETE FROM foo;
       DELETE 50

The BEGIN 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.

BEGIN;
       DELETE FROM foo;
       DELETE 50
    ROLLBACK; 

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:

BEGIN;
       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.