pg_test fails but manual test works

[danbluhmhansen]

Hello there, I have an sql script users.sql with a simple statement level trigger on insert:

create table users (
  "id"       uuid        not null default gen_random_uuid() primary key,
  "added"    timestamptz not null default now(),
  "updated"  timestamptz not null default now(),
  "username" text        not null,
  "salt"     text        not null,
  "passhash" text        not null,
  "email"    text        null
);

create table users_streams (
  "id"       uuid not null default gen_random_uuid() primary key,
  "users_id" uuid not null
);

create unique index on users_streams ("users_id");

create table users_events (
  "timestamp" timestamptz not null default now(),
  "stream_id" uuid        not null references users_streams ("id") on delete cascade,
  "name"      text        not null,
  "data"      jsonb       null,
  primary key ("stream_id", "timestamp")
);

create or replace function trg_foo () returns trigger language plpgsql as $$
declare
  sid uuid;
begin
  insert into users_streams (users_id) values (gen_random_uuid()) returning id into sid;
  insert into users_events (stream_id, name) values (sid, 'foo');
  return null;
end;
$$;

create or replace trigger trg_users_foo after insert on users referencing new table as newtab execute function trg_foo('id');

and a pg_test using pgrx:

#[pg_test]
fn users_insert() -> Result<(), spi::Error> {
    Spi::run(include_str!("../sql/users.sql"))?;

    let user_id = Spi::get_one::<pgrx::Uuid>(
        "insert into users (username, salt, passhash) values ('foo', '', '') returning id;",
    );

    assert_eq!(
        Ok(Some(1)),
        Spi::get_one::<i64>("select count(*) from users_streams;"),
        "users_stream should contain one row from the trigger"
    );

    Ok(())
}

running cargo test users_insert results in:

assertion `left == right` failed: users_stream should contain one row from the trigger
  left: Ok(Some(1))
 right: Ok(Some(0))

but running cargo pgrx run, applying the users.sql script psql -p 28816 -d tankard -f ./sql/users.sql and running queries by hand results in:

tankard=# insert into users (username, salt, passhash) values ('foo', '', '');
INSERT 0 1
tankard=# select * from users;
                  id                  |             added             |            updated            | username | salt | passhash | email
--------------------------------------+-------------------------------+-------------------------------+----------+------+----------+-------
 f8318952-e41e-4ff5-8286-c933e9798bb3 | 2024-06-20 21:44:05.697594+02 | 2024-06-20 21:44:05.697594+02 | foo      |      |          |
(1 row)

tankard=# select * from users_streams ;
                  id                  |               users_id
--------------------------------------+--------------------------------------
 c77adf01-4a2b-4688-89ec-e736e865688b | 4043dbfb-6a28-4eaa-84b7-32d555c6452c
(1 row)

tankard=# select * from users_events ;
           timestamp           |              stream_id               | name | data
-------------------------------+--------------------------------------+------+------
 2024-06-20 21:44:05.697594+02 | c77adf01-4a2b-4688-89ec-e736e865688b | foo  |
(1 row)

any idea why the pg_test fails?

I am running pgrx version =0.12.0-alpha.1 in cargo.toml and pg16.