Christopher Browne wrote:
> Is it possible that there might be some small value to the notion of
> running certain operations via 2PC?
Not only a small value IMHO. Doing schema changes is a major PITA
right now - and it's very easy to shoot yourself in the foot. I'd
welcome anything that improves that situation.

> For instance, EXECUTE SCRIPT has historically been somewhat risky, if
> not accompanied by fairly extreme operational discipline; a thought
> could be to run it something like the following:
> 
> - Slonik request comes in for EXECUTE SCRIPT (..., with 2pc); 
> 
> - We submit the event on the origin node; if all seems good, we run
>   PREPARE TRANSACTION rather than COMMIT
> 
> - On each node, the same happens.  If all seems good, rather than
>   COMMIT, we run PREPARE transaction.
> 
> - The slonik script then awaits success or failure on all nodes.
> 
> There are two choices:
>  - If all nodes got to do a PREPARE TRANSACTION, then run COMMIT
>    PREPARED on each node.
> 
>  - If any node had a failure, then run ROLLBACK PREPARED on each node.
> 
> Only any good if all nodes are running 8.1+...
What about the following, slightly modified proposal, that would even work
on 7.4 and 8.0, and just provide some extra safety on 8.1.

1) When doing execute script, first submit a "try execute script" event.
2) All nodes do "begin; <submitted script>".
    < 8.1: If there is no error, they ack the event with "ok", and _ROLLBACK_ (!).
           If there is an error, they ack the event with "failed", and ROLLBACK.
    <= 8.1: If there is no error, they ack the event with "ok", and "PREPARE".
            If there is an error, the ack the event with "failed" and ROLLBACK.
3) If all nodes acked with "ok", then "execute script" event is generated, referencing
    the previous "try" event.
4) All < 8.1 nodes reexecute the script, but doing "commit" instead of "rollback" at the end.
    All >= 8.1 nodes just commit the previously prepared transaction.
5) If one node didn't ack with "ok", then an "abort script" event is generated. < 8.1 nodes
    just ignore that event, while >= 8.1 nodes issue a "rollback prepared".

That would give you nearly-2pc semantics on 7.4 and 8.0, and real 2pc semantics on 8.1.
Maybe the user could even choose _not_ use 2pc on 8.1, to avoid the heavy locking that 2pc brings.

greetings, Florian Pflug