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.

Sign up for GitHub

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

Jump to bottom

(Minor) Inconsistency in delete ... returning #86

Closed
aherranz opened this issue Dec 16, 2015 · 9 comments
Closed

(Minor) Inconsistency in delete ... returning #86

aherranz opened this issue Dec 16, 2015 · 9 comments
Labels
bug

Comments

@aherranz
Copy link
Contributor

I think there is an inconsistent behaviour in delete...returning statement when no rows are deleted:

16> pgapp:squery(pre2,"delete from email_lnk returning *").
{ok,2,
    [{column,<<"id">>,text,-1,-1,0},
     {column,<<"created">>,int8,8,-1,0},
     {column,<<"email">>,text,-1,-1,0},
     {column,<<"tries">>,int4,4,-1,0}],
    [{<<"_6pj">>,<<"1450282807">>,<<"aherranz@...">>,
      <<"0">>},
     {<<"MKPh">>,<<"1450282813">>,<<"aherranz@...">>,
      <<"0">>}]}
17> pgapp:squery(pre2,"delete from email_lnk returning *").
{ok,0}

Since I am using returning I would expect the second expression to return

{ok,0,
    [{column,<<"id">>,text,-1,-1,0},
     {column,<<"created">>,int8,8,-1,0},
     {column,<<"email">>,text,-1,-1,0},
     {column,<<"tries">>,int4,4,-1,0}],
    []}

Am I wrong?
@seriyps
Copy link
Member

seriyps commented Dec 16, 2015

Probably epgsql returns exactly what postgresql server returns to it. Should ensure...

@aherranz
Copy link
Contributor Author 

email-> delete from email_lnk returning *;
  id  |  created   |        email         | tries 
------+------------+----------------------+-------
 bkAH | 1450305670 | aherranz@.........   |     0
 1Poy | 1450305673 | aherranz@.........   |     0
 YmjV | 1450305675 | angel.herranz@...... |     0
(3 rows)

DELETE 3
email=> delete from email_lnk returning *;
 id | created | email | tries 
----+---------+-------+-------
(0 rows)

DELETE 0
email=> delete from email_lnk returning *;
 id | created | email | tries 
----+---------+-------+-------
(0 rows)

DELETE 0
email=> delete from email_lnk;
DELETE 0

@seriyps
Copy link
Member

seriyps commented Nov 22, 2016

Ok, I've just realized that your problem may be related to pgapp, not epgsql driver.
Or it was resolved eventualy some time ago.

I checked at current devel branch:

> {ok, C} = epgsql:connect(#{host => "localhost", username => "postgres", password => "postgres", port => 5433}).
> epgsql:equery(C, "CREATE TEMPORARY TABLE t(c integer)").
{ok,[],[]}
> rr("include/epgsql.hrl").
[column,error,statement]
> epgsql:squery(C, "INSERT INTO t (c) VALUES (1), (2), (3) RETURNING c").
{ok,3,
    [#column{name = <<"c">>,type = int4,size = 4,modifier = -1,
             format = 0}],
    [{<<"1">>},{<<"2">>},{<<"3">>}]}
> epgsql:squery(C, "DELETE FROM t RETURNING c").                         
{ok,3,
    [#column{name = <<"c">>,type = int4,size = 4,modifier = -1,
             format = 0}],
    [{<<"1">>},{<<"2">>},{<<"3">>}]}

And the same for equery.

@aherranz
Copy link
Contributor Author

aherranz commented Nov 28, 2016 •
edited

On my view, the problem is still there: epgsql:squery(C, "DELETE FROM t RETURNING c"). returns {ok, N, Columns, Rows} when several rows were deleted and {ok, 0} when no rows are deleted:

> epgsql:squery(C, "DELETE FROM t RETURNING c").
{ok,3,
    [#column{name = <<"c">>,type = int4,size = 4,modifier = -1,
             format = 0}],
    [{<<"1">>},{<<"2">>},{<<"3">>}]}
> epgsql:squery(C, "DELETE FROM t RETURNING c").
{ok,0}

I think this is not consistent with the documentation (returning vs no returning), but the main problem is that you have no information about columns and likely you expect them.

Regards :)

@seriyps
Copy link
Member

seriyps commented Nov 28, 2016

Ok, so, I traced epgsql's internal function calls and messages:

