Обсуждение: Thoughts about updateable views

Поиск
Список
Период
Сортировка

Thoughts about updateable views

От
Bernd Helmle
Дата:
I am currently thinking of updateable views for a possible student research 
project. In this
case there comes some points to my mind, i want to share with the list.

a) Definition of an updateable view?

The first thing what i thought about was, what defines a updateable view. 
An updateable
view cannot always be updateable, according to several RDBMS (SAPDB or DB2) 
there
are the following issues:

- Multi-Join views without PKs of all underlying tables (so, how can the 
base tables
adressed anyway?)
- Views that contains DISTINCT, Aggregates, GROUP BY, ORDER BY, HAVING or 
functions
etc. in the col/table list
- Views that are based itself on views or on nested queries.

... [ room for enhancements.....]

b) The creation of an automatic INSERT/UPDATE or DELETE Rule had to be done 
with
the creation of the SELECT Rule. I understand how PostgreSQL handles views 
with its Rule
System, but what happens when no appropiate Rule can be created? Reject the 
view make
it non-updateable per default or other action? In this case i don't 
understand, if the WITH
CHECK OPTION is required for updateable views in PostgreSQL, since the view 
rules can
be created as part of the SELECT rule and, according to the docs, the query 
tree has
no entry for parts of the underlying table not mentioned in the views' 
query.

This points are only a small overview what i have though about this 
weekend. So, i believe
there are many more issues that should be mentioned when planning 
updateable views,
aren't they? Some input would be nice, since i need a feeling for the 
estimated complexity
of this project.

-- 

TIA
 Bernd


Re: Thoughts about updateable views

От
Gavin Sherry
Дата:
On Mon, 22 Mar 2004, Bernd Helmle wrote:

> I am currently thinking of updateable views for a possible student research
> project. In this
> case there comes some points to my mind, i want to share with the list.
>
> a) Definition of an updateable view?

The SQL spec. You should definately get a look at at least the SQL92
definition before proceeding.

> b) The creation of an automatic INSERT/UPDATE or DELETE Rule had to be done
> with
> the creation of the SELECT Rule. I understand how PostgreSQL handles views
> with its Rule
> System, but what happens when no appropiate Rule can be created? Reject the

CREATE VIEW needs to check if all columns in the target list and the base
table are updatable or insertable into. Eg: How can you update: select
foo, random() from bar?

If the user specifically asked for an updatable view, then they can't have
one. If they didn't specifically ask, they get the usual read only view.

Gavin


Re: Thoughts about updateable views

От
Tom Lane
Дата:
Bernd Helmle <mailings@oopsware.de> writes:
> a) Definition of an updateable view?

> The first thing what i thought about was, what defines a updateable view. 

The SQL spec clearly defines the requirements for a view to be
updateable.  It seems sufficient to me to handle the cases required by
the spec.

> b) The creation of an automatic INSERT/UPDATE or DELETE Rule had to be done 
> with
> the creation of the SELECT Rule. I understand how PostgreSQL handles views 
> with its Rule
> System, but what happens when no appropiate Rule can be created?

You don't create it.  This corresponds to the view not being updateable.
AFAICS the spec expects CREATE VIEW to create both kinds of view without
the implementation making any particular comment about it.

We might need to mark automatically created rules as such, and be
prepared to drop them if the user then defines a manually-created rule.
Otherwise we will have backwards-compatibility problems with existing
databases.
        regards, tom lane


Re: Thoughts about updateable views

От
Tom Lane
Дата:
Gavin Sherry <swm@linuxworld.com.au> writes:
> If the user specifically asked for an updatable view, then they can't have
> one. If they didn't specifically ask, they get the usual read only view.

"Specifically asked" how?  AFAICS the CREATE VIEW syntax doesn't make any
distinction.  (The WITH CHECK OPTION option requires the view to be
updateable, but I imagine we'd not support that to begin with, anyway.)
        regards, tom lane


Re: Thoughts about updateable views

От
Bruce Momjian
Дата:
Tom Lane wrote:
> Bernd Helmle <mailings@oopsware.de> writes:
> > a) Definition of an updateable view?
> 
> > The first thing what i thought about was, what defines a updateable view. 
> 
> The SQL spec clearly defines the requirements for a view to be
> updateable.  It seems sufficient to me to handle the cases required by
> the spec.
> 
> > b) The creation of an automatic INSERT/UPDATE or DELETE Rule had to be done 
> > with
> > the creation of the SELECT Rule. I understand how PostgreSQL handles views 
> > with its Rule
> > System, but what happens when no appropiate Rule can be created?
> 
> You don't create it.  This corresponds to the view not being updateable.
> AFAICS the spec expects CREATE VIEW to create both kinds of view without
> the implementation making any particular comment about it.
> 
> We might need to mark automatically created rules as such, and be
> prepared to drop them if the user then defines a manually-created rule.
> Otherwise we will have backwards-compatibility problems with existing
> databases.

