Detecting uncommitted changes

In response to Thomas Kellerer <spam_eater@gmx.net>:

Hi,

is there a way I can detect if the current session has any uncommitted changes?

I'm not trying to find uncommitted changes from other sessions (connections) only for the *current* one.

I thought there was a discussion on the mailing list that involved the txid_XXX functions, but I couldn't find that anymore.

Background:
I'm trying to implement a "Do you want to commit your changes?" question in a SQL tool, when the user closes the application.

If I'm understanding you correctly, you could just make it check the
transaction status. If there's an active transaction, then there are
"uncommitted changes".

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

Bill Moran wrote on 27.12.2011 19:37:

is there a way I can detect if the current session has any uncommitted changes?

I'm not trying to find uncommitted changes from other sessions (connections) only for the *current* one.

I thought there was a discussion on the mailing list that involved the txid_XXX functions, but I couldn't find that anymore.

Background:
I'm trying to implement a "Do you want to commit your changes?" question in a SQL tool, when the user closes the application.

If I'm understanding you correctly, you could just make it check the
transaction status. If there's an active transaction, then there are
"uncommitted changes".

Sounds like what I want, but how do I check the "transaction status" (I'm using JDBC)

Regards
Thomas

Thomas Kellerer <spam_eater@gmx.net> writes:

Bill Moran wrote on 27.12.2011 19:37:

is there a way I can detect if the current session has any uncommitted changes?

If I'm understanding you correctly, you could just make it check the
transaction status. If there's an active transaction, then there are
"uncommitted changes".

Sounds like what I want, but how do I check the "transaction status" (I'm using JDBC)

More specifically, look to see if the current transaction has assigned
itself a transaction ID. I think the easiest place to see this is in
pg_locks --- it will be holding exclusive lock on a TransactionId object
if so.

There are various cases where you could get a false positive from this
type of test, eg if a subtransaction made some changes and then rolled
back, you'll have an XID even though there's not really anything to
commit. But it will never give a false negative.

regards, tom lane

Tom Lane wrote on 27.12.2011 20:22:

If I'm understanding you correctly, you could just make it check the
transaction status. If there's an active transaction, then there are
"uncommitted changes".

Sounds like what I want, but how do I check the "transaction status" (I'm using JDBC)

More specifically, look to see if the current transaction has assigned
itself a transaction ID. I think the easiest place to see this is in
pg_locks --- it will be holding exclusive lock on a TransactionId object
if so.

There are various cases where you could get a false positive from this
type of test, eg if a subtransaction made some changes and then rolled
back, you'll have an XID even though there's not really anything to
commit. But it will never give a false negative.

regards, tom lane

Thanks for the answer. I came up with the following statement:

select count(*)
from pg_locks
where pid = pg_backend_pid()
and locktype in ('transactionid')

does that look right to you?

If the count is > 0 then I have uncommitted changes

Regards
Thomas

Thomas Kellerer <spam_eater@gmx.net> writes:

Tom Lane wrote on 27.12.2011 20:22:

More specifically, look to see if the current transaction has assigned
itself a transaction ID. I think the easiest place to see this is in
pg_locks --- it will be holding exclusive lock on a TransactionId object
if so.

Thanks for the answer. I came up with the following statement:

select count(*)
from pg_locks
where pid = pg_backend_pid()
and locktype in ('transactionid')

does that look right to you?

No. You forgot the exclusive-lock condition.

regards, tom lane

Tom Lane wrote on 28.12.2011 01:41:

Thomas Kellerer<spam_eater@gmx.net> writes:

Tom Lane wrote on 27.12.2011 20:22:

More specifically, look to see if the current transaction has assigned
itself a transaction ID. I think the easiest place to see this is in
pg_locks --- it will be holding exclusive lock on a TransactionId object
if so.

Thanks for the answer. I came up with the following statement:

select count(*)
from pg_locks
where pid = pg_backend_pid()
and locktype in ('transactionid')

does that look right to you?

No. You forgot the exclusive-lock condition.

Ah!

So this is it?

select count(*)
from pg_locks
where pid = pg_backend_pid()
and locktype in ('transactionid')
and mode = 'ExclusiveLock'

Thanks for your help

Thomas