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.AbstractJdbc2Statement$StatementResultHandler@9c5989,
maxRows=0, fetchSize=0, flags=1
FE=> Bind(stmt=S_1,portal=null)
FE=> Execute(portal=null,limit=0)
FE=> Parse(stmt=null,query="SELECT * FROM "StepExport" WHERE
"interfaceName" = 'OPD' AND "messageType" = 'DACourtEvent' AND "seqNo" =
125999 AND "countyNo"
= 9",oids={})
FE=> Bind(stmt=null,portal=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.AbstractJdbc2ResultSet.findColumn(AbstractJdbc2ResultSet.java:2362)
at
org.postgresql.jdbc2.AbstractJdbc2ResultSet.isUpdateable(AbstractJdbc2ResultSet.java:1533)
at
org.postgresql.jdbc2.AbstractJdbc2ResultSet.checkUpdateable(AbstractJdbc2ResultSet.java:2426)
at
org.postgresql.jdbc2.AbstractJdbc2ResultSet.updateValue(AbstractJdbc2ResultSet.java:2669)
at
org.postgresql.jdbc2.AbstractJdbc2ResultSet.updateObject(AbstractJdbc2ResultSet.java:1131)
at
org.postgresql.jdbc2.AbstractJdbc2ResultSet.updateObject(AbstractJdbc2ResultSet.java:1485)
at
us.wi.state.courts.jade.query.Query.updateOneRow(Query.java:3898)
at
us.wi.state.courts.jade.query.Query.executeUpdateFromCompound(Query.java:1770)
at
gov.wicourts.trancentral.query.customproc.TranApplyCQ.update(TranApplyCQ.java:457)
at
gov.wicourts.trancentral.query.customproc.TranApplyCQ.executeOptimistically(TranApplyCQ.java:394)
at
gov.wicourts.trancentral.query.customproc.TranApplyCQ.executeProcedure(TranApplyCQ.java:582)
at
us.wi.state.courts.jade.query.CustomProcedureQuery.execute(CustomProcedureQuery.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.AbstractJdbc2Statement$StatementResultHandler@ba4a4d,
maxRows=0, fetchSize=0, flags=1
FE=> Parse(stmt=null,query="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,portal=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.AbstractJdbc2Statement$StatementResultHandler@643af2,
maxRows=0, fetchSize=0, flags=1
FE=> Parse(stmt=null,query="SELECT attname FROM pg_catalog.pg_attribute
WHERE attrelid = $1 AND attnum = $2",oids={23,23})
FE=> Bind(stmt=null,portal=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.AbstractJdbc2Statement$StatementResultHandler@59f574,
maxRows=0, fetchSize=0, flags=5
FE=> Parse(stmt=null,query="UPDATE "StepExport" SET "timeModified" =
$1 WHERE "countyNo" = $2 and "interfaceName" = $3 and "messageType" = $4
and "seqNo"
= $5",oids={0,23,1043,1043,23})
FE=> Bind(stmt=null,portal=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.AbstractJdbc2Statement$StatementResultHandler@1b6c1,
maxRows=0, fetchSize=0, flags=5
FE=> Parse(stmt=null,query="UPDATE "DbTranImageStatus" SET "lastSeqNo"
= $1 WHERE "countyNo" = $2",oids={1700,23})
FE=> Bind(stmt=null,portal=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.AbstractJdbc2Connection$TransactionCommandHandler@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)