Обсуждение: [BUGS] BUG #14732: partitioned table cann't alter set parallel_workers?

The following bug has been logged on the website:

Bug reference:      14732
Logged by:          Zhou Digoal
Email address:      digoal@126.com
PostgreSQL version: 10beta1
Operating system:   CentOS 6.x x64
Description:

HI,

there is an normal table and a partitiond table.
normal table can set parallel_works parameter, but partitioned table
cann't.

```
postgres=# \d orders                                      Unlogged table "public.orders"    Column      |         Type
       | Collation | Nullable |                Default                   
 
-----------------+-----------------------+-----------+----------+--------------------------------------------o_orderkey
    | bigint                |           | not null | 
nextval('orders_o_orderkey_seq'::regclass)o_custkey       | bigint                |           | not null |
o_orderstatus  | character(1)          |           |          | o_totalprice    | double precision      |           |
      | o_orderdate     | date                  |           |          | o_orderpriority | character(15)         |
    |          | o_clerk         | character(15)         |           |          | o_shippriority  | integer
 |           |          | o_comment       | character varying(79) |           |          | 
 

postgres=# \d orders1                                      Unlogged table "public.orders1"    Column      |
Type         | Collation | Nullable |                 Default                   
 

-----------------+-----------------------+-----------+----------+---------------------------------------------o_orderkey
    | bigint                |           | not null | 
nextval('orders1_o_orderkey_seq'::regclass)o_custkey       | bigint                |           | not null |
o_orderstatus  | character(1)          |           |          | o_totalprice    | double precision      |           |
      | o_orderdate     | date                  |           |          | o_orderpriority | character(15)         |
    |          | o_clerk         | character(15)         |           |          | o_shippriority  | integer
 |           |          | o_comment       | character varying(79) |           |          | 
 
Partition key: RANGE (o_orderdate)
Number of partitions: 84 (Use \d+ to list them.)

postgres=# alter table orders set (parallel_workers =32);
ALTER TABLE
postgres=# alter table orders1 set (parallel_workers =32);
ERROR:  22023: unrecognized parameter "parallel_workers"
LOCATION:  parseRelOptions, reloptions.c:1094
```

but i can update pg_class to modify partitioned table's parallel_works.

```
postgres=# update pg_class set reloptions =array['parallel_workers=13']
where relname ~ 'lineitem' and relkind='r';
UPDATE 85
Time: 11.803 ms
postgres=# explain select count(*) from lineitem1;                                               QUERY PLAN
                                 
 
-----------------------------------------------------------------------------------------------------------Finalize
Aggregate (cost=130579654.20..130579654.21 rows=1 width=8)  ->  Gather  (cost=130579654.16..130579654.17 rows=13
width=8)       Workers Planned: 13        ->  Partial Aggregate  (cost=130579654.16..130579654.17 rows=1 
width=8)              ->  Append  (cost=0.00..130557628.94 rows=8810089 width=0)                    ->  Parallel Seq
Scanon lineitem_ptr_0  
(cost=0.00..209424.27 rows=106127 width=0)                    ->  Parallel Seq Scan on lineitem_ptr_1
(cost=0.00..585852.15 rows=106115 width=0)                    ->  Parallel Seq Scan on lineitem_ptr_2
(cost=0.00..1043031.30 rows=106130 width=0)                    ->  Parallel Seq Scan on lineitem_ptr_3
(cost=0.00..1419621.31 rows=106131 width=0)
...
```

best regards.



--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Hi,

