Error messages trying to update a linked table.

[gnarula]

I've managed to setup a linked server on an SQL 2005 Server back to a Postgres 8.3.7 server running on RHEL. Select statements using 4 part table name (namely rms...tablename) and usning 'openquery(rms,'select * from tablename')' work just fine. However when I try to run an update statement as follows :

update rms...prclst
set action_code = 'D'
where prodid = '061348' and division = '00001' and location = '00' and cover_grade = 'CA' and cover_series = '8907'

it excutes for about 58 seconds and then terminates with the following error :

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "PGNP" for linked server "rms" reported an error. The provider reported an unexpected catastrophic failure.
Msg 7343, Level 16, State 4, Line 1
The OLE DB provider "PGNP" for linked server "rms" could not UPDATE table "[rms]...[prclst]". Unknown provider error.

The postgres server logs contain the following entries :

2010-07-06 21:57:47 EDT PID:3448 ERROR: cursor "pgnpcsr1" does not exist
2010-07-06 21:57:47 EDT PID:3448 STATEMENT: CLOSE pgnpcsr1

Any help will be appreciated. I have not tried to use an 'openquery' update yet. I will try that next.

B.T.W. I'm on version 1.3.2152 of PGNP

[Moderator]

Would you send DDL schema for table prclst to support@pgoledb.com?

How many records does the table have?

[gnarula]

Here's the DDL for the table :

CREATE TABLE prclst (
"timestamp" character varying(14),
action_code character varying(4),
division character varying(5) NOT NULL,
prodid character varying(22) NOT NULL,
list character varying(5) NOT NULL,
sold_flag character varying(1) NOT NULL,
location character varying(2) NOT NULL,
cover_grade character varying(2) NOT NULL,
cover_series character varying(4) NOT NULL,
start_date character varying(14) NOT NULL,
base_price character varying(8),
list_price character varying(8),
landed_frt character varying(8),
add_on character varying(8)
);


ALTER TABLE ONLY prclst
ADD CONSTRAINT prclst_pkey PRIMARY KEY (prodid, cover_grade, cover_series, list, start_date, location, sold_flag, division);


The table has ~ 710000 rows.

[Moderator]

Thank you for the additional information. I reproduced the issue with large number of rows. Working on a fix now.

In the mean time I recommend you to use OPENQUERY syntax: it works and it is significantly better performer.

Example:
update openquery(rms, 'select * from lzbsale.prclst where prodid = ''061348'' and division = ''00001'' and location = ''00'' and cover_grade = ''CA'' and cover_series = ''8907''')
set action_code = 'D'

Another, even faster way of updating the table uses EXEC syntax, for example:
EXEC ('update lzbsale.prclst set action_code = ''D'' where prodid = ''061348'' and division = ''00001'' and location = ''00'' and cover_grade = ''CA'' and cover_series = ''8907''') AT rms