>> Well, I was able to get PostgreSQL Update-able views to work nearly as
>> well as the update-able queries did in Access.
> Would you mind sharing a sample schema?
I'll see what I can do. I did post some sample schema a while back
but I can't seem of find them in the archive. I do have some
production tables and views, but there is too much non related
attributes to make a useful example.
>> As a side note, you'll notice that MS-Access will not allow
>> update-able queries based on ODBC linked table like it does on its
>> native tables for this reason.
>
> That's right, I did find that out once but didn't know whether it was
> due to ODBC limitations or something else.
MS-Access Implements Optimistic locking with all ODBC data sources.
The basic differences with an ODBC data source is that MS-Access's Jet
Engine can't put a file lock on it like it can with other file type db
like access, excel, flat files et.al.
Optimistic locking means that every time Access issues an update to
the ODBC server, it includes the all old values of a record (that
ms-access is aware of) in the update statement's where clause. So if
your MS-Access client was only aware of a stale version of the record,
its update count will be zero, thereby access with throw an exception
saying that the update could not be completed as the underlying table
was changed by another user.
>> Natural Primary key/Foreign key CASCADE UPDATEs don't work well with
>> update-able views. Choose one or the other.
>
> Not sure what you mean; can you please tell more about what doesn't work
> well with update-able views what the choice is?
This one is kind of hard to explain. Basically it deals with the
order of operation between Referential Integrity updates versus client
side or update-able view updates.
Lets say your client app or update-able view wants to update a single
row in a view. However, in addition to changing the fields from each
table, you also want to update the natural primary key. This sounds
simple but its not.
1) The record changes made on the client application are not instantly
committed and refreshed for each field change that the user makes.
Basically, the whole row is updated with an update statement once when
the user commits the change.
2) The view redirects the update statement to its underlying rules
(usually on rule for each joined table update). First of all the
primary table fields are changed (lets call it tableA) with the rule
update including its natural primary key. Lets say the primary key
was changed from 'OLDVALUE' to 'NEWVALUE' on tableA.
3) Now tableB that has a foreign key referencing tableA with its
foreign key set to ON UPDATE CASCADE. Declarative Referential
Integrity (DRI) such as ON UPDATE CASCADE have a much higher priority
over the PostgreSQL Rule system. So before the rule system can
perform updates on tableB, tableB has it foreign key changed from
'OLDVALUE' to 'NEWVALUE'.
4) Here is where the problem occurs. When the next update-able view
rule is executed to update tableB its where clause still thinks that
tableB foreign key is 'OLDVALUE'. And because 'OLDVALUE' is in the
where clause of the rule's update statement instead of 'NEWVALUE', no
record is found to match and so the remaining field updates fail.
So the end result is that all of tableA updates are successful,
TableB's foreign key is updated by DRI but the rest of the field
updates are not. So you are left with an inconsistent update from the
perspective of the view. By the way, this really confuses MS-Access.
It doesn't know what to do when this happens.
That's why I says that "Natural Primary key/Foreign key CASCADE
UPDATEs don't work well with update-able views."
Also, if you have concurrent users on the same updateable view, update
anomolies like this can still occur just from problems with user
concurrent updates. Thats the reason I decided to abandon join tabled
updateable views.
--
Regards,
Richard Broersma Jr.
Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
No comments:
Post a Comment