Re: Update-able View linked to Access

I created an updateable view using a two part rule and linked it as a table to MS Access. I am
getting the error below when ever I try to update the name field. I tried manually updating the
view from an access update query using only "ID" and it worked perfectly. When I linked the view
as a table I defined id as the primary key, so why is the query sent by the ODBC driver using the
other fields as well when "ID" should be all that it needs?.

I could add these additional fields to the rules update where conditions, but if it works should
it even be necessary?

ERROR Message:

BEGIN;UPDATE "public"."vhusband" SET "name"=E'hell2' WHERE "id" = 14 AND "name" = E'hello3' AND
"tiesize" = 12
ROLLBACK

It seems my rules are working outside the odbc transaction.

Update portion of Rule Definition:

CREATE OR REPLACE RULE
vhusband_update_person
AS ON UPDATE TO
public.vhusband
DO INSTEAD
(
UPDATE
public.person
SET
name = NEW.name
WHERE
id = OLD.id;
UPDATE
public.husband
SET
tiesize = NEW.tiesize
WHERE
id = OLD.id
)
;

Thanks for any help.

Regards,

Richard Broersma Jr.

Richard Broersma Jr wrote:
> I created an updateable view using a two part rule and linked it as a table to MS Access. I am
> getting the error below when ever I try to update the name field. I tried manually updating the
> view from an access update query using only "ID" and it worked perfectly. When I linked the view
> as a table I defined id as the primary key, so why is the query sent by the ODBC driver using the
> other fields as well when "ID" should be all that it needs?.

MS Access adds the "name" and "tiesize" fields to the where clause to check if the
target row was updated by other users while editing the MS Access form etc.

> I could add these additional fields to the rules update where conditions,

Maybe it wouldn't work unfortunately.

> but if it works should
> it even be necessary?

This error seems to be caused by an essential flaw of the rule system.

Could you try the following ?

Add the CTID field of the target table of the last update rule to the
definition of the view. In your case, try to add husband.ctid to the
definition of vhusband.

regards,
Hiroshi Inoue

> Could you try the following ?
> Add the CTID field of the target table of the last update rule to the
> definition of the view. In your case, try to add husband.ctid to the
> definition of vhusband.

I am still getting similar errors, only now both columns are locked.
Here is my new view definition:
--------------------------------
CREATE OR REPLACE VIEW public.vhusband (id, personctid, husbandctid, name, tiesize) AS
SELECT
A.id, A.ctid, B.ctid, A.name, B.tiesize
FROM
public.person as A
INNER JOIN
public.husband as B
ON
A.id = B.ID
;

using OLD.husbandctid
------------------------------------
BEGIN;UPDATE "public"."vhusband"
SET "tiesize"=3
WHERE "id" = 14
AND "personctid" = E'(0,63)'
AND "husbandctid" = E'(0,42)'
AND "name" = E'hel0'
AND "tiesize" = 3
ROLLBACK
------------------------------------
Here is my new update rule:

CREATE OR REPLACE RULE
vhusband_update_person
AS ON UPDATE TO
public.vhusband
DO INSTEAD
(
UPDATE
public.person
SET
name = NEW.name
WHERE
id = OLD.id;
UPDATE
public.husband
SET
tiesize = NEW.tiesize
WHERE
id = OLD.id
AND
ctid = OLD.husbandctid
)
;

using NEW.husbandctid
---------------------------
BEGIN;
UPDATE "public"."vhusband"
SET "name"=E'4'
WHERE "id" = 14
AND "personctid" = E'(0,63)'
AND "husbandctid" = E'(0,42)'
AND "name" = E'hel0'
AND "tiesize" = 3
ROLLBACK

----------------------------
CREATE OR REPLACE RULE
vhusband_update_person
AS ON UPDATE TO
public.vhusband
DO INSTEAD
(
UPDATE
public.person
SET
name = NEW.name
WHERE
id = OLD.id;
UPDATE
public.husband
SET
tiesize = NEW.tiesize
WHERE
id = OLD.id
AND
ctid = new.husbandctid
)
;

Thanks for the help.

Regards,

Richard Broersma Jr.

Richard Broersma Jr wrote:
>> Could you try the following ?
>> Add the CTID field of the target table of the last update rule to the
>> definition of the view. In your case, try to add husband.ctid to the
>> definition of vhusband.
>
> I am still getting similar errors, only now both columns are locked.
> Here is my new view definition:
> --------------------------------
> CREATE OR REPLACE VIEW public.vhusband (id, personctid, husbandctid, name, tiesize) AS
> SELECT
> A.id, A.ctid, B.ctid, A.name, B.tiesize
> FROM
> public.person as A
> INNER JOIN
> public.husband as B
> ON
> A.id = B.ID
> ;

Please change the field name of B.ctid from hasbandctid to ctid.
The name should be "ctid" for the driver to detect the field is for versioning.
A.ctid isn't needed.

regards,
Hiroshi Inoue

> Please change the field name of B.ctid from hasbandctid to ctid.
> The name should be "ctid" for the driver to detect the field is for versioning.
> A.ctid isn't needed.

It works now! However, I have a question. If I have a view with more than two joined tables will
i need to reference the ctid from each table after the first update statement? Is this even
possible since I have to have unique column names?

