cannot start commands during a parallel operation

[Justin Pryzby]

On Sat, Aug 10, 2024 at 03:20:49PM -0400, Robert Haas wrote:
> This appears to be correct behavior to me. The function is labeled parallel safe but isn’t. If each workers were allowed to separately execute that function their notions of the current command counter would diverge. That’s not OK. In an example this simple you might not need able to observe the resulting chaos but you wouldn’t have to make it much more complicated to see terrible things happening, I believe.

Hm, maybe I'm being dense, but I can't see what it's doing that would
make it unsafe.

https://www.postgresql.org/docs/16/parallel-safety.html
| Functions and aggregates must be marked PARALLEL UNSAFE if they write to
| the database, access sequences, change the transaction state even
| temporarily (e.g., a PL/pgSQL function that establishes an EXCEPTION
| block to catch errors), or make persistent changes to settings.
| Similarly, functions must be marked PARALLEL RESTRICTED if they access
| temporary tables, client connection state, cursors, prepared statements,
| or miscellaneous backend-local state that the system cannot synchronize
| across workers. For example, setseed and random are parallel restricted
| for this last reason.

Maybe you read "SELECT INTO" as if it were a SQL's "into" rather than
pl/pgsql's "into" ?

FYI, one reason why I (re)phrased this as postgres bug is that it's
returning an XX00 error, which isn't supposed to be reachable.
I see now that that was changed for v17 in 0075d78947e...

> > On Aug 10, 2024, at 1:45 PM, Justin Pryzby <pryzby at telsasoft.com> wrote:
> > 
> > On Fri, Aug 09, 2024 at 02:09:44PM -0700, Paul Ramsey wrote:
> >> Crazy… it’s just a short little C function. Why wouldn’t this happen with every/any extension function?
> >> 
> >> https://github.com/postgis/postgis/blob/master/postgis/lwgeom_ogc.c#L609-L623
> > 
> > It turns out this isn't limited to postgis, nor extensions, nor even
> > functions called by plpgsql.  I think it's an issue with the parallel
> > safety exceptions for CREATE AS + SELECT INTO.  This fails back to pg11,
> > probably due to pg commit e9baa5e9f (Allow DML commands that create
> > tables to use parallel query.).  I'll report it on their development
> > list at a later date.
> > 
> > CREATE FUNCTION fn(i int) RETURNS int PARALLEL SAFE LANGUAGE plpgsql AS $$ DECLARE res int; BEGIN SELECT a AS a INTO res FROM tab WHERE a=i; RETURN res; END $$;
> > CREATE TABLE tab AS SELECT generate_series(1,99)a;
> > SET parallel_tuple_cost=0; SET parallel_setup_cost=0; SET min_parallel_table_scan_size=0;
> > CREATE TABLE new AS SELECT fn(a) FROM tab;
> > 
> > \errverbose
> > ERROR:  XX000: cannot start commands during a parallel operation
> > CONTEXT:  SQL statement "SELECT a AS a          FROM tab WHERE a=i"
> > PL/pgSQL function fn(integer) line 1 at SQL statement
> > LOCATION:  CommandCounterIncrement, xact.c:1094

-- 
Justin