Re: Detecting uncommitted changes

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