Re: what are rules for?

"Michael Shulman" <shulman(at)mathcamp(dot)org> writes:
> On Tue, Jun 24, 2008 at 11:08 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> ... But updates and deletes require a
>> pre-existing target tuple, and there just aren't any of those in a view
>> relation. (Another way to say it is that update/delete require a CTID
>> column, which a view hasn't got.)

> But isn't the CTID column only required in order for the executor to
> actually *do* the update or delete? And since with a view, there is
> nothing to actually update or delete in the view itself, the trigger
> would be doing the only actual updating or deleting, so where would
> the CTID column be needed?

Well, both the trigger call API and the underlying implementation deal
in CTIDs, so just airily saying "we don't need 'em" doesn't obviously
work. (Note I did not say "obviously doesn't work". Whether this is
feasible depends on much closer analysis than any of the hand-waving
that we've done so far.)

To my mind there are two really fundamental issues underlying this.
One, which is what CTID fixes, is that a view doesn't have any primary
key by which to identify which row you're talking about. (Even if
there is a candidate key implicit in the view semantics, we don't
have any way for the system to know what it is.) The other nasty little
issue is that if the view involves any non-immutable functions, it's
not necessarily the case that you can recompute the OLD row at all.

Also, if the view involves expensive functions, you'd probably rather
the system *didn't* recompute them unless absolutely needed, even if
they're immutable. A transform-based approach can succeed at that, but
a trigger-based approach really can't since it needs to see materialized
OLD and NEW rows.

regards, tom lane