tag:blogger.com,1999:blog-5541296000399974369.post6654931650562384186..comments2024-03-09T02:51:27.612-05:00Comments on Peter Eisentraut's Blog: An Update On ViewsAnonymoushttp://www.blogger.com/profile/02849480732923051923noreply@blogger.comBlogger21125tag:blogger.com,1999:blog-5541296000399974369.post-67869027316280358852012-01-21T17:17:04.249-05:002012-01-21T17:17:04.249-05:00It doesn't work, but it's being fixed.It doesn't work, but it's being fixed.Anonymoushttps://www.blogger.com/profile/02849480732923051923noreply@blogger.comtag:blogger.com,1999:blog-5541296000399974369.post-42906788479838767412012-01-19T04:55:08.565-05:002012-01-19T04:55:08.565-05:00It is pretty clear that the article is about Postg...It is pretty clear that the article is about Postgres, but it wasn't clear if you consider views as row access control a bad solution, or it simply doesn't work in a particular version of Postgres.<br />"Don't use this feature (and stay with basic features) because there is a bug/performance issue/whatever in version X" is the kind of attitude I'm used to see in MySQL world. As I'm investigating Postgres as an alternative between Oracle and MySQL, it would be disappointing to see it again (nothing personal here).<br />The point in using a RDBMS is to make applications more robust and to speed up development time with simpler and more reliable solutions. Views are a very powerful abstractions and can drastically reduce development time and/or volume of code if used properly.<br />Regarding fixing, it shouldn't be a big issue for someone familiar with the source code. Getting familiar with source code however, could take order of magnitude much more time than fixing a bug. I would rather afford a paid license than spending a lot of time.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-5541296000399974369.post-85500705576558556222012-01-18T14:45:09.023-05:002012-01-18T14:45:09.023-05:00Yes, it was perhaps not clear that my post was foc...Yes, it was perhaps not clear that my post was focused on PostgreSQL.<br /><br />As for your simple solution, please submit a patch. :)Anonymoushttps://www.blogger.com/profile/02849480732923051923noreply@blogger.comtag:blogger.com,1999:blog-5541296000399974369.post-68596026020956103382012-01-17T08:31:09.135-05:002012-01-17T08:31:09.135-05:00"Using views for row access control doesn'..."Using views for row access control doesn't actually really work"... Do you have other arguments for this? The link you provided looks like a bug in Postgres, which is an implementation issue, while using views for row access control is something logical. I don't see how a physical issue can invalidate a logical solution.<br />Moreover, this problem could be very easily fixed by caching RAISE and NOTICE output until entire WHERE clause has been evaluated, and discard it when evaluates to false. This actually seems absolutely logic in any way you look at, not only regarding security. For example you would not wand an exception to be raise for a row that is not part of the result. So just a stupid bug, with a simple solution.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-5541296000399974369.post-6260414324956490692010-12-25T12:48:11.614-05:002010-12-25T12:48:11.614-05:00Would help me a lot if there where updatable views...Would help me a lot if there where updatable views.<br /><br />I got some projects with crap db that i would really like to cleanup. But whats putting it off is that there is a lot of client code that would need to be updated.<br /><br />Br Björn BlomqvistAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-5541296000399974369.post-58359294463533910072010-11-20T11:52:13.942-05:002010-11-20T11:52:13.942-05:00I use views for reporting and it has paid off time...I use views for reporting and it has paid off time and again when the database design was enhanced.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-5541296000399974369.post-23267334542951338322010-07-03T06:10:02.130-04:002010-07-03T06:10:02.130-04:00Hi Peter,
I must say that updatable views are a fe...Hi Peter,<br />I must say that updatable views are a feature I would greatly appreciate.<br /><br />I use Java with ORM, and also have entities that map to views.<br /><br />In order go get a good object model to work with an OO language like Java if you use 1:1 table-entity mapping you imply a bad DB design. <br /><br />I use views to build a layer on top of a generically designed tables in the DB. This way I get a nice OO model mapping to views that the developers easily understand.<br /><br />The only draw back is that when you write to an entity mapped to a view you must provide a logic to update the generic (read complex) tables with the ORM. If we had updateable views we wouldn't worry about this and may even remove entity-table mappings all together and have only entity-view mappings.<br /><br />CheersDragan Sahpaskihttps://www.blogger.com/profile/01750883156944756903noreply@blogger.comtag:blogger.com,1999:blog-5541296000399974369.post-26290450550660818062010-07-03T06:07:35.563-04:002010-07-03T06:07:35.563-04:00This comment has been removed by the author.Dragan Sahpaskihttps://www.blogger.com/profile/01750883156944756903noreply@blogger.comtag:blogger.com,1999:blog-5541296000399974369.post-84444775484027371602010-07-02T14:55:31.074-04:002010-07-02T14:55:31.074-04:00I think updatable views would be great -- perhaps ...I think updatable views would be great -- perhaps it's just not the kind of feature likely to make a top-10 list.Jeff Davishttp://thoughts.j-davis.comnoreply@blogger.comtag:blogger.com,1999:blog-5541296000399974369.post-2242744169376418412010-07-02T13:18:58.512-04:002010-07-02T13:18:58.512-04:00@RoryCL:
Isn't it the case that updateable vie...@RoryCL:<br />Isn't it the case that updateable views require the SELECT to only be against one table?<br /><br />No:<br />http://www.exforsys.com/tutorials/oracle-10g/oracle-10g-updating-views.html<br /><br />While not fully implementing Codd's requirement, it's further along than most. The issue boils down to: how does the engine keep track of base rows in the expanded row under update? The answer is rowid (by whatever name the vendor uses), but the vendors haven't generally wanted to make the effort. If you want to only update columns in one of the joined tables during the update, as would likely be the case in the canonical order/orderline example, that should always be supported.Robert Younghttps://www.blogger.com/profile/09056808374481236610noreply@blogger.comtag:blogger.com,1999:blog-5541296000399974369.post-50828444067521644302010-07-02T11:48:08.603-04:002010-07-02T11:48:08.603-04:00agree with Anonymous @ http://petereisentraut.blog...agree with Anonymous @ http://petereisentraut.blogspot.com/2010/07/update-on-views.html?showComment=1277987064848#c6042600397682211501, james@vegas, Steven, Regina and Robert Young.<br /><br />rules may not be perfect but they're *very useful*. twice so as the triggers in PostgreSQL suck (row-level triggers are inherent suckers, descending from the declarative set-level comfort to the row-level mess, and statement-level triggers have no access to the update in progress [see the `inserted` and `deleted` pseudo-tables in SQL Server, *that* should also a top bullet in the TODO, otherwise statement-level triggers could just as well go: i've never had any use for that "feature" in PostgreSQL]).<br /><br />for example, rules were quite vital in porting a web-based helpdesk application from SQL Server to PostgreSQL, where we didn't just copy the db schema: we looked at the data and the operations performed on it by the app, and developed a new, relational, and very different schema for PostgreSQL, along with a set of views that mirrored the old schema, each with an insert view, and migrated the data through those.<br /><br />selecting from functions is ok (as in, it's just a different syntax for the same thing, right?), but symmetry is *very* important. how does one `INSERT` into (generally update) the result of a function call? in Oracle, you can update the result of a `SELECT`, if it's still a relation and trivially maps to base tables. but it's the same business as automatically updatable views: the requirements are so stringent they're only infrequently met, within that circumstance seldomly needed, and thus rarely used.<br /><br />ORM popularity is just that. MySQL is more popular than PostgreSQL. NoSQL databases seem to be more popular than either one.<br /><br />NB: it's outside of SQL, but would be enormously useful: the two relational DIVIDE operator described by Date. i wonder how frequently is that feature requested. ;) scratch the DIVIDE, i'd actually prefer the general feature of user-defined relational operators!Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-5541296000399974369.post-49026642719011164142010-07-01T17:59:58.475-04:002010-07-01T17:59:58.475-04:00Isn't it the case that updateable views requir...Isn't it the case that updateable views require the SELECT to only be against one table? If that is the case then it is no better than most ORMS which consider a table == object.<br /><br />The whole point about complex views and set returning functions is that they provide a spatial view of the data that is not simply evident from any particular table or composite of data from several tables.<br /><br />From my no doubt naive point of view materialized views are simply optimised versions of set returning functions.RoryCLnoreply@blogger.comtag:blogger.com,1999:blog-5541296000399974369.post-44051452970478657512010-07-01T15:35:07.837-04:002010-07-01T15:35:07.837-04:00-- but they can do a lot more with parameters and ...-- but they can do a lot more with parameters and general procedural programming.<br /><br />The reason is pitifully simple: 99.44% of database "developers" are either explicitly coders who appropriate DB "design" to themselves, or database "developers" who've never overcome their procedural coding background. I'd wager that those 99.44% have never read Date or Gray & Reuter, either.<br /><br />Such folks, as a coder colleague expressed it, "prefer to handle transactions and concurrency in the application code". All in all, as Pascal and Date have opined for years, unless and until we database folk pressure the RDBMS vendors to do it right (and SQL is not Codd's language, rather Chamberlin's), we'll get half-baked crap. <br /><br />As to the use of views, they are the subject of Codd's Rule 6, and using them as the interface, rather than base tables, would lead to a much simpler, and less coding in the, application. Any attempt to have less coding is anathema to coders, so they resist. They succeed. That does not make it right.<br /><br />The ORM's can target views; they exist in the catalog just as do base tables. Exactly how PG's catalog works, I'm not certain.<br /><br />With the advent of multi-core/processor/SSD machines, there really is no defensible reason to do otherwise. FWIW, Best Buy now *stocks in stores* the Intel X-25 parts. Designing a database to SSD is not foolish.Robert Younghttps://www.blogger.com/profile/09056808374481236610noreply@blogger.comtag:blogger.com,1999:blog-5541296000399974369.post-40752644712795568762010-07-01T10:27:40.055-04:002010-07-01T10:27:40.055-04:00I maintain one application where i could have used...I maintain one application where i could have used updatable views, at least a few times.<br /><br />The last bunch of changes to the database layout - getting rid of a messed up table which was never well designed to begin with, and was made worse with every update - was made just a few days ago, and while theoretically a view might have been possible (though complicated, the stuff was a real mess), i didn't even seriously think about it.<br />Because cleaning up the database and leaving the code in its less than clean state simply is not an option in the long run. The goal of the cleanup is cleaning up, not having one good interface and one bad interface together.<br /><br />And that is why i do not care much about views, though they are handy sometimes, especially if one needs direct data access of some kind.<br /><br />But updatable views? Using them to give write access to someone would bypass too much application level features - especially the caching module.Uwe Ohsehttp://www.ohse.de/uwe/noreply@blogger.comtag:blogger.com,1999:blog-5541296000399974369.post-39194950447317354562010-07-01T09:26:40.218-04:002010-07-01T09:26:40.218-04:00Peter,
Not sure what rock you are living under or...Peter,<br /><br />Not sure what rock you are living under or what rock I'm living under, but they are obviously not the same rock.<br /><br />For enterprise class applications -- you know financial applications and things I care about, I really can't imagine living without views. You've got all those people wanting to query their own data - for simplifying of reporting. Functions just don't do it -- because they require inputs. Not very friendly when you've got to feed it to people used to using query designers.<br /><br />For web apps, like twitter, yah I can see how you can do with views. In my opinion though for one of the main audiences PostgreSQL is trying to target - business applications, front end web reporting applications, you really can't do that easily without views.Regina Obehttps://www.blogger.com/profile/12156821701772508167noreply@blogger.comtag:blogger.com,1999:blog-5541296000399974369.post-37766695782579007352010-07-01T09:14:10.732-04:002010-07-01T09:14:10.732-04:00Yeah, I've been wondering for years why updata...Yeah, I've been wondering for years why updatable view does not belong to the top of the TODO list. That's been one of my biggest annoyances with Postgres (which otherwise would be more or less perfect), and the only reason I haven't dumped MS Access.<br /><br />Glad to see the self delusion about rule is being cleared though.Steven Haryantohttps://www.blogger.com/profile/04922802400068672153noreply@blogger.comtag:blogger.com,1999:blog-5541296000399974369.post-71733204517291161602010-07-01T09:09:30.401-04:002010-07-01T09:09:30.401-04:00I think you're close on the whole ORM thing......I think you're close on the whole ORM thing... I think many people are using these and completely ignoring good db design. I have an opinion of using the right tool for the job. some ORM's don't actually seem to be a bad tool, because they are flexible. These ORM's are increasingly being used to ignore good database design period. I find these ORM's don't take concurrent transactions into mind as much as they should (event the better ones).<br /><br />However, I would use update-able views if they existed. I suspect they just happen to be a feature easy (enough) to workaround not having, and thus status quo is 'good enough'.<br /><br />As you've said many existing implementations do either not have them or they are severely limited (to 1 table I believe) I personally don't think I would find them all that... useful unless I could update more than 1 table with them.<br /><br />So we're in a case of what what we have is 'good enough' and we don't know what we're missing.Anonymoushttps://www.blogger.com/profile/08185254298048097278noreply@blogger.comtag:blogger.com,1999:blog-5541296000399974369.post-49181419290659780512010-07-01T08:54:32.396-04:002010-07-01T08:54:32.396-04:00Have to at least partially agree with the last Ano...Have to at least partially agree with the last Anonymous, if people had an easy way to create updateable views, they'd be more used. Also some may disparage the rule system and want to rip it out completely, that would probably be an unpopular idea until there is a better way to create updateable views.<br /><br />Yes, you can replace view functionality with client code, you can do the same with stored procedures, data integrity checks, security, table joins and filtering.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-5541296000399974369.post-60426003976822115012010-07-01T08:24:24.848-04:002010-07-01T08:24:24.848-04:00So what you are saying is that we dont need update...So what you are saying is that we dont need updateable views because we dont have them in PG. In any case even if we did have updtable views we really dont understand how to use/apply them in applications or in security or their static read only sisters anyway. <br /><br />Materialized views apparrently do not have to be updateable ....... duh!<br /><br />Materialized views depend in both implementations on the existance of updateable view mechanisms.<br /><br />This article is so deeply flawed you should pull it.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-5541296000399974369.post-5479263253416424652010-07-01T04:14:43.047-04:002010-07-01T04:14:43.047-04:00Hi Peter,
I think one of the main reasons abundan...Hi Peter,<br /><br />I think one of the main reasons abundant usage of views is not common is the fact that there are a lot of DBMSs that don't support them, or support them partial with only a very small "common base".<br />As a software developer I know I would use them occasionally especially for backwards compatibility during schema redesigns or as a sort of "manual" interface to a complex table structure. The fact that they are not update-able forces me to take another approach.<br /><br />That said, I don't think view-heavy designs are never going to be an option for a large number of projects that want to support different SQL servers. For in those cases "basic" SQL designs are the easiest to support.Justinhttps://www.blogger.com/profile/03262395355065638997noreply@blogger.comtag:blogger.com,1999:blog-5541296000399974369.post-50877991360258509712010-07-01T02:25:34.675-04:002010-07-01T02:25:34.675-04:00Hi Peter,
As far as I'm concerned, I would sa...Hi Peter,<br /><br />As far as I'm concerned, I would say it would be great to be able to update views. If it ever appears in Postgresql, do you think there would be limitations on the structure of the view so it is "updatable"? (Maximum number of joins? Unions?)Philippe Langhttp://www.attiksystem.chnoreply@blogger.com