[postgres-xl-general] 回复: PGXL 10alpha1 using Postgresql 10 NativePartitioning BUG????

Tank.zhang 6220104 at qq.com
Wed Jul 12 02:48:40 PDT 2017


HI, Tomas
 
   Thank your Reply。
 
 The question Pavan Deolasee has answered.






On Wed, Jul 12, 2017 at 9:19 AM, Tank.zhang <6220104 at qq.com> wrote:
HI,


  When I try to use Postgresql 10 Native Partitioning multiple returns in PGXL10 results are inconsistent?? This is PGXL bug?? or not using  Native Partitioning ?




Thanks for the report. One of the reasons to publish Postgres-XL 10 alpha code is to give our users a chance to test new features early and report problems. I'm glad it's helping.
 



test=#   CREATE TABLE padre (
test(#      id             serial not null,
test(#      nombre         text not null,
test(#      fch_creado     timestamptz not null
test(#     )
test-#     PARTITION BY RANGE ( id );
CREATE TABLE


Since no explicit distribution mechanism is specified, system decides to distribute the table by HASH(id), which is the first column of the table and the data type is supported for HASH distribution.
 
test=# CREATE TABLE hijo_0
test-#       partition of padre (id, primary key (id), unique (nombre))
test-#       for values from (unbounded) to (9);


And this is the reason why it breaks. Since there is no explicit mention of the distribution strategy and since primary/unique keys are specified, the system chooses to replicate the table. There is no way we could enforce unique constraints on both the columns, but the way things are coded right now, even if one is to remove the unique constraint on "nombre", the system would still replicate the table.
 

I think we need to fix this problem and ensure that partitions always inherit the distribution strategy of the parent table and not cook something for its own. Once we do that, this CREATE TABLE statement should throw an error since the UNIQUE constraint can't be enforced. I'll work on that. In the meanwhile, you can try to create the partition without specifying the constraints and then add primary key to the table later. Something like:


CREATE TABLE hijo_1  PARTITION OF padre   FOR VALUES FROM (10) TO (unbounded); 

ALTER TABLE  hijo_1 ADD PARIMARY KEY (id);

postgres=> \d+ hijo_1
                                                          Table "public.hijo_1"
   Column   |           Type           | Collation | Nullable |              Default              | Storage  | Stats target | Description 
------------+--------------------------+-----------+----------+-----------------------------------+----------+--------------+-------------
 id         | integer                  |           | not null | nextval('padre_id_seq'::regclass) | plain    |              | 
 nombre     | text                     |           | not null |                                   | extended |              | 
 fch_creado | timestamp with time zone |           | not null |                                   | plain    |              | 
Partition of: padre FOR VALUES FROM (10) TO (UNBOUNDED)
Partition constraint: ((id IS NOT NULL) AND (id >= 10))
Indexes:
    "hijo_1_pkey" PRIMARY KEY, btree (id)
Distribute By: HASH(id)
Location Nodes: ALL DATANODES





test=# explain select * from padre;
                                QUERY PLAN                                
--------------------------------------------------------------------------
 Remote Subquery Scan on all (dn2)  (cost=0.00..42.60 rows=2260 width=44)
   ->  Append  (cost=0.00..42.60 rows=2260 width=44)
         ->  Seq Scan on hijo_0  (cost=0.00..21.30 rows=1130 width=44)
         ->  Seq Scan on hijo_1  (cost=0.00..21.30 rows=1130 width=44)
(4 rows)




Yes, the plan looked wrong. It should query all the datanodes, but since the partition tables were incorrectly marked as REPLICATED, it was running it on any one node and giving wrong results.


Thanks,
Pavan  









------------------ 原始邮件 ------------------
发件人: "Tomas Vondra";<tomas.vondra at 2ndquadrant.com>;
发送时间: 2017年7月12日(星期三) 下午5:33
收件人: "postgres-xl-general"<postgres-xl-general at lists.postgres-xl.org>; 

主题: Re: [postgres-xl-general] PGXL 10alpha1 using Postgresql 10 NativePartitioning BUG????



Hello,

Thanks for the report.

On 7/12/17 4:09 AM, Tank.zhang wrote:
 > HI,
 >
 > When I try to use Postgresql 10 Native Partitioning multiple returns
 > in PGXL10 results are inconsistent?? This is PGXL bug?? or not using
 > Native Partitioning ?
 >

Yes, this seems like a bug in planning queries on partitioned tables.

 > test=# select * from padre;
 >   id | nombre |       fch_creado
 > ----+--------+------------------------
 >    1 | asddd9 | 2001-09-06 23:00:00-04
 >    2 | astat9 | 2001-09-08 23:00:00-04
 > (2 rows)
 >
 > test=# select * from padre;
 >   id | nombre |       fch_creado
 > ----+--------+------------------------
 >    3 | astat9 | 2001-09-12 23:00:00-04
 > (1 row)
 >

Essentially what's happening is that we treat the table as if it was 
replicated for some reason, and the result vary depending on what node 
we pick (mostly by random). Sometimes we do this:

     test=# explain (costs off) select * from padre;
                    QUERY PLAN
     -----------------------------------------
      Remote Subquery Scan on all (datanode1)
        ->  Append
              ->  Seq Scan on hijo_0
              ->  Seq Scan on hijo_1
     (4 rows)

and sometimes this

     test=# explain (costs off) select * from padre;
                    QUERY PLAN
     -----------------------------------------
      Remote Subquery Scan on all (datanode2)
        ->  Append
              ->  Seq Scan on hijo_0
              ->  Seq Scan on hijo_1
     (4 rows)

I'm not sure why yet.


regards


-- Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
_______________________________________________
postgres-xl-general mailing list
postgres-xl-general at lists.postgres-xl.org
http://lists.postgres-xl.org/listinfo.cgi/postgres-xl-general-postgres-xl.org
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.postgres-xl.org/pipermail/postgres-xl-general-postgres-xl.org/attachments/20170712/6493d8f4/attachment.htm>


More information about the postgres-xl-general mailing list