Introducing functions `COST` for more parallel operations?

[Thammachart]

We tried using this query below to generate 5+ million records with nanoid, but got stuck by this error cannot start commands during a parallel operation

INSERT INTO __map_table__(old,new)
SELECT id, nanoid() FROM __old_table__;

we resolve this by introducing COST lower than or equal to 50

...
    RETURNS text -- A randomly generated NanoId String
    LANGUAGE plpgsql
    VOLATILE
    PARALLEL SAFE
    COST 10 -- added
...

I'm not an expert on this, so is this a legit solution to solve this kind of problem or is there another way?
If this is good enough, I'll create PR.

[nik-sta]

Hi @Thammachart

Thanks for creating the GitHub Issue. I do appreciate it! I've started reviewing it, but to get it running on my end could you share a few more details?

• PostgreSQL version
• Any non-standard settings (e.g. max_parallel_workers_per_gather)

Anything else you think might matter would be a big help. Thanks again!

---

I'm suspecting that the PARALLEL SAFE annotation on our PL/pgSQL functions is what's triggering the error, and that we'll need to remove it. Because marking our PL/pgSQL nanoid() as PARALLEL SAFE lets workers call it; and PL/pgSQL always opens an implicit savepoint at function entry, which parallel workers aren't allowed to do. I'm digging into the details now to be 100% sure; will follow up here as soon as I've confirmed.

---

Regarding your solution: It's a hack which worked because by lowering the declared COST to 10, well below the planner's parallel_setup_cost, Postgres never launched parallel workers, so nanoid() ran only in the leader backend and never hit the implicit savepoint restriction.

[Thammachart]

@nik-sta Thank you for your swift feedback

• Postgresql 16 (from AlloyDB for PostgreSQL in Google Cloud)
• Single machine (4 vCPU, 32 GB)
• superuser privilege are restricted, and cannot be set upon any role
• Postgres settings as much as I've gathered from SHOW ...; (all of them were set by Google)
  • max_parallel_workers_per_gather: 2
  • max_worker_processes: 64
  • max_parallel_workers: 8
  • parallel_tuple_cost: 0.1
  • parallel_setup_cost: 1000

Let me know if more info is needed