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 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 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 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
[HACKERS] Updateable views for 8.2 or 8.3?
Hi, is anybody working on the Bernd Helmle's updateable views patch? or know what the status of this is? -- 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 6: explain analyze is your friend
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 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 was:(Re: [HACKERS] Proposal for SYNONYMS)
Jaime Casanova wrote: On 3/9/06, Tom Lane [EMAIL PROTECTED] wrote: Josh Berkus josh@agliodbs.com 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 was:(Re: [HACKERS] Proposal for
--On Freitag, März 10, 2006 09:43:04 + Richard Huxton dev@archonet.com 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
[HACKERS] Updateable views was:(Re: [HACKERS] Proposal for SYNONYMS)
On 3/9/06, Tom Lane [EMAIL PROTECTED] wrote: Josh Berkus josh@agliodbs.com 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 ... regards, tom lane 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... -- regards, Jaime Casanova What they (MySQL) lose in usability, they gain back in benchmarks, and that's all that matters: getting the wrong answer really fast. Randal L. Schwartz ---(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
[HACKERS] Updateable views
Hi,
I'm currently working with Bernd in an implementation
of updateable views and want to know the hacker's
opinion on this issue.
What features have to be implemented in a first
extension in order to the patch to be accepted? What
features can wait until a second extension?
This are my first thought on this (i start working on
this just two weeks ago).
* thoughts ***
- 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)
General Restrictions!!!
---
- The column target list holds column fields only,
that are retrieved from one base relation / view
only. (NO joined views).
- UNION [ALL]/EXCEPT, DISTINCT and GROUP BY query
expressions aren't updateable at all.
- HAVING, Aggregates, function expressions and
Subqueries aren't allowed to be updateable, too
NOTE: one option is add a catalog that contains info
about updateability of the view attributes, just
like ORACLE's user_updateable_column view
(actually pg_attribute says what columns has a
view, can it be extended?).
That way we can have views in which some columns
are updateable and other are not. Views with
more complicated querys (even joined ones) can
be allowed this way.
Insertable???
--
We need to provide, at least, a value for every column
in the underlaying table that is NOT NULL and do not
have a DEFAULT value.
- If primary key of the table is a serial we can
manage it
CREATE RULE ins_people_full as ON
INSERT TO people_full DO INSTEAD
(
INSERT INTO people (person_id, inits, fname)
VALUES (nextval('people_person_id_seq'),NEW.inits,
NEW.fname);
INSERT INTO addresses (person_id,city, state, zip)
VALUES (currval('people_person_id_seq'), NEW.city,
NEW.state, NEW.zip);
);
- What if we add a new not null column without a
default value to the underlaying table? The insert
rule must be deleted?
Updateable???
--
Deleteable???
--
- Can we delete a row from the underlaying table if
the view where i execute the delete stmnt does not
view all the columns in that table?
- What about joined views? What is deleted?
Consider:
CREATE VIEW people_full AS
SELECT p.*, a.city, a.state, s.state_long,
a.country, a.zip
FROM people p JOIN addresses a USING (person_id)
JOIN states s USING (state);
The a.city, a.state, s.state_long, a.country, a.zip
columns must be deleted as well as the p.* columns
***
- Other point is: some people will not be happy
with updateable views, they will want their views to
be read-only. Should we have an extension to the sql
specs for this? Something like a READONLY keyword?
The patch Bernd did, actually covers some of this
points but is just for *very, very* simple views. We
want improve it.
These of course are just general ideas, and we really
want to know your opinion.
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 7: don't forget to increase your free space map settings
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...
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...
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...
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...
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...
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: 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])
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...
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 -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. 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 consists of 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 QS specifies exactly one table reference and that table reference refers either to a base table or 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 query expression contained in WC. e) The table expression immediately contained in QS does not include a group by clause or a having clause. 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 contained in 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 ---(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. 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...
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]
[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]
