Re: Transaction blocks

On Wed, Aug 10, 2005 at 07:13:23PM +0400, Alexander Kotelnikov wrote:
> I believe, any procedure language function is a transaction block, and
> I think, I even read this somewere in docs, but can not find where
> now, so, is it true, for all languages (SQL, PL/pgsql, perl etc)?

This might be what you read:

http://www.postgresql.org/docs/8.0/static/plpgsql-structure.html

"Functions and trigger procedures are always executed within a
transaction established by an outer query -- they cannot start or
commit that transaction, since there would be no context for them
to execute in."

> If yes, is it an ordinal transaction, just like one started with
> BEGIN?

http://www.postgresql.org/docs/8.0/static/sql-begin.html

"By default (without BEGIN), PostgreSQL executes transactions in
"autocommit" mode, that is, each statement is executed in its own
transaction and a commit is implicitly performed at the end of the
statement (if execution was successful, otherwise a rollback is
done)."

> there is no nested transactions in postgres, but is it possible to
> start a transaction in a procedure?

PostgreSQL 8.0 and later have savepoints, which, in some procedural
languages (e.g., PL/pgSQL), allow you to use an exception handler
to roll back to a certain point.

http://www.postgresql.org/docs/8.0/static/sql-savepoint.html
http://www.postgresql.org/docs/8.0/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING

--
Michael Fuhr