Re: [PATCHES] Updatable views

[Quick note: sorry if you received this mail multiple times,
i've moved to a new workstation and my MUA gots hosed up
with its identities (all of them has the same adress, suddenly)
and I recognized that too late.....i'm sorry]

--On Freitag, September 01, 2006 10:03:42 -0400 Tom Lane
<tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Bernd Helmle <bernd(dot)helmle(at)oopsware(dot)de> writes:

>
> You're certainly welcome to work on it --- I don't have time at the
> moment. But I agree there's little chance of getting it done in time
> for 8.2.
>

What we can do is to restrict view updates that involves a volatile function
completely. As soon as the rewriter wants to apply an implicit system rule
to a current query which holds volatile functions, we could treat this as
an error.
However, i haven't looked into that right now how doable that would be, but
it seems correct in terms of data reliability (treat it as "volatile in
view update is
evil" ;)

Maybe it's worth to look how other database systems solve this problem.

> I have not read the spec's definition of WITH CHECK OPTION lately, so
> there may be something fundamentally wrong in what I'm about to say,
> but the way I'm envisioning this working is that W.C.O. is embodied as
> a check constraint (or something pretty similar --- a pg_constraint
> entry certainly) attached to the view. The rewriter would then be
> responsible for collecting all the check options that need to be
> enforced for a given rewritten query. This'd require some rework of
> the rewriter/planner/executor API: right now the executor is solely
> responsible for collecting check constraints to apply in an updating
> query, and we'd want to change that. My thought is we might as well
> move the collection responsibility over to the rewriter 100%: the
> rewriter decorates a Query with the list of constraint expressions
> to apply, and the executor just checks what it's told to. The planner
> probably need not do much with the constraint expressions beyond what
> it normally does with, say, targetlist expressions.

In order you want to do a CASCADED CHECK OPTION,
you need to collect all expressions out of underlying relations and rewrite
them to match
the table you are selecting...that looks like a very expensive operation.

>
> Some thoughts:
>
> * It's too early in the morning for me to be clear about the difference
> between CASCADED and LOCAL CHECK OPTION --- I think that this would
> merely alter the set of check constraints collected for a particular
> query, but if there's something more fundamental there, this scheme
> might not work at all. So look into that first.
>

LOCAL checks the data to be updated against its own view WHERE condition
only, where
CASCADED involves all WHERE conditions of all underlying views. That said,
it's
necessary to grep out all WHERE conditions of all relations involved in an
update operation and apply them to the current query as a constraint
expression. The
current implementation passes this recursively via a conditional rule
through the
rewriter. It looked to me as an attractive implementation, but it has this
annoying
multiple evaluation side effects....:(

> * The reason we currently collect constraints at executor start is that
> ALTER TABLE ADD CONSTRAINT has no way to invalidate cached plans, so
> it's unsafe to store lists of constraints in plans. So this scheme
> *requires* that we have a plan invalidation mechanism in place (at least
> before we release, maybe not before the patch can go in). This doesn't
> bother me because I intend anyway to see to it that there's plan inval
> in 8.3.
>

So we need to stall this idea unless we have something workable in this
area. So
what's the plan for 8.2? Should we reject updatable views completely or
is there some interest to apply this without CHECK OPTION? Some basic
functionality
could be simulated with table constraints, however, it's not what users out
there
would expect....

> * With check constraints now passing through the planner, it'd become
> trivial to allow subqueries in check constraints. Just sayin'.
>

That would be a nice feature, indeed ;)

> * We'd probably eliminate the idea of storing constraints in TupleDescs,
> which would be a good simplification anyway (eg, eliminate the bogus
> distinction between CreateTupleDescCopy and CreateTupleDescCopyConstr).
> OTOH that might make it harder to allow rowtypes to have associated
> constraints? Needs thought.
>
> regards, tom lane

--
Thanks

Bernd