Crash when epgsql_sock:get_results/1 returns > 1 results after deferred constraint trigger raises exception

[ehamberg]

(epgsql 4.5.0)

I'm porting an app to use epgsql and when running my test suite there are a few crashes where epgsql handles a ReadyForQuery message and gets two results from epgsql_sock:get_results(Sock) where the latter is an error from an PostgreSQL exception that was raised by a constraint trigger.

Here's a (slightly redacted) error from running a common test suite (where an exception raised in PostgreSQL and thus an error result is expected). As you can see, epgsql_sock:get_results(Sock) gets two results ({ok,1} and {error, #error{...}}) and crashes:

{{{{case_clause,
       [{ok,1},
        {error,
            {error,error,<<"P0001">>,raise_exception,
                <<"some inconsistency error">>,
                [{file,<<"pl_exec.c">>},
                 {line,<<"3876">>},
                 {routine,<<"exec_stmt_raise">>},
                 {severity,<<"ERROR">>},
                 {where,
                     <<"PL/pgSQL function some_schema.some_function() line 26 at RAISE">>}]}}]},
   [{epgsql_cmd_equery,handle_message,4,
        [{file,
             "/myproject/_build/default/lib/epgsql/src/commands/epgsql_cmd_equery.erl"},
         {line,83}]},
    {epgsql_sock,command_handle_message,3,
        [{file,
             "/myproject/_build/default/lib/epgsql/src/epgsql_sock.erl"},
         {line,352}]},
    {epgsql_sock,loop,1,
        [{file,
             "/myproject/_build/default/lib/epgsql/src/epgsql_sock.erl"},
         {line,436}]},
    {gen_server,try_dispatch,4,[{file,"gen_server.erl"},{line,680}]},
    {gen_server,handle_msg,6,[{file,"gen_server.erl"},{line,756}]},
    {proc_lib,init_p_do_apply,3,[{file,"proc_lib.erl"},{line,226}]}]},
  {gen_server,call,
      [<0.3876.0>,
       {command,epgsql_cmd_equery,
           {{statement,[],[],
                [numeric],
                [{1700,numeric,false}]},
            [{numeric,"50"}]}},
       infinity]}},
 {gen_server,call,
     [<0.3875.0>,
      {query,
          <<"INSERT INTO \"some_schema\".\"some_table\"(\"some_column\") VALUES ($1)">>,
          ["50"],
          5000},
      5100]}}

Is this an error in epgsql, or am I doing something wrong in my code?

[seriyps]

Wow, I didn't know both error and "ok" can be returned at the same time. Are you sure you are not, eg, using the same connection from multiple processes at the same time?

Would you be able to create a reproducible minimal example?

[ehamberg]

After some investigation, I think this is related to initially deferred constraint triggers.

I was able to reproduce this with the following table + constraint trigger:

create table my_table (x int);

create or replace function my_check() returns trigger as $$
begin
    if new.x >= 10 then
        raise 'x must be < 10';
    end if;

    return null;
end; $$ language 'plpgsql';

create constraint trigger my_trigger
  after insert or update on my_table
  initially deferred
  for each row execute procedure my_check();

… and the following code:

    {ok, C} = epgsql:connect(#{
        host => "localhost",
        username => "mydb",
        database => "mydb"
    }),
    {error, _} = epgsql:equery(C, "insert into my_table values(11)", []),
    ok = epgsql:close(C).

Which crashes because epgsql_sock:get_results(Sock) returns more than one value:

    {case_clause,
        [{ok,1},
         {error,
             {error,error,<<"P0001">>,raise_exception,
                 <<"x must be < 10">>,
                 [{file,<<"pl_exec.c">>},
                  {line,<<"3876">>},
                  {routine,<<"exec_stmt_raise">>},
                  {severity,<<"ERROR">>},
                  {where,
                      <<"PL/pgSQL function my_check() line 4 at RAISE">>}]}}]}}

