PostgreSQL JDBC - Statement Timeout and Locking

Author

Statement Timeout and Locking

Markus Schaber

2005-04-28, 7:24 am

Hello,

In one of our maintainance apps that shuffles some data across
independent databases, we need to enshure data consistency, and decided
to do this by acquiring a lock in the target database. Now we do not
want the application to wait indefinitely for this lock, but fail and
complain loudly whenever locking fails.

When using the SQL statement
SET statement_timeout TO 5000; LOCK TABLE exampletable IN EXCLUSIVE
MODE; SET statement_timeout TO DEFAULT;
in psql, everything works fine. If someone else has the lock, it aborts with
ERROR: canceling query due to user request
after waiting 5 seconds.

However, the attached java file shows a different behaviour, it seems to
wait forever (at least it waited three minutes, and I'll keep it running
over lunch).

I use PostgreSQL 8.0 JDBC3 with SSL (build 311) and a debian PostgreSQL
7.4.7-5 server.

Does someone have an explanation for this, or even better, a fix or
workaround?

Markus

Tom Lane

2005-04-28, 9:23 am

Markus Schaber <schabi@logix-tt.com> writes:
> conn.createStatement().execute("SET statement_timeout TO 5000; LOCK TABLE exampletable IN EXCLUSIVE MODE; SET statement_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.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Kris Jurka

2005-04-28, 9:23 am

On Thu, 28 Apr 2005, Tom Lane wrote:

> Markus Schaber <schabi@logix-tt.com> writes:
>
> 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.

Kris Jurka

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Kris Jurka

2005-04-28, 11:24 am

On Thu, 28 Apr 2005, Kris Jurka wrote:
> On Thu, 28 Apr 2005, Tom Lane wrote:
>
> 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. AbstractJdbc2Stateme
nt$StatementResultHa
ndler@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,quer
y="SET statement_timeout TO 5000",oids={})
FE=> Bind(stmt=null,porta
l=null)
FE=> Describe(portal=null
)
FE=> Execute(portal=null,
limit=0)
FE=> Parse(stmt=null,quer
y=" LOCK TABLE locktest IN EXCLUSIVE
MODE",oids={})
FE=> Bind(stmt=null,porta
l=null)
FE=> Describe(portal=null
)
FE=> Execute(portal=null,
limit=0)
FE=> Parse(stmt=null,quer
y=" SET statement_timeout TO DEFAULT",oids={})
FE=> Bind(stmt=null,porta
l=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. AbstractJdbc2Stateme
nt$StatementResultHa
ndler@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,quer
y="SET statement_timeout TO 5000",oids={})
FE=> Bind(stmt=null,porta
l=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. AbstractJdbc2Stateme
nt$StatementResultHa
ndler@9ed927,
maxRows=0, fetchSize=0, flags=1
FE=> Parse(stmt=null,quer
y="LOCK TABLE locktest IN EXCLUSIVE
MODE",oids={})
FE=> Bind(stmt=null,porta
l=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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Markus Schaber

2005-04-28, 1:26 pm

Hi, Tom,

Tom Lane schrieb:

> Anyway the short-term answer for Markus is "don't do it that way".
> We ought to think about making the backend's behavior more consistent,
> though.

I'll split the query into three. Having it in one query just was a
convenience here.

Thanks,
Markus

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere
" to majordomo@postgresql
.org)

Sponsored Links