Thread: Thoughts about updateable views
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
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
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
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
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
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
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
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
--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
--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
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
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
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
--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
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 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
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@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
--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
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
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
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
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
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