Nested transaction workaround?

[John Sidney-Woollett]

Is it possible to use the dblink and dblink_exec features from inside
pl/pgsql functions to mimic the behaviour of nested transactions by
calling another function or executing some SQL via the dblink (into the
same database)?

Does the SQL statement executed within the dblink sit in its own isolated
transaction/session? And would an error thrown in the dblink statement
abort the enclosing session that initiated the call?

What is the overhead/cost in creating the dblink connection?

I'm asking these questions before trying this out, just in case someone
else has already tried, and either failed or succeeded.

John Sidney-Woollett

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Tom Lane]

"John Sidney-Woollett" <jo****@wardbrook.com> writes:

Is it possible to use the dblink and dblink_exec features from inside
pl/pgsql functions to mimic the behaviour of nested transactions by
calling another function or executing some SQL via the dblink (into the
same database)?

I wouldn't call it "nested" transactions: the remote transactions would
be committed, and would stay committed even if you roll back the caller.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[John Sidney-Woollett]

Tom Lane said:

I wouldn't call it "nested" transactions: the remote transactions would

be committed, and would stay committed even if you roll back the caller.

Ah, that's true. This means the dblink could be used to provide
functionality equivalent to Oracle's #PRAGMA AUTONOMOUS directive.

Thanks for clearing that up.

John


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Chris Travers]

I was thinking about the nested transaction problem, and I came across an
interesting insight. While it is true you could use dblink to perform db
operations outside the transaction (which could be useful for logging, etc.)
what is lacking is a way to roll back the internal transactions when the
parent rolls back.

It also occured to me that the main problem with nested transactions is that
the hard part is this inherited rollback/commit, esp. with MVCC which places
constraints on how one could look at managing these commit/rollbacks without
paying huge performance costs even where, as in the majority of cases, this
feature is not used. I am assuming that part of the problem is how the
visibility/transaction information is handled via MVCC. Is my understanding
correct?

My final, albeit half-baked, conclusion is that one of the things that would
make nested transactions MUCH easier would be a two-phase commit (2PC)
framework which would be stored on the transaction level. Something like a
transaction status storage which contains the following information: Status
(in progress, committed, rolled back, pending commit as in 2PC), and
"depends on xid" where you can then have the pending commit become
'committed' when transaction xid is commited. Again this is just off the
top of my head.

Also an 2PC framework if added into the protocol would allow for true nested
transactions via DBLink.

Best Wishes,
Chris Travers
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Martijn van Oosterhout]

On Wed, Jan 14, 2004 at 02:45:38PM +0700, Chris Travers wrote:

I was thinking about the nested transaction problem, and I came across an
interesting insight. While it is true you could use dblink to perform db
operations outside the transaction (which could be useful for logging, etc.)
what is lacking is a way to roll back the internal transactions when the
parent rolls back.
Well, you could not commit the dblink'ed transaction until the parent
commits. The problem is that the dblink'ed statement cannot see the parent
transaction's uncommitted changes.
It also occured to me that the main problem with nested transactions is that
the hard part is this inherited rollback/commit, esp. with MVCC which places
constraints on how one could look at managing these commit/rollbacks without
paying huge performance costs even where, as in the majority of cases, this
feature is not used. I am assuming that part of the problem is how the
visibility/transaction information is handled via MVCC. Is my understanding
correct?
Well, actually, the problem appears to be that people want to be able to
roll back each individual statement without killing the parent transaction,
and they want to make this the default behaviour. This takes it out of the
"never used" category to "everybody does it" category.

So you have to make something that works even if you use it all the time.
My final, albeit half-baked, conclusion is that one of the things that would
make nested transactions MUCH easier would be a two-phase commit (2PC)
framework which would be stored on the transaction level. Something likea
transaction status storage which contains the following information: Status
(in progress, committed, rolled back, pending commit as in 2PC), and
"depends on xid" where you can then have the pending commit become
'committed' when transaction xid is commited. Again this is just off the
top of my head.
I think something like that has been suggested, check the archives. There is
a bit of discussion on how to actually store that info in a way that can be
checked efficiently because remember, visibility needs to be checked for
every tuple on every sequential scan in every process that runs subsequently,
so it needs to be *fast*.
Also an 2PC framework if added into the protocol would allow for true nested
transactions via DBLink.
Dt doesn't solve the visibility problem though.

Hope this helps,
--
Martijn van Oosterhout <kl*****@svana.org> http://svana.org/kleptog/ (... have gone from d-i being barely usable even by its developers
anywhere, to being about 20% done. Sweet. And the last 80% usually takes
20% of the time, too, right?) -- Anthony Towns, debian-devel-announce

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFABQDQY5Twig3Ge+YRAqMjAKCZXhIcXK88QSXJ/WPOP69XKloajACggHL6
3fCsVL5qNTHJxVCHM5Covjs=
=px3u
-----END PGP SIGNATURE-----

[Chris Travers]

From: "Martijn van Oosterhout" <kl*****@svana.org>

Well, actually, the problem appears to be that people want to be able to
roll back each individual statement without killing the parent transaction, and they want to make this the default behaviour. This takes it out of the
"never used" category to "everybody does it" category.
Ok. Now I am confused. I thought that a nested transaction would involve
two features:
1: The ability to incrimentally commit/rollback changes, i.e. at certain
points in the transaction have a sub-commit.
2: The ability to have a transaction within another transaction with
transactional visibility rules applying within the transaction tree.

