Re: [patch] demote

On Thu, 18 Jun 2020 11:15:02 -0400
Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> On Thu, Jun 18, 2020 at 6:02 AM Jehan-Guillaume de Rorthais
> <jgdr(at)dalibo(dot)com> wrote:
> > At some expense, Admin can already set the system as readonly from the
> > application point of view, using:
> >
> > alter system set default_transaction_read_only TO on;
> > select pg_reload_conf();
> >
> > Current RW xact will finish, but no other will be allowed.
>
> That doesn't block all WAL generation, though:
>
> rhaas=# alter system set default_transaction_read_only TO on;
> ALTER SYSTEM
> rhaas=# select pg_reload_conf();
> pg_reload_conf
> ----------------
> t
> (1 row)
> rhaas=# cluster pgbench_accounts_pkey on pgbench_accounts;
> rhaas=#

Yes, this, and the fact that any user can switch transaction_read_only back to
on easily. This was a terrible example.

My point was that ALTER SYSTEM READ ONLY as described here doesn't feel like a
required user feature, outside of the demote scope. It might be useful for the
demote process, but only from the core point of view, without user interaction.
It seems there's no other purpose from the admin standpoint.

> There's a bunch of other things it also doesn't block, too. If you're
> trying to switch to a new primary, you really want to stop WAL
> generation completely on the old one. Otherwise, you can't guarantee
> that the machine you're going to promote is completely caught up,
> which means you might lose some changes, and you might have to
> pg_rewind the old master.

Yes, of course. I wasn't explaining transaction_read_only was useful in a
switchover procedure, sorry for the confusion and misleading comment.

Regards,