On Sun, Sep 28, 2008 at 9:57 PM, Seb <[EMAIL PROTECTED]> wrote: >> 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
