Re: Implementing RESET CONNECTION ...

Kris,

I have seen that the JDBC driver is doing some GUC settings.
However, this does not prevent you from bad users who change GUCs for
some reason. It might very well happen that a user sets the DateStyle to
some different value temporarily. A different program would in this case
behave RANDOMLY depending on the connection assigned by the pool
The basic idea is that all GUCs are cleaned up because they might have
been changed.
Personally I expect a new connection to be clean.

The same applies to prepared statements - different programs (let's say
websites) might give plans the same name and this would lead to RANDOM
conflicts (depending on which connection you get from the pool).
However, they still might share the same connection pool.

As far as prepared statements are concerned: Maybe a tablefunction
pg_list_prepared_plans() might make sense - you could use that for your
purpose (the same applies to cursors).
Actually I was thinking of including DEALLOCATE ALL into this plan so
that just prepared plans can be deallocated as well.

I don't think a mark would make too much sense.
If partial resets are really desirable it is better to add RESET
CURSORS, RESET PREPARED STATEMENTS, ...
Fell free to add code :).

Best regards,

Hans

Kris Jurka wrote:

>On Thu, 30 Dec 2004, [ISO-8859-1] Hans-J�rgen Sch�nig wrote:
>
>
>
>>We have implemented a patch which can be used by connection pools for
>>instance. RESECT CONNECTION cleans up a backend so that it can be
>>reused. Temp tables, LISTEN / NOTIFY stuff, WITH HOLD cursors, open
>>transactions, prepared statements and GUCs are cleaned up. I hope we
>>have not missed important per-backend information.
>>
>>
>>
>
>From the JDBC driver's perspective this doesn't meet the needs I'd like to
>see in a connection reset. In the initial connection setup a number of
>GUC variables are tweaked to what the JDBC driver desires (DateStyle,
>client_encoding). When resetting we'd want to reset to this point, not
>the default values. Perhaps some kind of MARK command, kind of like a
>savepoint to rollback to would better specify this.
>
>Also I don't like the idea of cleaning up prepared statements. While it
>doesn't do so now, the JDBC driver would like to do statement pooling at
>some point. This means the same underlying server prepared statement can
>be reused transparently from multiple callers. In a connection pool where
>a connection is grabbed and returned for virtually each sql execution this
>is key to getting the performance boost from prepared statements. We
>
>
>don't want to have to reprepare on each connection and we don't want them
>to disappear from underneath us, because the prepared statements are
>generated transparently by the JDBC driver, not directly by a user
>statement.
>
>Kris Jurka
>
>