A certain error message like “Error type: Odbc error. Odbc operation attempted: SQLExecDirect. [22001:3452545: on HSTMT] [Strategy][ODBC PostgreSQL Wire Protocol driver][PostgreSQL]ERROR: value too long for type character varying(8000)(File src\backend\utils\adt\varchar.c;Line 623;Routine varchar;) [22001:3624914: on HSTMT] [Strategy][ODBC PostgreSQL Wire Protocol driver][PostgreSQL]Failed transaction. The current transaction rolled back.” is outputted to DSSErrors.log
Symptom
Error message as below is recorded in DSSErrors.log when using PostgreSQL database 9.1 or above as history list repository in Strategy 10.4. Accessing to History list result in Strategy Web would get error and fail.
Error Message:
2018-08-13 04:28:15.983 [HOST:XXXXXXX][SERVER:CastorServer][PID:5632][THR:7748][Database Classes][Error] Execute Query failed.
Error type: Odbc error. Odbc operation attempted: SQLExecDirect. [22001:3452545: on HSTMT] [Strategy][ODBC PostgreSQL Wire Protocol driver][PostgreSQL]ERROR: value too long for type character varying(8000)(File src\backend\utils\adt\varchar.c;Line 623;Routine varchar;) [22001:3624914: on HSTMT] [Strategy][ODBC PostgreSQL Wire Protocol driver][PostgreSQL]Failed transaction. The current transaction rolled back. Connection String: DSN=PostgreDBHL;UID=xxxxxx;DB=xxxxxx;HOST=xx.xx.xx.xx;PORT=xxxx;. SQL Statement: INSERT INTO DSSCSRPTCACH ......
CAUSE:
This is caused by a change in a default setting in PostgreSQL starting from version 9.1. standard_conforming_strings would be set to On by default. This would automatically escape out backslashes in strings and increase the length of the string which causes the above ODBC error.
REFERENCE:
Changes from PostgreSQL 9.1: https://www.postgresql.org/docs/9.1/static/runtime-config-compatible.html
ACTION:
Inside of the ODBC DSN, define the initialization string to be
SET STANDARD_CONFORMING_STRINGS TO OFF;
Note:
If the same PostgreSQL DB has been used as a metadata repository also (using the same ODBC DSN), the setting above may cause the metadata to behave abnormally.
In such scenario, users need to create a DSN for history list repository separately and set the initialization string to this DSN only.
KB441520
