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

From: "Hou, Zhijie" <houzj(dot)fnst(at)cn(dot)fujitsu(dot)com>
To: Greg Nancarrow <gregn4422(at)gmail(dot)com>
Cc: Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>, Antonin Houska <ah(at)cybertec(dot)at>, Dilip Kumar <dilipbalaut(at)gmail(dot)com>, vignesh C <vignesh21(at)gmail(dot)com>, "Bharath Rupireddy" <bharath(dot)rupireddyforpostgres(at)gmail(dot)com>, Andres Freund <andres(at)anarazel(dot)de>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>, "Tsunakawa, Takayuki" <tsunakawa(dot)takay(at)fujitsu(dot)com>
Subject: RE: Parallel INSERT (INTO ... SELECT ...)
Date: 2021-01-26 04:18:22
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers


I have an issue of the check about column default expressions.

+ if (command_type == CMD_INSERT)
+ {
+ /*
+ * Column default expressions for columns in the target-list are
+ * already being checked for parallel-safety in the
+ * max_parallel_hazard() scan of the query tree in standard_planner().
+ */
+ tupdesc = RelationGetDescr(rel);
+ for (attnum = 0; attnum < tupdesc->natts; attnum++)

IMO, max_parallel_hazard() only check the parent table's default expressions, But if the table has partitions and its partition have its own default expressions, max_parallel_hazard() seems does not check that.
And we seems does not check that too.

I am not sure should we allow parallel insert for this case ?


set parallel_setup_cost=0;
set parallel_tuple_cost=0;
set min_parallel_table_scan_size=0;
set max_parallel_workers_per_gather=4;

create table origin(a int);
insert into origin values(generate_series(1,5000));

create or replace function bdefault_unsafe () returns int language plpgsql parallel unsafe as $$ begin
end $$;

create table parttable1 (a int, b name) partition by range (a); create table parttable1_1 partition of parttable1 for values from (0) to (5000); create table parttable1_2 partition of parttable1 for values from (5000) to (10000);

alter table parttable1_1 ALTER COLUMN b SET DEFAULT bdefault_unsafe();

postgres=# explain insert into parttable1 select * from origin ;
Gather (cost=0.00..41.92 rows=5865 width=0)
Workers Planned: 3
-> Insert on parttable1 (cost=0.00..41.92 rows=0 width=0)
-> Parallel Seq Scan on origin (cost=0.00..41.92 rows=1892 width=68)
(4 rows)

postgres=# explain insert into parttable1_1 select * from origin ;
Insert on parttable1_1 (cost=0.00..1348.00 rows=0 width=0)
-> Seq Scan on origin (cost=0.00..1348.00 rows=5000 width=68)
(2 rows)


Best regards,

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Kenneth Marshall 2021-01-26 04:23:30 Re: Add SQL function for SHA1
Previous Message Julien Rouhaud 2021-01-26 04:10:16 Re: Extensions not dumped when --schema is used