Skip site navigation (1) Skip section navigation (2)

Re: JDBC Large ResultSet problem + BadTimeStamp Patch

From: Joseph Shraibman <jks(at)selectacast(dot)net>
To: Steve Wampler <swampler(at)noao(dot)edu>
Cc: Peter Mount <peter(at)retep(dot)org(dot)uk>, "pgsql-interfaces(at)postgreSQL(dot)org" <pgsql-interfaces(at)postgresql(dot)org>
Subject: Re: JDBC Large ResultSet problem + BadTimeStamp Patch
Date: 2000-10-11 18:56:41
Message-ID: 39E4B7E9.FB8605F2@selectacast.net (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-interfaces
Steve Wampler wrote:
> 
> Peter Mount wrote:
> ...
> > Yes, there is a problem with multiple statements and transactions on the
> > same connection when it comes to thread safety.
> >
> > The problem as I see it is two fold.
> >
> > 1: How to deal with two statements within one transaction. Related to this
> > is the metadata methods that issue queries, how to deal with them while
> > within a transaction.
> >
> > 2: Currently the JDBC Specs only have transactions supported at the
> > Connection level, so I can't see how they thought that Statements could
> > possibly run within their own transactions, unless they thought that a
> > workaround of this is the use of batches.
> 
> Ah, that probably explains why I've seen "tuple arrived before metadata"
> messages when I've got several apps talking through CORBA to a java app
> that connects to postgres.  Do I need to synchronize both inserts and
> queries at the java app level to prevent this?  (I was hoping that
> the BEGIN/END block in a transaction would be sufficient, but this makes
> it sound as though it isn't.)
> 
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?  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).
Right now each new connection requires forking off another backend,
which makes it impractical to connect whenever you have a new
transaction to do.

In response to

Responses

pgsql-hackers by date

Next:From: Bruce MomjianDate: 2000-10-11 21:25:39
Subject: Re: postmaster errors with index on temp table?
Previous:From: Bruce MomjianDate: 2000-10-11 18:40:47
Subject: Re: TIOGA

pgsql-interfaces by date

Next:From: keke abeDate: 2000-10-12 07:28:23
Subject: COPY BINARY to stdout
Previous:From: Steve WamplerDate: 2000-10-11 18:27:40
Subject: Re: JDBC Large ResultSet problem + BadTimeStamp Patch

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group