Thread: How should I end a transaction with possibly failed queries?

Hello.

Does it seem right to send "COMMIT" without checking whether any of
the queries I've done in the transaction failed?

Here is pseudo code illustrating this behaviour where "COMMIT" is
always sent no matter what result of do_some_queries() was:

some_transaction(Conn)
{
    query(Conn, "BEGIN"),
    try do_some_queries(Conn)
    catch
        Reason ->
            Reason
    after
        query(Conn, "COMMIT")
    end
}

Is this correct? What if there is a select from non-existing table
inside do_some_queries()? Should I detect it (e.g. by thrown an
exception) and do ROLLBACK as the following code does:

some_transaction(Conn)
{
    try query(Conn, "BEGIN"),
         do_some_queries(Conn),
         query(Conn, "COMMIT")
    catch
        Reason ->
            query(Conn, "ROLLBACK"),
            Reason
    end
}

First sample seems more elegant, but I'm not sure that it's safe. Will
there be any problems with this in the future?

Thanks.

--
Sergey Samokhin

Sergey Samokhin <prikrutil@gmail.com> writes:
> Does it seem right to send "COMMIT" without checking whether any of
> the queries I've done in the transaction failed?

If anything failed, COMMIT is interpreted as ROLLBACK anyway.

Some other DBMSes might do this differently, but that's what PG does.

            regards, tom lane

Hello!

On Thu, Sep 3, 2009 at 12:01 AM, Tom Lane<tgl@sss.pgh.pa.us>
> If anything failed, COMMIT is interpreted as ROLLBACK anyway.

Tom, thanks for your answer! This means that I will end up with the
first sample which is more elegant and concise than second one!

--
Sergey Samokhin