Обсуждение: Transaction blocks
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
>>>>> 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
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
>>>>> 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
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