Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

pg_test fails but manual test works #1739

Open
danbluhmhansen opened this issue Jun 20, 2024 · 0 comments
Open

pg_test fails but manual test works #1739

danbluhmhansen opened this issue Jun 20, 2024 · 0 comments

Comments

@danbluhmhansen
Copy link
Contributor

danbluhmhansen commented Jun 20, 2024

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

1 participant