Обсуждение: Unable to use VIEWS (Ok button remains shaded)
Once I try to do something (whatever that may be) with VIEWS, the OK button never goes from gray to black (as if I didn't have all the privileges) to be clicked on as to accept the creation or changes. I'm running it all on a desktop version, no users, localhost, usr postgres has all the privileges available. I.e.: simplest configuration possible Views created on other Administration Software (e.g. NAVCAT) are recognized, but I am unable to edit them. The VIEWS window pops up, all the tabs are un place, but it behaves as if I hadn't entered anything into them. Any ideas? Thanks in advance -- View this message in context: http://postgresql.1045698.n5.nabble.com/Unable-to-use-VIEWS-Ok-button-remains-shaded-tp2841601p2841601.html Sent from the PostgreSQL - novice mailing list archive at Nabble.com.
joseflores wrote: > Once I try to do something (whatever that may be) with VIEWS, the OK button > never goes from gray to black (as if I didn't have all the privileges) to be > clicked on as to accept the creation or changes. > Views don't have buttons, they usually come with the zipper. -- Mladen Gogala Sr. Oracle DBA 1500 Broadway New York, NY 10036 (212) 329-5251 www.vmsinfo.com
On Wed, Sep 15, 2010 at 6:29 PM, joseflores <jose.floresv@gmail.com> wrote: > Once I try to do something (whatever that may be) with VIEWS, the OK button > never goes from gray to black (as if I didn't have all the privileges) to be > clicked on as to accept the creation or changes. Views are not update-able by default unlike a table. It is possible to make a view update-able I you build custom rules into the view. However, I should mention that even with custom rules, you really shouldn't attempt to make an update-able view based upon a query that joins or unions two or more tables together. Here is how to make a view update-able: http://www.postgresql.org/docs/8.4/interactive/rules-views.html#RULES-VIEWS-UPDATE And here is the current stance taken on the use of update-able views: http://wiki.postgresql.org/wiki/Updatable_views -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug
Richard Broersma wrote: > On Wed, Sep 15, 2010 at 6:29 PM, joseflores <jose.floresv@gmail.com> wrote: > > >> Once I try to do something (whatever that may be) with VIEWS, the OK button >> never goes from gray to black (as if I didn't have all the privileges) to be >> clicked on as to accept the creation or changes. >> > > Views are not update-able by default unlike a table. It is possible > to make a view update-able I you build custom rules into the view. > However, I should mention that even with custom rules, you really > shouldn't attempt to make an update-able view based upon a query that > joins or unions two or more tables together. > > > Here is how to make a view update-able: > http://www.postgresql.org/docs/8.4/interactive/rules-views.html#RULES-VIEWS-UPDATE > > > And here is the current stance taken on the use of update-able views: > http://wiki.postgresql.org/wiki/Updatable_views > > I always wandered why would anybody want to update a view? View is, by definition, a stored query which is executed frequently enough to be given its own first name, middle name and a family name. If the view is being updated, the middle name is F, just as in the case of Bucky Dent. I don't see any database design pattern which would necessitate updating a view. That is a perversion, not unlike putting ketchup on a hot dog. Statements with views are notoriously hard to optimize. If views are involved, the access path may be skewed, there is a join involved and optimizer doesn't always select the right access path, resulting in a very bad performance. Things are exacerbated in Postgres which doesn't allow programmers to influence optimizer, which is in my opinion a big philosophical mistake. Oracle doesn't have "UPDATE FROM" as does Postgres, which sometimes makes updating a view necessary, but Postgres has a beautiful "update from" clause which makes such escapades completely unnecessary. scott=# update emp e1 set sal=e2.sal from emp1 e2 scott-# where e1.empno=e2.empno; UPDATE 14 Time: 1.138 ms PS: ---- EnterpriseDB is a company located in Boston, MA, the reference to Bucky Dent shouldn't be considered too cryptic. For the people unfamiliar with the legend of the Red Sox and the "curse of the Bambino", here are the Wikipedia entries for Bucky Dent and the curse: http://en.wikipedia.org/wiki/Bucky_Dent http://en.wikipedia.org/wiki/Curse_of_the_Bambino -- Mladen Gogala Sr. Oracle DBA 1500 Broadway New York, NY 10036 (212) 329-5251 www.vmsinfo.com
On Thu, Sep 16, 2010 at 7:00 PM, Mladen Gogala <mladen.gogala@vmsinfo.com> wrote: > I always wandered why would anybody want to update a view? View is, by > definition, a stored query which is executed frequently enough to be given > its own first name, middle name and a family name. I understand the practicality of what you're saying. However, I'm curious about where your definition of views comes from. My understanding is that update-able views were concepts of Codd's original relational model and such views are also part of the SQL standard. And since "PostgreSQL prides itself in standards compliance...," (http://www.postgresql.org/about/) I wouldn't expect to see update-able views on the features *not* to do list any time soon. (http://wiki.postgresql.org/wiki/Todo#Features_We_Do_Not_Want) Although I do see planner hints are one of the features listed there. -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug
On Thu, Sep 16, 2010 at 10:00 PM, Mladen Gogala <mladen.gogala@vmsinfo.com> wrote: > I always wandered why would anybody want to update a view? View is, by > definition, a stored query which is executed frequently enough to be given > its own first name, middle name and a family name. If the view is being > updated, the middle name is F, just as in the case of Bucky Dent. I don't > see any database design pattern which would necessitate updating a view. > That is a perversion, not unlike putting ketchup on a hot dog. See C.J. Date's "SQL and Relational Theory", a good read. Page 195, "SQL and Views: Update Operations" talks about this. Quote: >> The Principle of Interchangeability implies that views must be >> updatable (i.e., assignable to) ... [snip] ... updates on base relvars can >> always fail on integrity constraint violations—and the same is true >> for updates on views. In other words, it isn’t that some views are >> inherently nonupdatable, but rather that some updates on some >> views will fail on integrity constraint violations (i.e., violations of >> The Golden Rule). On page 197 Date gives an excerpt from SQL99 that defines when a view is updatable, see page 269 of SQL99 here: http://www.cs.pdx.edu/~len/sql1999.pdf Josh
Josh Kupershmidt wrote: > On Thu, Sep 16, 2010 at 10:00 PM, Mladen Gogala > <mladen.gogala@vmsinfo.com> wrote: > >> I always wandered why would anybody want to update a view? View is, by >> definition, a stored query which is executed frequently enough to be given >> its own first name, middle name and a family name. If the view is being >> updated, the middle name is F, just as in the case of Bucky Dent. I don't >> see any database design pattern which would necessitate updating a view. >> That is a perversion, not unlike putting ketchup on a hot dog. >> > > See C.J. Date's "SQL and Relational Theory", a good read. Page 195, > "SQL and Views: Update Operations" talks about this. Quote: > > That is all good and well. My objection wasn't to the relational theory and the principle of interchangeability, it was to a bad practice. As a DBA since 1989., I've never had a need for updating a view, except when simulating "update from". That's all I've said. Furthermore, my experience tells me that updating views usually leads to under-performing applications. The same applies to the philosophy which puts optimizer hints at the "unwanted" list. Hints are bad in theory, but unless optimizer can always make a good decision, there should be a way of influencing the optimizer decisions. -- Mladen Gogala Sr. Oracle DBA 1500 Broadway New York, NY 10036 (212) 329-5251 http://www.vmsinfo.com The Leader in Integrated Media Intelligence Solutions