Re: subtransactions and FETCH behaviour (was Re: PREPARE and transactions) - Mailing list pgsql-hackers

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