I was thinking we create an updatable view if possible, and throw an
error if they try to insert/update/delete into a non-updatable view. 
Right now we ignore such activity, but that seems wrong.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 


Re: Thoughts about updateable views

От
Tom Lane
Дата:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I was thinking we create an updatable view if possible, and throw an
> error if they try to insert/update/delete into a non-updatable view. 

Right.

> Right now we ignore such activity, but that seems wrong.

Nonsense.

regression=# create view bar as select * from foo;
CREATE VIEW
regression=# insert into bar values (1);
ERROR:  cannot insert into a view
HINT:  You need an unconditional ON INSERT DO INSTEAD rule.
regression=#

The only backwards-compatibility risk I see is if there's a manually
created ON-whatever rule; this probably has to override the automatic ones.
        regards, tom lane


Re: Thoughts about updateable views

От
Bruce Momjian
Дата:
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > I was thinking we create an updatable view if possible, and throw an
> > error if they try to insert/update/delete into a non-updatable view. 
> 
> Right.
> 
> > Right now we ignore such activity, but that seems wrong.
> 
> Nonsense.
> 
> regression=# create view bar as select * from foo;
> CREATE VIEW
> regression=# insert into bar values (1);
> ERROR:  cannot insert into a view
> HINT:  You need an unconditional ON INSERT DO INSTEAD rule.
> regression=#
> 
> The only backwards-compatibility risk I see is if there's a manually
> created ON-whatever rule; this probably has to override the automatic ones.

Oh, good.   What release fixed this?  I sure hope it is 7.5 because I
don't see any mention of it in the release notes.  :-)  I poked around
in CVS and can't see when it was added.  No matter --- it is fixed now.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 


Re: Thoughts about updateable views

От
Tom Lane
Дата:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Tom Lane wrote:
>> regression=# insert into bar values (1);
>> ERROR:  cannot insert into a view

> Oh, good.   What release fixed this?

[experiments...]  7.1.
        regards, tom lane


Re: Thoughts about updateable views

От
Bernd Helmle
Дата:

--On Montag, März 22, 2004 16:48:29 -0500 Tom Lane <tgl@sss.pgh.pa.us>
wrote:

> Gavin Sherry <swm@linuxworld.com.au> writes:
>> If the user specifically asked for an updatable view, then they can't
>> have one. If they didn't specifically ask, they get the usual read only
>> view.
>
> "Specifically asked" how?  AFAICS the CREATE VIEW syntax doesn't make any
> distinction.  (The WITH CHECK OPTION option requires the view to be
> updateable, but I imagine we'd not support that to begin with, anyway.)

Currently no SQL spec handy (i will have one soon) , i took a look onto
O'Reillys "SQL in a
Nutshell", where the WITH CHECK OPTION is defined as follows (translated
from German....):

Only data that can be read from the specific view can be updated, fields
that aren't
part of the view can't be updated. E.g. if a view is defined to display
only the monthly
salary of an employee, it wouldn't be possible to modify an employees
hourly salary.
O'Reilly says this is SQL99. There is also a CASCADED and LOCAL keyword
mentioned
which makes this check local to the specific view, or triggers the check to
all
possible underlying views.

Makes that sense or do i misunderstand important things?

In O'Reilly there are also the conditions mentioned a view has to pass when
declaring it updateable (also SQL99):

- The SELECT of the CREATE VIEW command depends only on one table
- UNION, EXCEPT and INTERSECT are not part of the View's SELECT
- No GROUP BY or HAVING
- Pseudo-Columns are forbidden, too (but i think that can improved to
functions....)
- No Aggregates
- No DISTINCT

so joined views are even not updateable, too.

--
 Bernd





Re: Thoughts about updateable views

От
Bernd Helmle
Дата:
--On Montag, März 22, 2004 16:17:34 -0500 Tom Lane <tgl@sss.pgh.pa.us>
wrote:

