Re: [HACKERS] Portals and nested transactions
My answers: Q1: Should Portals successfully created within the failed subxact be closed? Or should they remain open? no for protocol level I can understand a yes to this one for sql level, because it will be hard to clean up by hand :-( But I like the analogy to hold cursors, so I would also say no to sql level. Is the pro yes argument ACID allowed here ? I thought ACID is about data integrity and not flow control, and also deals with main transactions and not subtransactions. Q2: If the subxact changed the state of a pre-existing Portal, should that state change roll back? In particular, can a Close Portal operation roll back? NO for both SQL and protocol level. The analogy is imho that closing a 'hold cursor' is also never rolled back How to do it non-transactionally Sounds like a good plan, but also sounds like a lot of work. Andreas ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Portals and nested transactions
On Tue, Jul 13, 2004 at 04:57:06PM -0400, Tom Lane wrote: I've been thinking about what to do with cursors in subtransactions. The problem really includes both cursors (created with DECLARE CURSOR) and portals (created with the V3-protocol Bind message) since they are the same kind of animal internally, namely a Portal. So within this proposal, a query executed by normal means will get its resources saved in the transaction ResourceOwner? How is the unnamed portal affected by it? Supposing that the unnamed portal is treated like any other portal (with its own ResourceOwner), we have to make sure to shut it down properly if something goes wrong. Not sure how this applies to portals created by SPI. Q1: Should Portals successfully created within the failed subxact be closed? Or should they remain open? Q2: If the subxact changed the state of a pre-existing Portal, should that state change roll back? In particular, can a Close Portal operation roll back? IMHO the transactional view is better; if we take the other approach, then users can't just use a simple retry loop around a subtransaction. The discussion sort of trailed off there because we had no ideas how to implement either. I will now sketch some implementation ideas about how to do the nontransactional way. Sounds excellent to me. We could support the transactional behavior as well, but not very efficiently (at least not in the first cut). I think we should decide what behavior is best now, and not change it in a later release. If it's going to be somewhat inefficient, try to minimise it. But just as I decided not to support the nested transaction syntax and instead change to the savepoint syntax, lets keep things consistent. IMHO anyway. On the other hand, some people supported the idea that v3 Bind portals should behave nontransactionally, while DECLARE portals should behave transactionally. Maybe we could make that a property of the portal, or even a user-selectable property (where we would define a reasonable default behavior). -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) In a specialized industrial society, it would be a disaster to have kids running around loose. (Paul Graham) ---(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
Re: [HACKERS] Portals and nested transactions
Tom, As much as I can understand the arguments -- many of them performance-oriented -- for handling Portals non-transactionally, I simply don't see how we can do it and not create huge problems for anyone who uses both cursors and NTs together ... as those who use either are liable to do. What I think we could do, though, is record the Portal's high-level state as the number of rows fetched from it. On abort, rewind the Portal and then fetch that number of rows again (this is the same method used by MOVE ABSOLUTE). We could optimize things a little bit by not doing this repositioning until and unless the Portal is actually used again. Still, it wouldn't be cheap... From what you're describing, this seems like the wisest course. I can't endorse us getting into any situation where *some* operations are rolled back by an NT abort, and some are not.That seems like begging for 12-hour-long debugging sessions. The only cost of doing things transactionally seems to be the performance cost of re-fetching the Portal in the event of a subtransaction abort containing a Portal command. If it's a comparison between the performance loss of re-fetching a Portal, and the debugging nightmare of not knowing what state a Portal is in after an abort and rollback (consider NTs containing loops), I'll take the latter any day. Of course this only handles SELECT-query portals, not portals that contain data-modification commands. But the latter cannot be suspended partway through anyhow, so there is no scenario where we need to recover to a partly-executed state. (Recall what I said before about not allowing continuation of a portal that itself got an error.) Yes, and the possibility of updatable cursors makes the transactional argument even more compelling. -- Josh Berkus Aglio Database Solutions San Francisco ---(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
Re: [HACKERS] Portals and nested transactions
Alvaro Herrera [EMAIL PROTECTED] writes: On Tue, Jul 13, 2004 at 04:57:06PM -0400, Tom Lane wrote: I've been thinking about what to do with cursors in subtransactions. So within this proposal, a query executed by normal means will get its resources saved in the transaction ResourceOwner? No, *all* queries are executed within portals. The reason we need a transaction ResourceOwner is because query parsing/planning happens in advance of creating the portal, so we need someplace to keep track of resources acquired during that process. How is the unnamed portal affected by it? Same as the rest. I don't recall whether SPI creates actual portals, but we'd definitely want it to create a new ResourceOwner for queries it runs. On the other hand, some people supported the idea that v3 Bind portals should behave nontransactionally, while DECLARE portals should behave transactionally. Maybe we could make that a property of the portal, or even a user-selectable property (where we would define a reasonable default behavior). This is certainly possible. Whether it's a good idea needs further discussion... regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Portals and nested transactions
Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: On Tue, Jul 13, 2004 at 04:57:06PM -0400, Tom Lane wrote: I've been thinking about what to do with cursors in subtransactions. So within this proposal, a query executed by normal means will get its resources saved in the transaction ResourceOwner? No, *all* queries are executed within portals. The reason we need a transaction ResourceOwner is because query parsing/planning happens in advance of creating the portal, so we need someplace to keep track of resources acquired during that process. How is the unnamed portal affected by it? Same as the rest. I don't recall whether SPI creates actual portals, but we'd definitely want it to create a new ResourceOwner for queries it runs. On the other hand, some people supported the idea that v3 Bind portals should behave nontransactionally, while DECLARE portals should behave transactionally. Maybe we could make that a property of the portal, or even a user-selectable property (where we would define a reasonable default behavior). This is certainly possible. Whether it's a good idea needs further discussion... I didn't want to be the first to speak up on this as I'm relatively new to the group (so thank you Alvaro), but I would definitely perfer the option of either trans or non-trans behavior. I can see using the non-trans behavior in a cursor based FOR loop with a savepoint/subtrans allowing me to fail on row x and continue on to row x+1 immediately. Then, after choosing trans-mode, I could implement a multi-strategy row processor. Of course, just to be difficult, my ideal default would be: Q1 -- Portals close Q2 -- Portals do NOT roll back to previous state. However, I do see the logical inconsistency in that. But then again, subtransactions/savepoints are not ACID, so it seems to be implementation dependent. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) -- --miker ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Portals and nested transactions
Mike Rylander wrote:
Tom Lane wrote:
Alvaro Herrera [EMAIL PROTECTED] writes:
On Tue, Jul 13, 2004 at 04:57:06PM -0400, Tom Lane wrote:
I've been thinking about what to do with cursors in subtransactions.
So within this proposal, a query executed by normal means will get its
resources saved in the transaction ResourceOwner?
No, *all* queries are executed within portals. The reason we need a
transaction ResourceOwner is because query parsing/planning happens in
advance of creating the portal, so we need someplace to keep track of
resources acquired during that process.
How is the unnamed portal affected by it?
Same as the rest.
I don't recall whether SPI creates actual portals, but we'd definitely
want it to create a new ResourceOwner for queries it runs.
On the other hand, some people supported the idea that v3 Bind portals
should behave nontransactionally, while DECLARE portals should behave
transactionally. Maybe we could make that a property of the portal, or
even a user-selectable property (where we would define a reasonable
default behavior).
This is certainly possible. Whether it's a good idea needs further
discussion...
I didn't want to be the first to speak up on this as I'm relatively new to
the group (so thank you Alvaro), but I would definitely perfer the option
of either trans or non-trans behavior. I can see using the non-trans
behavior in a cursor based FOR loop with a savepoint/subtrans allowing me
to fail on row x and continue on to row x+1 immediately. Then, after
choosing trans-mode, I could implement a multi-strategy row processor.
Of course, just to be difficult, my ideal default would be:
Q1 -- Portals close
Q2 -- Portals do NOT roll back to previous state.
However, I do see the logical inconsistency in that. But then again,
subtransactions/savepoints are not ACID, so it seems to be implementation
dependent.
To make that a little more specific, something along the lines of:
DECLARE name [ BINARY ] [ INSENSITIVE ] [ [ NO ] SCROLL ]
CURSOR [ { WITH | WITHOUT } HOLD ] FOR query
[ FOR { READ ONLY | UPDATE [ OF column [, ...] ] } ]
[ IN { LEXICAL | GLOBAL } SCOPE
^^^
... or some such... I think in perl. :)
regards, tom lane
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
--
--miker
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [HACKERS] Portals and nested transactions
Josh Berkus wrote: Tom, As much as I can understand the arguments -- many of them performance-oriented -- for handling Portals non-transactionally, I simply don't see how we can do it and not create huge problems for anyone who uses both cursors and NTs together ... as those who use either are liable to do. I'd argue against rolling back portal state on subxact commit for three reasons that aren't performance-related: it makes (some?) client code harder, it's incompatible with other implementations of savepoints, and it's inconsistent with how WITH HOLD cursors already behave. ... The JDBC driver is going to be unhappy if this happens. It is not expecting the portal state of any cursors backing its ResultSets to change unexpectedly, as a ROLLBACK TO SAVEPOINT will do. To correctly handle this, at a minimum it needs notification of changes to the transaction nesting level as they happen (did anything get resolved here?); then it has to store the client-side state of each open portal whenever a new subxact (== SAVEPOINT) is opened, and restore the appropriate state on rollback. I'd expect any layer that uses portals/cursors to buffer results to have similar problems. There are two problems going on here: 1) The state of the portal is not necessarily directly visible to the application -- in the case of the JDBC driver they are used to buffer large resultsets -- so at that level the behaviour on rollback isn't visible or useful to the application anyway, and rolling back state actually makes life more difficult for the buffering code. 2) The application-visible result object semantics (the ResultSet in JDBC's case) may have its own semantics that don't correspond to the behaviour of portals, and it may not be possible to arbitarily change the result object's semantics (the only thing that the JDBC spec says about ResultSets vs. ROLLBACK is specifying the holdability of the resultset -- rolling back resultset state on rollback to savepoint is going to break most existing JDBC apps that use savepoints, IMO). So the driver ends up doing lots of extra work to fake nontransactional behaviour. ... Rolling back state is the opposite of what DB2 does according to the DB2 docs, as I mentioned in an earlier email: # The impact on cursors resulting from a ROLLBACK TO SAVEPOINT depends on the statements within the savepoint * If the savepoint contains DDL on which a cursor is dependent, the cursor is marked invalid. Attempts to use such a cursor results in an error (SQLSTATE 57007). * Otherwise: o If the cursor is referenced in the savepoint, the cursor remains open and is positioned before the next logical row of the result table. (A FETCH must be performed before a positioned UPDATE or DELETE statement is issued.) o Otherwise, the cursor is not affected by the ROLLBACK TO SAVEPOINT (it remains open and positioned). I don't know what Oracle does. The 2003 draft says that the behaviour of cursors established before the savepoint that was rolled back to is implementation-defined. Bah. ... Finally, we don't roll back WITH HOLD cursor state on top-level transaction rollback. Why are the semantics in a subxact rollback different? -O ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Portals and nested transactions
Alvaro Herrera wrote: On the other hand, some people supported the idea that v3 Bind portals should behave nontransactionally, while DECLARE portals should behave transactionally. Maybe we could make that a property of the portal, or even a user-selectable property (where we would define a reasonable default behavior). If this is going to happen, either the protocol-level portals need access to all the functionality of DECLARE, or it needs to be done as a user-selectable property of DECLARE. Currently the JDBC driver uses only protocol-level portals, but as soon as we want to support large scrollable or holdable ResultSets (effectively unsupported by the current driver) it will have to use DECLARE to get access to SCROLL / WITH HOLD. -O ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Portals and nested transactions
On Wed, Jul 14, 2004 at 03:11:54PM -0400, Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: On Tue, Jul 13, 2004 at 04:57:06PM -0400, Tom Lane wrote: I've been thinking about what to do with cursors in subtransactions. So within this proposal, a query executed by normal means will get its resources saved in the transaction ResourceOwner? No, *all* queries are executed within portals. The reason we need a transaction ResourceOwner is because query parsing/planning happens in advance of creating the portal, so we need someplace to keep track of resources acquired during that process. Ah-ha, got it (should have known better). Do you want me to do the legwork for this to happen, or was your initial plan to do it yourself? Either way is OK with me ... -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) Granting software the freedom to evolve guarantees only different results, not better ones. (Zygo Blaxell) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Portals and nested transactions
Alvaro Herrera [EMAIL PROTECTED] writes: Do you want me to do the legwork for this to happen, or was your initial plan to do it yourself? Either way is OK with me ... I'm working on it, should have it done in a day or so. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
