Re: [PATCH] Add prepared_orphaned_transaction_timeout GUC

>> Silent rollback in any scenario I can imagine is a disaster.
> ...
> The same analogy is being applied with orphaned prepared transactions, which may/may not complete ever , but going to hinder autovacuum, increasing bloat. When user sets the value, user will be aware that the prepared transactions can disappear automatically.

I agree with Andrey. And I think we cannot use the same analogy of
"long running transactions" to prepared transactions. Prepared
transactions are part of 2PC protocol. And this GUC would break 2PC
protocol, which is after all distributed participants successfully
executed PREPARE you can successfully execute COMMIT, if a database is
healthy.

Another point is that rollback is not always a proper action on a
dangling prepared transaction. If you move data from Instance1 to
Instance2 and a prepared transaction on Instance1 was committed, but
you have a dangling prepared transaction on Instance2 for some reason,
then you want to COMMIT it on Instance2, not to roll back. And vice
versa, if the prepared transaction on Instance1 was rolled back, then
you want to roll it back on Instance2. What to do with the prepared
transaction should be decided by a transaction manager.

From the documentation [1]:

> PREPARE TRANSACTION is not intended for use in applications or interactive sessions. Its purpose is to allow an external transaction manager to perform atomic global transactions across multiple databases or other transactional resources.

It seems we don't have ways to enforce this rule, and a user doesn't
need to have advanced privileges to run PREPARE TRANSACTION. Correct
me if I'm wrong. What if instead of adding a GUC we would add a new
role, only this role (and superuser) will be able to run PREPARE
TRANSACTION.

1 - https://www.postgresql.org/docs/current/sql-prepare-transaction.html

--
Kind regards,
Artur