Re: [HACKERS] Updateable views for 8.2 or 8.3?
On 7/13/06, Bernd Helmle <[EMAIL PROTECTED]> wrote: --On Mittwoch, Juli 12, 2006 20:58:08 -0500 Jaime Casanova <[EMAIL PROTECTED]> wrote: > if nobody step up i can do the list. > > i think this is the last patch that he post: > http://archives.postgresql.org/pgsql-hackers/2006-03/msg00586.php > The code drifted since then in some parts. I'll sent a new version to -patches soon. yeah!!! i realized that when trying to apply it to HEAD... ;) that's why i delay the list of open issues... -- regards, Jaime Casanova "Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning." Richard Cook ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Updateable views for 8.2 or 8.3?
On 7/13/06, Bernd Helmle <[EMAIL PROTECTED]> wrote: I'm still working on this and trying to get all open issues done for 8.2 feature freeze. Sweet! -- Jonah H. Harris, Software Architect | phone: 732.331.1300 EnterpriseDB Corporation| fax: 732.331.1301 33 Wood Ave S, 2nd Floor| [EMAIL PROTECTED] Iselin, New Jersey 08830| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Updateable views for 8.2 or 8.3?
--On Mittwoch, Juli 12, 2006 09:30:38 -0700 Joe Conway <[EMAIL PROTECTED]> wrote: Jaime Casanova wrote: is anybody working on the Bernd Helmle's updateable views patch? or know what the status of this is? I'm still working on this and trying to get all open issues done for 8.2 feature freeze. I was just wondering about this also. If no one else is working on it, I'd like to try to push it through to completion for 8.2 myself. Can anyone summarize what the open issues are? The main issues currently are to clean up the code and do the documentation, all functional parts of the patch should be complete (read: it supports the SQL92 spec). -- Thanks Bernd ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Updateable views for 8.2 or 8.3?
--On Mittwoch, Juli 12, 2006 20:58:08 -0500 Jaime Casanova <[EMAIL PROTECTED]> wrote: if nobody step up i can do the list. i think this is the last patch that he post: http://archives.postgresql.org/pgsql-hackers/2006-03/msg00586.php The code drifted since then in some parts. I'll sent a new version to -patches soon. i will try to rebuild a test script have made for this... That would be cool. -- Thanks Bernd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Updateable views for 8.2 or 8.3?
On 7/12/06, Joe Conway <[EMAIL PROTECTED]> wrote: Jaime Casanova wrote: > > is anybody working on the Bernd Helmle's updateable views patch? or > know what the status of this is? I was just wondering about this also. If no one else is working on it, I'd like to try to push it through to completion for 8.2 myself. Can anyone summarize what the open issues are? Joe if nobody step up i can do the list. i think this is the last patch that he post: http://archives.postgresql.org/pgsql-hackers/2006-03/msg00586.php i will try to rebuild a test script have made for this... -- regards, Jaime Casanova "Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning." Richard Cook ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Updateable views for 8.2 or 8.3?
Jaime Casanova wrote: is anybody working on the Bernd Helmle's updateable views patch? or know what the status of this is? I was just wondering about this also. If no one else is working on it, I'd like to try to push it through to completion for 8.2 myself. Can anyone summarize what the open issues are? Joe ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Updateable views was:(Re: [HACKERS] Proposal for
--On Freitag, März 10, 2006 09:43:04 + Richard Huxton wrote: I'd certainly be interested in having auto-updatable views in 8.2 - even if it was only for the simplest of cases. If I can be of any help testing etc. let me know. Yeah, that would be cool. I've sent the latest patch to -hackers. Feel free to check it out. I currently know that array fields (e.g. field[1]) causes problems, but i'm pretty sure there's much more work left... Bernd ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Updateable views was:(Re: [HACKERS] Proposal for SYNONYMS)
Jaime Casanova wrote: On 3/9/06, Tom Lane <[EMAIL PROTECTED]> wrote: Josh Berkus writes: Eh? I thought that it was just syntatic sugar that was missing. I've built lots of updatable views manually; I don't see what's difficult about it. I think you'll find that corner cases like inserts involving nextval() don't work real well with a rule-based updatable view. But perhaps I'm just scarred by the many complaints we've had about rules. With a plain unconditional DO INSTEAD rule it might be OK ... the last time i talk with Bernd Helmle, he was preparing the code to send to patches for discussion... that was two months ago... the current code had problems with casts and i think with domains too... i will contact with Bernd to know if he did some more work, if not i can send to patches the latest path he sent me... I'd certainly be interested in having auto-updatable views in 8.2 - even if it was only for the simplest of cases. If I can be of any help testing etc. let me know. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Updateable views
Greg Stark <[EMAIL PROTECTED]> writes: > I think you want to extend the SQL syntax to allow updating views, and > implement plan nodes and executor functionality to handle them. So things > like this works: > UPDATE (SELECT id,val FROM t) SET val=0 where id < 100 > Then the rules you create on the views are just like the rules for SELECT, > they simply mechanically replace the view with the view definition. > I think this is the right approach because: > a) I think creating the general rules to transform an update into an update on >the underlying table will be extremely complex, and you'll only ever be >able to handle the simplest cases. By handling the view at planning time >you'll be able to handle arbitrarily complex cases limited only by whether >you can come up with reasonable semantics. Please provide an existence proof. I don't really see any basis for the claim that this will be simpler to implement --- the semantic problems will be the same either way. 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] Updateable views
--- Greg Stark <[EMAIL PROTECTED]> escribió: > > > - What if we cannot create one of the three > >rules? > >Make the rule not updateable at all? > >Or create the rules we can? (i think this is > >the correct) > > I seem to be in the minority here. But I think > creating complex rules to fiddle with the updates > to translate them to the underlying tables is the > wrong approach. > > I think you want to extend the SQL syntax to allow > updating views, and implement plan nodes and > executor functionality to handle them. What if someone want his views to be readonly? with rules he can just drop rule. In the approach you mention he cannot. > So things like this works: > > UPDATE (SELECT id,val FROM t) SET val=0 where id < > 100 > You really do things like that??? For what?? I'm asking because i do not know any situation when it becomes usefull. Views, conceptually, should have the same behavior a table has, because you can use it to let some people view part of your info without letting them touch the table. Sometimes you need they can update the fields they can see, but then how u can prevent them touching other fields they have no rights to? Updateable views are handy for that. In your example is obvious that you can access to the t table, why not do the update directly?? Besides, this enforce to create privileges per columns rather than per table. > Then the rules you create on the views are just like > the rules for SELECT, they simply mechanically > replace the view with the view definition. > > I think this is the right approach because: > > a) I think creating the general rules to transform >an update into an update on the underlying table >will be extremely complex, and you'll only ever be >able to handle the simplest cases. By handling >the view at planning time you'll be able to >handle arbitrarily complex cases limited only by >whether you can come up with reasonable semantics. > I don't think is *extremely complex* to create the rules; but yes, there will be limitations. > b) I think it's aesthetically weird to have >functionality that's only accessible via creating >DDL objects and then using them, and not >accessible directly in a single SQL DML command. >Ie, it would be strange to have to create >a "temporary view" just in order to execute an >update because there's no equivalent syntax >available for use directly. > alter table (SELECT id,val FROM t) alter column val set default 3; > > General Restrictions!!! > > --- > > - The column target list holds column fields only, > > that are retrieved from one base relation / view > > only. (NO joined views). > > I know there are other uses for updatable views (eg > implementing column-based security policies) but the > _only_ reason I ever found them useful in Oracle > was precisely for joined views. The NOTE i included in my last post says that oracle do that with user_updateable_columns view and i suggest the creation (or the extension of pg_attribute) of a catalog to implement this. And i state that can be useful to create joined updateable views. > They're the Oracle blessed method for achieving the > same performance win as Postgres's FROM clause. > > So in Oracle you can do: > > UPDATE (select a.val as newval, b.b_id, b.val from > a,b where a.b_id = b.b_id) SET val = newval > I think Postgres's UPDATE ... FROM is a lot more clear to understand. 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 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Updateable views
> - What if we cannot create one of the three rules? >Make the rule not updateable at all? >Or create the rules we can? (i think this is the >correct) I seem to be in the minority here. But I think creating complex rules to fiddle with the updates to translate them to the underlying tables is the wrong approach. I think you want to extend the SQL syntax to allow updating views, and implement plan nodes and executor functionality to handle them. So things like this works: UPDATE (SELECT id,val FROM t) SET val=0 where id < 100 Then the rules you create on the views are just like the rules for SELECT, they simply mechanically replace the view with the view definition. I think this is the right approach because: a) I think creating the general rules to transform an update into an update on the underlying table will be extremely complex, and you'll only ever be able to handle the simplest cases. By handling the view at planning time you'll be able to handle arbitrarily complex cases limited only by whether you can come up with reasonable semantics. b) I think it's aesthetically weird to have functionality that's only accessible via creating DDL objects and then using them, and not accessible directly in a single SQL DML command. Ie, it would be strange to have to create a "temporary view" just in order to execute an update because there's no equivalent syntax available for use directly. > General Restrictions!!! > --- > - The column target list holds column fields only, > that are retrieved from one base relation / view > only. (NO joined views). I know there are other uses for updatable views (eg implementing column-based security policies) but the _only_ reason I ever found them useful in Oracle was precisely for joined views. They're the Oracle blessed method for achieving the same performance win as Postgres's FROM clause. So in Oracle you can do: UPDATE (select a.val as newval, b.b_id, b.val from a,b where a.b_id = b.b_id) SET val = newval -- greg ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Updateable Views?
On Sat, 07 Aug 2004 10:24:34 -0400, Jan Wieck <[EMAIL PROTECTED]> wrote: >I have not heard of "updatable subselects" yet. http://asktom.oracle.com/pls/ask/f?p=4950:8:6693556430011788783::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:273215737113, | Here we update a join. [...] | [EMAIL PROTECTED]> update | 2( select columnName, value | 3from name, lookup | 4 where name.keyname = lookup.keyname | 5 and lookup.otherColumn = :other_value ) | 6 set columnName = value | 7 / Google for oracle "delete statement" syntax or oracle "update statement" syntax Servus Manfred ---(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] Updateable Views?
On 8/3/2004 11:38 PM, Greg Stark wrote: "Scott Marlowe" <[EMAIL PROTECTED]> writes: On Tue, 2004-08-03 at 13:05, CSN wrote: > Just wondering, is updateable views slated for a > future version of Postgresql? In addition to using > rules that is. I would think that a basic fleshing out of the logic with some kind of stored proc to make the views and triggers would likely get someone started on the backend work. You know, a proof of concept thingy. I have some fears here. It seems everyone's first thought when they think about updateable views is to think about constructing rules on the views. How would that approach help with inline views? Things like: UPDATE (SELECT a+b AS x, c AS y FROM foo) SET c=1 WHERE x = 10 There is no such thing as an "inline view". What you show here is a subselect, and I have not heard of "updatable subselects" yet. Could you point me to the section in the ANSI SQL specifications that describes this feature please? Jan It seems like starting with these types of views in the backend would be more productive than implementing something in rules. Once postgres can handle inline views it should be trivial to handle persistent views just like they're handled on selects. -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Updateable Views?
On Tuesday 03 August 2004 08:38 pm, Greg Stark wrote: > "Scott Marlowe" <[EMAIL PROTECTED]> writes: > > On Tue, 2004-08-03 at 13:05, CSN wrote: > > > Just wondering, is updateable views slated for a > > > future version of Postgresql? In addition to using > > > rules that is. > > > > I would think that a basic fleshing out of the logic with some kind of > > stored proc to make the views and triggers would likely get someone > > started on the backend work. You know, a proof of concept thingy. > > I have some fears here. It seems everyone's first thought when they think > about updateable views is to think about constructing rules on the views. > > How would that approach help with inline views? Things like: > > UPDATE (SELECT a+b AS x, c AS y FROM foo) SET c=1 WHERE x = 10 > > It seems like starting with these types of views in the backend would be > more productive than implementing something in rules. Once postgres can > handle inline views it should be trivial to handle persistent views just > like they're handled on selects. I think you are putting the cart before the horse. We have to get things working and get the rules figured out before we can start modifying the backend. Once we get it all figured out, implemented, tested, and debugged, then maybe we can start considering modifying the backend. -- Jonathan Gardner [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Updateable Views?
"Scott Marlowe" <[EMAIL PROTECTED]> writes: > On Tue, 2004-08-03 at 13:05, CSN wrote: > > Just wondering, is updateable views slated for a > > future version of Postgresql? In addition to using > > rules that is. > > I would think that a basic fleshing out of the logic with some kind of > stored proc to make the views and triggers would likely get someone > started on the backend work. You know, a proof of concept thingy. I have some fears here. It seems everyone's first thought when they think about updateable views is to think about constructing rules on the views. How would that approach help with inline views? Things like: UPDATE (SELECT a+b AS x, c AS y FROM foo) SET c=1 WHERE x = 10 It seems like starting with these types of views in the backend would be more productive than implementing something in rules. Once postgres can handle inline views it should be trivial to handle persistent views just like they're handled on selects. -- greg ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Updateable views...
Eric D Nielsen <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
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 QS is updatable if and only if the fol- > lowing conditions hold: > > a) QS does not specify DISTINCT. No explanation needed. > > b) Every contained in the imme- > diately contained in QS consists of a , and > no appears more than once. This appears to say that the select list must be of the form: [ ] [[AS] ] [, ...] 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 immediately contained in thepression> immediately contained in QS specifies exactly one >and that 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 immediately contained in QS imme- > diately contains a WC, then no leaf generally > underlying table of QS shall be a generally underlying table > of any 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 immediately contained in QS does not > include a or a . No explanation needed. Eric Nielsen ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Updateable views...
Stanards URL's are in the developers FAQ. --- Tom Lane wrote: > Eric D Nielsen <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] so that your > message can get through to the mailing list cleanly > -- 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 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...
Eric D Nielsen <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
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...
Tom Lane <[EMAIL PROTECTED]> writes: > Eric D Nielsen <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Updateable views...
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 <[EMAIL PROTECTED]> +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're all light. --XTC ---(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] Updateable views...
On Wed, 2003-03-05 at 10:39, Eric D Nielsen wrote: > > 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? Lets try to meet the spec first, then debate about whether extending it is a good or bad thing :) -- Rod Taylor <[EMAIL PROTECTED]> PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Updateable views...
Eric D Nielsen <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
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
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. 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 QS is updatable if and only if the fol- lowing conditions hold: a) QS does not specify DISTINCT. b) Every contained in the imme- diately contained in QS consists of a , and no appears more than once. c) The immediately contained in the immediately contained in QS specifies exactly one and that refers either to a base table or to an updatable derived table. Note: updatable derived table is defined in Subclause 6.3, "". d) If the immediately contained in QS imme- diately contains a WC, then no leaf generally underlying table of QS shall be a generally underlying table of any contained in WC. e) The immediately contained in QS does not include a or a . The reference to 6.3 appears to be pointing at this: 8) A is an updatable derived table if and only if the simply contained in the of the of the 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
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...
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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Updateable views...
Neil Conway <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
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 -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 <[EMAIL PROTECTED]> || PGP Key ID: DB3C29FC ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Updateable views...
Eric D Nielsen <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
