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

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

am 13.09.2006 04:08:17 von noreply

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=10007 31&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 update on the view to fail. This problem has been replicated in linked ODBC tables on MS Access 2000, VS Net2005, and Borland Delphi 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" and attempt 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 as oid, c.tableoid as tableoid, n.nspname as nspname, c.relname, c.relkind FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n 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_type t 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_type t 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 from pg_catalog.pg_index i, pg_catalog.pg_class c, pg_catalog.pg_class d, pg_catalog.pg_am a, pg_catalog.pg_namespace n where d.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=10007 31&group_id=1000125

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match