Thread: 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
Attachment
"Bernd Helmle" <mailings@oopsware.de> > 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
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
--On Sonntag, März 12, 2006 23:52:12 -0500 Neil Conway <neilc@samurai.com>
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
On 3/13/06, Bernd Helmle <mailings@oopsware.de> wrote: > > > --On Sonntag, März 12, 2006 23:52:12 -0500 Neil Conway <neilc@samurai.com> > 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
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
Neil Conway <neilc@samurai.com> 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
> 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.
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
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 EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +