Re: Updatable views

--On Donnerstag, Mai 08, 2008 13:28:14 +0100 Simon Riggs
<simon(at)2ndquadrant(dot)com> wrote:

> On Thu, 2008-05-08 at 13:48 +0200, Bernd Helmle wrote:
>> --On Mittwoch, Mai 07, 2008 20:38:59 +0100 Simon Riggs
>> <simon(at)2ndquadrant(dot)com> wrote:
>>
>> >> Where are we on this feature?
>> >
>> > Any update, Bernd?
>>
>> I've merged the patch into current -HEAD and updated some parts. My
>> current *working* state can be reviewed at
>>
>> <http://git.postgresql.org/?p=~psoo/postgresql.git;a=shortlog;h=updatabl
>> e_views>
>>
>> I'm still not sure how to implement a reliable CHECK OPTION, but short
>> on time i haven't done a very deep investigation yet. Next idea was to
>> look at the updatable cursor stuff, maybe something there can be reused.
>
> Your earlier patch seemed to add two rules if the view had a with check
> option? One with a pass through and another one with a do-nothing and a
> where clause.
>
> As I understand it
>
> CREATE VIEW x AS SELECT * FROM foo WHERE where-clause WITH CHECK OPTION
>
> should generate an INSERT rule like this
>
> CREATE RULE somename AS ON INSERT TO x WHERE where-clause DO INSERT ...
>

This was indeed the implementation i've proposed. We have rejected this
idea then because it doesn't work with volatile functions reliable due to
double evaluation:

<http://archives.postgresql.org/pgsql-patches/2006-08/msg00483.php>

Tom's example even demonstrates a serious constraint in rule based updates,
since you get side effects in such conditions you won't expect, even
without a CHECK OPTION.

> which seems straightforward, no?
>
> The SQLStandard default is CASCADED and it seems easier not to worry too
> much about the LOCAL option until we have the basics working. I'm not
> even sure that we *want* the LOCAL option anyway having read what it
> means, plus it isn't supported by many other DBMS.
>
> Do you store anything in the catalog to mark the view as updatable or
> not? I couldn't see that but it seemed easier than trying to resolve all
> of the updatability characteristics at run-time.

I'm not sure want you mean, but pg_rewrite.ev_kind stores the nature of the
rule. Updatability is determined by the checkTree() function internally.
It's easy to query pg_rewrite to examine wether a view is updatable or not.

>
> I may be able to help some with the patch, if you'd like?
>

You're welcome ;)

> --
> Simon Riggs
> 2ndQuadrant http://www.2ndQuadrant.com

--
Thanks

Bernd