Message-ID: <39E61C58.97E78E3E@selectacast.net>
Date: Thu, 12 Oct 2000 16:17:28 -0400
From: Joseph Shraibman <jks@selectacast.net>
Organization: Xtenit
MIME-Version: 1.0
To: "pgsql-interfaces@postgreSQL.org" <pgsql-interfaces@postgresql.org>
CC: pgsql-hackers@postgresql.org
Subject: Re: [INTERFACES] JDBC Large ResultSet problem + BadTimeStamp Patch
References: <Pine.LNX.4.21.0010120924260.435-100000@maidast.demon.co.uk>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

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@retep.org.uk http://www.retep.org.uk
> PostgreSQL JDBC Driver http://www.retep.org.uk/postgres/
> Java PDF Generator http://www.retep.org.uk/pdf/