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

Re: [JDBC] Support for JDBC setQueryTimeout, et al.

From: Stephen Frost <sfrost(at)snowman(dot)net>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Radosław Smogura <rsmogura(at)softperience(dot)eu>,Magnus Hagander <magnus(at)hagander(dot)net>,David Fetter <david(at)fetter(dot)org>,Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>,PG Hackers <pgsql-hackers(at)postgresql(dot)org>,PostgreSQL JDBC List <pgsql-jdbc(at)postgresql(dot)org>,robertmhaas(at)gmail(dot)com
Subject: Re: [JDBC] Support for JDBC setQueryTimeout, et al.
Date: 2010-10-15 14:52:48
Message-ID: 20101015145248.GR26232@tamriel.snowman.net (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-jdbc
* Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> Stephen Frost <sfrost(at)snowman(dot)net> writes:
> > errr, I don't believe RESET ALL touches cache'd plans and whatnot (which
> > is actually a problem I've run into in the past, because changing the
> > search_path also doesn't invalidate plans, and neither does set role, so
> > you end up with cache'd plans that try to hit things you don't have
> > permissions to any more :( ).
> 
> Yeah, actually what you need is DISCARD ALL when reassigning a
> connection to another client.  Anything less than that assumes the
> clients are cooperating closely, ie they *want* the same prepared
> statements etc.

That assumption is certainly something I feel we should consider a valid
and important use-case.  I'd think a lot of the time your typical website
is going to be using a dedicated pooler for connections and a dedicated
database where having those queries cache'd would be good.

I recall seeing a module that even set things up so you feed it all the
queries that you're going to run and it plans them all out for you when
you start up the pooler.  Been meaning to look into it more, but..

The whole problem with search_path and role is very frustrating.  We've
taken to just hacking things to be dynamic SQL whenever it's
role-specific, but that's a really poor solution.  I wonder if it would
be possible to have the function and prepare'd plan caches be key'd off
of the search_path and role too..?  So if you change one of those you
end up having to re-plan it, but then that's also cached, etc..

> But even if you make that assumption, a pooler that
> isn't even capable of sending an ABORT between clients doesn't seem
> usable to me.  For example, if a client loses its network connection
> mid-transaction, that failure will cascade to other clients if you
> don't have any ability to reset the database session before handing
> it to another client.

I agree, the pooler definitely needs to be able to handle those
situations or you'll be running into some serious problems down the
road.

	Thanks,

		Stephen

In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2010-10-15 14:54:57
Subject: Re: string function - "format" function proposal
Previous:From: Mike FowlerDate: 2010-10-15 14:51:51
Subject: Re: First patch proposal

pgsql-jdbc by date

Next:From: Kevin GrittnerDate: 2010-10-15 16:02:47
Subject: Re: [JDBC] Support for JDBC setQueryTimeout, et al.
Previous:From: Tom LaneDate: 2010-10-15 14:38:54
Subject: Re: [JDBC] Support for JDBC setQueryTimeout, et al.

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