Re: Complex transactions without using plPgSQL Functions. It is possible?

On Wed, Mar 7, 2012 at 6:30 AM, Andre Lopes <lopes80andre(at)gmail(dot)com> wrote:
> I'm writing a web application that uses PostgreSQL and I need to do
> some operations where I read/write to 3 tables in the same
> transaction.

Is what you're looking for simply the "begin transaction"[1] and
"commit"[2] commands? With those, you can write your code to do
whatever it likes, and it'll still be all one transaction.

With some caveats, of course, but if all you're doing is INSERT /
DELETE / UPDATE, you'll be fully protected by the transaction
boundaries (eg if your script dies unexpectedly in the middle, the
whole thing will be rolled back, all those usual safeties).

As a side point: Is it possible to disable Postgres's default
autocommit behavior and have it automatically open a transaction on
connection and after commit/rollback? That's what I grew up on with
DB2 - you do some work, then you commit, then you do more work, then
you commit, but never have to say "and begin a transaction too".

Chris Angelico
[1] http://www.postgresql.org/docs/current/static/sql-begin.html
[2] http://www.postgresql.org/docs/current/static/sql-commit.html