Selecting a related object modified by an `AFTER INSERT TRIGGER` does not show the inserted data

[laurenceisla]

PostgREST: 12.0.2 PostgreSQL: 14.9

Problem

Doing a POST request to a resource that has an AFTER INSERT TRIGGER that inserts data to another table, and selecting said table as an embedded resource in the request, shows no data in the related response.

Example

create table api.orders (
  id bigint primary key generated by default as identity,
  description text not null
);

create table api.documents (
  id bigint primary key generated by default as identity,
  order_id bigint not null references api.orders (id),
  label text not null
);

create or replace function api.create_order_document()
 returns trigger
 language plpgsql
 security definer
as $function$
begin
  insert into api.documents (order_id, label)
  values
    (new.id, 'Supplier Invoice'),
    (new.id, 'Packing List');

  return new;
end;
$function$
;

create or replace trigger create_order_document_trigger
after insert on api.orders
for each row execute function api.create_order_document();

Request:

curl -X POST "http://localhost:3000/orders?select=id,documents(*)" \
  -H "Content-Type: application/json" \
  -H "Prefer: return=representation" \
  -d "{\"description\": \"Some value\"}"

Result:

[{
  "id":5,
  "documents":[]
}]

Expected:

[{
  "id":5,
  "documents": [{"order_id":5,"label":"Supplier Invoice"}, {"order_id":5,"label":"Packing List"}]
}]

Proposal

Since we do the whole mutation/selection inside a single query, then fixing this might not be possible. I proposed to document this as a limitation (#3254) but it could also be considered as a bug if changing the design is not a problem, and it should not be documented then.

A workaround would be to do the related query inside a function, which is related to #818.

[wolfgangwalther]

Does this also happen when you have the trigger on the table, but both tables are hidden and exposed via views, through which you access those tables?

If yes: What if the views have INSTEAD OF triggers managing the insert to the base table - does it still happen, then?

---

Another workaround could be to turn the after trigger into a before trigger, get a sequence number manually and set NEW.id to it - then do the same "related insert" query.

---

Does it still happen if the CTE in which we do the INSERT is MATERIALIZED?

---

But overall, I don't really understand the use-case of such an after trigger, when everything happens on a table. I can't pass any data for the related insert, because everything is a table.

I have many similar situations, but in all of them I use a view, insert into the view - and then have an INSTEAD OF trigger, which runs both inserts (orders and documents in this case).

[laurenceisla]

As a disclaimer, this was an issue reported by a user in a Discord channel for Supabase. They mentioned that they can use workarounds, but was just curious to know why it behaved this way. The answer is mentioned in the linked PR and goes as follows:

"The sub-statements in WITH are executed concurrently with each other and with the main query", according to the PostgreSQL docs) and confirmed by this this PostgreSQL Q/A. This applies to how PostgREST inserts data and selects related tables afterwards.

---

Does this also happen when you have the trigger on the table, but both tables are hidden and exposed via views, through which you access those tables?

If yes: What if the views have INSTEAD OF triggers managing the insert to the base table - does it still happen, then?

Yes and yes. It behaves in the same way.

Another workaround could be to turn the after trigger into a before trigger, get a sequence number manually and set NEW.id to it - then do the same "related insert" query.

Not sure if it may work since the related insert still wouldn't have the newly inserted rows from the trigger. The results can't "see" each other between CTEs according to the docs (except when using RETURNING).

Does it still happen if the CTE in which we do the INSERT is MATERIALIZED?

Using WITH ... AS MATERIALIZED ( INSERT INTO...) right? Then yes, it still happens. It seems that it applies to all the WITH sub-statements regardless.

But overall, I don't really understand the use-case of such an after trigger, when everything happens on a table.

Yes, I agree, it seems to be for a special case that uses only the ID of the base table (in the example by generating two documents using default values and the ID from orders). Nevertheless, it was an issue encountered in the wild by a user so I considered it was worth mentioning.

[wolfgangwalther]

The results can't "see" each other between CTEs according to the docs (except when using RETURNING).

Wait, but we are using RETURNING, right?

[laurenceisla]

Wait, but we are using RETURNING, right?

Ah yes, but we're only RETURNING the base table columns, not the modified columns for the table inside the TRIGGER. Something like this:

WITH pgrst_source AS (
  INSERT INTO base_table (...)
  SELECT ...
  RETURNING * -- returns only columns from the base_table
)
SELECT * 
-- pgrst_source "sees" the values of base_table inside the WITH due to the RETURNING
FROM pgrst_source
-- other_table doesn't "see" the change inside the trigger (any trigger I believe)
-- because it was done inside the WITH and it isn't RETURNING those values
JOIN other_table ... 

Unless I misunderstood something.

[jdgamble555]

Maybe related - https://github.com/PostgREST/postgrest/discussions/2933

[wolfgangwalther]

As mentioned in https://github.com/PostgREST/postgrest/pull/3226#issuecomment-2211772584, I think the solution to this problem is to provide relational inserts, thus allowing this kind of insert without a trigger.