Re: subtransactions and FETCH behaviour (was Re: [HACKERS] PREPARE and transactions)
Well, the proposal of implementing it like holdable cursors means using a Materialize node which, if I understand correctly, means taking the whole result set and storing it on memory (or disk). Would it help to hold the lock for a record that is the current cursor position, iff this record was updated (and subsequently rolled back) by this subtxn, and release that lock as soon as you fetch next ? Andreas ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
subtransactions and FETCH behaviour (was Re: [HACKERS] PREPARE and transactions)
Jeroen T. Vermeulen wrote: That makes me wonder why people want to maintain transactionality w.r.t. nested transactions but not with outer ones. Odd! Yep. But then the FETCH should still occur before the transaction as far as I'm concerned. You fetch a batch (if it fails, you terminate) and *try* to process it. This is a non-starter for JDBC: it has no control over when an application decides to access a ResultSet in a way that results in a FETCH of new data. Buffering *all* the ResultSet data client-side isn't an option -- cursors are used specifically to handle resultsets that don't fit into heap on the client side. And implementing a disk cache or similar a) doesn't work if you don't have disk access, b) is bandwidth-intensive and c) is really silly -- that's work that belongs on the server side, or why bother with implementing cursors at all?! Invalidating all open resultsets on creation of a savepoint would make savepoints useless in many cases, and isn't hinted at in the JDBC spec for savepoints so is likely to break many otherwise portable apps. Having ResultSets spontaneously change position on transaction boundaries would cause even more portability problems -- and it goes completely against how that API is designed (it's meant to *insulate* the application from details like cursors that may be used behind the scenes). I don't like rollback of FETCH for much the same reasons as I don't like rollback of PREPARE -- lots more work on the client side. See my mail on the other thread. Avoiding changing the behaviour of FETCH in the above case is also an argument against it. In the case of FETCH, where does that extra work come from? See my other email. The driver will either have to use SCROLL cursors and FETCH ABSOLUTE everywhere (which involves an extra Materialize step in the plan for nontrivial queries) or track each open cursor's position at the start of every active subtransaction so it can restore that information on rollback. The driver needs to track where the server thinks the cursor is positioned so it can do an appropriate FETCH or Execute when the application requests data in a resultset that's not currently available on the client side. Reporting the new cursor positions at the protocol level when rollback happens might help but it's still fairly ugly and would need a protocol version change. Also consider that the V3 protocol Execute message is essentially a FETCH (you can only do FETCH FORWARD count, but it's otherwise equivalent). This is another case of overlap between the SQL level and the protocol level and has much of the same problems as we have with PREPARE vs. Bind/Execute. The protocol-level portal state shouldn't suddenly change on a transaction boundary. I can understand closing nonholdable portals when the creating transaction closes (the data source just disappeared) but having the portal change *position* would be very weird. -O ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: subtransactions and FETCH behaviour (was Re: [HACKERS] PREPARE and transactions)
On Tue, Jul 06, 2004 at 08:45:52AM +1200, Oliver Jowett wrote: This is a non-starter for JDBC: it has no control over when an application decides to access a ResultSet in a way that results in a FETCH of new data. From what you're telling me, I'm not sure I like JDBC! Why did they come up with such a low-level design? Sounds like little more than a thin ODBC wrapper plus JVM marshalling... Buffering *all* the ResultSet data client-side isn't an option -- cursors are used specifically to handle resultsets that don't fit into heap on the client side. And implementing a disk cache or similar a) doesn't work if you don't have disk access, b) is bandwidth-intensive and c) is really silly -- that's work that belongs on the server side, or why bother with implementing cursors at all?! But does this type of ResultSet scroll cursors? Because in that case, it should be easy to reset the cursor's position at rollback! Not fast perhaps, but easy. Screw fast when you're rolling back, because you'll have other things to worry about. Okay, I know, you might not _want_ to reset on rollback. But it does give the middleware a lot more freedom to play with connections etc. like we discussed before. So personally, if it meant that I had to support rollbacks, I would think it was a small price to pay for full ACID guarantees. Having ResultSets spontaneously change position on transaction boundaries would cause even more portability problems -- and it goes completely against how that API is designed (it's meant to *insulate* the application from details like cursors that may be used behind the scenes). Are you saying this is not something you'd be able to hide in the driver? Reporting the new cursor positions at the protocol level when rollback happens might help but it's still fairly ugly and would need a protocol version change. It would be nice IMHO to have a tell function for cursors, giving the enumerated current position of the cursor. I can fake that by counting rows, in fact I've already done that, but it's not pretty and it easily gets confused with the lower isolation levels (which fortunately Postgres doesn't have). Also consider that the V3 protocol Execute message is essentially a FETCH (you can only do FETCH FORWARD count, but it's otherwise equivalent). This is another case of overlap between the SQL level and the protocol level and has much of the same problems as we have with PREPARE vs. Bind/Execute. The protocol-level portal state shouldn't suddenly change on a transaction boundary. I can understand closing nonholdable portals when the creating transaction closes (the data source just disappeared) but having the portal change *position* would be very weird. You're beginning to convince me that maybe ACID for transactions in postgres is unsalvageable and we should be thinking about some alternative, such as ways of finding out whether ACID still applies to the current transaction, and/or whether the current statement will change that... Jeroen ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: subtransactions and FETCH behaviour (was Re: [HACKERS] PREPARE and transactions)
On Tue, Jul 06, 2004 at 08:45:52AM +1200, Oliver Jowett wrote: Buffering *all* the ResultSet data client-side isn't an option -- cursors are used specifically to handle resultsets that don't fit into heap on the client side. And implementing a disk cache or similar a) doesn't work if you don't have disk access, b) is bandwidth-intensive and c) is really silly -- that's work that belongs on the server side, or why bother with implementing cursors at all?! Well, the proposal of implementing it like holdable cursors means using a Materialize node which, if I understand correctly, means taking the whole result set and storing it on memory (or disk). So the same question arises: why bother implementing that at all? Of course the answer is that the server definitely _has_ to provide the functionality. Now, the cursor problem is beyond me ATM -- it needs deep understanding of the executor code that I do not have and won't be able to develop in two weeks ... if there's no reasonable solution in sight maybe the best we can do is revert the whole nested xacts patch (or at least disable the funcionality) so we have more time to solve this particular problem. Sadly, AFAICS this is the only major problem with the functionality, so it would be a pity to throw away all work only for this. -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) I call it GNU/Linux. Except the GNU/ is silent. (Ben Reiter) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
