Re: PostgreSQL JDBC bug with XA recovery?

On 28.02.2013 16:54, Andrew Frolov wrote:
> I've encountered a problem with xa transaction recovery.
>
> PGXAConnection.recover() functions returns all pending XA transactions.
> But the problem is, that in general case we cannot recover this
> transactions in current connection, PostgreSQL require us to connect to
> target database before recovering. This behavior breaks the expectations
> of transaction managers. We literally can't perform recovery if we have
> a lot of pending xa transactions in different databases in PostgreSQL
> cluster.
>
> Maybe it would be good to replace
> SELECT gid FROM pg_prepared_xacts
> by
> SELECT gid FROM pg_prepared_xacts,where owner = current_user ?
>
> I found an old thread with same question, but without an answer.
> http://www.postgresql.org/message-id/CAPSK6ngYLaRAy_FGGQqknTPE9FUDCRn32UShxuncWEyD+swD=w@mail.gmail.com

There's some confusion here. Both you and the old thread talks about
transactions in multiple databases being the problem, but the error
message, and your suggestion above, suggest that the problem is actually
about permissions. We probably should do:

SELECT gid FROM pg_prepared_xacts where database = current_database()

That should help with the problem with multiple databases. But do you
also have a problem with multiple PostgreSQL users being involved?

- Heikki