[HACKERS] Debugging tool for viewing parse trees?
I've gotten back to working on the SQL99 version of updateable views and I'ld like to confirm my current understanding of the parse tree data structure. Is there a debugging-type tool for outputting the parse tree for a given query? Eric Nielsen ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Updateable views...
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 ---(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] Updateable views...
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 bar10; 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Updateable views...
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 ---(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] Updateable views...
Gavin Sherry [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
