Thread: Transaction blocks

Transaction blocks

From
Alexander Kotelnikov
Date:
Hello.

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)?

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

Then, SET TRANSACTION can be used inside a procedure?

Thanks,
--
Alexander Kotelnikov
Saint-Petersburg, Russia

Re: Transaction blocks

From
Alexander Kotelnikov
Date:
>>>>> On Wed, 10 Aug 2005 17:37:28 +0400
>>>>> "AK" == Alexander Kotelnikov <sacha@myxomop.com> wrote:
AK>
AK> I believe, any procedure language function is a transaction block, and
AK> I think, I even read this somewere in docs, but can not find where
AK> now, so, is it true, for all languages (SQL, PL/pgsql, perl etc)?
AK>
AK> If yes, is it an ordinal transaction, just like one started with
AK> BEGIN?

And I forgot to ask:
there is no nested transactions in postgres, but is it possible to
start a transaction in a procedure?
--
Alexander Kotelnikov
Saint-Petersburg, Russia

Re: Transaction blocks

From
Michael Fuhr
Date:
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

Re: Transaction blocks

From
Alexander Kotelnikov
Date:
>>>>> On Wed, 10 Aug 2005 18:25:57 -0600
>>>>> "MF" == Michael Fuhr <mike@fuhr.org> wrote:
MF>
MF> 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)?
MF>
MF> This might be what you read:
MF>
MF> http://www.postgresql.org/docs/8.0/static/plpgsql-structure.html
MF>
MF> "Functions and trigger procedures are always executed within a
MF> transaction established by an outer query -- they cannot start or
MF> commit that transaction, since there would be no context for them
MF> to execute in."

Oops, I missed this, thanks. But what about PL's other than pgsql?

--
Alexander Kotelnikov
Saint-Petersburg, Russia

Re: Transaction blocks

From
Michael Fuhr
Date:
On Thu, Aug 11, 2005 at 04:03:45PM +0400, Alexander Kotelnikov wrote:
> >>>>> On Wed, 10 Aug 2005 18:25:57 -0600
> >>>>> "MF" == Michael Fuhr <mike@fuhr.org> wrote:
> MF>
> MF> http://www.postgresql.org/docs/8.0/static/plpgsql-structure.html
> MF>
> MF> "Functions and trigger procedures are always executed within a
> MF> transaction established by an outer query -- they cannot start or
> MF> commit that transaction, since there would be no context for them
> MF> to execute in."
>
> Oops, I missed this, thanks. But what about PL's other than pgsql?

The statement itself (i.e., the outer query) has transaction
semantics, regardless of what language is used for the functions
it calls.

http://www.postgresql.org/docs/8.0/static/tutorial-transactions.html

"PostgreSQL actually treats every SQL statement as being executed
within a transaction.  If you do not issue a BEGIN command, then
each individual statement has an implicit BEGIN and (if successful)
COMMIT wrapped around it.  A group of statements surrounded by BEGIN
and COMMIT is sometimes called a _transaction block_."

--
Michael Fuhr