Re: Thoughts about updateable views

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

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 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.

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 columns in the view. This
clearly rules out aggregates etc.

--
Richard Huxton
Archonet Ltd

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

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

Richard Huxton <dev(at)archonet(dot)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

--On Mittwoch, Dezember 22, 2004 11:25:42 -0500 Tom Lane
<tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Richard Huxton <dev(at)archonet(dot)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

Richard Huxton <dev(at)archonet(dot)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

Tom Lane wrote:
> Richard Huxton <dev(at)archonet(dot)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

Richard Huxton <dev(at)archonet(dot)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.

regards, tom lane

Tom Lane wrote:
> Richard Huxton <dev(at)archonet(dot)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

Tom Lane wrote:
> Richard Huxton <dev(at)archonet(dot)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