Обсуждение: issue
We are running a third party application on windows. It inserts about 15,000 rows a day into a postgres database. From time to time we see an issue where the insert rollsbacks.
We do not have access to the client code but I would like to rule out odbc driver and postgres. We see no errors or warnings in the postgres log.
Windows 7 we have installed psqlodbc Driver Version 9.02.01.00 Unicode
Windows 7 client connects to a postgres sql server on freebsd 10.0 Postgresql 9.0.15
Looking for some gentle advice on how to diagnose this issue.
Thank You
mylog typical result
[1788-1055553.436]SC_init_Result(653b058)[1788-1055553.436]SC_Destructor: self=0653B058, self->result=00000000, self->hdbc=0483B460
[1788-1055553.436]reset_a_column_binding: entering ... self=0653B0E0, bindings_allocated=1, icol=1
[1788-1055553.436]APD_free_params: ENTER, self=0653B158
...
mylog error result
[1788-1055559.077]SC_init_Result(653b058)[1788-1055559.077]SC_Destructor: self=0653B058, self->result=00000000, self->hdbc=0483B460
[1788-1055559.077][[SQLEndTran]][1788-1055559.077]entering PGAPI_Transact: hdbc=0483B460, henv=00000000
[1788-1055559.077]PGAPI_Transact: sending on conn 0483B460 '1'
psql log
[1055547.871]conn=0483B460, query='SELECT * , "ctid", "id" FROM raw_trans where ctid = '(7840,70)' '
[1055547.886] [ fetched 1 rows ]
[1055547.902]conn=0483B460, query='COMMIT'
[1055553.374]conn=0483B460, query='insert into "public"."raw_trans" ("_name", "_numericid", "_value", "_timestamp", "_quality") values (E'OMRONS.LINE34.PULLER SPEED', 10, E'418', '2014-11-04 04:14:50.301'::timestamp, 192) returning ctid'
[1055553.405] [ fetched 1 rows ]
[1055553.405]conn=0483B460, query='SELECT * , "ctid", "id" FROM raw_trans where ctid = '(7840,72)' '
[1055553.420] [ fetched 1 rows ]
[1055553.436]conn=0483B460, query='COMMIT'
[1055559.014]conn=0483B460, query='insert into "public"."raw_trans" ("_name", "_numericid", "_value", "_timestamp", "_quality") values (E'OMRONS.LINE34.PULLER SPEED', 10, E'405', '2014-11-04 04:14:55.942'::timestamp, 192) returning ctid'
[1055559.045] [ fetched 1 rows ]
[1055559.045]conn=0483B460, query='SELECT * , "ctid", "id" FROM raw_trans where ctid = '(7840,74)' '
[1055559.061] [ fetched 1 rows ]
[1055559.077]conn=0483B460, query='ROLLBACK'
[1055559.264]conn=0483B460, query='close "SQL_CUR0653A4F0"'
[1055559.264]conn=0483B460, PGAPI_Disconnect
[1055559.295]conn=0483B460, PGAPI_DriverConnect( in)='DSN=PostgreSQL35W;UID=royal_plant1;PWD=processlinx;', fDriverCompletion=0
[1055559.295]DSN info: DSN='PostgreSQL35W',server='172.19.37.8',port='5432',dbase='royal',user='royal_plant1',passwd='xxxxx'
[1055559.295] onlyread='0',protocol='7.4',showoid='0',fakeoidindex='0',showsystable='0'
[1055559.295] conn_settings='', conn_encoding='(null)'
[1055559.295] translation_dll='',translation_option=''
[1055559.295]Driver Version='09.02.0100,201306020000' linking 1600 static Multithread library
[1055559.295]Global Options: fetch=100, socket=4096, unknown_sizes=0, max_varchar_size=255, max_longvarchar_size=8190
[1055559.295] disable_optimizer=0, ksqo=0, unique_index=1, use_declarefetch=1
[1055559.295] text_as_longvarchar=1, unknowns_as_longvarchar=0, bools_as_char=1 NAMEDATALEN=64
[1055559.295] extra_systable_prefixes='dd_;', conn_settings='' conn_encoding=''
[1055559.311] [ PostgreSQL version string = '9.0.15' ]
[1055559.311] [ PostgreSQL version number = '9.0' ]
We do not have access to the client code but I would like to rule out odbc driver and postgres. We see no errors or warnings in the postgres log.
Windows 7 we have installed psqlodbc Driver Version 9.02.01.00 Unicode
Windows 7 client connects to a postgres sql server on freebsd 10.0 Postgresql 9.0.15
Looking for some gentle advice on how to diagnose this issue.
Thank You
mylog typical result
[1788-1055553.436]SC_init_Result(653b058)[1788-1055553.436]SC_Destructor: self=0653B058, self->result=00000000, self->hdbc=0483B460
[1788-1055553.436]reset_a_column_binding: entering ... self=0653B0E0, bindings_allocated=1, icol=1
[1788-1055553.436]APD_free_params: ENTER, self=0653B158
...
mylog error result
[1788-1055559.077]SC_init_Result(653b058)[1788-1055559.077]SC_Destructor: self=0653B058, self->result=00000000, self->hdbc=0483B460
[1788-1055559.077][[SQLEndTran]][1788-1055559.077]entering PGAPI_Transact: hdbc=0483B460, henv=00000000
[1788-1055559.077]PGAPI_Transact: sending on conn 0483B460 '1'
psql log
[1055547.871]conn=0483B460, query='SELECT * , "ctid", "id" FROM raw_trans where ctid = '(7840,70)' '
[1055547.886] [ fetched 1 rows ]
[1055547.902]conn=0483B460, query='COMMIT'
[1055553.374]conn=0483B460, query='insert into "public"."raw_trans" ("_name", "_numericid", "_value", "_timestamp", "_quality") values (E'OMRONS.LINE34.PULLER SPEED', 10, E'418', '2014-11-04 04:14:50.301'::timestamp, 192) returning ctid'
[1055553.405] [ fetched 1 rows ]
[1055553.405]conn=0483B460, query='SELECT * , "ctid", "id" FROM raw_trans where ctid = '(7840,72)' '
[1055553.420] [ fetched 1 rows ]
[1055553.436]conn=0483B460, query='COMMIT'
[1055559.014]conn=0483B460, query='insert into "public"."raw_trans" ("_name", "_numericid", "_value", "_timestamp", "_quality") values (E'OMRONS.LINE34.PULLER SPEED', 10, E'405', '2014-11-04 04:14:55.942'::timestamp, 192) returning ctid'
[1055559.045] [ fetched 1 rows ]
[1055559.045]conn=0483B460, query='SELECT * , "ctid", "id" FROM raw_trans where ctid = '(7840,74)' '
[1055559.061] [ fetched 1 rows ]
[1055559.077]conn=0483B460, query='ROLLBACK'
[1055559.264]conn=0483B460, query='close "SQL_CUR0653A4F0"'
[1055559.264]conn=0483B460, PGAPI_Disconnect
[1055559.295]conn=0483B460, PGAPI_DriverConnect( in)='DSN=PostgreSQL35W;UID=royal_plant1;PWD=processlinx;', fDriverCompletion=0
[1055559.295]DSN info: DSN='PostgreSQL35W',server='172.19.37.8',port='5432',dbase='royal',user='royal_plant1',passwd='xxxxx'
[1055559.295] onlyread='0',protocol='7.4',showoid='0',fakeoidindex='0',showsystable='0'
[1055559.295] conn_settings='', conn_encoding='(null)'
[1055559.295] translation_dll='',translation_option=''
[1055559.295]Driver Version='09.02.0100,201306020000' linking 1600 static Multithread library
[1055559.295]Global Options: fetch=100, socket=4096, unknown_sizes=0, max_varchar_size=255, max_longvarchar_size=8190
[1055559.295] disable_optimizer=0, ksqo=0, unique_index=1, use_declarefetch=1
[1055559.295] text_as_longvarchar=1, unknowns_as_longvarchar=0, bools_as_char=1 NAMEDATALEN=64
[1055559.295] extra_systable_prefixes='dd_;', conn_settings='' conn_encoding=''
[1055559.311] [ PostgreSQL version string = '9.0.15' ]
[1055559.311] [ PostgreSQL version number = '9.0' ]
On 11/14/2014 11:47 PM, perry@processlinx.com wrote: > > > We do not have access to the client code but I would like to rule out > odbc driver and postgres. We see no errors or warnings in the postgres log. > > Windows 7 we have installed psqlodbc Driver Version 9.02.01.00 Unicode > Windows 7 client connects to a postgres sql server on freebsd 10.0 > Postgresql 9.0.15 > > Looking for some gentle advice on how to diagnose this issue. These are all quite old versions, which doesn't help a great deal. Enable transaction and statement logging on the PostgreSQL server for starters. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services