Cannot rename database due to prepared transactions-CodePudding

I'm trying to rename a database using:

ALTER DATABASE xxx RENAME TO yyy

I got an error message saying there is another open session. I killed it using:

SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'xxx' AND pid <> pg_backend_pid();

However, I then got an error message saying there are 2 prepared transactions pending. I made sure to kill all processes in the background with:

SELECT pg_cancel_backend(pid) FROM pg_stat_activity WHERE pid <> pg_backend_pid();

But still, I am not able to rename the database.

I then saw there is a view by postgres called pg_prepared_xacts that shows me the prepared transactions (shortened the gid for a better overview), and indeed, there are two of them:

transaction|gid           |prepared                     |owner|database|
----------- -------------- ----------------------------- ----- -------- 
5697779    |4871251_EAAAAC|2022-08-05 15:50:59.782  0200|xxx  |xxx     |
5487701    |4871251_DAAAAA|2022-07-08 08:05:36.066  0200|xxx  |xxx     |

According to the Postgres documentation on prepared transactions, I can execute a Rollback on the transaction id - which is what I did:

ROLLBACK PREPARED '5697779';

I made sure to execute the ROLLBACK with the same user, but it shows an error message saying that the transaction does not exist...

How can I get rid of it / kill it in order to be able to rename the database?

Thank you for reading and taking time to respond.

CodePudding user response：

From here Prepared transaction:

transaction_id

An arbitrary identifier that later identifies this transaction for COMMIT PREPARED or ROLLBACK PREPARED. The identifier must be written as a string literal, and must be less than 200 bytes long. It must not be the same as the identifier used for any currently prepared transaction.

and from here Rollback prepared:

transaction_id

The transaction identifier of the transaction that is to be rolled back.

Lastly from here pg_prepared_xacts:

gid text

Global transaction identifier that was assigned to the transaction

So to rollback the transaction you need the global identifier assigned in the PREPARE TRANSACTION as shown in the gid column in pg_prepared_xacts.