Horton

Thread

  1. Proposal for updatable views

    Bernd Helmle <mailings@oopsware.de> — 2006-03-10T10:21:45Z

    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

  2. Re: Proposal for updatable views

    William ZHANG <uniware@zedware.org> — 2006-03-12T15:39:07Z

    "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

  3. Re: Proposal for updatable views

    Neil Conway <neilc@samurai.com> — 2006-03-13T04:52:12Z

    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

  4. Re: Proposal for updatable views

    Bernd Helmle <mailings@oopsware.de> — 2006-03-13T19:01:56Z

    
--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

  5. Re: Proposal for updatable views

    Jaime Casanova <systemguards@gmail.com> — 2006-03-14T05:06:52Z

    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

  6. Re: Proposal for updatable views

    Neil Conway <neilc@samurai.com> — 2006-03-14T05:59:12Z

    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

  7. Re: Proposal for updatable views

    Tom Lane <tgl@sss.pgh.pa.us> — 2006-03-14T06:40:03Z

    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

  8. Re: Proposal for updatable views

    William ZHANG <uniware@zedware.org> — 2006-03-14T07:13:16Z

    > 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.

  9. Re: Proposal for updatable views

    Andrew Dunstan <andrew@dunslane.net> — 2006-03-14T08:18:51Z

    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

  10. Re: Proposal for updatable views

    Bruce Momjian <pgman@candle.pha.pa.us> — 2006-06-16T18:50:38Z

    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. +