Thread: Detecting uncommitted changes
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. Regards Thomas
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