Re: Statement Timeout and Locking - Mailing list pgsql-jdbc

On Thu, 28 Apr 2005, Kris Jurka wrote:
> On Thu, 28 Apr 2005, Tom Lane wrote:
> > Markus Schaber <schabi@logix-tt.com> writes:
> > >         conn.createStatement().execute("SET statement_timeout TO 5000; LOCK TABLE exampletable IN EXCLUSIVE MODE;
SETstatement_timeout TO DEFAULT;"); 
> >
> > At least in more recent JDBC drivers, I'd expect the above to fail
> > entirely because you can only put one SQL command per V3 Parse message.
> > Try splitting it into three statements.
>
> The JDBC driver splits this query itself and issues it in three queries
> internally.
>

This actually is the problem.  It works as three separate statements, but
fails as one.   The server doesn't seem to recognize the SET when other
commands come in before Sync.

Driver splitting queries:

 <=BE ReadyForQuery(I)
simple execute,
handler=org.postgresql.jdbc2.AbstractJdbc2Statement$StatementResultHandler@1aaa14a,
maxRows=0, fetchSize=0, flags=1
 FE=> Parse(stmt=S_1,query="BEGIN",oids={})
 FE=> Bind(stmt=S_1,portal=null)
 FE=> Execute(portal=null,limit=0)
 FE=> Parse(stmt=null,query="SET statement_timeout TO 5000",oids={})
 FE=> Bind(stmt=null,portal=null)
 FE=> Describe(portal=null)
 FE=> Execute(portal=null,limit=0)
 FE=> Parse(stmt=null,query=" LOCK TABLE locktest IN EXCLUSIVE
MODE",oids={})
 FE=> Bind(stmt=null,portal=null)
 FE=> Describe(portal=null)
 FE=> Execute(portal=null,limit=0)
 FE=> Parse(stmt=null,query=" SET statement_timeout TO DEFAULT",oids={})
 FE=> Bind(stmt=null,portal=null)
 FE=> Describe(portal=null)
 FE=> Execute(portal=null,limit=0)
 FE=> Sync

This hangs while a version split by the caller works:

 <=BE ReadyForQuery(I)
simple execute,
handler=org.postgresql.jdbc2.AbstractJdbc2Statement$StatementResultHandler@7a84e4,
maxRows=0, fetchSize=0, flags=1
 FE=> Parse(stmt=S_1,query="BEGIN",oids={})
 FE=> Bind(stmt=S_1,portal=null)
 FE=> Execute(portal=null,limit=0)
 FE=> Parse(stmt=null,query="SET statement_timeout TO 5000",oids={})
 FE=> Bind(stmt=null,portal=null)
 FE=> Describe(portal=null)
 FE=> Execute(portal=null,limit=0)
 FE=> Sync
 <=BE ParseComplete [S_1]
 <=BE BindComplete [null]
 <=BE CommandStatus(BEGIN)
 <=BE ParseComplete [null]
 <=BE BindComplete [null]
 <=BE NoData
 <=BE CommandStatus(SET)
 <=BE ReadyForQuery(T)
simple execute,
handler=org.postgresql.jdbc2.AbstractJdbc2Statement$StatementResultHandler@9ed927,
maxRows=0, fetchSize=0, flags=1
 FE=> Parse(stmt=null,query="LOCK TABLE locktest IN EXCLUSIVE
MODE",oids={})
 FE=> Bind(stmt=null,portal=null)
 FE=> Describe(portal=null)
 FE=> Execute(portal=null,limit=0)
 FE=> Sync
 <=BE ParseComplete [null]
 <=BE BindComplete [null]
 <=BE NoData
 <=BE ErrorMessage(ERROR: canceling query due to user request

Kris Jurka