Re: [COMMITTERS] pgsql: Mark pg_start_backup and pg_stop_backup as parallel-restricted.

David Steele <david(at)pgmasters(dot)net> writes:
> ... So apparently it is possible. To get them working as soon as possible I
> recommended that they run:
> alter role postgres set max_parallel_workers_per_gather = 0;
> And that solved their problem. 9.6 is getting on in years so I'm not
> sure how much time/effort we want to spend on this, but I figured it was
> worth mentioning.

> I did another round of trying to reproduce the issue but came up short a
> second time.

I was able to force it like this:

regression=# select pg_start_backup('foo', 'f', 'f');
pg_start_backup
-----------------
0/18000028
(1 row)

regression=# set force_parallel_mode TO 'on';
SET
regression=# set max_parallel_workers_per_gather = 4;
SET
regression=# explain select * from pg_catalog.pg_stop_backup(false);
QUERY PLAN
--------------------------------------------------------------------------
Gather (cost=1000.00..1000.11 rows=1 width=72)
Workers Planned: 1
Single Copy: true
-> Function Scan on pg_stop_backup (cost=0.00..0.01 rows=1 width=72)
(4 rows)

regression=# select * from pg_catalog.pg_stop_backup(false);
ERROR: non-exclusive backup is not in progress
HINT: Did you mean to use pg_stop_backup('t')?
CONTEXT: parallel worker

It doesn't seem terribly likely that anybody would be using
force_parallel_mode = on in production, but perhaps there's some
reasonable combination of the other parallelization planning GUCs
that can make this plan look attractive.

I'm kind of inclined not to bother with a code fix at this late date,
given that we've had so few trouble reports. The right answer is
to tell anyone who's affected to fix their catalogs manually, viz

update pg_proc set proparallel = 'r' where proname = 'pg_start_backup';
update pg_proc set proparallel = 'r' where proname = 'pg_stop_backup';

If we had back-patched 9fe3c644a (sans catversion bump of course)
at the time, then initdb's done with 9.6.3 or later would have gotten
this right. In hindsight, not doing that was clearly wrong. But it
seems a bit late to do it now.

regards, tom lane