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

* 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