Strange CRUD mutation behavior on views with triggers

[rudism]

I'm submitting a ...

• bug report
• feature request
• question

PostGraphile version: 4.4.3

Minimal SQL file that can be loaded into a clean database:

create table entity_a (
  id serial primary key,
  value_a text
);

comment on table entity_a is '@omit read,update,create,delete,all,many';

create table entity_b (
  id int not null primary key references entity_a(id) on delete cascade,
  value_b text
);

comment on table entity_b is '@omit read,update,create,delete,all,many';

create view entity_joined as
select
  a.id,
  a.value_a,
  b.value_b
from entity_a a
inner join entity_b b
  on a.id = b.id;

comment on view entity_joined is '@primaryKey id';

create function entity_insert()
returns trigger
as $$
declare
  t_id int;
begin
  insert into entity_a (value_a) values (new.value_a) returning id into t_id;
  insert into entity_b (id, value_b) values (t_id, new.value_b);
  return new;
end;
$$ language plpgsql;

create trigger entity_instead_insert
instead of insert on entity_joined
for each row execute procedure entity_insert();

Steps to reproduce:

After loading the above sql schema into a db named testdb, running:

postgraphile -c postgres://localhost/testdb --schema public --enhance-graphiql

Current behavior:

GraphiQL shows the expected queries for the entity_joined view, plus two mutations updateEntityJoined and updateEntityJoinedById. Calling those mutations results in a postgresql exception cannot update view "entity_joined".

Expected behavior:

Since entity_joined selects from two tables, it is not updatable, so without an INSTEAD OF UPDATE trigger defined I wouldn't expect to see the update mutations generated for it.

Since it does have an INSTEAD OF INSERT trigger defined, I would expect to see create mutations generated for it.

Now if I also add an INSTEAD OF DELETE trigger to the view and re-run postgraphile, it does generate a createEntityJoined mutation, but no deleteEntityJoined mutation. So it seems like the CRUD mutations are being affected somehow by the presence or absence of triggers on the view, but not in a way that seems to make any sense.

create function entity_delete()
returns trigger
as $$
begin
  delete from entity_a where id=old.id;
  return old;
end;
$$ language plpgsql;

create trigger entity_instead_delete
instead of delete on entity_joined
for each row execute procedure entity_delete();

[rudism]

After a bit more testing, adding the INSTEAD OF UPDATE trigger causes the deleteEntityJoined mutation to get generated. Seems like these are all just mis-wired somehow?

[benjie]

Could you check these assertions, perhaps they're wrong:

https://github.com/graphile/graphile-engine/blob/cf0d1e1d3f1cc5ce318e763a71f2a6778ad57e3a/packages/graphile-build-pg/src/plugins/introspectionQuery.js#L169-L171

[rudism]

Here's my (slightly abbreviated to remove boilerplate output) test session, using the postgres:11-alpine docker image:

testdb=# select version();
> PostgreSQL 11.5 on x86_64-pc-linux-musl, compiled by gcc (Alpine 8.3.0) 8.3.0, 64-bit

testdb=# select oid from pg_class where relname='entity_joined';
> 16409

testdb=# select (pg_catalog.pg_relation_is_updatable(16409, true)::bit(8));
> 00000000

testdb=# create trigger entity_insert instead of insert on entity_joined for each row execute procedure entity_insert();
testdb=# select (pg_catalog.pg_relation_is_updatable(16409, true)::bit(8));
> 00001000

testdb=# drop trigger entity_insert on entity_joined;

testdb=# create trigger entity_update instead of update on entity_joined for each row execute procedure entity_update();
testdb=# select (pg_catalog.pg_relation_is_updatable(16409, true)::bit(8));
> 00000100

testdb=# drop trigger entity_update on entity_joined;

testdb=# create trigger entity_delete instead of delete on entity_joined for each row execute procedure entity_delete();
testdb=# select (pg_catalog.pg_relation_is_updatable(16409, true)::bit(8));
> 00010000

testdb=# create trigger entity_update instead of update on entity_joined for each row execute procedure entity_update();
testdb=# create trigger entity_insert instead of insert on entity_joined for each row execute procedure entity_insert();
testdb=# select (pg_catalog.pg_relation_is_updatable(16409, true)::bit(8));
> 00011100

Based on that, it would appear that the assertions you linked are mixed up (at least in the case of my example schema from the OP on this issue). The corrected assertions would be:

• 00000100 -> isUpdatable
• 00001000 -> isInsertable
• 00010000 -> isDeletable