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?

> I did it slightly hacky way - if DeallocateStmt->name is
> NULL is signifies DEALLOCATE ALL command. All the code
> that looks into DeallocateStmt seems to survive the situation
> so it should be problem. If still a new node is needed
> or additional field in the node I can rework the patch.

Wouldn't it be easier to just add a bool to DeallocateStmt?

--
Alvaro Herrera http://www.PlanetPostgreSQL.org
"Si un desconocido se acerca y te regala un CD de Ubuntu ...
Eso es ... Eau de Tux"

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Alvaro Herrera <alvherre@alvh.no-ip.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.

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.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

On 3/27/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Alvaro Herrera <alvherre@alvh.no-ip.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...2/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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Marko Kreen wrote:
> 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.

Totally agree. Please make the adjustments to DEALLOCATE ALL, and roll
that into the 2004 patch for RESET SESSION and post and updated version.
Thanks.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings