Selecting a related object modified by an `AFTER INSERT TRIGGER` does not show the inserted data about postgrest HOT 4 OPEN

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).

from postgrest.

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.

from postgrest.

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

Wait, but we are using RETURNING, right?

from postgrest.

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.

from postgrest.