[Postgres-xl-bugs] postgres-xl 9.5r1.1 - issue/bug - DISTRIBUTE BY REPLICATION does not replicate to all nodes - causing incorrect results

Bart Debersaques Bart.Debersaques at keyrus.com
Mon Jul 11 01:53:56 PDT 2016


Hi,

Affects postgres-xl 9.5r1.1 obtained via http://files.postgres-xl.org/postgres-xl-9.5r1.1.tar.gz -O /opt/server/download/postgres-xl-9.5r1.1.tar.gz<http://files.postgres-xl.org/postgres-xl-9.5r1.1.tar.gz%20-O%20/opt/server/download/postgres-xl-9.5r1.1.tar.gz>

Affected concept: DISTRIBUTE BY REPLICATION

Reproduce Via Methods: Alter table and create table as select (CTAS)

Goal: goal is to perform co-locate joins using small reference tables by keeping a copy of the reference tables on each node using DISTRIBUTE BY REPLICATION

Observations: upon Alter table and create table as select (CTAS) applying DISTRIBUTE BY REPLICATION = data is on a single node only

Example:

node_name  | node_type | node_port | node_host | nodeis_primary | nodeis_preferred |   node_id
------------+-----------+-----------+-----------+----------------+------------------+-------------
coord1     | C         |      6432 | localhost | f              | f                |  1885696643
datanode_1 | D         |     15432 | localhost | f              | f                |  -675012441
datanode_2 | D         |     15433 | localhost | f              | f                | -1047623914
(3 rows)

create UNLOGGED table dwh_rel_02.dim_address2 DISTRIBUTE BY REPLICATION TO NODE (datanode_1, datanode_2) as
select * from dwh_rel_02.dim_address;

select xc_node_id, count(*) FROM dwh_rel_02.dim_address2 GROUP BY xc_node_id;


xc_node_id  |  count

-------------+---------

 -1047623914 | 3392240


Impact: incorrect results, upon join are returned as join is performed in a co-located way as intended but some nodes do not get a copy and so no data to join with

Regard,

Bart Debersaques,

________________________________
Disclaimer - Unless indicated otherwise, the information contained in this message is privileged and confidential, and is intended only for the use of the addressee(s) named above and others who have been specifically authorized to receive it. If you are not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this message and/or attachments is strictly prohibited. Keyrus and its subsidiaries accept no liability for any damage caused by any virus transmitted by this email. Furthermore, they do not warrant a proper and complete transmission of this information, nor do they accept liability for any delays. If you have received this message in error, please contact the sender and delete all copies. Thank you for your kind collaboration.


This message has been scanned for malware by Websense. www.websense.com
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.postgres-xl.org/private.cgi/postgres-xl-bugs-postgres-xl.org/attachments/20160711/25232436/attachment.htm>


More information about the postgres-xl-bugs mailing list