Wrong results with postgres_fdw and merge anti join from RHEL 7.9 to RHEL 8.7

Поиск
Список
Период
Сортировка
От Daniel Westermann (DWE)
Тема Wrong results with postgres_fdw and merge anti join from RHEL 7.9 to RHEL 8.7
Дата
Msg-id GV0P278MB0419C826AF173592DA648C88D2909@GV0P278MB0419.CHEP278.PROD.OUTLOOK.COM
обсуждение исходный текст
Ответы Re: Wrong results with postgres_fdw and merge anti join from RHEL 7.9 to RHEL 8.7  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-bugs
Hi,

I am not sure if this qualifies as  bug, but anyway:

Source instance: PostgreSQL 13.7 on RHEL 7.9
Target instance PostgreSQL 13.7 on RHEL 8.7

This is the statement:
SELECT * FROM "rsu_adm"."data_2d_clb_global_product" f2
LEFT OUTER JOIN "rsu_adm"."clb_global_product" f1 on f1.cprd=f2.cprd
WHERE f1.cprd is null;

Per default we see a merge anti join, and this gives results, which is wrong:

rsup1=# SELECT * FROM "rsu_adm"."data_2d_clb_global_product" f2
LEFT OUTER JOIN "rsu_adm"."clb_global_product" f1 on f1.cprd=f2.cprd
WHERE f1.cprd is null;
     cprd      | xtc_id |  rprd   | prdgalsts_id |   dlz_last_transaction_ts    | cprd | xtc_id | rprd | prdgalsts_id |
dlz_last_tran

---------------+--------+---------+--------------+------------------------------+------+--------+------+--------------+--------------
 0027033       |        |  179722 |            1 | 2023-04-03 06:15:09.45135+02 |      |        |      |              |

 0112113       |        | 3199208 |            1 | 2023-04-03 06:15:09.45135+02 |      |        |      |              |

 0116713       |        | 2071012 |            1 | 2023-04-03 06:15:09.45135+02 |      |        |      |              |

 0116953       |        | 2070136 |            1 | 2023-04-03 06:15:09.45135+02 |      |        |      |              |

...


                                                                                                                  QUERY
PLAN                                                                                                                  

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Merge Anti Join  (cost=100.84..67203.45 rows=50713 width=122)
   Output: f2.cprd, f2.xtc_id, f2.rprd, f2.prdgalsts_id, f2.dlz_last_transaction_ts, clb_global_product.cprd,
clb_global_product.xtc_id,clb_global_product.rprd, clb_global_product.prdgalsts_id,
clb_global_product.dlz_last_transaction_ts
   Merge Cond: ((f2.cprd)::text = (clb_global_product.cprd)::text)
   ->  Index Scan using data_2d_clb_global_product_pkey on rsu_adm.data_2d_clb_global_product f2  (cost=0.42..2898.56
rows=101426width=34) 
         Output: f2.cprd, f2.xtc_id, f2.rprd, f2.prdgalsts_id, f2.dlz_last_transaction_ts
   ->  Foreign Scan on ro_dlz.clb_global_product  (cost=100.42..52506.16 rows=923613 width=34)
         Output: clb_global_product.cprd, clb_global_product.xtc_id, clb_global_product.rprd,
clb_global_product.prdgalsts_id,clb_global_product.dlz_last_transaction_ts 
         Remote SQL: SELECT cprd, xtc_id, rprd, prdgalsts_id, dlz_last_transaction_ts FROM ro_rsu.clb_global_product
ORDERBY cprd ASC NULLS LAST 
(8 rows)


Disabling merge join gives the correct result:
rsup1=# set enable_mergejoin = off;
SET
rsup1=# explain verbose SELECT * FROM "rsu_adm"."data_2d_clb_global_product" f2
LEFT OUTER JOIN "rsu_adm"."clb_global_product" f1 on f1.cprd=f2.cprd
WHERE f1.cprd is null;
                                                                                                                  QUERY
PLAN                                                                                                                  

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Hash Anti Join  (cost=60274.55..681118.72 rows=50713 width=122)
   Output: f2.cprd, f2.xtc_id, f2.rprd, f2.prdgalsts_id, f2.dlz_last_transaction_ts, clb_global_product.cprd,
clb_global_product.xtc_id,clb_global_product.rprd, clb_global_product.prdgalsts_id,
clb_global_product.dlz_last_transaction_ts
   Hash Cond: ((f2.cprd)::text = (clb_global_product.cprd)::text)
   ->  Seq Scan on rsu_adm.data_2d_clb_global_product f2  (cost=0.00..1768.26 rows=101426 width=34)
         Output: f2.cprd, f2.xtc_id, f2.rprd, f2.prdgalsts_id, f2.dlz_last_transaction_ts
   ->  Hash  (cost=41513.39..41513.39 rows=923613 width=34)
         Output: clb_global_product.cprd, clb_global_product.xtc_id, clb_global_product.rprd,
clb_global_product.prdgalsts_id,clb_global_product.dlz_last_transaction_ts 
         ->  Foreign Scan on ro_dlz.clb_global_product  (cost=100.00..41513.39 rows=923613 width=34)
               Output: clb_global_product.cprd, clb_global_product.xtc_id, clb_global_product.rprd,
clb_global_product.prdgalsts_id,clb_global_product.dlz_last_transaction_ts 
               Remote SQL: SELECT cprd, xtc_id, rprd, prdgalsts_id, dlz_last_transaction_ts FROM
ro_rsu.clb_global_product
(10 rows)

rsup1=# SELECT * FROM "rsu_adm"."data_2d_clb_global_product" f2
LEFT OUTER JOIN "rsu_adm"."clb_global_product" f1 on f1.cprd=f2.cprd
WHERE f1.cprd is null;
 cprd | xtc_id | rprd | prdgalsts_id | dlz_last_transaction_ts | cprd | xtc_id | rprd | prdgalsts_id |
dlz_last_transaction_ts 

------+--------+------+--------------+-------------------------+------+--------+------+--------------+-------------------------
(0 rows)

This is the server definition:
rsup1=# \des+
                                                                                       List of foreign servers
  Name   |  Owner   | Foreign-data wrapper | Access privileges | Type | Version |
     FDW options                                              | Description  

---------+----------+----------------------+-------------------+------+---------+------------------------------------------------------------------------------------------------------+-------------
 tgt_srv | postgres | postgres_fdw         |                   |      |         | (host '192.168.100.245', dbname
'dlzp1',port '5432', use_remote_estimate 'true', fetch_size '5000') |  
(1 row)

I am aware that the version of glibc is not the same between those red hats. Is this expected?

Thanks in advance
Daniel






В списке pgsql-bugs по дате отправления:

Предыдущее
От: PG Bug reporting form
Дата:
Сообщение: BUG #17887: EDB Community Installer for windows, locale selection not working properly
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Wrong results with postgres_fdw and merge anti join from RHEL 7.9 to RHEL 8.7