Re: Implementing RESET CONNECTION ... - Mailing list pgsql-patches

On Mon, 3 Jan 2005, [UTF-8] Hans-Jürgen Schönig wrote:

> 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.

Actually it does.  The V3 protocol provides a ParameterStatus message that
notifies us when certain GUC parameters are modified.  If someone changes
the DateStyle underneath us, we throw an Exception and destroy the
connection.

> 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.

Let me explain a little more how this works from the JDBC driver's
perspective.  The API for getting a PreparedStatement object is:

PreparedStatement pstmt = Connection.prepareStatement(String sql);

The sql string may have placeholders to indicate where parameters go.
From this API the JDBC driver can do one of three things with the
PreparedStatement object when it is executed.

1) It can do the parameter substituition directly on the driver side and
send a simple query to the server.

2) It can use an unnamed statement to execute the query sending the
parameters separately.

3) It can use a named statement to execute the query sending the
parameters separately.

We are really only interested in the third case here, because this is the
only one that leaves a permanent server state.  The namespace for protocol
executed named statements is shared with sql executed PREPARE commands, so
this is applicable to the RESET command you've implemented.

Note that the user has never provided a name for this named statement.
The JDBC driver uses S_N where N is an incrementing number per connection,
so there will be no conflicts.  What we'd like the driver to eventually do
is detect the following condition:

PreparedStatement ps1 = conn.prepareStatement(sql);
PreparedStatement ps2 = conn.prepareStatement(sql);

Since both PreparedStatements are derived from the same sql string it
would be nice if they could use the same underlying S_N server named
statement instead of creating two identical ones.  Now consider this in a
connection pool:

Connection conn;
PreparedStatement ps;

conn = pool.getConnection();
ps = conn.prepareStatement(sql);
conn.close();

conn = pool.getConnection();
ps = conn.prepareStatement(sql);
conn.close();

This situation is slightly different because we may or may not have gotten
the same connection back, but we don't really care.  We only want to know
if whatever connection we currently have has already seen and prepared the
sql string we are looking for.  If we add the RESET you've implemented
then it will never have a pre-prepared statement for us to use, so we'll
have to create a new one every time.

Kris Jurka