Re: Transition tables for triggers on foreign tables and views

On Tue, Apr 25, 2017 at 6:17 PM, Thomas Munro
<thomas(dot)munro(at)enterprisedb(dot)com> wrote:

> My colleague Prabhat Sahu reported off list that transition tables
> don't work for views. I probably should have thought about that when
> I fixed something similar for partitioned tables, and after some
> experimentation I see that this is also broken for foreign tables.

Good spot. Don't know why that didn't occur to me to look at.

> For foreign tables using postgres_fdw, I see that transition tables
> capture new rows for INSERT but capture nothing for DELETE and UPDATE.

Will dig into that in a bit, but first...

> For views, aside from the question of transition tables, I noticed
> that statement triggers don't seem to fire at all with updatable
> views. Surely they should -- isn't that a separate bug?
>
> Example:
>
> create table my_table (i int);
> create view my_view as select * from my_table;
> create function my_trigger_function() returns trigger language plpgsql as
> $$ begin raise warning 'hello world'; return null; end; $$;
> create trigger my_trigger after insert or update or delete on my_view
> for each statement execute procedure my_trigger_function();
> insert into my_view values (42);
>
> ... and the world remains ungreeted.

I checked out 25dc142a (before any of my commits for $subject),
built it, and tried the above -- with no warning generated. I then
used an UPDATE and DELETE against the view, also with no trigger
fired (nor any error during trigger creation or DML). Does anyone
know whether such trigger ever fired at any point in the commit
history? Should we remove the documentation that anything except
INSTEAD OF triggers work on a view, and generate errors for attempt
to do otherwise, or is there something to salvage that has worked at
some point? If we do get these working, don't they deserve at least
one regression test?

Will post again after I have a chance to review the FDW issue.

--
Kevin Grittner
VMware vCenter Server
https://www.vmware.com/