However, if I change from equery to squery, it works as expected:

    […]
    {error, _} = epgsql:squery(C, "insert into my_table values(11)"), % matches
    […]

Also, if the constraint trigger is not initially deferred, things work with equery again:

drop trigger my_trigger on my_table;

create constraint trigger my_trigger
  after insert or update on my_table
  -- initially deferred ⚠️ DON'T defer the constraint trigger
  for each row execute procedure my_check();

I guess what's happening is that the {ok,1} comes from the insert which initially succeeds – until the deferred constraint trigger is invoked and raises the exception, which returns an error value. That would explain why two results are returned.

[seriyps]

Yes, it for sure makes sense. I'll look at it a bit later. Do you know if the operation then succeeds in the end? Or insert is then rolled back? I'm not really familiar with deferred triggers. What do you think makes sense to return in such cases? I guess if operation is rolled back in the end, then we should return only the error. Also interesting how result would look if there is a RETURNING xxx clause.

On Fri, 26 Feb 2021, 21:36 Erlend Hamberg, [email protected] wrote:

After some investigation, I think this is related to initially deferred constraint triggers.

I was able to reproduce this with the following table + constraint trigger:

create table my_table (x int);

create or replace function my_check() returns trigger as $$ begin

if new.x >= 10 then

    raise 'x must be < 10';

end if;



return null;

end; $$ language 'plpgsql';

create constraint trigger my_trigger

after insert or update on my_table

initially deferred

for each row execute procedure my_check();

… and the following code:

{ok, C} = epgsql:connect(#{

    host => "localhost",

    username => "mydb",

    database => "mydb"

}),

{error, _} = epgsql:equery(C, "insert into my_table values(11)", []),

ok = epgsql:close(C).

Which crashes because epgsql_sock:get_results(Sock) returns more than one value:

{case_clause,

    [{ok,1},

     {error,

         {error,error,<<"P0001">>,raise_exception,

             <<"x must be < 10">>,

             [{file,<<"pl_exec.c">>},

              {line,<<"3876">>},

              {routine,<<"exec_stmt_raise">>},

              {severity,<<"ERROR">>},

              {where,

                  <<"PL/pgSQL function my_check() line 4 at RAISE">>}]}}]}}

However, if I change from equery to squery, it works as expected:

[…]

{error, _} = epgsql:squery(C, "insert into my_table values(11)"), % matches

[…]

Also, if the constraint trigger is not initially deferred, things work with equery again:

drop trigger my_trigger on my_table;

create constraint trigger my_trigger

after insert or update on my_table

--initially deferred

for each row execute procedure my_check();

I guess what's happening is that the {ok,1} comes from the insert which initially succeeds – until the deferred constraint trigger is invoked and raises the exception, which returns an error value. That would explain why two results are returned.

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/epgsql/epgsql/issues/256#issuecomment-786880891, or unsubscribe https://github.com/notifications/unsubscribe-auth/AADHA7RWTOLUPIRA45OX6ZDTBAA6XANCNFSM4YIVKESA .

[ehamberg]

Do you know if the operation then succeeds in the end? Or insert is then rolled back?

The transaction fails and the insert is rolled back, so only the error should be returned.

Also interesting how result would look if there is a RETURNING xxx clause.

If I add a returning clause to the insert (epgsql:equery(C, "insert into my_table values(11) returning 43", [])), it returns {ok,1,Rows}:

   {case_clause,
        [{ok,1,[{column,<<"?column?">>,int4,23,4,-1,1,0,0}],[{43}]},
         {error,
             {error,error,<<"P0001">>,raise_exception,<<"x must be < 10">>,
                 [{file,<<"pl_exec.c">>},
                  {line,<<"3876">>},
                  {routine,<<"exec_stmt_raise">>},
                  {severity,<<"ERROR">>},
                  {where,
                      <<"PL/pgSQL function my_check() line 4 at RAISE">>}]}}]}}