Re: Avoiding data loss with synchronous replication

On 7/24/21, 3:54 AM, "Andrey Borodin" <x4mmm@yandex-team.ru> wrote:
> Let's clearly enumerate problems of blocking.
> It's been mentioned that backend is not responsive when cancelation is blocked. But on the contrary, it's very
responsive.

It is responsive in the sense that it emits a WARNING to the client
whose backend received the request.  However, it is still not
responsive in other aspects.  The backend won't take the requested
action, and if the action was requested via
pg_cancel/terminate_backend(), no useful feedback is provided to the
user to explain why it is blocked.

> Semantics of cancelation assumes correct query interruption. This is not possible already when we committed locally.
Therecannot be any correct cancelation. And I don't think it worth to add incorrect cancelation.
 

The latest version of the block-cancels patch that I've seen still
allows you to cancel things if you really want to.  For example, you
can completely turn off synchronous replication by unsetting
synchronous_standby_names.  Alternatively, you could store the value
of the new block-cancels parameter in shared memory and simply turn
that off to allow cancellations to proceed.  In either case, a user is
forced to change the settings for the whole server.  I think allowing
users to target a specific synchronous replication wait is useful.
Even if I want to block canceling waits for most queries, perhaps I am
okay with unblocking an administrative session that is stuck trying to
update a password (although that could also be achieved by remembering
to set synchronous_commit = local).

What do you think about allowing multiple sets of behavior with the
new parameter?  The "always allow" value would make things work just
like they do today.  The "when specifically requested" value would
allow users to use a new mechanism (perhaps new signals) to
intentionally cancel synchronous replication waits.  And the "always
block" value would disallow blocking such waits without altering the
server-wide settings.

Nathan