Re: About rules on views and M$ Access

>
> Please note that if I comment that line and send the query
> manually to
> PG it works perfectly.
> I think that when PG reads this SQL it looks in the view for a row
> satisfacting the WHERE conditions, then
> if it founds it performs the update, elsewhere it does nothing.
> But it simply doesn't find any row with
> "CostoEffettivo"='1.44232004547119' and this is probably due
> to internal
> handling of double precision numbers, I don't know exactly why. The
> query successes if I cast the column "CostoEffettivo" as text.
> Remember anyway that I can't change the queries the Access
> sends to ODBC!
>
> Please, if you have suggestions about solving this problem, tell me!
>

This is because Access uses so called "optimistic locking". It remembers the last values of record and updates the record ONLY when noone hasn't changed it. I use similar approach in my application and I also have this problem.

Actually, this wouldn't be problem, if the view had primary key. Because generally only those fields get added to WHERE clause, that have been changed (you never change "CostoEffettivo"?). But in case of view, all fields are added, because this is the only possible way to identify the changed record. If you try the same thing on ordinary table, you see that only primary key and old value of changed field are in where clause: primary key to identify the record and old value to implement optimistic locking.

I'm aware of only one solution to this problem - create table with the same fields as view and make sure it has a primary key. Then create SELECT and UPDATE rules on that table with appropriate SELECT and UPDATE queries. This tricks Access to think that the view has primary key. You cannot just add primary key to existing view.

As you see, this is quite ugly and incompatible solution. But only alternative I know is to change database design.

Tambet