Re: [HACKERS] Proposal for updatable views
URL added to TODO. I assume there has been no more progress on this patch. --- Bernd Helmle wrote: Hi folks, Please find attached a patch that implements SQL92-compatible updatable views. The patch introduces new semantics into the rule system: implicit and explicit rules. Implicit rules are created to implement updatable views: _INSERT _NOTHING_INSERT (unconditional DO INSTEAD NOTHING rule) _DELETE _NOTHING_DELETE (unconditional DO INSTEAD NOTHING rule) _UPDATE _NOTHING_UPDATE (unconditional DO INSTEAD NOTHING rule) These rules are marked 'implicit' in pg_rewrite, the rewriter is teached to handle them different, depending on wether they are created with a rule condition (a view's CHECK OPTION) or not. The CHECK OPTION itself is implemented with a new system function and a conditional rule that evaluates the view's WHERE condition (pg_view_update_error()). The supported syntax is CREATE VIEW foo AS SELECT ... [WITH [LOCAL | CASCADED] CHECK OPTION]; The LOCAL and CASCADED keywords are optional when a CHECK OPTION is specified, the default is CASCADED (this syntax creates a shift/reduce conflict in the grammar file i don't know how to fix). If a user wants his own rules with CREATE RULE to be created, the implicit rule gets dropped, depending what action the user selects. The patch introduces support for pg_dump as well. Please note that the patch isn't complete yet, but it seems it's necessary to discuss its implementation on -hackers now. Bernd [ Attachment, skipping... ] ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org -- Bruce Momjian http://candle.pha.pa.us EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Proposal for updatable views
Tom Lane wrote: Worst case is we promote WITH to a fully reserved word. While I don't normally care for doing that, it *is* a reserved word per SQL99, and offhand I don't see likely scenarios for someone using with as a table or column or function name. (Anyone know of a language in which with is a noun or verb?) If we eventually support a WITH clause for recursive queries I suspect we won't have much choice anyway. I could imagine someone using with as a column name, but I can't see how to avoid hurting those people. cheers andrew ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Proposal for updatable views
--On Sonntag, März 12, 2006 23:52:12 -0500 Neil Conway [EMAIL PROTECTED] wrote: On Fri, 2006-03-10 at 11:21 +0100, Bernd Helmle wrote: Please find attached a patch that implements SQL92-compatible updatable views. I'm currently reviewing this. Comments later... ok Please note that the patch isn't complete yet Do you have a list of known TODO items? The code needs to be teached to handle indexed array fields correctly, at the moment this causes the backend to crash. And there's also a shift/reduce conflict, which needs to be fixed in gram.y. The code has some fragments around which aren't used anymore, so a cleanup is on my todo as well (however, some are already ifdef'ed out). Bernd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Proposal for updatable views
On 3/13/06, Bernd Helmle [EMAIL PROTECTED] wrote: --On Sonntag, März 12, 2006 23:52:12 -0500 Neil Conway [EMAIL PROTECTED] wrote: On Fri, 2006-03-10 at 11:21 +0100, Bernd Helmle wrote: Please find attached a patch that implements SQL92-compatible updatable views. I'm currently reviewing this. Comments later... ok Please note that the patch isn't complete yet Do you have a list of known TODO items? There's a problem with CASTed expressions because it thinks (and with reason) that they are functions expressions (and those are not allowed) but with CAST you have to be flexible... i was working on that but at the time i am very busy... -- 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 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] Proposal for updatable views
On Sun, 2006-03-12 at 23:39 +0800, William ZHANG wrote: Maybe you can fix it like UNIONJOIN. Indeed, that is one option. Because the syntax is WITH [ LOCAL | CASCADED ] CHECK OPTION, ISTM we'll actually need three new tokens: WITH_LOCAL, WITH_CASCADED, and WITH_CHECK, which is even uglier :-( Per a suggestion from Dennis Bjorklund, it might be cleaner to introduce a lexer hack for the places where WITH can occur in a SelectStmt, which I believe is just WITH TIME ZONE. But Tom said he want to remove the support for UNION JOIN and save the overhead It would be unfortunate to revert the change, but I doubt the overhead is very significant. Does anyone have any better suggestions for how to resolve the problem? (My Bison-foo is weak, I have to confess...) -Neil ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Proposal for updatable views
Neil Conway [EMAIL PROTECTED] writes: On Sun, 2006-03-12 at 23:39 +0800, William ZHANG wrote: Maybe you can fix it like UNIONJOIN. Indeed, that is one option. Not any more ;-) It would be unfortunate to revert the change, but I doubt the overhead is very significant. Does anyone have any better suggestions for how to resolve the problem? (My Bison-foo is weak, I have to confess...) Worst case is we promote WITH to a fully reserved word. While I don't normally care for doing that, it *is* a reserved word per SQL99, and offhand I don't see likely scenarios for someone using with as a table or column or function name. (Anyone know of a language in which with is a noun or verb?) A quick look at the grammar suggests that the key problem is the opt_timezone production --- it might be that if we removed that in favor of spelling out the alternatives at the call sites, the conflict would go away. bison-fu is all about postponing shift/reduce decisions until you've seen enough to be sure ... regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Proposal for updatable views
A quick look at the grammar suggests that the key problem is the opt_timezone production --- it might be that if we removed that in favor of spelling out the alternatives at the call sites, the conflict would go away. bison-fu is all about postponing shift/reduce decisions until you've seen enough to be sure ... regards, tom lane Yes, if we can change opt_timezone and related production rules, it is a better choice. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Proposal for updatable views
Bernd Helmle [EMAIL PROTECTED] Hi folks, The supported syntax is CREATE VIEW foo AS SELECT ... [WITH [LOCAL | CASCADED] CHECK OPTION]; The LOCAL and CASCADED keywords are optional when a CHECK OPTION is specified, the default is CASCADED (this syntax creates a shift/reduce conflict in the grammar file i don't know how to fix). Maybe you can fix it like UNIONJOIN. See parser.c. But Tom said he want to remove the support for UNION JOIN and save the overhead: http://archives.postgresql.org/pgsql-hackers/2006-03/msg00344.php Regards, William ZHANG ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Proposal for updatable views
On Fri, 2006-03-10 at 11:21 +0100, Bernd Helmle wrote: Please find attached a patch that implements SQL92-compatible updatable views. I'm currently reviewing this. Comments later... Please note that the patch isn't complete yet Do you have a list of known TODO items? -Neil ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] Proposal for updatable views
Hi folks, Please find attached a patch that implements SQL92-compatible updatable views. The patch introduces new semantics into the rule system: implicit and explicit rules. Implicit rules are created to implement updatable views: _INSERT _NOTHING_INSERT (unconditional DO INSTEAD NOTHING rule) _DELETE _NOTHING_DELETE (unconditional DO INSTEAD NOTHING rule) _UPDATE _NOTHING_UPDATE (unconditional DO INSTEAD NOTHING rule) These rules are marked 'implicit' in pg_rewrite, the rewriter is teached to handle them different, depending on wether they are created with a rule condition (a view's CHECK OPTION) or not. The CHECK OPTION itself is implemented with a new system function and a conditional rule that evaluates the view's WHERE condition (pg_view_update_error()). The supported syntax is CREATE VIEW foo AS SELECT ... [WITH [LOCAL | CASCADED] CHECK OPTION]; The LOCAL and CASCADED keywords are optional when a CHECK OPTION is specified, the default is CASCADED (this syntax creates a shift/reduce conflict in the grammar file i don't know how to fix). If a user wants his own rules with CREATE RULE to be created, the implicit rule gets dropped, depending what action the user selects. The patch introduces support for pg_dump as well. Please note that the patch isn't complete yet, but it seems it's necessary to discuss its implementation on -hackers now. Bernd pgsql-view_update_8.2dev.tar.bz2 Description: Binary data ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
