Re: Mark a transaction uncommittable

On Sat, Apr 22, 2023 at 8:01 AM Gurjeet Singh <gurjeet@singh.im> wrote:
>
> This is a proposal for a new transaction characteristic. I haven't
> written any code, yet, and am interested in hearing if others may find
> this feature useful.

Please see attached the patch that introduces this new feature. The
patch includes all the code changes that I could foresee; that is, it
includes server changes as well as changes for psql's auto-completion.
The patch does not include doc changes, or any tests. Please see a
sample session at the end of the email, though.

The patch introduces a new keyword, COMMITTABLE, and uses that to
introduce the new transaction attribute via BEGIN [TRANSACTION] and
SET TRANSACTION commands. The new code follows the semantics of the
[NOT] DEFERRABLE attribute of a transaction almost exactly.

> Many a times we start a transaction that we never intend to commit;
> for example, for testing, or for EXPLAIN ANALYZE, or after detecting
> unexpected results but still interested in executing more commands
> without risking commit,  etc.
>
> A user would like to declare their intent to eventually abort the
> transaction as soon as possible, so that the transaction does not
> accidentally get committed.
>
> This feature would allow the user to mark a transaction such that it
> can never be committed. We must allow such marker to be placed when
> the transaction is being started, or while it's in progress.

The user can mark the transaction as uncommittable either when
starting the transaction, or while it is still in progress.

> Once marked uncommittable, do not allow the marker to be removed.
> Hence, once deemed uncommittable, the transaction cannot be committed,
> even intentionally. This protects against cases where one script
> includes another (e.g. psql's \i command), and the included script may
> have statements that turn this marker back on.

Although the patch implements this desired behavior from the initial
proposal, I'm no longer convinced of the need to prevent user from
re-enabling committability of the transaction.

> Any command that ends a transaction (END, COMMIT, ROLLBACK) must
> result in a rollback.
>
> All of these properties seem useful for savepoints, too. But I want to
> focus on just the top-level transactions, first.

The patch does not change any behaviour related to savepoints. Having
made it work for the top-level transaction, and having seen the
savepoint/subtransaction code as I came across it as I developed this
patch, I feel that it will be very tricky to implement this behavior
safely for savepoints. Moreover, having thought more about the
possible use cases, I don't think implementing uncommittability of
savepoints will be of much use in the real world.

> I feel like the BEGIN and SET TRANSACTION commands would be the right
> places to introduce this feature.
>
> BEGIN [ work | transaction ] [ [ NOT ] COMMITTABLE ];
> SET TRANSACTION [ [ NOT ] COMMITTABLE ];

I tried to avoid adding a new keyword (COMMITTABLE) to the grammar,
but could not think of a better alternative. E.g. DISALLOW COMMIT
sounds like a good alternative, but DISALLOW is currently not a
keyword, so this form doesn't buy us anything.

> I'm not yet sure if the COMMIT AND CHAIN command should carry this
> characteristic to the next transaction.

After a little consideration, in the spirit of POLA, I have not done
anything special to change the default behaviour of COMMIT/ROLLBACK
AND CHAIN.

Any feedback is welcome. Please see below an example session
demonstrating this feature.

postgres=# begin transaction committable;
BEGIN

postgres=# commit;
COMMIT

postgres=# begin transaction not committable;
BEGIN

postgres=# commit;
WARNING:  transaction is not committable
ROLLBACK

postgres=# begin transaction not committable;
BEGIN

postgres=# set transaction_committable = true;
-- for clarity, we may want to emit additional "WARNING:  cannot make
transaction committable", although the patch currently doesn't do so.
ERROR:  invalid value for parameter "transaction_committable": 1

postgres=# commit;
ROLLBACK

postgres=# begin transaction not committable;
BEGIN

postgres=# set transaction committable ;
-- for clarity, we may want to emit additional "WARNING:  cannot make
transaction committable", although the patch currently doesn't do so.
ERROR:  invalid value for parameter "transaction_committable": 1

postgres=# set transaction committable ;
ERROR:  current transaction is aborted, commands ignored until end of
transaction block

postgres=# commit;
ROLLBACK

Best regards,
Gurjeet
http://Gurje.et