PostgreSQL Newbies - Transaction blocks

Author

Transaction blocks

Alexander Kotelnikov

2005-08-10, 11:25 am

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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Alexander Kotelnikov

2005-08-10, 11:25 am

>>>>> On Wed, 10 Aug 2005 17:37:28 +0400[color=darkred]

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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Michael Fuhr

2005-08-10, 8:25 pm

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/...-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/.../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/...-savepoint.html
http://www.postgresql.org/docs/8.0/...-ERROR-TRAPPING

--
Michael Fuhr

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Alexander Kotelnikov

2005-08-11, 7:24 am

>>>>> On Wed, 10 Aug 2005 18:25:57 -0600[color=darkred]
MF>
MF> On Wed, Aug 10, 2005 at 07:13:23PM +0400, Alexander Kotelnikov wrote:[color=darkred
]
MF>
MF> This might be what you read:
MF>
MF> http://www.postgresql.org/docs/8.0/...-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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Michael Fuhr

2005-08-11, 1:27 pm

On Thu, Aug 11, 2005 at 04:03:45PM +0400, Alexander Kotelnikov wrote:
> MF>
> MF> http://www.postgresql.org/docs/8.0/...-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/...ansactions.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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Sponsored Links