Room to optimize updates through ResultSet

Поиск
Список
Период
Сортировка
От Kevin Grittner
Тема Room to optimize updates through ResultSet
Дата
Msg-id 43849CD80200002500000910@gwmta.wicourts.gov
обсуждение исходный текст
Ответы Re: Room to optimize updates through ResultSet  (Oliver Jowett <oliver@opencloud.com>)
Re: Room to optimize updates through ResultSet  (Oliver Jowett <oliver@opencloud.com>)
Список pgsql-jdbc
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)



В списке pgsql-jdbc по дате отправления:

Предыдущее
От: Kris Jurka
Дата:
Сообщение: Removing examples
Следующее
От: "Kevin Grittner"
Дата:
Сообщение: Logging from multiple connections