> Bernd Helmle <mailings@oopsware.de> writes:

[...]

> We might need to mark automatically created rules as such, and be
> prepared to drop them if the user then defines a manually-created rule.
> Otherwise we will have backwards-compatibility problems with existing
> databases.

Not going into deeper details, but where should such information be stored?
It sounds like to have this in pg_rewrite?



--  Bernd


Re: Thoughts about updateable views

От
Tom Lane
Дата:
Bernd Helmle <mailings@oopsware.de> writes:
> --On Montag, M�rz 22, 2004 16:17:34 -0500 Tom Lane <tgl@sss.pgh.pa.us> 
> wrote:
>> We might need to mark automatically created rules as such, and be
>> prepared to drop them if the user then defines a manually-created rule.

> Not going into deeper details, but where should such information be stored? 
> It sounds like to have this in pg_rewrite?

Yes, a boolean column added to pg_rewrite is what I was thinking of.
        regards, tom lane


Re: Thoughts about updateable views

От
Tom Lane
Дата:
Bernd Helmle <mailings@oopsware.de> writes:
> Currently no SQL spec handy (i will have one soon) , i took a look onto 
> O'Reillys "SQL in a
> Nutshell", where the WITH CHECK OPTION is defined as follows (translated 
> from German....):

> Only data that can be read from the specific view can be updated, fields 
> that aren't
> part of the view can't be updated. E.g. if a view is defined to display 
> only the monthly
> salary of an employee, it wouldn't be possible to modify an employees 
> hourly salary.

That sounds bogus to me.  It's obvious that columns not present in the
view can't be updated through the view --- you simply do not have a way
to name them, so how could you affect them?

What the spec actually says, if I'm reading it correctly, is that CHECK
OPTION forbids you from using the view to insert/update *rows* that
would not appear in the view.  For example given
CREATE VIEW v AS SELECT * FROM t WHERE flag != 42WITH CHECK OPTION;

you could see the "flag" column in the view, and could set it on insert
or update --- so long as you didn't try to set it to 42.  That would
mean that the result row was invisible in the view, which is what CHECK
OPTION forbids.

In the general case with complicated WHERE conditions, it seems this
would be extremely expensive to enforce.  It would certainly be very
difficult to do it using only Postgres RULE mechanisms.  So I'd suggest
not implementing the WITH CHECK OPTION feature; certainly not as part of
your first cut.

(But: it looks to me like the spec gives license to be restrictive about
the form of WHERE clauses in updatable views, so it might be that
something could be done about WITH CHECK OPTION with less pain than I'm
imagining.)
        regards, tom lane


Re: Thoughts about updateable views

От
Tom Lane
Дата:
Bernd Helmle <mailings@oopsware.de> writes:
> Currently no SQL spec handy (i will have one soon) ,

BTW, I find that the SQL92 spec is vastly more readable than SQL99,
partly because it's vastly shorter, and partly because the SQL99 authors
seem to have been into unnecessary formalism and obscurantism.  It's
usually a good idea to read SQL92 first to see what the feature is
actually intended to do, before you try to make sense of SQL99's
treatment.