On 2017/07/04 11:09, digoal@126.com wrote:
> The following bug has been logged on the website:
> 
> Bug reference:      14732
> Logged by:          Zhou Digoal
> Email address:      digoal@126.com
> PostgreSQL version: 10beta1
> Operating system:   CentOS 6.x x64
> Description:        
> 
> HI,
> 
> there is an normal table and a partitiond table.
> normal table can set parallel_works parameter, but partitioned table
> cann't.
> 
> ```
> postgres=# \d orders
>                                        Unlogged table "public.orders"
>      Column      |         Type          | Collation | Nullable |           
>       Default                   
> -----------------+-----------------------+-----------+----------+--------------------------------------------
>  o_orderkey      | bigint                |           | not null |
> nextval('orders_o_orderkey_seq'::regclass)
>  o_custkey       | bigint                |           | not null | 
>  o_orderstatus   | character(1)          |           |          | 
>  o_totalprice    | double precision      |           |          | 
>  o_orderdate     | date                  |           |          | 
>  o_orderpriority | character(15)         |           |          | 
>  o_clerk         | character(15)         |           |          | 
>  o_shippriority  | integer               |           |          | 
>  o_comment       | character varying(79) |           |          | 
> 
> postgres=# \d orders1
>                                        Unlogged table "public.orders1"
>      Column      |         Type          | Collation | Nullable |           
>        Default                   
> -----------------+-----------------------+-----------+----------+---------------------------------------------
>  o_orderkey      | bigint                |           | not null |
> nextval('orders1_o_orderkey_seq'::regclass)
>  o_custkey       | bigint                |           | not null | 
>  o_orderstatus   | character(1)          |           |          | 
>  o_totalprice    | double precision      |           |          | 
>  o_orderdate     | date                  |           |          | 
>  o_orderpriority | character(15)         |           |          | 
>  o_clerk         | character(15)         |           |          | 
>  o_shippriority  | integer               |           |          | 
>  o_comment       | character varying(79) |           |          | 
> Partition key: RANGE (o_orderdate)
> Number of partitions: 84 (Use \d+ to list them.)
> 
> postgres=# alter table orders set (parallel_workers =32);
> ALTER TABLE
> postgres=# alter table orders1 set (parallel_workers =32);
> ERROR:  22023: unrecognized parameter "parallel_workers"
> LOCATION:  parseRelOptions, reloptions.c:1094
> ```

This is not really a bug.  We do not support setting reloptions
(parallel_workers is a reloption) for partitioned tables, because setting
them will currently have no effect.  Maybe the error message doesn't make
that clear.  It's actually trying to say: "parallel workers" is
unrecognized parameter for partitioned tables.

You can set them on the individual partitions.

> but i can update pg_class to modify partitioned table's parallel_works.
> 
> ```
> postgres=# update pg_class set reloptions =array['parallel_workers=13']
> where relname ~ 'lineitem' and relkind='r';
> UPDATE 85

Yes, you can to do that, but the system still won't use it.  When
appropriate system support for partitioned tables to use parallel query
will be added, then we will also make the above alter table command
succeed, but not until then.

By the way, the update statement above doesn't actually affect partitioned
tables, because its relkind is 'p'.

> postgres=# explain select count(*) from lineitem1;
>                                                 QUERY PLAN                  
>                               
> -----------------------------------------------------------------------------------------------------------
>  Finalize Aggregate  (cost=130579654.20..130579654.21 rows=1 width=8)
>    ->  Gather  (cost=130579654.16..130579654.17 rows=13 width=8)
>          Workers Planned: 13
>          ->  Partial Aggregate  (cost=130579654.16..130579654.17 rows=1
> width=8)
>                ->  Append  (cost=0.00..130557628.94 rows=8810089 width=0)
>                      ->  Parallel Seq Scan on lineitem_ptr_0 
> (cost=0.00..209424.27 rows=106127 width=0)
>                      ->  Parallel Seq Scan on lineitem_ptr_1 
> (cost=0.00..585852.15 rows=106115 width=0)
>                      ->  Parallel Seq Scan on lineitem_ptr_2 
> (cost=0.00..1043031.30 rows=106130 width=0)
>                      ->  Parallel Seq Scan on lineitem_ptr_3 
> (cost=0.00..1419621.31 rows=106131 width=0)
> ...
> ```

Use of parallelism in this case may be the result of setting parallel
workers on the individual partitions, that is, lineitem_ptr_* relations.

Thanks,
Amit



-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs