473,899 Members | 3,305 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Nested transaction workaround?

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

Nov 22 '05 #1
9 7627
"John Sidney-Woollett" <jo****@wardbro ok.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*******@postg resql.org

Nov 22 '05 #2
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

Nov 22 '05 #3
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 YourEmailAddres sHere" to ma*******@postg resql.org)

Nov 22 '05 #4
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

iD8DBQFABQDQY5T wig3Ge+YRAqMjAK CZXhIcXK88QSXJ/WPOP69XKloajACg gHL6
3fCsVL5qNTHJxVC HM5Covjs=
=px3u
-----END PGP SIGNATURE-----

Nov 22 '05 #5
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

Nov 22 '05 #6
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*******@postg resql.org so that your
message can get through to the mailing list cleanly

Nov 22 '05 #7
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

iD8DBQFABS0cY5T wig3Ge+YRAopwAK CDkrUTs5lW/HslKd0hbBzp3SHY jACgro4J
N663fPhbiw4XaBZ eQbh7BXE=
=suAO
-----END PGP SIGNATURE-----

Nov 22 '05 #8
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..exceptio n..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 YourEmailAddres sHere" to ma*******@postg resql.org)

Nov 22 '05 #9
"John Sidney-Woollett" <jo****@wardbro ok.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 representationa l 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

Nov 22 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

26
29658
by: Joshua Beall | last post by:
Hi All, I remember reading that both nested classes and namespaces would be available in PHP5. I know that namespaces got canceled (much sadness...), however, I *thought* that nested classes were still an option. However, I am coming up dry looking for information on how to do this, and the most recent references I am able to find to nested classes in PHP are dated 2003. And they all say the same thing: sorry, can't do that.
2
2406
by: M Wells | last post by:
Hi All, This seems like a tricky question to me. I have a Stored Procedure that encapsulates a number of updates to various tables within a transaction. However, at a later part of the transaction I need to be able to select records changed by an update statement made earlier within the same stored proc (and within the same transaction) and need for that select to reflect the changed values.
0
2565
by: CJM | last post by:
I'm executing a series of SQL Server stored procs in one ASP page. They are wrapped within an ADO transaction. The reason for several SP calls is that the SPs are used individually elsewhere in the application, therefore it is not really an option to roll them up into one procedure. I've just added another SP to the lists and now I am getting an error: "Cannot create new connection because in manual or distributed transaction mode."
0
4056
by: P. Emigh | last post by:
A client that synchronizes over the internet encountered Error #3003: "Could not start transaction; too many transactions already nested" when attempting to synchronize. I checked user groups and MS Knowledge Base and found nothing helpful. This didn't seem to be acknowledged as a synchroization error. Investigations revealed data errors that resulted in a cascade of related errors. Resolving the data errors apparently cured whatever...
6
3197
by: Thapliyal, Deepak | last post by:
Hi, Assume I have a bank app.. When customer withdraws $10 from his accouint I have to do following --> update account_summary table --> update account detail_table Requirement: either both transactions should succeed or both transactions should be rolled back in case of failure.
2
2328
by: Karl O. Pinc | last post by:
Hi, I don't suppose that the todo item: Referential Integrity o Add deferred trigger queue file (Jan) Means that there will be a statement like: CREATE TRIGGER ... FOR EACH TRANSACTION
0
1445
by: Pavel Stehule | last post by:
Hello I can't use nested transaction in perl DBI interface. I have CVS PostgreSQL. Propably DBI doesn't support last pg features. Can I set off DBI transaction control mechanism? Thank You Pavel Stehule
1
18890
by: Mana | last post by:
Hi, I want to implement nested transactions in C#. When I write BEGIN TRANSACTION inside another BEGIN TRANSACTION in an SQL Script it works fine. But when I call BeginTransaction() inside another BeginTransaction() in a c# code on same connection object it throws exception as "SQLConnection doesnot support parallel transaction". Following is the code snippet that i have written.
8
7274
by: Nathan Sokalski | last post by:
I have several nested For loops, as follows: For a As Integer = 0 To 255 For b As Integer = 0 To 255 For c As Integer = 0 To 255 If <Boolean ExpressionThen <My CodeElse Exit For Next If Not <Boolean ExpressionThen Exit For Next If Not <Boolean ExpressionThen Exit For
0
9843
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10863
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10494
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9666
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
7201
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5887
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
6081
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4720
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3317
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.