What exactly do you mean by roll back individual statements? What exactly
would be the default behavior?
There is
a bit of discussion on how to actually store that info in a way that can be checked efficiently because remember, visibility needs to be checked for
every tuple on every sequential scan in every process that runs subsequently, so it needs to be *fast*.

Then you might have to have an array of "related transactions" which are
also visible for each thransaction, sort of like a tree with bidirectional
links. Unfortunately I can imagine this being a source of subtle, hard to
troubleshoot bugs. Something like
_x_id, _x_status, related_x_id[], child_x_id[], so that a rollback can
rollback all child_x_id's without touching the other transactions which are
parents, cousins, etc. but visible.

Best Wishes,
Chris Travers
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Richard Huxton]

On Wednesday 14 January 2004 09:45, Chris Travers wrote:

From: "Martijn van Oosterhout" <kl*****@svana.org>

Well, actually, the problem appears to be that people want to be able to
roll back each individual statement without killing the parent
transaction,

and they want to make this the default behaviour. This takes it out of
the "never used" category to "everybody does it" category.

Ok. Now I am confused. I thought that a nested transaction would involve
two features:
1: The ability to incrimentally commit/rollback changes, i.e. at certain
points in the transaction have a sub-commit.
2: The ability to have a transaction within another transaction with
transactional visibility rules applying within the transaction tree.

Of course you can do #1 with #2.
What exactly do you mean by roll back individual statements? What exactly
would be the default behavior?

I think we're talking about the "insert and if that fails update" sequence
that seems to be a common approach.

--
Richard Huxton
Archonet Ltd

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

[Martijn van Oosterhout]

On Wed, Jan 14, 2004 at 04:45:46PM +0700, Chris Travers wrote:

Ok. Now I am confused. I thought that a nested transaction would involve
two features:
1: The ability to incrimentally commit/rollback changes, i.e. at certain
points in the transaction have a sub-commit.
I think they're referred to as savepoints, not sure. I think they become
trivial once nested transactions are done.
2: The ability to have a transaction within another transaction with
transactional visibility rules applying within the transaction tree.

What exactly do you mean by roll back individual statements? What exactly
would be the default behavior?
Well, one of the problems people would like to solve is that if a trigger or
constraint fails then it doesn't roll back the whole transaction. The way
this would be acheived is by making every statement within the parent
transaction it's own implicit subtransaction so any errors can be caught and
undone cleanly.

This would mean that even normal transaction blocks that people do now could
involve dozens of subtransactions, especially if you start involving
triggers and function calls.

In any case, I don't quite understand the intended semantics of "duplicate
key shouldn't rollback transaction". If I call a function that inserts a
duplicate key, should the other effects of the function be rolled back or
not? Anyone know how MSSQL deals with this?
Then you might have to have an array of "related transactions" which are
also visible for each thransaction, sort of like a tree with bidirectional
links. Unfortunately I can imagine this being a source of subtle, hard to
troubleshoot bugs. Something like
_x_id, _x_status, related_x_id[], child_x_id[], so that a rollback can
rollback all child_x_id's without touching the other transactions which are
parents, cousins, etc. but visible.
The issue is atomicity. You need to be able to make sure that even if the
power fails halfway through a write that the whole transaction is either
committed or it's not. The mrore you have to update the harder it gets.

Anyway, far more intelligent people than I have been over this, check the
archives of this list and -hackers.
--
Martijn van Oosterhout <kl*****@svana.org> http://svana.org/kleptog/ (... have gone from d-i being barely usable even by its developers
anywhere, to being about 20% done. Sweet. And the last 80% usually takes
20% of the time, too, right?) -- Anthony Towns, debian-devel-announce

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFABS0cY5Twig3Ge+YRAopwAKCDkrUTs5lW/HslKd0hbBzp3SHYjACgro4J
N663fPhbiw4XaBZeQbh7BXE=
=suAO
-----END PGP SIGNATURE-----

[John Sidney-Woollett]

Martijn van Oosterhout said:

In any case, I don't quite understand the intended semantics of "duplicate
key shouldn't rollback transaction". If I call a function that inserts a
duplicate key, should the other effects of the function be rolled back or
not? Anyone know how MSSQL deals with this?

In Oracle (I believe) that the exception is propogated up to abort the
initiating transaction unless you explictly place the SQL call which
failed in a begin..exception..end block (like below)

begin
-- do some SQL stuff here
exception
when NO_DATA_FOUND then
-- handle the exception
end;

You also have the option to RAISE an exception to abort the transaction
after you handled the error and decided that there is nothing useful you
can do.

Maybe, (don't shoot me) better procedural (pl/pgsql) error/exception
handling ought to be a dealt with before nested transaction support?

John Sidney-Woollett

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Tom Lane]

"John Sidney-Woollett" <jo****@wardbrook.com> writes:

Maybe, (don't shoot me) better procedural (pl/pgsql) error/exception
handling ought to be a dealt with before nested transaction support?

They are largely the same problem. The representational issues for
keeping track of nested-transaction commit states are solved, at least
on paper --- see the pghackers archives. The big nut to crack is that
all of the error-handling logic in the backend is based on the
assumption that any error kills the transaction and allows all transient
state to be thrown away. Partial cleanup of transient state is simply
not there, and it's not clear how to add it without (a) a lot of new
code, (b) a big performance hit, and (c) tons of new bugs.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match