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

"Bernd Helmle" <mailings(at)oopsware(dot)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(at)samurai(dot)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(at)oopsware(dot)de> wrote:
>
>
> --On Sonntag, März 12, 2006 23:52:12 -0500 Neil Conway <neilc(at)samurai(dot)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(at)samurai(dot)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. +