[ psqlodbc-Bugs-1000731 ] Write conflicts with multiple update statements in update rule on view

Bugs item #1000731, was opened at 2006-09-13 02:08
You can respond by visiting:
http://pgfoundry.org/tracker/?func=detail&atid=538&aid=1000731&group_id=1000125

Category: None
Group: None
Status: Open
Resolution: None
Priority: 3
Submitted By: Nobody (None)
Assigned to: Nobody (None)
Summary: Write conflicts with multiple update statements in update rule on view

Initial Comment:
Problem
-------

When two update statements are included in a views' update rule, any updates made by the first statement causes the
updateon the view to fail.  This problem has been replicated in linked ODBC tables on MS Access 2000, VS Net2005, and
BorlandDelphi 7 with ODBC via BDE. Errors returned always indicated that the record has been changed by another user. 

ODBC configuration parameters have been changed in an attempt to rectify the problem without success.

Software Versions
-----------------

Postgres Version String: PostgreSQL 8.1.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.5 (Debian 1:3.3.5-13)

ODBC Version(s) : PostgreSQL 8.02.00.02, PostgreSQL Unicode 801.02.00

Client OS : WinXP SP2, MDAC 2.8 SP1

Recreation
----------
--Create neccesary structures...

SET client_encoding = 'UTF8';
SET check_function_bodies = false;
SET client_min_messages = warning;

SET search_path = pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

CREATE TABLE rulprob1 (
    id integer NOT NULL,
    value1 integer
);
INSERT INTO rulprob1 (id, value1) VALUES (3, 1003);
INSERT INTO rulprob1 (id, value1) VALUES (4, 1004);
INSERT INTO rulprob1 (id, value1) VALUES (5, 1005);
INSERT INTO rulprob1 (id, value1) VALUES (6, 1006);
INSERT INTO rulprob1 (id, value1) VALUES (7, 1007);
INSERT INTO rulprob1 (id, value1) VALUES (8, 1008);
INSERT INTO rulprob1 (id, value1) VALUES (9, 1009);
INSERT INTO rulprob1 (id, value1) VALUES (10, 1010);
INSERT INTO rulprob1 (id, value1) VALUES (1, 1015);
INSERT INTO rulprob1 (id, value1) VALUES (2, 1002);
ALTER TABLE ONLY rulprob1
    ADD CONSTRAINT rulprob1_pkey PRIMARY KEY (id);

CREATE TABLE rulprob2 (
    id integer NOT NULL,
    value2 integer
);

INSERT INTO rulprob2 (id, value2) VALUES (3, 2003);
INSERT INTO rulprob2 (id, value2) VALUES (4, 2004);
INSERT INTO rulprob2 (id, value2) VALUES (5, 2005);
INSERT INTO rulprob2 (id, value2) VALUES (6, 2006);
INSERT INTO rulprob2 (id, value2) VALUES (7, 2007);
INSERT INTO rulprob2 (id, value2) VALUES (8, 2008);
INSERT INTO rulprob2 (id, value2) VALUES (9, 2009);
INSERT INTO rulprob2 (id, value2) VALUES (10, 2010);
INSERT INTO rulprob2 (id, value2) VALUES (1, 1010);
INSERT INTO rulprob2 (id, value2) VALUES (2, 1012);

CREATE OR REPLACE VIEW _v_rulprob AS
 SELECT one.id, one.value1, two.value2
   FROM rulprob1 one
   JOIN rulprob2 two ON one.id = two.id
  ORDER BY one.value1;

CREATE OR REPLACE RULE v_rulprob_update AS
    ON UPDATE TO v_rulprob DO INSTEAD (

   UPDATE rulprob1 SET value1 = new.value1
      WHERE rulprob1.id = old.id;
   UPDATE rulprob2 SET value2 = new.value2
     WHERE rulprob2.id = old.id;
);

--end structure creation

Create an ODBC DSN to the database containing the above objects. Open the view "v_rulprob". Alter the value of "value1"
andattempt to save the record. A conflict error occurs. Cancel the edit and change the value of "value2". Save this
change.No error occurs. 

Copy of psqlodbc.log output
---------------------------

conn = 14695616, PGAPI_Connect(DSN='pgJobCostDev', UID='postgres', PWD='xxxxx')
Global Options: Version='08.01.0200', fetch=100, socket=0, unknown_sizes=254, max_varchar_size=8190,
max_longvarchar_size=14697024
                disable_optimizer=1, ksqo=1, unique_index=1, use_declarefetch=0
                text_as_longvarchar=0, unknowns_as_longvarchar=0, bools_as_char=0 NAMEDATALEN=64
                extra_systable_prefixes='dd_;', conn_settings='' conn_encoding='OTHER'
