Proposal for updatable views

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