[Bug]: parallel operation error during CREATE TABLE statements

[bonesmoses]

What's wrong?

This was first reported in #50, and PR #138 (pushed to main Nov 2, 2023) claimed to have fixed the problem. However, the repro case provided there still applies as recently as 11.1-12, dated May 21, 2024.

As a refresh. The repro case:

CREATE TABLE sampletable (x numeric);
INSERT INTO sampletable SELECT i FROM generate_series(1,10000000) i;

Make sure that there are sufficient background workers to cause the race condition. The settings I used:

max_worker_processes = 16
max_parallel_workers_per_gather = 8

Note that I increased the size of the sample data to 10,000,000 rows rather than 1,000,000, as the previous amount was not enough to trigger the error reliably. This is because it's far more likely to occur when there are sufficient extra parallel workers.

Then repeatedly run the following SQL in a new session:

CREATE TABLE sampletable_columnar USING columnar AS SELECT * FROM sampletable ORDER BY 1 ASC;

I did this with a simple for loop:

for x in {1..20}; do 
  psql -c "CREATE TABLE sampletable_columnar USING columnar AS SELECT * FROM sampletable ORDER BY 1 ASC;"
done

It may run successfully, but should eventually fail as before:

ERROR:  cannot start commands during a parallel operation

[wuputah]

Unfortunately there's very little at this time that we can do as extension authors to definitively address this error. #138 tried to address the issue by doing a simple detection for this type of query and disabling parallelism in that case. It's certainly possible to still run queries that will result in this error.

That said, it would appear you ran into this issue despite the fact that it appears that the code from #138 would have been triggered, which implies to me that the issue may be a different problem, and in Postgres itself. A similar issue was recently reported on the pgsql-bugs mailing list.

[bonesmoses]

That bug report to Postgres was actually me, before we learned that this was happening because the customer had set default_table_access_method = columnar. And indeed, setting it back to heap makes the problem go away.

The test case I submitted here is from a pristine Docker container loaded with 11.1-12.

[bonesmoses]

I'll note that there's a very lengthy thread from the Postgres devs when they had to add their own detection for these types of scenarios. I'm not sure how many of those hooks are available to extension authors as opposed to core, but it may help.