Re: Parallel INSERT (INTO ... SELECT ...)

On Fri, Feb 5, 2021 at 2:58 PM Hou, Zhijie <houzj(dot)fnst(at)cn(dot)fujitsu(dot)com> wrote:
>
> Hi,
>
> I took a look into the hasModifyingCTE bugfix recently,
> and found a possible bug case without the parallel insert patch.
>
> ---------------------------------
> drop table if exists test_data1;
> create table test_data1(a int, b int) ;
> insert into test_data1 select generate_series(1,1000), generate_series(1,1000);
> set force_parallel_mode=on;
>
> CREATE TEMP TABLE bug6051 AS
> select i from generate_series(1,3) as i;
>
> SELECT * FROM bug6051;
> CREATE RULE bug6051_ins AS ON INSERT TO bug6051 DO INSTEAD select a as i from test_data1;
>
> WITH t1 AS ( DELETE FROM bug6051 RETURNING * ) INSERT INTO bug6051 SELECT * FROM t1;
>
> *******
> ***ERROR: cannot assign XIDs during a parallel operation
> *******
> ---------------------------------
>
> I debugged it and it did have modifycte in the parsetree after rewrite.
> I think if we can properly set the hasModifyingCTE, we can avoid this error by not consider parallel for this.
>

Thanks. You've identified that the bug exists for SELECT too. I've
verified that the issue is fixed by the bugfix included in the
Parallel INSERT patch.
Are you able to review my bugfix?
Since the problem exists for SELECT in the current Postgres code, I'd
like to pull that bugfix out and provide it as a separate fix.
My concern is that there may well be a better way to fix the issue -
for example, during the re-writing, rather than after the query has
been re-written.

Regards,
Greg Nancarrow
Fujitsu Australia