PostgreSQL JDBC - Room to optimize updates through ResultSet

Author

Room to optimize updates through ResultSet

Kevin Grittner

2005-11-23, 8:24 pm

I'm not really complaining, since PostgreSQL is significantly faster on
these updates than another product (for which the license agreement
prohibits posting benchmarks without their approval); but, it does seem
that there is room to optimize this if anyone is so inclinded. (I may
look at it myself some day, but there are other issues that matter much
more to me, so not soon.)

(1) I would think the creation of the exception could be eliminated.
There is no exceptional situation here.

(2) It seems like it might be possible to avoid some round trips here,
although I haven't looked at the issues in detail.

I had about 350,000 of these stack traces in about two hours of
loglevel=2 output, which bloats things a bit.

<=BE CommandStatus(COMMIT
)
<=BE ReadyForQuery(I)
simple execute,
handler=org.postgresql.jdbc2. AbstractJdbc2Stateme
nt$StatementResultHa
ndler@9c5989,
maxRows=0, fetchSize=0, flags=1
FE=> Bind(stmt=S_1,portal
=null)
FE=> Execute(portal=null,
limit=0)
FE=> Parse(stmt=null,quer
y="SELECT * FROM "StepExport" WHERE
"interfaceName" = 'OPD' AND "messageType" = 'DACourtEvent' AND "seqNo" =
125999 AND "countyNo"
= 9",oids={})
FE=> Bind(stmt=null,porta
l=null)
FE=> Describe(portal=null
)
FE=> Execute(portal=null,
limit=0)
FE=> Sync
<=BE BindComplete [null]
<=BE CommandStatus(BEGIN)

<=BE ParseComplete [null]
<=BE BindComplete [null]
<=BE RowDescription(19)
<=BE DataRow
<=BE CommandStatus(SELECT
)
<=BE ReadyForQuery(T)
checking if rs is updateable
getting primary keys
org.postgresql.util.PSQLException: The column name oid was not found in
this ResultSet.
at
org.postgresql.jdbc2. AbstractJdbc2ResultS
et. findColumn(AbstractJ
dbc2ResultSet.java:2362)
at
org.postgresql.jdbc2. AbstractJdbc2ResultS
et. isUpdateable(Abstrac
tJdbc2ResultSet.java:1533)
at
org.postgresql.jdbc2. AbstractJdbc2ResultS
et. checkUpdateable(Abst
ractJdbc2ResultSet.java:2426)
at
org.postgresql.jdbc2. AbstractJdbc2ResultS
et. updateValue(Abstract
Jdbc2ResultSet.java:2669)
at
org.postgresql.jdbc2. AbstractJdbc2ResultS
et. updateObject(Abstrac
tJdbc2ResultSet.java:1131)
at
org.postgresql.jdbc2. AbstractJdbc2ResultS
et. updateObject(Abstrac
tJdbc2ResultSet.java:1485)
at
us.wi.state.courts.jade.query.Query.updateOneRow(Query.java:3898)
at
us.wi.state.courts.jade.query.Query. executeUpdateFromCom
pound(Query.java:1770)
at
gov.wicourts.trancentral.query.customproc.TranApplyCQ.update(TranApplyCQ.java:457)
at
gov.wicourts.trancentral.query.customproc.TranApplyCQ. executeOptimisticall
y(TranApplyCQ.java:394)
at
gov.wicourts.trancentral.query.customproc.TranApplyCQ. executeProcedure(Tra
nApplyCQ.java:582)
at
us.wi.state.courts.jade.query. CustomProcedureQuery
. execute(CustomProced
ureQuery.java:58)
at
us.wi.state.courts.jade.server.dbs.DbRequestServer.run(DbRequestServer.java:1154)
at java.lang.Thread.run(Thread.java:595)
SQLException: SQLState(42703)
Exception: org.postgresql.util.PSQLException: The column name oid was
not found in this ResultSet.
simple execute,
handler=org.postgresql.jdbc2. AbstractJdbc2Stateme
nt$StatementResultHa
ndler@ba4a4d,
maxRows=0, fetchSize=0, flags=1
FE=> Parse(stmt=null,quer
y="SELECT NULL AS TABLE_CAT, n.nspname AS
TABLE_SCHEM, ct.relname AS TABLE_NAME, a.attname AS COLUMN_NAME,
a.attnum AS KEY_SEQ,
ci.relname AS PK_NAME FROM pg_catalog.pg_namespace n,
pg_catalog.pg_class ct, pg_catalog.pg_class ci, pg_catalog.pg_attribute
a, pg_catalog.pg_index i W
HERE ct.oid=i.indrelid AND ci.oid=i.indexrelid AND a.attrelid=ci.oid
AND i.indisprimary AND ct.relname = 'StepExport' AND ct.relnamespace =
n.oid ORDER
BY table_name, pk_name, key_seq",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 RowDescription(6)
<=BE DataRow
<=BE DataRow
<=BE DataRow
<=BE DataRow
<=BE CommandStatus(SELECT
)
<=BE ReadyForQuery(T)
no of keys=4
checking primary key true
simple execute,
handler=org.postgresql.jdbc2. AbstractJdbc2Stateme
nt$StatementResultHa
ndler@643af2,
maxRows=0, fetchSize=0, flags=1
FE=> Parse(stmt=null,quer
y="SELECT attname FROM pg_catalog.pg_attribute
WHERE attrelid = $1 AND attnum = $2",oids={23,23})
FE=> Bind(stmt=null,porta
l=null,$1=<180887>,$2=<6> )
FE=> Describe(portal=null
)
FE=> Execute(portal=null,
limit=0)
FE=> Sync
<=BE ParseComplete [null]
<=BE BindComplete [null]
<=BE RowDescription(1)
<=BE DataRow
<=BE CommandStatus(SELECT
)
<=BE ReadyForQuery(T)
updating UPDATE "StepExport" SET "timeModified" = ? WHERE "countyNo" =
? and "interfaceName" = ? and "messageType" = ? and "seqNo" = ?
simple execute,
handler=org.postgresql.jdbc2. AbstractJdbc2Stateme
nt$StatementResultHa
ndler@59f574,
maxRows=0, fetchSize=0, flags=5
FE=> Parse(stmt=null,quer
y="UPDATE "StepExport" SET "timeModified" =
$1 WHERE "countyNo" = $2 and "interfaceName" = $3 and "messageType" = $4
and "seqNo"
= $5",oids=& #123;0,23,1043,1043,
23})
FE=> Bind(stmt=null,porta
l=null,$1=<2005-11-23 14:34:39.868000
-0600>,$2=<9>,$3=<OPD >,$4=<DACourtEvent>,$5=<125999> )
FE=> Describe(portal=null
)
FE=> Execute(portal=null,
limit=1)
FE=> Sync
<=BE ParseComplete [null]
<=BE BindComplete [null]
<=BE NoData
<=BE CommandStatus(UPDATE
1)
<=BE ReadyForQuery(T)
copying data
done updates
simple execute,
handler=org.postgresql.jdbc2. AbstractJdbc2Stateme
nt$StatementResultHa
ndler@1b6c1,
maxRows=0, fetchSize=0, flags=5
FE=> Parse(stmt=null,quer
y="UPDATE "DbTranImageStatus" SET "lastSeqNo"
= $1 WHERE "countyNo" = $2",oids={1700,23})
FE=> Bind(stmt=null,porta
l=null,$1=<1132778079883>,$2=<9> )
FE=> Describe(portal=null
)
FE=> Execute(portal=null,
limit=1)
FE=> Sync
<=BE ParseComplete [null]
<=BE BindComplete [null]
<=BE NoData
<=BE CommandStatus(UPDATE
1)
<=BE ReadyForQuery(T)
simple execute,
handler=org.postgresql.jdbc2. AbstractJdbc2Connect
ion$TransactionComma
ndHandler@16bfcc8,
maxRows=0, fetchSize=0, flags=22
FE=> Bind(stmt=S_2,portal
=null)
FE=> Execute(portal=null,
limit=1)
FE=> Sync
<=BE BindComplete [null]
<=BE CommandStatus(COMMIT
)
<=BE ReadyForQuery(I)

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

Oliver Jowett

2005-11-23, 8:24 pm

Kevin Grittner wrote:
> I'm not really complaining, since PostgreSQL is significantly faster on
> these updates than another product (for which the license agreement
> prohibits posting benchmarks without their approval); but, it does seem
> that there is room to optimize this if anyone is so inclinded. (I may
> look at it myself some day, but there are other issues that matter much
> more to me, so not soon.)
>
> (1) I would think the creation of the exception could be eliminated.
> There is no exceptional situation here.

Yeah. Patch? :)

(the only reason it gets logged is that the implementation of
java.sql.SQLException logs on construction -- i.e. we can't avoid
logging every exception we create..)

> (2) It seems like it might be possible to avoid some round trips here,
> although I haven't looked at the issues in detail.

What specifically? IIRC we're piggybacking on the metadata code which
already has lots of server-version-specific logic in it .. we don't
really want to duplicate that logic.

-O

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Oliver Jowett

2005-11-24, 7:23 am

Kevin Grittner wrote:

> (1) I would think the creation of the exception could be eliminated.
> There is no exceptional situation here.

Done in HEAD.

-O

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

Sponsored Links