Skip site navigation (1) Skip section navigation (2)

Re: Thoughts about updateable views

From: Richard Huxton <dev(at)archonet(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Jaime Casanova <systemguards(at)yahoo(dot)com>,pgsql-hackers(at)postgresql(dot)org
Subject: Re: Thoughts about updateable views
Date: 2004-12-22 17:32:50
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
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

In response to

pgsql-hackers by date

Next:From: Dave HartwigDate: 2004-12-22 18:25:38
Subject: Re: Can't Restart ver 8.0b3
Previous:From: Richard HuxtonDate: 2004-12-22 17:10:36
Subject: Re: Thoughts about updateable views

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group