Re: DEALLOCATE ALL

On 3/27/07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> writes:
> > Marko Kreen wrote:
> >> When pooling connections where prepared statements are in use,
> >> it is hard to give new client totally clean connection as
> >> there may be allocated statements that give errors when
> >> new client starts preparing statements again.
>
> > Huh, didn't we have a RESET SESSION command to do just that? What about
> > cursors, for example?
>
> We don't actually *have* one, but I believe it was agreed that that is
> the right API to provide. If a pooler has to remember to clear prepared
> statements, GUCs, cursors, and who knows what else, it'll be perpetually
> broken because there'll be something it omits.

Well. Please apply following patch then:

http://archives.postgresql.org/pgsql-patches/2004-12/msg00228.php

Even if it is incomplete, the missing parts can be added later.
I see no reason to keep it from users.

> There might be a use-case for DEALLOCATE ALL, but needs of poolers
> aren't it. I'd be inclined to vote against this unless someone can
> point to a better use-case.

Ok, a non-pooler argument: prepared statements are supposed to be
garbage-collected by the user. Thats it. There should be friendly
way to get a clean state without the need for user to specifically
keep track of whats allocated, or to do messy exception-handling
around PREPARE/DEALLOCATE. (PREPARE OR REPLACE and DEALLOCATE IF EXISTS
would also lessen the pain.)

Then a pooler argument: there is one pooler where RandomJoe executes
queries and another for specific app where the subset of SQL it uses is
known. I want to RESET only specific things in app case. So it would be
good if the RESET-s for specific areas would be available.

Also the objections to the Hans' patch give impression that different
pooling solutions want different RESET EVERYTHING, so again,
it would be good if RESET-s for different areas are available
and the all-encomassing RESET EVERYTHING just ties all the specific
RESETs together.

--
marko