conn=14695616, query='select version()'
    [ PostgreSQL version string = 'PostgreSQL 8.1.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.5 (Debian
1:3.3.5-13)'] 
    [ PostgreSQL version number = '8.1' ]
conn=14695616, query='set DateStyle to 'ISO''
conn=14695616, query='set geqo to 'OFF''
conn=14695616, query='set extra_float_digits to 2'
conn=14695616, query='select oid from pg_type where typname='lo''
conn=14695616, query='select pg_client_encoding()'
    [ Client encoding = 'UTF8' (code = 6) ]
conn=14695616, query='SELECT DISTINCT tt.relname, tt.nspname, tt.relkind, COALESCE(d.description,'') from (SELECT c.oid
asoid, c.tableoid as tableoid, n.nspname as nspname, c.relname, c.relkind FROM pg_catalog.pg_class c LEFT JOIN
pg_catalog.pg_namespacen ON n.oid = c.relnamespace WHERE c.relkind IN ('r', 'v')  and nspname like '_dev' and relname
like'v_rulprob' and relname !~ '^pg_|^dd_' ) AS tt LEFT JOIN pg_catalog.pg_description d  ON (tt.oid = d.objoid AND
tt.tableoid= d.classoid AND d.objsubid = 0) order by nspname, relname' 
conn=14695616, query='select u.nspname, c.relname, a.attname, a.atttypid, t.typname, a.attnum, a.attlen, a.atttypmod,
a.attnotnull,c.relhasrules, c.relkind from pg_catalog.pg_namespace u, pg_catalog.pg_class c, pg_catalog.pg_attribute a,
pg_catalog.pg_typet where u.oid = c.relnamespace and (not a.attisdropped) and c.oid= a.attrelid and a.atttypid = t.oid
and(a.attnum > 0) and c.relname like 'v_rulprob' and u.nspname like '_dev'  and nspname !~ '^pg_' order by u.nspname,
c.relname,attnum' 
PGAPI_Columns: table='v_rulprob',field_name='id',type=23,name='int4'
PGAPI_Columns: table='v_rulprob',field_name='value1',type=23,name='int4'
PGAPI_Columns: table='v_rulprob',field_name='value2',type=23,name='int4'
conn=14695616, query='select u.nspname, c.relname, a.attname, a.atttypid, t.typname, a.attnum, a.attlen, a.atttypmod,
a.attnotnull,c.relhasrules, c.relkind from pg_catalog.pg_namespace u, pg_catalog.pg_class c, pg_catalog.pg_attribute a,
pg_catalog.pg_typet where u.oid = c.relnamespace and (not a.attisdropped) and c.oid= a.attrelid and a.atttypid = t.oid
and(a.attnum > 0) and c.relname = 'v_rulprob' and u.nspname = '_dev'  and nspname !~ '^pg_' order by u.nspname,
c.relname,attnum' 
PGAPI_Columns: table='v_rulprob',field_name='id',type=23,name='int4'
PGAPI_Columns: table='v_rulprob',field_name='value1',type=23,name='int4'
PGAPI_Columns: table='v_rulprob',field_name='value2',type=23,name='int4'
conn=14695616, query='select c.relname, i.indkey, i.indisunique, i.indisclustered, a.amname, c.relhasrules, n.nspname
frompg_catalog.pg_index i, pg_catalog.pg_class c, pg_catalog.pg_class d, pg_catalog.pg_am a, pg_catalog.pg_namespace n
whered.relname = 'v_rulprob' and n.nspname = '_dev' and n.oid = d.relnamespace and d.oid = i.indrelid and i.indexrelid
=c.oid and c.relam = a.oid order by i.indisprimary desc, i.indisunique, n.nspname, c.relname' 
conn=14695616, query='SELECT "id" ,"value1" ,"value2"  FROM "_dev"."v_rulprob"'
conn=14695616, query='SELECT COUNT(*)  FROM "_dev"."v_rulprob" WHERE "id"=2 AND "value1"=1002 AND "value2"=1013'
conn=14695616, query='UPDATE "_dev"."v_rulprob" SET "value1"=1015 WHERE "id"=2 AND "value1"=1002 AND "value2"=1013'
conn=14695616, query='ROLLBACK'
conn=14695616, query='SELECT COUNT(*)  FROM "_dev"."v_rulprob" WHERE "id"=2 AND "value1"=1002 AND "value2"=1013'
conn=14695616, query='UPDATE "_dev"."v_rulprob" SET "value2"=1015 WHERE "id"=2 AND "value1"=1002 AND "value2"=1013'
conn=14695616, query='COMMIT'
conn=14695616, PGAPI_Disconnect




----------------------------------------------------------------------

You can respond by visiting:
http://pgfoundry.org/tracker/?func=detail&atid=538&aid=1000731&group_id=1000125