pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: transaction in plpgsql
4+ messages / 3 participants
[nested] [flat]
* Re: transaction in plpgsql
@ 2002-06-28 06:08 Josh Berkus <josh@agliodbs.com>
parent: Joseph Syjuco <joseph@asti.dost.gov.ph>
1 sibling, 0 replies; 4+ messages in thread
From: Josh Berkus @ 2002-06-28 06:08 UTC (permalink / raw)
To: Joseph Syjuco <joseph@asti.dost.gov.ph>; pgsql-sql
Joe,
> how can i implement transactions inside my plpgsql functions
You can't.
Each PL/pgSQL function is automatically treated as a single transaction
by Postgres. As such, the entire function either succeeds or fails.
To manage a transaction *inside* the PL/pgSQL function, Postgres would
have to support nested transactions ... which we currently don't.
Nested transactions and MVCC are somewhat of a tangle to reconcile,
and we don't expect a solution until Postgres 8.0 (if then).
-Josh Berkus
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: transaction in plpgsql
@ 2002-06-28 06:53 Nils Höglund <nils@naqua.se>
parent: Joseph Syjuco <joseph@asti.dost.gov.ph>
1 sibling, 0 replies; 4+ messages in thread
From: Nils Höglund @ 2002-06-28 06:53 UTC (permalink / raw)
To: Joseph Syjuco <joseph@asti.dost.gov.ph>; +Cc: pgsql-sql
> how can i implement transactions inside my plpgsql functions
I would guess that this is not possible since postgres don't support
nested transactions(?).
--
/Nils Höglund, Naqua KB
E-mail: nils@naqua.se
Web: http://www.naqua.se/
Home Phone: +46 (0)18 30 09 19
Cellular Phone: +46 (0)709 91 97 65
Address: Nya Valsätrav. 26 B
SE-756 46
Uppsala, Sweden
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: transaction in plpgsql
@ 2002-06-28 15:36 Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 4+ messages in thread
From: Josh Berkus @ 2002-06-28 15:36 UTC (permalink / raw)
To: Joseph Syjuco <joseph@asti.dost.gov.ph>; pgsql-sql
Joseph,
> so does that mean that if i call function 2 from function 1 and
> function
> 2 fails than function 1 and function 2 rolls back
>
> hopefully the answer is yes
That's correct. To diagram:
Function 1 calls
|
V
Function 2 calls
|
V
Function 3, which fails
|
V
Function 3 is rolled back
|
V
Function 2 is rolled back
|
V
Function 1 is rolled back
While this automatic tranactioning and "All succeed or fail together"
functionality is sometimes useful, its often also a major annoyance.
Changing it is on the "todo" list, but poses some major technical
challenges.
-Josh Berkus
^ permalink raw reply [nested|flat] 4+ messages in thread
* transaction in plpgsql
@ 2002-06-28 16:58 Joseph Syjuco <joseph@asti.dost.gov.ph>
0 siblings, 2 replies; 4+ messages in thread
From: Joseph Syjuco @ 2002-06-28 16:58 UTC (permalink / raw)
To: pgsql-sql
how can i implement transactions inside my plpgsql functions
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2002-06-28 16:58 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-06-28 15:36 Re: transaction in plpgsql Josh Berkus <josh@agliodbs.com>
2002-06-28 16:58 transaction in plpgsql Joseph Syjuco <joseph@asti.dost.gov.ph>
2002-06-28 06:08 ` Josh Berkus <josh@agliodbs.com>
2002-06-28 06:53 ` Nils Höglund <nils@naqua.se>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox