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 :).
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.
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?