[Libpqxx-general] Subtransactions - is this safe?

[Chris Angelico]

I'm building an application that gets commands from the database, and
stores its results in the database. I want to do the entire job in a
single transaction, so that if the database or application dies, the
command will still be waiting there (ie roll back the 'DELETE FROM
jobs WHERE ID=123' query); but if I get any sort of error during
processing (be it SQL error, exception thrown, or something detected
at high level), I want to roll back the entire task *except* for the
deletion of the job - to prevent infinite loops processing failing
commands. The source shows that subtransaction is backed by SAVEPOINT
/ RELEASE SAVEPOINT / ROLLBACK TO SAVEPOINT, which ought to be what
I'm after.

Currently, what I'm looking at is:

work trans(conn);
trans.query("delete from jobs where blah");
try {
subtransaction subtrans(conn);
subtrans.exec("do the actual work");
if (something_is_invalid) ; //???
subtrans.exec("do more work");
} catch(){}
//if we get here, everything's fine
trans.commit();

According to the example in the docs, a subtransaction doesn't need to
be committed. But it doesn't show rolling back the subtrans on error;
is it automatically rolled back, or was that quietly ignored because
there was only one query in the subtrans?

I'm also not sure what the best way to handle the
"something_is_invalid" part. The subtransaction code is all in a
function, so I could use 'return' there; or I could throw an
exception. The docs are a bit scanty on the subject of exceptions - do
SQL errors do anything special to the subtrans? What about other
exceptions?

I'm pretty sure pqxx::subtransaction is what I'm looking for, but I'd
like to hear from someone who's used the feature first.

Thanks in advance!

Chris Angelico

[Jeroen Vermeulen]

Post by Chris Angelico
According to the example in the docs, a subtransaction doesn't need to
be committed. But it doesn't show rolling back the subtrans on error;
is it automatically rolled back, or was that quietly ignored because
there was only one query in the subtrans?

Actually, a subtransaction does need to be committed for its results to
become permanent.

Sounds like the example needs fixing. Could you point me to the example
you found this in?

Post by Chris Angelico
I'm also not sure what the best way to handle the
"something_is_invalid" part. The subtransaction code is all in a
function, so I could use 'return' there; or I could throw an
exception. The docs are a bit scanty on the subject of exceptions - do
SQL errors do anything special to the subtrans? What about other
exceptions?

Exceptions do nothing special to the subtransaction. The subtransaction
only knows that it's being destroyed at some point, and whether it has
been committed before then. (If not, it will abort itself on destruction).

This is a general pattern in C++. A class would have to play some
pretty weird games for an object to have knowledge of exceptions that
happen in the caller. I'm sure it can be done, but it's one of those
things that are just not done.

Post by Chris Angelico
I'm pretty sure pqxx::subtransaction is what I'm looking for, but I'd
like to hear from someone who's used the feature first.

Yes, I think you've got the right idea. When the subtransaction goes
out of scope, unless you succeeded and got to the commit, it will abort
itself. The surrounding regular transaction is not affected, as long as
you don't let that one go out of scope as well.


Jeroen

[Chris Angelico]

Post by Jeroen Vermeulen

Post by Chris Angelico
According to the example in the docs, a subtransaction doesn't need to
be committed. But it doesn't show rolling back the subtrans on error;
is it automatically rolled back, or was that quietly ignored because
there was only one query in the subtrans?

Actually, a subtransaction does need to be committed for its results to
become permanent.
Sounds like the example needs fixing. ?Could you point me to the example you
found this in?

http://pqxx.org/devprojects/libpqxx/doc/stable/html/Reference/a00097.html
http://pqxx.org/devprojects/libpqxx/doc/development/Reference/a00099.html

The /stable/ version has another docs bug which was extremely
confusing (using the wrong object for exec() inside the try/catch -
made it look like subtransaction is magical). I've been using
/development/ for a while now, and matching it by building libpqxx
from source straight from svn.

Post by Jeroen Vermeulen
Exceptions do nothing special to the subtransaction. ?The subtransaction
only knows that it's being destroyed at some point, and whether it has been
committed before then. ?(If not, it will abort itself on destruction).

That's perfect for me, then. I have many early-aborts from the
function that does the work (various sorts of errors), and precisely
one successful completion.

Post by Jeroen Vermeulen
Yes, I think you've got the right idea. ?When the subtransaction goes out of
scope, unless you succeeded and got to the commit, it will abort itself.
?The surrounding regular transaction is not affected, as long as you don't
let that one go out of scope as well.

Thanks. Incidentally, it should be fine to nest subtransactions,
right? They'll be properly nested, of course - no mismatching the
order.

ChrisA

[Chris Angelico]

Post by Chris Angelico

Post by Jeroen Vermeulen
Sounds like the example needs fixing. ?Could you point me to the example you
found this in?

http://pqxx.org/devprojects/libpqxx/doc/stable/html/Reference/a00097.html
http://pqxx.org/devprojects/libpqxx/doc/development/Reference/a00099.html

(which both come from subtransaction.hxx)

ChrisA

[Jeroen Vermeulen]

Post by Chris Angelico

Post by Chris Angelico
http://pqxx.org/devprojects/libpqxx/doc/stable/html/Reference/a00097.html
http://pqxx.org/devprojects/libpqxx/doc/development/Reference/a00099.html

(which both come from subtransaction.hxx)

Thanks. I fixed the example: it was indeed missing a commit.


Jeroen

[Chris Angelico]

Post by Chris Angelico
Thanks. Incidentally, it should be fine to nest subtransactions,
right? They'll be properly nested, of course - no mismatching the
order.

Hmm, turns out they're not. My CATCH_ERRORS / ENDCATCH_xyz macros work
like this:

try {subtransaction subtrans(trans); subtransaction &trans=subtrans;

and

subtrans.commit();} catch (sql_error e) { /*...*/ }

(where the /*...*/ bit depends on the form of ENDCATCH that I use -
various ways of logging the error). Attempting to nest these produces
errors about the subtransaction constructor being unable to accept
another subtransaction.

Hm. Changing my reference from subtransaction& to the parent
dbtransaction& makes it compile. Is this a safe thing to do? (The
reason for the odd construct is that I want to be able to use
trans.exec() etc regardless of whether I'm in a subtransaction or not.
I'm not sure what would happen if I used the outer transaction while
inside the subtransaction's scope.)

Chris Angelico

[Jeroen Vermeulen]

Hi Chris,

try {subtransaction subtrans(trans); subtransaction&trans=subtrans;
and
subtrans.commit();} catch (sql_error e) { /*...*/ }
(where the /*...*/ bit depends on the form of ENDCATCH that I use -
various ways of logging the error). Attempting to nest these produces
errors about the subtransaction constructor being unable to accept
another subtransaction.

Subtransactions are supposed to nest freely. You say the subtransaction
constructor won't accept another subtransaction? That shouldn't happen.
It accepts a dbtransaction, which is a common (public!) base class for
both regular transactions and subtransactions.

Are you sure it's not some other issue that the compiler is complaining
about, such as constness or it not being an rvalue?

Hm. Changing my reference from subtransaction& to the parent
dbtransaction& makes it compile. Is this a safe thing to do? (The
reason for the odd construct is that I want to be able to use
trans.exec() etc regardless of whether I'm in a subtransaction or not.
I'm not sure what would happen if I used the outer transaction while
inside the subtransaction's scope.)

I believe it would raise a usage_error, saying you're trying to execute
on the transaction when another transaction is open.


Jeroen

[Chris Angelico]

Subtransactions are supposed to nest freely. ?You say the subtransaction
constructor won't accept another subtransaction? ?That shouldn't happen. ?It
accepts a dbtransaction, which is a common (public!) base class for both
regular transactions and subtransactions.
Are you sure it's not some other issue that the compiler is complaining
about, such as constness or it not being an rvalue?

I think it's something to do with it being a subtransaction& that
caused the issue. The actual errors were spewed all over the place,
blaming lines in pqxx headers and my code indiscriminately, and all
were silenced when I made it a dbtransaction& instead. It may be
something to do with the compiler thinking it was a copy constructor
or somesuch... anyway, using the parent reference works fine.

ChrisA

[Jeroen Vermeulen]

I think it's something to do with it being a subtransaction& that
caused the issue. The actual errors were spewed all over the place,
blaming lines in pqxx headers and my code indiscriminately, and all
were silenced when I made it a dbtransaction& instead. It may be
something to do with the compiler thinking it was a copy constructor
or somesuch... anyway, using the parent reference works fine.

A-ha! The problem is with two similar constructors:

1. subtransaction(dbtransaction &) opens a nested transaction.

2. subtransaction(const subtransaction &) is a copy constructor. It's
deliberately disabled.

I'll just sneak a fix for that into the 4.0 release today. In the
meantime, you can replace...

subtransaction inner_trans(outer_trans);

...with...

dbtransaction &outer_trans_ref(outer_trans);
subtransaction inner_trans(outer_trans_ref);

Now there's no doubt about which constructor to use.


Jeroen

[Chris Angelico]

? ?subtransaction inner_trans(outer_trans);
...with...
? ?dbtransaction &outer_trans_ref(outer_trans);
? ?subtransaction inner_trans(outer_trans_ref);

Which is what I stumbled to; glad it's "sanctioned" now :)

If it were pointers, I could upcast the pointer inside the
declaration. References are weird :)

ChrisA

[Jeroen Vermeulen]

Post by Chris Angelico

Post by Jeroen Vermeulen
subtransaction inner_trans(outer_trans);
...with...
dbtransaction&outer_trans_ref(outer_trans);
subtransaction inner_trans(outer_trans_ref);

Which is what I stumbled to; glad it's "sanctioned" now :)

Ah. I misunderstood what you meant by "using the parent reference":
you're using it for the constructor then, not for your database
interaction during the nested subtransaction.

I'm going about releasing 4.0 now; it contains an extra constructor so
that you won't have this problem.


Jeroen

[Chris Angelico]

Ah. ?I misunderstood what you meant by "using the parent reference": you're
using it for the constructor then, not for your database interaction during
the nested subtransaction.

I am - I'm doing both.

I'm building my own libpqxx from source control, so releases don't
mean anything to me. Soon as I get to work tomorrow I'll upgrade to
the very latest. (We live on the edge...)

Thanks for the help!

ChrisA