Thread: Updateable views...
Let me preface this by expressing my appreciation for all the hard work for the people who develop, maintain, and support PostGreSQL. I've been using it for a little over two years for a variety of projects and have been extremely happy with both the software and the support on these lists. Recently I began trying to fill in my gaps in understanding the theories underlying database work -- mainly by reading some textbooks and research papers -- and I've had my eyes opened to lot of interesting things I hadn't considered before. Then I began digging around PostGreSQL to see if it offered the tools to play around with these ideas; in many cases it did and I've been happily playing around with them. The one place I haven't been able to use PostGreSQL to experiment is with regards to updateable views. I've found a few threads in -general and -hackers (including one linked from the ToDo list), but they all seem to die out without really reaching any sort of conclusion. I've also seen that in many cases it appears possible to use triggers/rules to simulate updateable views, but that feels like an inelegant solution to me. Are there people working on this topic? I'ld be interested in helping out. Eric
Eric D Nielsen <nielsene@MIT.EDU> writes:
> The one place I haven't been able to use PostGreSQL to experiment is with
> regards to updateable views. I've found a few threads in -general and -hackers
> (including one linked from the ToDo list), but they all seem to die out without
> really reaching any sort of conclusion.
That's because we've discussed it about as far as is interesting, until
someone actually steps up and does the work ;-). We know how it should
be implemented: in Postgres terms, an updateable-view facility would
simply mean generating the appropriate ON INSERT/UPDATE/DELETE rules
automatically, whenever a view is created that is simple enough that
the code can figure out what said rules ought to be. (Hopefully this
condition will be pretty nearly equivalent to the rules the SQL spec
lays down for whether a view is updatable.)
> Are there people working on this topic? I'ld be interested in helping out.
AFAIR, no one has done anything about it.
regards, tom lane
On Tue, 2003-03-04 at 15:26, Eric D Nielsen wrote: > The one place I haven't been able to use PostGreSQL to experiment is with > regards to updateable views. I've found a few threads in -general and -hackers > (including one linked from the ToDo list), but they all seem to die out without > really reaching any sort of conclusion. I've also seen that in many > cases it appears possible to use triggers/rules to simulate updateable views, > but that feels like an inelegant solution to me. How so? A view is defined by ON SELECT rules; it seems natural, then, that an updateable view would be defined ON INSERT / ON UPDATE rules. AFAIK the only deficiency with the status quo is that the system does not automatically define those insertion rules for you (in the subset of cases where rules actually *can* be defined: for example, the view can't include aggregation/grouping, calls to a user-defined function, etc.) If you'd like to work on getting PostgreSQL to make views updateable automatically, that would be cool -- AFAIK no one else is currently working on it. Cheers, Neil -- Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC
Neil Conway <neilc@samurai.com> writes: > On Tue, 2003-03-04 at 15:26, Eric D Nielsen wrote: > > The one place I haven't been able to use PostGreSQL to experiment is with > > regards to updateable views. I've found a few threads in -general and -hackers > > (including one linked from the ToDo list), but they all seem to die out without > > really reaching any sort of conclusion. I've also seen that in many > > cases it appears possible to use triggers/rules to simulate updateable views, > > but that feels like an inelegant solution to me. > > How so? A view is defined by ON SELECT rules; it seems natural, then, > that an updateable view would be defined ON INSERT / ON UPDATE rules. > AFAIK the only deficiency with the status quo is that the system does > not automatically define those insertion rules for you (in the subset of > cases where rules actually *can* be defined: for example, the view can't > include aggregation/grouping, calls to a user-defined function, etc.) > > If you'd like to work on getting PostgreSQL to make views updateable > automatically, that would be cool -- AFAIK no one else is currently > working on it. Would the rules approach be able to handle inline views? Ie, queries like: UPDATE (select * from a natural join b) set a.foo = b.foo On Oracle this is often the most efficient way to write update queries where the data is coming from other tables. -- greg
On Tue, 4 Mar 2003, Eric D Nielsen wrote: > The one place I haven't been able to use PostGreSQL to experiment is > with regards to updateable views. I've found a few threads in > -general and -hackers (including one linked from the ToDo list), but > they all seem to die out without really reaching any sort of > conclusion. I've also seen that in many cases it appears possible to > use triggers/rules to simulate updateable views, but that feels like > an inelegant solution to me. At some of the conferences I've been to, updatable/insertable-into views are a big request. I've looked into the spec, which basically said that views can be marked updatable/insertable-into if all attributes into the query expression are updatable/insertable into. I haven't had time to look into it further, but it occurs to me that handling views which rely on joins would be far from trivial. (The big selling point of views, for me, is as a means of tying together objects so that an application has a simplified interface to application logic). Anyone else do anything on this? Gavin
> On Tue, 2003-03-04 at 15:26, Eric D Nielsen wrote: > > The one place I haven't been able to use PostGreSQL to experiment is with > > regards to updateable views. I've found a few threads in -general and -hac > kers > > (including one linked from the ToDo list), but they all seem to die out wit > hout > > really reaching any sort of conclusion. I've also seen that in many > > cases it appears possible to use triggers/rules to simulate updateable view > s, > > but that feels like an inelegant solution to me. > > How so? A view is defined by ON SELECT rules; it seems natural, then, > that an updateable view would be defined ON INSERT / ON UPDATE rules. > AFAIK the only deficiency with the status quo is that the system does > not automatically define those insertion rules for you (in the subset of > cases where rules actually *can* be defined: for example, the view can't > include aggregation/grouping, calls to a user-defined function, etc.) Using user-written rules seems inelegant to me because they force the user to do something the DBMS should be able to do itself. Should the rules be auto-generated by the DBMS then I wouldn't consider it inelegant. > If you'd like to work on getting PostgreSQL to make views updateable > automatically, that would be cool -- AFAIK no one else is currently > working on it. I'm definately willing to look into it, can anyone offer any advice for getting "situated" in the code? Are there paticular areas I should focus on understanding/areas I should be able to safely ignore? All my PostGreSQL experiences have been in user-land so far. Is there a good place to view the SQL99 standard without shelling out the $20 to ASNI? I know I'll have more questions later, but until then, happy coding... Eric
Gavin Sherry <swm@linuxworld.com.au> writes:
> I haven't had time to look into it further, but it occurs to me that
> handling views which rely on joins would be far from trivial.
Views containing joins would not be updatable; problem solved. The set
of views the automatic-rule-generation machinery needs to handle are
those defined as updatable by the SQL spec. SQL92 says
12)A <query specification> QS is updatable if and only if the fol- lowing conditions hold:
a) QS does not specify DISTINCT.
b) Every <value expression> contained in the <select list> imme- diately contained in QS
consistsof a <column reference>, and no <column reference> appears more than once.
c) The <from clause> immediately contained in the <table ex- pression> immediately contained in
QSspecifies exactly one <table reference> and that <table reference> refers either to a base
tableor to an updatable derived table.
Note: updatable derived table is defined in Subclause 6.3, "<table reference>".
d) If the <table expression> immediately contained in QS imme- diately contains a <where clause>
WC,then no leaf generally underlying table of QS shall be a generally underlying table of any
<queryexpression> contained in WC.
e) The <table expression> immediately contained in QS does not include a <group by clause> or a
<havingclause>.
The reference to 6.3 appears to be pointing at this:
8) A <derived table> is an updatable derived table if and only if the <query expression> simply
containedin the <subquery> of the <table subquery> of the <derived table> is updatable.
I haven't quite wrapped my head around what clause 12d means, but 12c is
perfectly clear that you only get one table reference.
regards, tom lane
> Gavin Sherry <swm@linuxworld.com.au> writes: > > I haven't had time to look into it further, but it occurs to me that > > handling views which rely on joins would be far from trivial. > > Views containing joins would not be updatable; problem solved. I see how that is what the spec says, but aren't the majority of joins that people use/want to update a join of some type? I thought that SQL99 allowed updating view created by joins. In either case is this a place where "exceeding" the spec would be a good thing or a bad thing? Eric
Eric D Nielsen <nielsene@MIT.EDU> writes:
> In either case is this a place where "exceeding" the spec would be a good
> thing or a bad thing?
Unless there is an obvious definition of what updating a join means
(obvious not only to the implementor, but to the user) I think this
is dangerous territory.
regards, tom lane
On Wed, 2003-03-05 at 10:39, Eric D Nielsen wrote: > > Gavin Sherry <swm@linuxworld.com.au> writes: > > > I haven't had time to look into it further, but it occurs to me that > > > handling views which rely on joins would be far from trivial. > > > > Views containing joins would not be updatable; problem solved. > > I see how that is what the spec says, but aren't the majority of joins that > people use/want to update a join of some type? I thought that SQL99 allowed > updating view created by joins. > > In either case is this a place where "exceeding" the spec would be a good > thing or a bad thing? Lets try to meet the spec first, then debate about whether extending it is a good or bad thing :) -- Rod Taylor <rbt@rbt.ca> PGP Key: http://www.rbt.ca/rbtpub.asc
On Wed, 5 Mar 2003, Eric D Nielsen wrote: > I see how that is what the spec says, but aren't the majority of joins that > people use/want to update a join of some type? I thought that SQL99 allowed > updating view created by joins. > > In either case is this a place where "exceeding" the spec would be a good > thing or a bad thing? This is a case where exceeding the spec would be a very good thing. And there has been lots of research on how to make more complex views updatable; see Date's _Introduction to Database Systems, 7th Edition_ for an in-depth discussion of this. That said, one step at a time is always good, and even having just the very simplest views updatable would be a very nice thing. cjs -- Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're alllight. --XTC
Tom Lane <tgl@sss.pgh.pa.us> writes: > Eric D Nielsen <nielsene@MIT.EDU> writes: > > In either case is this a place where "exceeding" the spec would be a good > > thing or a bad thing? > > Unless there is an obvious definition of what updating a join means > (obvious not only to the implementor, but to the user) I think this > is dangerous territory. Joins are a *BIG* part of the reason people want updateable views. In every single case that I updated a view it was a join. Just being able to update subsets of tables or restricted sets of columns is really a fairly trivial use of a powerful feature. In Oracle the constraint is fairly straightforward (at least to describe): for each column you're updating the primary key of the table it came from has to be present in the view. -- greg
I'm pressing ahead with trying to implement the SQL92 version of updateable views. I'm trying to track down a copy of the SQL92 standard, I thought that ANSI sold them, but I can only find SQL89 and SQL99 there; am I looking in the wrong place? Eric Nielsen
Eric D Nielsen <nielsene@MIT.EDU> writes:
> I'm pressing ahead with trying to implement the SQL92 version of updateable
> views. I'm trying to track down a copy of the SQL92 standard, I thought that
> ANSI sold them, but I can only find SQL89 and SQL99 there; am I looking in
> the wrong place?
I'm not sure that ANSI would bother with obsoleted versions of
standards. What I tend to use is the final draft version of SQL92,
mainly because it's available in plain text that I can grep (PDF is not
a user-friendly format IMHO). The draft version is available for free
on the net --- I've forgotten exactly where, but if you trawl the list
archives you will find a URL. SQL99 is available in the same way, btw.
But I like SQL92 because it's much smaller and more readable.
regards, tom lane
Stanards URL's are in the developers FAQ. --------------------------------------------------------------------------- Tom Lane wrote: > Eric D Nielsen <nielsene@MIT.EDU> writes: > > I'm pressing ahead with trying to implement the SQL92 version of updateable > > views. I'm trying to track down a copy of the SQL92 standard, I thought that > > ANSI sold them, but I can only find SQL89 and SQL99 there; am I looking in > > the wrong place? > > I'm not sure that ANSI would bother with obsoleted versions of > standards. What I tend to use is the final draft version of SQL92, > mainly because it's available in plain text that I can grep (PDF is not > a user-friendly format IMHO). The draft version is available for free > on the net --- I've forgotten exactly where, but if you trawl the list > archives you will find a URL. SQL99 is available in the same way, btw. > But I like SQL92 because it's much smaller and more readable. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > -- 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
After finding the SQL92 draft spec that Tom quoted from earlier I think I understand the conditions for the spec's version of view updatability. I've made few comments below on the conditions and I'ld appreciate it if anyone would correct any mis-interpretations on my part. > 12)A <query specification> QS is updatable if and only if the fol- > lowing conditions hold: > > a) QS does not specify DISTINCT. No explanation needed. > > b) Every <value expression> contained in the <select list> imme- > diately contained in QS consists of a <column reference>, and > no <column reference> appears more than once. This appears to say that the select list must be of the form: [<qualifier> <period>] <column name> [[AS] <column name>] [, ...] No operations/functions may be applied to the column. Columns may be renamed from the base table to the view using either "SELECT .. AS .." in the query defining the view or in the "column name list" of the view, the latter taking precedence if specified. No column in the view may be a literal constant. No column from the base table may appear more than once. (The '*' is expanded as discussed in the spec into a form that matches the format listed above.) > c) The <from clause> immediately contained in the <table ex- > pression> immediately contained in QS specifies exactly one > <table reference> and that <table reference> refers either to > a base table or to an updatable derived table. No joins (implicit or explicit) are allowed in an updateable view. Updateable derived tables include: views that meet the requirements as well as unnamed, intermediate dervived tables that meet the same standards. I beleive this should allow queries such as: UPDATE (SELECT bar, baz FROM foo) SET bar=1 WHERE baz=2; as well as the CREATE VIEW foo_view AS SELECT bar, baz FROM foo; UPDATE foo_view SET bar=1 WHERE baz==2; DROP VIEW foo_view; three-query analog. However the one-query version can't be handled by the auto- generated ON UPDATE/DELETE/INSERT rules for views that I'm looking at. CREATE VIEW foo_view AS SELECT bar, baz FROM (SELECT bar, baz FROM foo) AS qux; should yield an updateable view as the derived table used in the from clause is itself an updateable derived table. > d) If the <table expression> immediately contained in QS imme- > diately contains a <where clause> WC, then no leaf generally > underlying table of QS shall be a generally underlying table > of any <query expression> contained in WC. I beleive this is saying that the ultimate base tables of the QS and the ultimate base table invoved in a query in the WC must be disjoint. e.g. (stupid example, but...) CREATE VIEW foo_view AS SELECT bar,baz FROM foo WHERE bar<10; CREATE VIEW foo2_view AS SELECT bar,baz FROM foo WHERE baz in (SELECT bar,baz FROM foo_view) AND baz >15; foo_view would be updateable. foo2_view would not be as the same ultimate base table appears in both the table expression for the view and in the query expression of the WC. Changing foo2_view to CREATE VIEW foo2_view AS SELECT bar,baz, FROM foo_view ... would not fix the problem as its the _ultimate_ base tables that matter. > e) The <table expression> immediately contained in QS does not > include a <group by clause> or a <having clause>. No explanation needed. Eric Nielsen
Eric D Nielsen <nielsene@MIT.EDU> writes: > I beleive this should allow queries such as: > UPDATE (SELECT bar, baz FROM foo) SET bar=1 WHERE baz=2; > as well as the > CREATE VIEW foo_view AS SELECT bar, baz FROM foo; > UPDATE foo_view SET bar=1 WHERE baz==2; > DROP VIEW foo_view; > three-query analog. > > However the one-query version can't be handled by the auto- > generated ON UPDATE/DELETE/INSERT rules for views that I'm looking at. Well, if you don't support joins or complex expressions then there's really no value in inline views in update statements. WHERE clauses and excluded columns are only really useful for security restrictions in real views. It does seem to me that allowing complex expressions is fairly straightforward: you bar updates to those columns, but allow use of them in the rhs of set clauses. That makes things like this possible: CREATE VIEW foo as (select col, func1(col) as new_val where func2(col)) UPDATE foo SET col = new_val which should be translated to: UPDATE foo SET col = func1(col) WHERE func2(col) That's not terribly useful in itself, but it means if you need those additional columns for some other purpose, then you still get to take advantage of the updateableness of the other columns. I still hold out hope for eventually supporting joins, but that's obviously more complicated to implement. -- greg