6> {ok, C} = epgsql:connect(#{host=>"localhost", username=>"postgres", password=>"postgres", database=>"postgres"}).
{ok,<0.191.0>}
7> epgsql:squery(C, "CREATE TEMPORARY TABLE t(c integer)").                                                         
{ok,[],[]}
8> epgsql:squery(C, "INSERT INTO t(c) VALUES (1), (2), (3) RETURNING c").
{ok,3,
    [{column,<<"c">>,int4,4,-1,0}],
    [{<<"1">>},{<<"2">>},{<<"3">>}]}
9> dbg:start().
{ok,<0.196.0>}
10> dbg:tracer().
{ok,<0.196.0>}
11> dbg:tpl(epgsql_sock, on_message, 2, []).
{ok,[{matched,nonode@nohost,1}]}
12> dbg:p(C, [c, m]).
{ok,[{matched,nonode@nohost,1}]}
% ================== Rows returned ============================
13> epgsql:squery(C, "DELETE FROM t RETURNING c").
(<0.191.0>) << {'$gen_call',{<0.188.0>,#Ref<0.0.1.1445>},
                            {squery,"DELETE FROM t RETURNING c"}}
(<0.191.0>) << {inet_reply,#Port<0.37136>,ok}
(<0.191.0>) << {tcp,#Port<0.37136>,
                    <<84,0,0,0,26,0,1,99,0,0,1,77,145,0,1,0,0,0,23,0,4,255,
                      255,255,255,0,0,68,0,0,0,11,0,1,0,0,0,1,49,68,0,0,0,11,
                      0,1,0,0,0,1,50,68,0,0,0,11,0,1,0,0,0,1,51,67,0,0,0,13,
                      68,69,76,69,84,69,32,51,0,90,0,0,0,5,73>>}
%    84 ("T") is "row description" packet. It contains this #column{} infos
(<0.191.0>) call epgsql_sock:on_message({84,<<0,1,99,0,0,1,77,145,0,1,0,0,0,23,0,4,255,255,255,255,0,0>>},{state,gen_tcp,#Port<0.37136>,
       <<68,0,0,0,11,0,1,0,0,0,1,49,68,0,0,0,11,0,1,0,0,0,1,50,68,0,0,0,11,0,1,
         0,0,0,1,51,67,0,0,0,13,68,69,76,69,84,69,32,51,0,90,0,0,0,5,73>>,
       {25093,2074650982},
       on_message,
       {codec,[],[]},
       {[{{call,{<0.188.0>,#Ref<0.0.1.1445>}},
          {squery,"DELETE FROM t RETURNING c"}}],
        []},
       undefined,
       [{<<"application_name">>,<<>>},
        {<<"client_encoding">>,<<"UTF8">>},
        {<<"DateStyle">>,<<"ISO, DMY">>},
        {<<"integer_datetimes">>,<<"on">>},
        {<<"IntervalStyle">>,<<"postgres">>},
        {<<"is_superuser">>,<<"on">>},
        {<<"server_encoding">>,<<"UTF8">>},
        {<<"server_version">>,<<"9.1.13">>},
        {<<"session_authorization">>,<<"postgres">>},
        {<<"standard_conforming_strings">>,<<"on">>},
        {<<"TimeZone">>,<<"W-SU">>}],
       [],[],[],[],[],undefined,73,undefined,undefined,undefined,undefined,
       undefined,undefined,undefined,undefined})
{ok,3,
    [{column,<<"c">>,int4,4,-1,0}],
    [{<<"1">>},{<<"2">>},{<<"3">>}]}
% 68 ("D") is data row packet
(<0.191.0>) call epgsql_sock:on_message({68,<<0,1,0,0,0,1,49>>},{state,gen_tcp,#Port<0.37136>,
       <<68,0,0,0,11,0,1,0,0,0,1,50,68,0,0,0,11,0,1,0,0,0,1,51,67,0,0,0,13,68,
         69,76,69,84,69,32,51,0,90,0,0,0,5,73>>,
       {25093,2074650982},
       on_message,
       {codec,[],[]},
       {[{{call,{<0.188.0>,#Ref<0.0.1.1445>}},
          {squery,"DELETE FROM t RETURNING c"}}],
        []},
       undefined,
       [{<<"application_name">>,<<>>},
        {<<"client_encoding">>,<<"UTF8">>},
        {<<"DateStyle">>,<<"ISO, DMY">>},
        {<<"integer_datetimes">>,<<"on">>},
        {<<"IntervalStyle">>,<<"postgres">>},
        {<<"is_superuser">>,<<"on">>},
        {<<"server_encoding">>,<<"UTF8">>},
        {<<"server_version">>,<<"9.1.13">>},
        {<<"session_authorization">>,<<"postgres">>},
        {<<"standard_conforming_strings">>,<<"on">>},
        {<<"TimeZone">>,<<"W-SU">>}],
       [],
       [{column,<<"c">>,int4,4,-1,0}],
       [],[],[],undefined,73,undefined,undefined,undefined,undefined,
       undefined,undefined,undefined,undefined})
14> (<0.191.0>) call epgsql_sock:on_message({68,<<0,1,0,0,0,1,50>>},{state,gen_tcp,#Port<0.37136>,
       <<68,0,0,0,11,0,1,0,0,0,1,51,67,0,0,0,13,68,69,76,69,84,69,32,51,0,90,0,
         0,0,5,73>>,
       {25093,2074650982},
       on_message,
       {codec,[],[]},
       {[{{call,{<0.188.0>,#Ref<0.0.1.1445>}},
          {squery,"DELETE FROM t RETURNING c"}}],
        []},
       undefined,
       [{<<"application_name">>,<<>>},
        {<<"client_encoding">>,<<"UTF8">>},
        {<<"DateStyle">>,<<"ISO, DMY">>},
        {<<"integer_datetimes">>,<<"on">>},
        {<<"IntervalStyle">>,<<"postgres">>},
        {<<"is_superuser">>,<<"on">>},
        {<<"server_encoding">>,<<"UTF8">>},
        {<<"server_version">>,<<"9.1.13">>},
        {<<"session_authorization">>,<<"postgres">>},
        {<<"standard_conforming_strings">>,<<"on">>},
        {<<"TimeZone">>,<<"W-SU">>}],
       [],
       [{column,<<"c">>,int4,4,-1,0}],
       [{<<"1">>}],
       [],[],undefined,73,undefined,undefined,undefined,undefined,undefined,
       undefined,undefined,undefined})
(<0.191.0>) call epgsql_sock:on_message({68,<<0,1,0,0,0,1,51>>},{state,gen_tcp,#Port<0.37136>,
       <<67,0,0,0,13,68,69,76,69,84,69,32,51,0,90,0,0,0,5,73>>,
       {25093,2074650982},
       on_message,
       {codec,[],[]},
       {[{{call,{<0.188.0>,#Ref<0.0.1.1445>}},
          {squery,"DELETE FROM t RETURNING c"}}],
        []},
       undefined,
       [{<<"application_name">>,<<>>},
        {<<"client_encoding">>,<<"UTF8">>},
        {<<"DateStyle">>,<<"ISO, DMY">>},
        {<<"integer_datetimes">>,<<"on">>},
        {<<"IntervalStyle">>,<<"postgres">>},
        {<<"is_superuser">>,<<"on">>},
        {<<"server_encoding">>,<<"UTF8">>},
        {<<"server_version">>,<<"9.1.13">>},
        {<<"session_authorization">>,<<"postgres">>},
        {<<"standard_conforming_strings">>,<<"on">>},
        {<<"TimeZone">>,<<"W-SU">>}],
       [],
       [{column,<<"c">>,int4,4,-1,0}],
       [{<<"2">>},{<<"1">>}],
       [],[],undefined,73,undefined,undefined,undefined,undefined,undefined,
       undefined,undefined,undefined})
% 67 ("C") is CommandComplete packet
(<0.191.0>) call epgsql_sock:on_message({67,<<68,69,76,69,84,69,32,51,0>>},{state,gen_tcp,#Port<0.37136>,
       <<90,0,0,0,5,73>>,
       {25093,2074650982},
       on_message,
       {codec,[],[]},
       {[{{call,{<0.188.0>,#Ref<0.0.1.1445>}},
          {squery,"DELETE FROM t RETURNING c"}}],
        []},
       undefined,
       [{<<"application_name">>,<<>>},
        {<<"client_encoding">>,<<"UTF8">>},
        {<<"DateStyle">>,<<"ISO, DMY">>},
        {<<"integer_datetimes">>,<<"on">>},
        {<<"IntervalStyle">>,<<"postgres">>},
        {<<"is_superuser">>,<<"on">>},
        {<<"server_encoding">>,<<"UTF8">>},
        {<<"server_version">>,<<"9.1.13">>},
        {<<"session_authorization">>,<<"postgres">>},
        {<<"standard_conforming_strings">>,<<"on">>},
        {<<"TimeZone">>,<<"W-SU">>}],
       [],
       [{column,<<"c">>,int4,4,-1,0}],
       [{<<"3">>},{<<"2">>},{<<"1">>}],
       [],[],undefined,73,undefined,undefined,undefined,undefined,undefined,
       undefined,undefined,undefined})
% 90 ("Z") - ready for next query
(<0.191.0>) call epgsql_sock:on_message({90,<<"I">>},{state,gen_tcp,#Port<0.37136>,<<>>,
       {25093,2074650982},
       on_message,
       {codec,[],[]},
       {[{{call,{<0.188.0>,#Ref<0.0.1.1445>}},
          {squery,"DELETE FROM t RETURNING c"}}],
        []},
       undefined,
       [{<<"application_name">>,<<>>},
        {<<"client_encoding">>,<<"UTF8">>},
        {<<"DateStyle">>,<<"ISO, DMY">>},
        {<<"integer_datetimes">>,<<"on">>},
        {<<"IntervalStyle">>,<<"postgres">>},
        {<<"is_superuser">>,<<"on">>},
        {<<"server_encoding">>,<<"UTF8">>},
        {<<"server_version">>,<<"9.1.13">>},
        {<<"session_authorization">>,<<"postgres">>},
        {<<"standard_conforming_strings">>,<<"on">>},
        {<<"TimeZone">>,<<"W-SU">>}],
       [],[],[],
       [{ok,3,[{column,<<"c">>,int4,4,-1,0}],[{<<"1">>},{<<"2">>},{<<"3">>}]}],
       [],undefined,73,
       {delete,3},
       undefined,undefined,undefined,undefined,undefined,undefined,undefined})
% and epgsql sends reply back
(<0.191.0>) <0.188.0> ! {#Ref<0.0.1.1445>,
                         {ok,3,
                             [{column,<<"c">>,int4,4,-1,0}],
                             [{<<"1">>},{<<"2">>},{<<"3">>}]}}

14> 
14> %=================== No rows returned ==========================
14> epgsql:squery(C, "DELETE FROM t RETURNING c").
(<0.191.0>) << {'$gen_call',{<0.188.0>,#Ref<0.0.2.1271>},
                            {squery,"DELETE FROM t RETURNING c"}}
(<0.191.0>) << {inet_reply,#Port<0.37136>,ok}
(<0.191.0>) << {tcp,#Port<0.37136>,
                    <<84,0,0,0,26,0,1,99,0,0,1,77,145,0,1,0,0,0,23,0,4,255,
                      255,255,255,0,0,67,0,0,0,13,68,69,76,69,84,69,32,48,0,
                      90,0,0,0,5,73>>}
{ok,0}
% 84 ("T") - row descriptions. YES! postgresql sends row descriptions even when there are no rows in response
15> (<0.191.0>) call epgsql_sock:on_message({84,<<0,1,99,0,0,1,77,145,0,1,0,0,0,23,0,4,255,255,255,255,0,0>>},{state,gen_tcp,#Port<0.37136>,
       <<67,0,0,0,13,68,69,76,69,84,69,32,48,0,90,0,0,0,5,73>>,
       {25093,2074650982},
       on_message,
       {codec,[],[]},
       {[{{call,{<0.188.0>,#Ref<0.0.2.1271>}},
          {squery,"DELETE FROM t RETURNING c"}}],
        []},
       undefined,
       [{<<"application_name">>,<<>>},
        {<<"client_encoding">>,<<"UTF8">>},
        {<<"DateStyle">>,<<"ISO, DMY">>},
        {<<"integer_datetimes">>,<<"on">>},
        {<<"IntervalStyle">>,<<"postgres">>},
        {<<"is_superuser">>,<<"on">>},
        {<<"server_encoding">>,<<"UTF8">>},
        {<<"server_version">>,<<"9.1.13">>},
        {<<"session_authorization">>,<<"postgres">>},
        {<<"standard_conforming_strings">>,<<"on">>},
        {<<"TimeZone">>,<<"W-SU">>}],
       [],[],[],[],[],undefined,73,undefined,undefined,undefined,undefined,
       undefined,undefined,undefined,undefined})
% 67 ("C") - and right after rows descriptions there is CommandComplete
(<0.191.0>) call epgsql_sock:on_message({67,<<68,69,76,69,84,69,32,48,0>>},{state,gen_tcp,#Port<0.37136>,
       <<90,0,0,0,5,73>>,
       {25093,2074650982},
       on_message,
       {codec,[],[]},
       {[{{call,{<0.188.0>,#Ref<0.0.2.1271>}},
          {squery,"DELETE FROM t RETURNING c"}}],
        []},
       undefined,
       [{<<"application_name">>,<<>>},
        {<<"client_encoding">>,<<"UTF8">>},
        {<<"DateStyle">>,<<"ISO, DMY">>},
        {<<"integer_datetimes">>,<<"on">>},
        {<<"IntervalStyle">>,<<"postgres">>},
        {<<"is_superuser">>,<<"on">>},
        {<<"server_encoding">>,<<"UTF8">>},
        {<<"server_version">>,<<"9.1.13">>},
        {<<"session_authorization">>,<<"postgres">>},
        {<<"standard_conforming_strings">>,<<"on">>},
        {<<"TimeZone">>,<<"W-SU">>}],
       [],
       [{column,<<"c">>,int4,4,-1,0}],
       [],[],[],undefined,73,undefined,undefined,undefined,undefined,
       undefined,undefined,undefined,undefined})
(<0.191.0>) call epgsql_sock:on_message({90,<<"I">>},{state,gen_tcp,#Port<0.37136>,<<>>,
       {25093,2074650982},
       on_message,
       {codec,[],[]},
       {[{{call,{<0.188.0>,#Ref<0.0.2.1271>}},
          {squery,"DELETE FROM t RETURNING c"}}],
        []},
       undefined,
       [{<<"application_name">>,<<>>},
        {<<"client_encoding">>,<<"UTF8">>},
        {<<"DateStyle">>,<<"ISO, DMY">>},
        {<<"integer_datetimes">>,<<"on">>},
        {<<"IntervalStyle">>,<<"postgres">>},
        {<<"is_superuser">>,<<"on">>},
        {<<"server_encoding">>,<<"UTF8">>},
        {<<"server_version">>,<<"9.1.13">>},
        {<<"session_authorization">>,<<"postgres">>},
        {<<"standard_conforming_strings">>,<<"on">>},
        {<<"TimeZone">>,<<"W-SU">>}],
       [],[],[],
       [{ok,0}],
       [],undefined,73,
       {delete,0},
       undefined,undefined,undefined,undefined,undefined,undefined,undefined})
(<0.191.0>) <0.188.0> ! {#Ref<0.0.2.1271>,{ok,0}}

% ================  Sample SELECT, returning 0 rows =====================
21> epgsql:squery(C, "SELECT * FROM t"). 
(<0.191.0>) << {'$gen_call',{<0.188.0>,#Ref<0.0.2.1291>},
                            {squery,"SELECT * FROM t"}}
(<0.191.0>) << {inet_reply,#Port<0.37136>,ok}
(<0.191.0>) << {tcp,#Port<0.37136>,
                    <<84,0,0,0,26,0,1,99,0,0,1,77,145,0,1,0,0,0,23,0,4,255,
                      255,255,255,0,0,67,0,0,0,13,83,69,76,69,67,84,32,48,0,
                      90,0,0,0,5,73>>}
{ok,[{column,<<"c">>,int4,4,-1,0}],[]}
% RowDescription
22> (<0.191.0>) call epgsql_sock:on_message({84,<<0,1,99,0,0,1,77,145,0,1,0,0,0,23,0,4,255,255,255,255,0,0>>},{state,gen_tcp,#Port<0.37136>,
       <<67,0,0,0,13,83,69,76,69,67,84,32,48,0,90,0,0,0,5,73>>,
       {25093,2074650982},
       on_message,
       {codec,[],[]},
       {[{{call,{<0.188.0>,#Ref<0.0.2.1291>}},{squery,"SELECT * FROM t"}}],[]},
       undefined,
       [{<<"application_name">>,<<>>},
        {<<"client_encoding">>,<<"UTF8">>},
        {<<"DateStyle">>,<<"ISO, DMY">>},
        {<<"integer_datetimes">>,<<"on">>},
        {<<"IntervalStyle">>,<<"postgres">>},
        {<<"is_superuser">>,<<"on">>},
        {<<"server_encoding">>,<<"UTF8">>},
        {<<"server_version">>,<<"9.1.13">>},
        {<<"session_authorization">>,<<"postgres">>},
        {<<"standard_conforming_strings">>,<<"on">>},
        {<<"TimeZone">>,<<"W-SU">>}],
       [],[],[],[],[],undefined,73,undefined,undefined,undefined,undefined,
       undefined,undefined,undefined,undefined})
% CommandComplete
(<0.191.0>) call epgsql_sock:on_message({67,<<83,69,76,69,67,84,32,48,0>>},{state,gen_tcp,#Port<0.37136>,
       <<90,0,0,0,5,73>>,
       {25093,2074650982},
       on_message,
       {codec,[],[]},
       {[{{call,{<0.188.0>,#Ref<0.0.2.1291>}},{squery,"SELECT * FROM t"}}],[]},
       undefined,
       [{<<"application_name">>,<<>>},
        {<<"client_encoding">>,<<"UTF8">>},
        {<<"DateStyle">>,<<"ISO, DMY">>},
        {<<"integer_datetimes">>,<<"on">>},
        {<<"IntervalStyle">>,<<"postgres">>},
        {<<"is_superuser">>,<<"on">>},
        {<<"server_encoding">>,<<"UTF8">>},
        {<<"server_version">>,<<"9.1.13">>},
        {<<"session_authorization">>,<<"postgres">>},
        {<<"standard_conforming_strings">>,<<"on">>},
        {<<"TimeZone">>,<<"W-SU">>}],
       [],
       [{column,<<"c">>,int4,4,-1,0}],
       [],[],[],undefined,73,undefined,undefined,undefined,undefined,
       undefined,undefined,undefined,undefined})
% ReadyForQuery
(<0.191.0>) call epgsql_sock:on_message({90,<<"I">>},{state,gen_tcp,#Port<0.37136>,<<>>,
       {25093,2074650982},
       on_message,
       {codec,[],[]},
       {[{{call,{<0.188.0>,#Ref<0.0.2.1291>}},{squery,"SELECT * FROM t"}}],[]},
       undefined,
       [{<<"application_name">>,<<>>},
        {<<"client_encoding">>,<<"UTF8">>},
        {<<"DateStyle">>,<<"ISO, DMY">>},
        {<<"integer_datetimes">>,<<"on">>},
        {<<"IntervalStyle">>,<<"postgres">>},
        {<<"is_superuser">>,<<"on">>},
        {<<"server_encoding">>,<<"UTF8">>},
        {<<"server_version">>,<<"9.1.13">>},
        {<<"session_authorization">>,<<"postgres">>},
        {<<"standard_conforming_strings">>,<<"on">>},
        {<<"TimeZone">>,<<"W-SU">>}],
       [],[],[],
       [{ok,[{column,<<"c">>,int4,4,-1,0}],[]}],
       [],undefined,73,select,undefined,undefined,undefined,undefined,
       undefined,undefined,undefined})
(<0.191.0>) <0.188.0> ! {#Ref<0.0.2.1291>,
                         {ok,[{column,<<"c">>,int4,4,-1,0}],[]}}

So, yep, from the point of view of postgres protocol DELETE, returning 0 rows is the same as SELECT returning 0 rows. Why epgsql returns different responses for them, it's a question. And yep, looks like a bug finaly =)
Thanks!

@seriyps
Copy link
Member

seriyps commented Nov 28, 2016

Ah, and DELETE without RETURNING:

22> epgsql:squery(C, "DELETE FROM t").            
(<0.191.0>) << {'$gen_call',{<0.188.0>,#Ref<0.0.2.1296>},
                            {squery,"DELETE FROM t"}}
(<0.191.0>) << {inet_reply,#Port<0.37136>,ok}
(<0.191.0>) << {tcp,#Port<0.37136>,
                    <<67,0,0,0,13,68,69,76,69,84,69,32,48,0,90,0,0,0,5,73>>}
{ok,0}
% CommandComplete
23> (<0.191.0>) call epgsql_sock:on_message({67,<<68,69,76,69,84,69,32,48,0>>},{state,gen_tcp,#Port<0.37136>,
       <<90,0,0,0,5,73>>,
       {25093,2074650982},
       on_message,
       {codec,[],[]},
       {[{{call,{<0.188.0>,#Ref<0.0.2.1296>}},{squery,"DELETE FROM t"}}],[]},
       undefined,
       [{<<"application_name">>,<<>>},
        {<<"client_encoding">>,<<"UTF8">>},
        {<<"DateStyle">>,<<"ISO, DMY">>},
        {<<"integer_datetimes">>,<<"on">>},
        {<<"IntervalStyle">>,<<"postgres">>},
        {<<"is_superuser">>,<<"on">>},
        {<<"server_encoding">>,<<"UTF8">>},
        {<<"server_version">>,<<"9.1.13">>},
        {<<"session_authorization">>,<<"postgres">>},
        {<<"standard_conforming_strings">>,<<"on">>},
        {<<"TimeZone">>,<<"W-SU">>}],
       [],[],[],[],[],undefined,73,undefined,undefined,undefined,undefined,
       undefined,undefined,undefined,undefined})
% ReadyForQuery
(<0.191.0>) call epgsql_sock:on_message({90,<<"I">>},{state,gen_tcp,#Port<0.37136>,<<>>,
       {25093,2074650982},
       on_message,
       {codec,[],[]},
       {[{{call,{<0.188.0>,#Ref<0.0.2.1296>}},{squery,"DELETE FROM t"}}],[]},
       undefined,
       [{<<"application_name">>,<<>>},
        {<<"client_encoding">>,<<"UTF8">>},
        {<<"DateStyle">>,<<"ISO, DMY">>},
        {<<"integer_datetimes">>,<<"on">>},
        {<<"IntervalStyle">>,<<"postgres">>},
        {<<"is_superuser">>,<<"on">>},
        {<<"server_encoding">>,<<"UTF8">>},
        {<<"server_version">>,<<"9.1.13">>},
        {<<"session_authorization">>,<<"postgres">>},
        {<<"standard_conforming_strings">>,<<"on">>},
        {<<"TimeZone">>,<<"W-SU">>}],
       [],[],[],
       [{ok,0}],
       [],undefined,73,
       {delete,0},
       undefined,undefined,undefined,undefined,undefined,undefined,undefined})
(<0.191.0>) <0.188.0> ! {#Ref<0.0.2.1296>,{ok,0}}

In this case there is no RowDescription packet

@seriyps
Copy link
Member

seriyps commented Nov 28, 2016

So, the problem is here:
https://github.com/epgsql/epgsql/blob/2e699f2a00702d5983c2a987135cb73353821767/src/epgsql_sock.erl##L746-L751

DELETE with no rows returned falls into 1st selected clause, while SELECT with no rows into last one.
I guess case should match not by {Command, Complete, Rows} but by {Command, Complete, State#state.columns} or by {Command, Complete, get_columns(State)}.

@seriyps seriyps added the bug label Nov 28, 2016
@aherranz
Copy link
Contributor Author

Nice!

Thank you very much.

seriyps added a commit to seriyps/epgsql that referenced this issue Nov 30, 2016
@seriyps
Fix UPDATE/DELETE ... RETURNING .. returning 0 rows. Fixes epgsql#86
cfd3994 
* Make such a response to be always a 4-tuple {ok, Count, Columns, Rows}
@seriyps seriyps mentioned this issue Nov 30, 2016
Merged
@seriyps
Copy link
Member

seriyps commented Dec 20, 2016

Fixed in #115

@seriyps seriyps closed this as completed Dec 20, 2016
egobrain pushed a commit to egobrain/epgsql that referenced this issue Jun 21, 2017
@seriyps
Fix UPDATE/DELETE ... RETURNING .. returning 0 rows. Fixes epgsql#86
9f16e56 
* Make such a response to be always a 4-tuple {ok, Count, Columns, Rows}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
bug
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@aherranz @seriyps