You can find draft copies of both the 92 and 99 specs for free on the
web (I think there are links in our developer's FAQ).  I tend to use
these even though I have the official PDFs, mainly because plain ASCII
text is much easier to search than a PDF.
        regards, tom lane


Re: Thoughts about updateable views

От
Bernd Helmle
Дата:
--On Montag, März 22, 2004 18:00:55 -0500 Tom Lane <tgl@sss.pgh.pa.us>
wrote:

>
> That sounds bogus to me.  It's obvious that columns not present in the
> view can't be updated through the view --- you simply do not have a way
> to name them, so how could you affect them?
>
> What the spec actually says, if I'm reading it correctly, is that CHECK
> OPTION forbids you from using the view to insert/update *rows* that
> would not appear in the view.  For example given
>
>     CREATE VIEW v AS SELECT * FROM t WHERE flag != 42
>     WITH CHECK OPTION;
>
> you could see the "flag" column in the view, and could set it on insert
> or update --- so long as you didn't try to set it to 42.  That would
> mean that the result row was invisible in the view, which is what CHECK
> OPTION forbids.
>

Ah, okay. I should have read this more carefully. Thanks for the
clarification. No it
makes sense....


--  Bernd


Re: Thoughts about updateable views

От
Jaime Casanova
Дата:
In a galaxy far, far away Bernd wrote:

The context:
http://archives.postgresql.org/pgsql-hackers/2004-03/msg00999.php

> so joined views are even not updateable, too.

I don't find the why of this on the specs and the 
threads about this issue ignore the comment.

Is this right?

regards,
Jaime Casanova


_________________________________________________________
Do You Yahoo!?
Información de Estados Unidos y América Latina, en Yahoo! Noticias.
Visítanos en http://noticias.espanol.yahoo.com


Re: Thoughts about updateable views

От
Richard Huxton
Дата:
Jaime Casanova wrote:
> In a galaxy far, far away Bernd wrote:
> 
> The context:
> http://archives.postgresql.org/pgsql-hackers/2004-03/msg00999.php
> 
> 
>>so joined views are even not updateable, too.
> 
> 
> I don't find the why of this on the specs and the 
> threads about this issue ignore the comment.

BEGIN QUOTE
In O'Reilly there are also the conditions mentioned a view has to pass 
when declaring it updateable (also SQL99):


- The SELECT of the CREATE VIEW command depends only on one table
- UNION, EXCEPT and INTERSECT are not part of the View's SELECT
- No GROUP BY or HAVING
- Pseudo-Columns are forbidden, too (but i think that can improved to 
functions....)
- No Aggregates
- No DISTINCT

so joined views are even not updateable, too.
END QUOTE

Well, I haven't checked the specs, but this list is clearly incorrect 
from a theoretical point of view (I've been thinking about this recently).

There are two things (AFAICT) you need to be able to do to update (NOTE 
- not insert) a view. 1. Identify the underlying table(s) for the updated column(s) 2. Identify (primary) key values
forthe table(s) being updated.
 
So - I could have a join listing users and how many email aliases they 
have (so sum()) and still update their name, so long as the key for the 
users table was present in the view.

Any summarising rules out updates for the summarised tables (because you 
no longer have a key available). Similarly, expression/function columns 
can't be updated (except in certain special cases).

UNION etc doesn't necessarily mean you can't update, so long as the 
underlying table/key can be identified.

For INSERTing to a view, the same rules apply, but obviously you need to  be able to identify table/keys for all
columnsin the view. This 
 
clearly rules out aggregates etc.

--  Richard Huxton  Archonet Ltd


Re: Thoughts about updateable views

От
Yann Michel
Дата:
Hi,

On Wed, Dec 22, 2004 at 09:41:40AM +0000, Richard Huxton wrote:
> UNION etc doesn't necessarily mean you can't update, so long as the 
> underlying table/key can be identified.

I think you mean UNION ALL, i.e. the set addition, don't you?
Otherwise UNION (wothout ALL) is kind of a aggregation due to it only
adds a row once to the resulting set wheter it is found twice or not.
Therefore any updates are not possible.

Regards,
Yann


Re: Thoughts about updateable views

От
Richard Huxton
Дата:
Yann Michel wrote:
> Hi,
> 
> On Wed, Dec 22, 2004 at 09:41:40AM +0000, Richard Huxton wrote:
> 
>>UNION etc doesn't necessarily mean you can't update, so long as the 
>>underlying table/key can be identified.
> 
> 
> I think you mean UNION ALL, i.e. the set addition, don't you?
> Otherwise UNION (wothout ALL) is kind of a aggregation due to it only
> adds a row once to the resulting set wheter it is found twice or not.
> Therefore any updates are not possible.

Not if you can identify the underlying table(s) and key(s). If the UNION 
hides that information, then you are correct. Imagine the case where you 
were running a calendar system and had people_invited unioned with 
rooms_booked - you could in theory alter the name on both.

--   Richard Huxton  Archonet Ltd


Re: Thoughts about updateable views

От
Tom Lane
Дата:
Richard Huxton <dev@archonet.com> writes:
> There are two things (AFAICT) you need to be able to do to update (NOTE 
> - not insert) a view.
>   1. Identify the underlying table(s) for the updated column(s)
>   2. Identify (primary) key values for the table(s) being updated.
> So - I could have a join listing users and how many email aliases they 
> have (so sum()) and still update their name, so long as the key for the 
> users table was present in the view.

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.
        regards, tom lane


Re: Thoughts about updateable views

От
Bernd Helmle
Дата:
--On Mittwoch, Dezember 22, 2004 11:25:42 -0500 Tom Lane 
<tgl@sss.pgh.pa.us> wrote:

> Richard Huxton <dev@archonet.com> writes:
>> There are two things (AFAICT) you need to be able to do to update (NOTE
>> - not insert) a view.
>>   1. Identify the underlying table(s) for the updated column(s)
>>   2. Identify (primary) key values for the table(s) being updated.
>> So - I could have a join listing users and how many email aliases they
>> have (so sum()) and still update their name, so long as the key for the
>> users table was present in the view.
>
> 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.
>

Not sure if i understand correctly, but that means JOINs between 1:n 
relations
falls under the "not updateable" category, because the "parent row" 
triggers updates to n possible "child" rows?




-- 
 Bernd


Re: Thoughts about updateable views

От
Tom Lane
Дата:
Richard Huxton <dev@archonet.com> writes:
> Yann Michel wrote:
>> I think you mean UNION ALL, i.e. the set addition, don't you?

> Not if you can identify the underlying table(s) and key(s). If the UNION 
> hides that information, then you are correct.

If a unique key of the underlying table is included in the UNION data, then
there can't be any duplicate rows and so the UNION really reduces to
UNION ALL.  However, I'm unconvinced that there are any cases like this
that are interesting in practice.  Consider
CREATE TABLE a (id int primary key, ...);
CREATE TABLE b (id int primary key, ...);
CREATE VIEW v AS SELECT * FROM a UNION SELECT * FROM b;

If a and b have disjoint key sets then the UNION is theoretically
updatable, but there is no way to specify such a constraint and thus
no way for the system to know that the UNION is updatable.
        regards, tom lane


Re: Thoughts about updateable views

От
Richard Huxton
Дата:
Tom Lane wrote:
> Richard Huxton <dev@archonet.com> writes:
> 
>>There are two things (AFAICT) you need to be able to do to update (NOTE 
>>- not insert) a view.
>>  1. Identify the underlying table(s) for the updated column(s)
>>  2. Identify (primary) key values for the table(s) being updated.
>>So - I could have a join listing users and how many email aliases they 
>>have (so sum()) and still update their name, so long as the key for the 
>>users table was present in the view.
> 
> 
> 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.

If you have a many-many relationship, (say worker<=>department) then 
again you may update multiple rows in the view ("WHERE dept_id=123"), 
but so what - that's what you asked to do.

I'm not saying this is always the behaviour you'd want. Imagine an 
address table where you have a country-code field and a lookup table of 
countries. I almost certainly DON'T want the lookup table updated via 
the view, but there's no easy solution to that - it's to do with the 
semantics of the join, not its syntax.

--  Richard Huxton  Archonet Ltd


Re: Thoughts about updateable views

От
Tom Lane
Дата:
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 likeUPDATE 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.
        regards, tom lane


Re: Thoughts about updateable views

От
Richard Huxton
Дата:
Tom Lane wrote:
> Richard Huxton <dev@archonet.com> writes:
> 
>>Yann Michel wrote:
>>
>>>I think you mean UNION ALL, i.e. the set addition, don't you?
> 
> 
>>Not if you can identify the underlying table(s) and key(s). If the UNION 
>>hides that information, then you are correct.
> 
> 
> If a unique key of the underlying table is included in the UNION data, then
> there can't be any duplicate rows and so the UNION really reduces to
> UNION ALL.  However, I'm unconvinced that there are any cases like this
> that are interesting in practice.  Consider
> 
>     CREATE TABLE a (id int primary key, ...);
> 
>     CREATE TABLE b (id int primary key, ...);
> 
>     CREATE VIEW v AS SELECT * FROM a UNION SELECT * FROM b;
> 
> If a and b have disjoint key sets then the UNION is theoretically
> updatable, but there is no way to specify such a constraint and thus
> no way for the system to know that the UNION is updatable.

What about:  CREATE TABLE a(id int primary key check < 100, ...)  CREATE TABLE b(id int primary key check > 100, ...)

In any case, imagine a diary system where you might have an booking 
involving one or more people and/or resources (room/projector). You'd 
quite possibly have:

SELECT 'P' as res_type, p_id, p_name FROM event_person
UNION
SELECT 'R', r_id, r_type FROM event_resource
WHERE ...

Again, updatable (although I'm not sure how you'd detect the implied 
CHECK on the first column).

None of this is easy, or even practical but I'm fairly sure it's all 
possible.
--  Richard Huxton  Archonet Ltd


Re: Thoughts about updateable views

От
Richard Huxton
Дата:
Tom Lane wrote:
> 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