Richard Huxton <dev@archonet.com> writes:
Tom Lane wrote:
No; you'd also have to have some guarantee that a given underlying table row gives rise to at most one join row. If the same table row gives rise to multiple join rows, then a request specifying an UPDATE of just one of those join rows can't be satisfied.
But you can't specify an update of a single row, only those where certain values match. Say you have a view "user_email_vw" with the following columns (from obvious tables):
user_email_vw: u_id, u_name, e_id, e_address
Updating the view "WHERE u_id=123" may well update more than one row (where a user has multiple emails), but that's exactly equivalent to updating the user-table "WHERE u_name = 'John Smith'". In the view (u_id) is not a key any more.
Consider a request like UPDATE vw SET u_name = 'Joe' WHERE u_id = 123 AND e_id = 456; where u_id 123 links to multiple e_ids including 456. There is no way to update the underlying tables in such a way that only this row of the view changes. Therefore you can't sustain the illusion that the view is an updatable table.
Agreed, but the reason we can't maintain the illusion that it's a "simple" table (i.e. plain CREATE TABLE) is that it's not. I might have a shelf_position column that, when I update it fires a trigger to renumber all the positions for that shelf. That breaks the illusion too.
Perhaps a more common example. A column "updated_ts" that always gets set to now() regardless of supplied value. That's non-intuitive (or at least implicit) behaviour, but perfectly common (and reasonable, I'd argue).
Now, on the client I'll grant we've got a problem unless we re-fetch after each update, or have some server-driven signalling. However, Microsoft have some sort of solution because their resultset-style model of the world in VB etc encounter this sort of thing.
--
Richard Huxton
Archonet Ltd
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
