Supabase trigger NEW reference to autogenerated ID is null

In supabase I have a trigger that triggers after INSERT at table x:
_{demo at db-fiddle}

----for tests on vanilla PostgreSQL, off Supabase
--create schema auth;
--create function auth.uid()returns uuid return gen_random_uuid();

create table public.x(
  id int generated by default as identity primary key
, x_name text);

create table public.user_x(
    user_id uuid
  , x_id int references public.x(id)
  , user_role text);

create function add_user_x()
returns trigger language plpgsql as '
BEGIN
    INSERT INTO public.user_x (user_id, x_id, user_role)
    VALUES (auth.uid(), NEW.id, ''owner'');
    return NEW;
END';

This is the trigger definition:

CREATE TRIGGER add_user_x 
AFTER INSERT ON public.x
FOR EACH STATEMENT EXECUTE FUNCTION add_user_x()

Now my problem is that the "user_id" and "user_role" fields work fine in the insert, but for the "x_id" variable only a NULL is inserted. The ID of x gets autogenerated using the default supabase "gen_random_uuid ()" function and it works fine in the "x" table.

Could the issue be that the NEW object that I reference to does not yet contain the autogenerated ID field? How could I fix this issue and make sure that the x_id column of my user_x table gets filled by the trigger?

Solution

Statement-level triggers require that you define an alias for the transition relations explicitly.
From CREATE TRIGGER doc:

The REFERENCING option enables collection of transition relations, which are row sets that include all of the rows inserted, deleted, or modified by the current SQL statement. This feature lets the trigger see a global view of what the statement did, not just one row at a time. (...) NEW TABLE may only be specified once, and only for a trigger that can fire on UPDATE or INSERT; it creates a transition relation containing the after-images of all rows updated or inserted by the statement.

And as already quoted by @Adrian Klaver from 43.10.1. Triggers on Data Changes:

When a PL/pgSQL function is called as a trigger, several special variables are created automatically in the top-level block. They are:

NEW record
new database row for INSERT/UPDATE operations in row-level triggers. This variable is null in statement-level triggers and for DELETE operations.

Makes sense, because it's a singular, scalar record, and statement-level triggers should see a whole table or an array of those, rather than just one.
The change that would get rid of your error is pretty minor: add the referencing clause, and replace the insert..values in the trigger function, with an insert..select..from, using the transition relation that holds all newly generated id's:
_{demo at db-fiddle}

create function add_user_x()returns trigger language plpgsql as '
BEGIN
    INSERT INTO public.user_x (user_id, x_id, user_role)
    SELECT auth.uid(), new_table.id, ''owner''
    FROM new_table;
    
    RETURN NEW;
END';


CREATE TRIGGER add_user_x 
AFTER INSERT ON public.x 
REFERENCING NEW TABLE AS new_table --mandatory
FOR EACH STATEMENT EXECUTE FUNCTION add_user_x()

Now you can see that in response to an insert into x, the user_x table gets a new record with the x_id correctly populated based on incoming x:

insert into x(x_name)values('x_1');
select * from user_x;

user_id	x_id	user_role
b2bba742-f299-4cd8-8ed4-5070b9ff84e5	1	owner

And the table can now be joined on that foreign key:

select*from x join user_x on x.id=user_x.x_id;

id	x_name	user_id	x_id	user_role
1	x_1	b2bba742-f299-4cd8-8ed4-5070b9ff84e5	1	owner

Another way to fix this would be to downgrade to a row-level trigger, which you could do by simply swapping out for each statement for for each row and leaving everything else unaltered, but this is a good use case for statement-level triggers. They'll perform way better and fire just once.