View Definition
-----------------------------
CREATE OR REPLACE VIEW public.vhusband (id, ctid, name, tiesize) AS
SELECT
A.id, B.ctid, A.name, B.tiesize
FROM
public.person as A
INNER JOIN
public.husband as B
ON
A.id = B.ID
;

New UPDATE Rule
-----------------------------
CREATE OR REPLACE RULE
vhusband_update_person
AS ON UPDATE TO
public.vhusband
DO INSTEAD
(
UPDATE
public.person
SET
name = NEW.name
WHERE
id = OLD.id;

UPDATE
public.husband
SET
tiesize = NEW.tiesize
WHERE
id = OLD.id
AND
ctid = OLD.ctid
)
;

Successful Log showing commit
-----------------------------
BEGIN;

UPDATE "public"."vhusband"
SET "name"=E'hello44',"tiesize"=52
WHERE "id" = 10
AND "ctid" = E'(0,47)'

SELECT "id","ctid","name","tiesize"
FROM "public"."vhusband"
WHERE "id" = 10

COMMIT

:-)

Regards,

Richard Broersma Jr.

Richard Broersma Jr wrote:
>> Please change the field name of B.ctid from hasbandctid to ctid.
>> The name should be "ctid" for the driver to detect the field is for versioning.
>> A.ctid isn't needed.
>
> It works now! However, I have a question. If I have a view with more than two joined tables will
> i need to reference the ctid from each table after the first update statement?

The ctid of the target table of the rule's last statement should be referenced.
For example, in your case the command

UPDATE "public"."vhusband"
SET "name"=E'hello44',"tiesize"=52
WHERE "id" = 10
AND "ctid" = E'(0,47)'

is issued by MS Access.
The where condition "id" = 10 AND "ctid" = E'(0,47)' is added to
each statment in the rule when the command is executed.
So it's very siginificant that the contents of "id" and "ctid"
are not changed until the last statement is called.
Please note that the ctid field is modified autmatically when
the table is updated.

regards,
Hiroshi Inoue

Hiroshi Inoue wrote:
> Richard Broersma Jr wrote:
>>> Please change the field name of B.ctid from hasbandctid to ctid.
>>> The name should be "ctid" for the driver to detect the field is for
>>> versioning.
>>> A.ctid isn't needed.
>>
>> It works now! However, I have a question. If I have a view with more
>> than two joined tables will
>> i need to reference the ctid from each table after the first update
>> statement?
>
> The ctid of the target table of the rule's last statement should be
> referenced.

Please note this is not a perfect solution.

For example, the change of table A can't be detected using the "ctid".
If you update the link table with where clause including "name" field
and update the "name" and the "tiesize" field, the "tiesize" may not
be updated.....

It seems almost impossible for the driver to solve the problems perfectly.
Essentially it's a problem of the rule system.

regards,
Hiroshi Inoue

> It seems almost impossible for the driver to solve the problems perfectly.
> Essentially it's a problem of the rule system.

Yes, I was able to reproduce these bad results from within psql. I take it that this is a well
know limitation of the rule system or should I try reporting it with my simple test case? Now
that I've finially learned to implement rules on a view, now I am deeply concerned about their
relablility. As you mentioned, the fact that I can get inconsistant updates makes me very
concerned. I guess they are okey as long as you specify explicit queries that only reference
single column's primary key id, or if the view doesn't span multiple tables.

My next question, is it possible to create an updatable view using triggers on a instead of rules?
:-) If so, I guess my next task is to learn about plpgsql and triggers.

postgres=# select * from vwife;
id | name | dresssize
----+---------+-----------
3 | dodie | 13
4 | heather | 10
2 | katie | 11
(3 rows)

postgres=# update vwife
set name = 'Katheryn',
dresssize = 12
where (id,name,dresssize)=(2,'katie',11);
UPDATE 0

postgres=# select * from vwife;
id | name | dresssize
----+----------+-----------
3 | dodie | 13
4 | heather | 10
2 | Katheryn | 11
^^^^^^^^ <-- update 0 is false
(3 rows)

Partial updates is a very bad thing.

Regards,

Richard Broersma Jr.

Richard Broersma Jr wrote:
>> It seems almost impossible for the driver to solve the problems perfectly.
>> Essentially it's a problem of the rule system.
>
> Yes, I was able to reproduce these bad results from within psql. I take it that this is a well
> know limitation of the rule system or should I try reporting it with my simple test case?

I've had little experience with the PostgreSQL rule system. Someone with much experience may
have the solution. Please report the problem to the other MLs.

<snip>

> My next question, is it possible to create an updatable view using triggers on a instead of rules?

AFAIK it's impossible though I'm not sure.

> postgres=# select * from vwife;
> id | name | dresssize
> ----+---------+-----------
> 3 | dodie | 13
> 4 | heather | 10
> 2 | katie | 11
> (3 rows)
>
> postgres=# update vwife
> set name = 'Katheryn',
> dresssize = 12
> where (id,name,dresssize)=(2,'katie',11);
> UPDATE 0
>
> postgres=# select * from vwife;
> id | name | dresssize
> ----+----------+-----------
> 3 | dodie | 13
> 4 | heather | 10
> 2 | Katheryn | 11
> ^^^^^^^^ <-- update 0 is false
> (3 rows)
>
> Partial updates is a very bad thing.

Yes it's a disaster.

regards,
Hiroshi Inoue