How to know whether a commit or rollback happened

[manuel...@gmail.com]

Hi everyone,

I'd like to know if there is a way to differentiate if a commit ends up being a commit or a rollback. Let me explain it with an example:

> squery(C, "BEGIN;");

{ok, [], []}

> squery(C, "SELECT * FROM table_undefined;");

{error, _} %% Error because table_undefined does not exist

> squery(C, "COMMIT;");

{ok, [], []} %% This is actually a rollback

The commit executed at the end of the example will end up executing a rollback. However, it is impossible for me to handle that situation.

Is there anyway to know that a rollback occurred? 

Cheers

Manuel Cherep

[Sergey Prokhorov]

Hm.. That's interesting.

I found that transaction status is stored in `txstatus` state field but never used.

FYI, description of this ReadyForQuery from https://www.postgresql.org/docs/current/static/protocol-message-formats.html

ReadyForQuery (B)
Byte1('Z')
Identifies the message type. ReadyForQuery is sent whenever the backend is ready for a new query cycle.
Int32(5)
Length of message contents in bytes, including self.
Byte1
Current backend transaction status indicator. Possible values are 'I' if idle (not in a transaction block); 'T' if in a transaction block; or 'E' if in a failed transaction block (queries will be rejected until block is ended).

The problem is that I'm not sure how this parameter could be exposed in the API, because, afaik, BEGIN/COMMIT/ROLLBACK commands are handled transparently by driver, so, we can't just check each query by pattern matching like "is it COMMIT? then check txstatus".

Probably we should check this field each time before we return query results (but this probably won't work for asynchronous version).

Can you, please, file a bug in GitHub https://github.com/epgsql/epgsql/issues?

понедельник, 21 ноября 2016 г., 13:19:29 UTC+3 пользователь manuel...@gmail.com написал:

[David Welton]

Hi,

Might be worth a look to see how it's being handled by the 1) Elixir
driver and 2) some other popular drivers like Ruby, Java, whatever.

--
David N. Welton

http://www.welton.it/davidw/

http://www.dedasys.com/

[Sergey Prokhorov]

Python's psycopg2

In [21]: con = psycopg2.connect(database="postgres", user="postgres", host="localhost")

In [22]: c = con.cursor()

In [23]: c.execute("BEGIN")

In [24]: c.execute("SELECT 1/0")

---------------------------------------------------------------------------

DataError                                 Traceback (most recent call last)

<ipython-input-24-1b5c505f414d> in <module>()

----> 1 c.execute("SELECT 1/0")

DataError: division by zero

In [25]: c.execute("SELECT 1")

---------------------------------------------------------------------------

InternalError                             Traceback (most recent call last)

<ipython-input-25-261af617f27d> in <module>()

----> 1 c.execute("SELECT 1")

InternalError: current transaction is aborted, commands ignored until end of transaction block

In [26]: c.statusmessage

Out[26]: ''

In [27]: c.execute("COMMIT")

In [28]: c.statusmessage

Out[28]: 'ROLLBACK'

In [29]: c.execute("SELECT 1")

In [30]: c.fetchall()

Out[30]: [(1,)]

Where this 'statusmessage' comes from - I don't know.

http://initd.org/psycopg/docs/cursor.html#cursor.statusmessage

Probably I should dig into psycopg sources?

But, as you can see, executing "commit" doesn't produce any exceptions.

вторник, 22 ноября 2016 г., 2:06:20 UTC+3 пользователь David Welton написал:

[Sergey Prokhorov]

Ok, this 'statusmessage' is return value of LibPQ  https://www.postgresql.org/docs/current/static/libpq-exec.html#LIBPQ-PQCMDSTATUS

This function implementation: https://github.com/postgres/postgres/blob/886f6c5ccdb500eeeec7e0abdf1500e20a304c45/src/interfaces/libpq/fe-exec.c#L2944

This field is filled here: https://github.com/postgres/postgres/blob/55c3391d1e6a201b5b891781d21fe682a8c64fe6/src/interfaces/libpq/fe-protocol3.c#L214

As you can see, last chunk of code is decoding of 'C' packet, which is 'COMMAND_COMPLETE' packet. Description:

https://www.postgresql.org/docs/current/static/protocol-message-formats.html

CommandComplete (B)
Byte1('C')
Identifies the message as a command-completed response.
Int32

Length of message contents in bytes, including self.

String
The command tag. This is usually a single word that identifies which SQL command was completed.
For an INSERT command, the tag is INSERT oid rows, where rows is the number of rows inserted. oid is the object ID of the inserted row if rows is 1 and the target table has OIDs; otherwise oid is 0.
For a DELETE command, the tag is DELETE rows where rows is the number of rows deleted.
For an UPDATE command, the tag is UPDATE rows where rows is the number of rows updated.
For a SELECT or CREATE TABLE AS command, the tag is SELECT rows where rows is the number of rows retrieved.
For a MOVE command, the tag is MOVE rows where rows is the number of rows the cursor's position has been changed by.
For a FETCH command, the tag is FETCH rows where rows is the number of rows that have been retrieved from the cursor.
For a COPY command, the tag is COPY rows where rows is the number of rows copied. (Note: the row count appears only in PostgreSQL 8.2 and later.)

And corresponding epgsql source

https://github.com/epgsql/epgsql/blob/3.2.0/src/epgsql_sock.erl#L649

It uses epgsql_wire:decode_complete(Bin), which can return (anong others) 'rollback' https://github.com/epgsql/epgsql/blob/3.2.0/src/epgsql_wire.erl#L136

but it's just ignored in 'case' block below.

So, we probably should just add special case clause for 'rollback' result.

Anyway, issue on github would be great.

And I'm not sure if this change is not breaking...

вторник, 22 ноября 2016 г., 2:29:12 UTC+3 пользователь Sergey Prokhorov написал:

[Sergey Prokhorov]

Or we can keep this 'complete' message text value in our '#state{}' record and export it via some getter API.

вторник, 22 ноября 2016 г., 3:09:33 UTC+3 пользователь Sergey Prokhorov написал:

[Sergey Prokhorov]

Found interesting workaround for your case. `epgsqli` is the only API interface that sends back full status replies, so, you can use it to send your "COMMIT" command and ensure it was committed successfully:

1> {ok, C} = epgsql:connect(#{host => "localhost", username => "postgres", password => "postgres", port => 5433}).
{ok,<0.97.0>}
2> epgsql:equery(C, "BEGIN").
{ok,[],[]}
3> epgsql:squery(C, "SELECT 1/0").
{error,{error,error,<<"22012">>,division_by_zero,
              <<"division by zero">>,[]}}
4> epgsqli:squery(C, "COMMIT"). %# it is "epgsqli", not "epgsql"
#Ref<0.0.1.632>
5> flush().
Shell got {<0.97.0>,#Ref<0.0.1.632>,{complete,rollback}}
Shell got {<0.97.0>,#Ref<0.0.1.632>,done}

So, you may want to make some wrapper function like (of course, in real code you should handle timings and other types of async messages):

sure_commit(C)->
   Ref = epgsqli:squery(C,"COMMIT"),
   CompleteCmd = receive {C, Ref, {complete, CompleteCommand}} -> CompleteCommand end,
   receive {C, Ref, done} ->ok end,
   case CompleteComand of
       commit -> ok;
       rollback -> {error, rollback}
   end.

понедельник, 21 ноября 2016 г., 13:19:29 UTC+3 пользователь manuel...@gmail.com написал:

Hi everyone,