PostgreSQL JDBC bug with XA recovery

[rvsoni]

Sorry, my fault. I've just copy-pasted SQL from previous topic. We have a
problem with multiple databases, it has nothing to do with users and
privileges.

What do we want, is
SELECT gid FROM pg_prepared_xacts where database = current_database()
in PGXAConnection.recover() function.

As I understand, you agree that it will fix the problem with multiple
databases. Could you please make a fix in official code shortly?

Thanks!

28.02.2013 18:23, Heikki Linnakangas пишет:

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

[ringerc]

On 07/18/2014 05:26 PM, Ravi Soni wrote:

Sorry, my fault. I've just copy-pasted SQL from previous topic. We have a
problem with multiple databases, it has nothing to do with users and
privileges.

What do we want, is
SELECT gid FROM pg_prepared_xacts where database = current_database()
in PGXAConnection.recover() function.

As I understand, you agree that it will fix the problem with multiple
databases. Could you please make a fix in official code shortly?

It'd also be a good idea to return only transactions for the current
user unless the current user is superuser.

Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

[rvsoni]

To return pending transaction, its need only database, as any valid user connected will be owner or super user, i dont see there are any case of using multiple user with same database. so this query will work nice to rollback transaction for owner or super user of postgres database.

[davecramer]

From what I can see this was resolved in PR #174