Re: [INTERFACES] JDBC Large ResultSet problem + BadTimeStamp Patch

Peter Mount wrote:
>
> On Wed, 11 Oct 2000, Joseph Shraibman wrote:
>
> [snip]
>
> > It isn't. I wish there were online mail archives. But anyway the
> > reason it isn't is that if two statements use the same connection, when
> > one of them calls enters a transaction the other one does too. So if
> > you do:
> > 1) BEGIN;
> > 2) SELECT blah FROM tablea FOR UPDATE;
> > 3) UPDATE tablea SET blah = newblah;
> >
> > since both statements are using the same connection they ARE NOT LOCKED
> > FROM EACH OTHER, and when one calls and END; or COMIT; both of them exit
> > their transactions. That is why I'm using a connection pool now when I
> > have to do locking. And if Peter wants to use cursors behind the scenes
> > it will be even worse, because the cursors themselves need to be in a
> > BEGIN; END;, and what happens if they user thinks he is in a transaction
> > but the cursor ended it for him a while ago?
>
> We already have this problem with the two MetaData interfaces. Some of
> those methods issue their own queries, and if they fail while within the
> client's app's transaction all hell can break loose. This is one of the
> reasons why I'm intending that the cursor based ResultSet only gets used
> if the client has requested one.
>
> > Named transactions would help with this, but the real answer would be
> > to be able to have more then one connection to a backend (maybe
> > tunnelled over on TCP/IP link).
>
> Named transactions would help with both of these problems. Not knowing
> enough of how the existing transaction mechanism works internally, I'd say
> it's easer to do than tunnelling.
>

I don't think tunnelling would be that hard, just add a number to say
which connection this query is for. The real reason it won't be done is
that the coders who coded the backend don't want to share a backends
with more than one connection to keep a segfault (or other error) from
one connection taking down all the others. I see the logic in that, but
there is a compromise solution of allowing one client to have more than
one connection (with locks for each connection being seperate) over the
same TCP/IP socket so only one client is talking with one backend
process.

> > Right now each new connection requires forking off another backend,
> > which makes it impractical to connect whenever you have a new
> > transaction to do.
>
> Yes, that's the last thing we want, especially while we are trying to
> optimise things...
>
> Peter
>
> --
> Peter T Mount peter(at)retep(dot)org(dot)uk http://www.retep.org.uk
> PostgreSQL JDBC Driver http://www.retep.org.uk/postgres/
> Java PDF Generator http://www.retep.org.uk/pdf/