BUG #4440: positioned_load in pos_newload failed - Mailing list pgsql-bugs

The following bug has been logged online:

Bug reference:      4440
Logged by:          Steve Jones
Email address:      steve.jones@codeweavers.net
PostgreSQL version: V8.1.4
Operating system:   Windows XP + sp3
Description:        positioned_load in pos_newload failed
Details:

I have an issue using the following versions of software:

PostgreSQL ANSI ODBC driver – V8.03.02.00
PostgreSQL - V8.1.4

I have a table (say TableA) in PostgreSQL with an INSERT trigger in place,
which simply has a SELECT statement inside (in fact it doesn’t seem to
matter what is inside the trigger, the error is always the same).

When attempting to insert a row into TableA via the ODBC driver from SQL
Server (Query Analyzer or DTS), the following error is received:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: positioned_load in pos_newload failed]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::InsertRow
returned 0x80004005: ].

The error from the ODBC debug log is (abridged):

[480.312]PGAPI_Columns:
table='area_loadings',field_name='IsFemale',type=1043,name='varchar'
[480.341]PGAPI_Columns:
table='area_loadings',field_name='Age',type=1043,name='varchar'
[480.371]PGAPI_Columns: table='area_loadings',field_name='Area
Code',type=1043,name='varchar'
[480.400]PGAPI_Columns:
table='area_loadings',field_name='Loading',type=1043,name='varchar'
[480.402]conn=054440F0, query='close "SQL_CUR05449728"'
[480.443]conn=054440F0, query='select relhasoids, c.oid from pg_class c,
pg_namespace n where relname = 'area_loadings' and nspname =
'campaign_import' and c.relnamespace = n.oid'
[480.450] [ fetched 1 rows ]
[480.458]conn=054440F0, query='select a.attname, a.atttypid from pg_index i,
pg_attribute a where indrelid=17634 and indnatts=1 and indisunique and
indexprs is null and indpred is null and i.indrelid = a.attrelid and
a.attnum=i.indkey[0] and attnotnull and atttypid in (23, 26)'
[480.465] [ fetched 0 rows ]
[480.475]conn=054440F0, query='declare "SQL_CUR0544D140" scroll cursor with
hold for select * , "ctid" from "campaign_import"."area_loadings"'+'fetch
100 in "SQL_CUR0544D140"'
[480.487] [ fetched 2 rows ]
[480.576]conn=054440F0, query='insert into "campaign_import"."area_loadings"
("IsFemale", "Age", "Area Code", "Loading") values (E'1', E'23', E'A',
E'1.11')'
[480.585]conn=054440F0, query='select * , "ctid" from
"campaign_import"."area_loadings" where ctid = currtid(0, '(0,0)') '
[480.593] [ fetched 0 rows ]
[480.597]STATEMENT ERROR: func=positioned_load, desc='', errnum=8,
errmsg='can't find the add and updating row because of the lack of oid'

Disabling the trigger removes the error so I’m assuming here the issue is
with the trigger. I’ve tested the trigger is functional by running an
INSERT from a SQL session in PGAdmin

The same error occurs from either a DTS package or linked server call via
OPENQUERY.

If I test using a PostgreSQL v8.2 database instead, the issue does not
happen. This is with the same ODBC driver.

I’ve seen this posted elsewhere but with no responses – a fundamental
problem for people wanting to use third party data transformation tools
connecting to 8.1.x via the ODBC driver, using transforming triggers in the
PostgreSQL database.