Re: Wrong results with postgres_fdw and merge anti join from RHEL 7.9 to RHEL 8.7
От | Tom Lane |
---|---|
Тема | Re: Wrong results with postgres_fdw and merge anti join from RHEL 7.9 to RHEL 8.7 |
Дата | |
Msg-id | 2017352.1680720065@sss.pgh.pa.us обсуждение исходный текст |
Ответ на | Wrong results with postgres_fdw and merge anti join from RHEL 7.9 to RHEL 8.7 ("Daniel Westermann (DWE)" <daniel.westermann@dbi-services.com>) |
Ответы |
Re: Wrong results with postgres_fdw and merge anti join from RHEL 7.9 to RHEL 8.7
Re: Wrong results with postgres_fdw and merge anti join from RHEL 7.9 to RHEL 8.7 |
Список | pgsql-bugs |
"Daniel Westermann (DWE)" <daniel.westermann@dbi-services.com> writes: > 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: You didn't provide anything useful like the table schemas, but correctness of a merge join depends on the servers having the same ideas about sort ordering, and if "cprd" is a text-type column then inconsistent collations could break that. The given plan is at hazard for that because it intends to do one sort locally and the other remotely: > 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 > I am aware that the version of glibc is not the same between those red hats. Is this expected? That's certainly a hazard, but do the servers even have the same collation settings for these columns? regards, tom lane
В списке pgsql-bugs по дате отправления: