Re: [HACKERS] Thoughts about updateable views
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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Thoughts about updateable views
Hi, On Wed, Dec 22, 2004 at 09:41:40AM +, 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Thoughts about updateable views
Yann Michel wrote: Hi, On Wed, Dec 22, 2004 at 09:41:40AM +, 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Thoughts about updateable views
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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Thoughts about updateable views
--On Mittwoch, Dezember 22, 2004 11:25:42 -0500 Tom Lane [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Thoughts about updateable views
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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Thoughts about updateable views
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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Thoughts about updateable views
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. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Thoughts about updateable views
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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Thoughts about updateable views
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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] 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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Thoughts about updateable views
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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Thoughts about updateable views
Bernd Helmle [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Thoughts about updateable views
Tom Lane wrote: Bernd Helmle [EMAIL PROTECTED] 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 [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Thoughts about updateable views
Bruce Momjian [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Thoughts about updateable views
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] 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 [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Thoughts about updateable views
Bruce Momjian [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Thoughts about updateable views
--On Montag, März 22, 2004 16:17:34 -0500 Tom Lane [EMAIL PROTECTED] wrote: Bernd Helmle [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Thoughts about updateable views
Bernd Helmle [EMAIL PROTECTED] writes: --On Montag, März 22, 2004 16:17:34 -0500 Tom Lane [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Thoughts about updateable views
Bernd Helmle [EMAIL PROTECTED] 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 != 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. 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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Thoughts about updateable views
Bernd Helmle [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Thoughts about updateable views
--On Montag, März 22, 2004 18:00:55 -0500 Tom Lane [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
