Re: How do I ensure same session over multiple statements??

Sean Shanny wrote:
> To all,
>
> I have a situation where I need to issue the following:
>
> set enable_nestloop = off
>
> before executing a prepared statement. We are using connection pooling.
> m_conn is a connection object that is retrieved earlier in the code.
>
> Code fragment....
>
> PreparedStatement ps = null;
> ResultSet rs = null;
>
> try
> {
> NEED TO DO THE SET HERE
> ps = m_conn.prepareStatement(
> "SELECT t1.id, t2.md5, t2.url from referral_temp t2 LEFT
> OUTER JOIN d_referral t1 ON t2.md5 = t1.referral_md5" );
> ps.setFetchSize( T_FETCH_SIZE );
>
> rs = ps.executeQuery();
>
> WHEN I AM DONE LIKE TO REVERSE THE SET
>
> .......
>
> I can do this just fine through psql since I am always in the same
> session. How would I do that given the above code fragment?

'Statement.executeUpdate("set enable_nestloop = off")' should do the
trick, unless your connection pooling implementation is very strange.
All the pools I've seen give you exclusive access to the connection
you're given until you return it to the pool (via close() etc) for
exactly this reason -- there is per-connection state that is important.
'set' is one case of this, but transaction state is the more obvious one!

You'll want to either put the un-"set" code into a finally block before
returning the connection to the pool or teach your connection pooling
implementation about per-connection state and how to reset it, though,
or you might leak the setting to other users of the connection.

-O