Re: 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>
From: "Daniel Westermann (DWE)" <daniel.westermann@dbi-services.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: "pgsql-bugs@lists.postgresql.org" <pgsql-bugs@lists.postgresql.org>
Date: 2023-04-05T18:51:51Z
Lists: pgsql-bugs
>You didn't provide anything useful like the table schemas, but
>correctness of a merge join depends on the servers having the same
>deas about sort ordering, and if "cprd" is a text-type column then
>inconsistent collations could break that.
rsup1=# \d "rsu_adm"."data_2d_clb_global_product"
Table "rsu_adm.data_2d_clb_global_product"
Column | Type | Collation | Nullable | Default
-------------------------+--------------------------+-----------+----------+---------
cprd | character varying | | not null |
xtc_id | numeric | | |
rprd | numeric | | |
prdgalsts_id | integer | | |
dlz_last_transaction_ts | timestamp with time zone | | |
Indexes:
"data_2d_clb_global_product_pkey" PRIMARY KEY, btree (cprd)
"data_2d_clb_global_product_idx4" btree (dlz_last_transaction_ts)
rsup1=# \d "rsu_adm"."clb_global_product"
View "rsu_adm.clb_global_product"
Column | Type | Collation | Nullable | Default
-------------------------+-----------------------------+-----------+----------+---------
cprd | character varying | | |
xtc_id | numeric(10,0) | | |
rprd | numeric(10,0) | | |
prdgalsts_id | numeric(10,0) | | |
dlz_last_transaction_ts | timestamp(5) with time zone | | |
Both instances use the same collation;
rsup1=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
rsup1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
dlzp1=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
dlzp1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
>The given plan is at hazard for that because it intends to do
>one sort locally and the other remotely:
Remote is a view:
dlzp1=# \d ro_rsu.clb_global_product
View "ro_rsu.clb_global_product"
Column | Type | Collation | Nullable | Default
-------------------------+-----------------------------+-----------+----------+---------
cprd | character varying | | |
xtc_id | numeric(10,0) | | |
rprd | numeric(10,0) | | |
prdgalsts_id | numeric(10,0) | | |
dlz_last_transaction_ts | timestamp(5) with time zone | | |
dlzp1=# select definition from pg_views where viewname = 'clb_global_product';
definition
----------------------------------------
SELECT clb_flbgpr.cprd, +
clb_flbgpr.xtc_id, +
clb_flbgpr.rprd, +
clb_flbgpr.prdgalsts_id, +
clb_flbgpr.dlz_last_transaction_ts+
FROM dlz_clb.clb_flbgpr;
(1 row)
dlzp1=# \d dlz_clb.clb_flbgpr
Table "dlz_clb.clb_flbgpr"
Column | Type | Collation | Nullable | Default
-------------------------+-----------------------------+-----------+----------+---------
cprd | character varying | | not null |
fp_lwr_celsius | numeric(3,0) | | |
fp_lwr_farenheit | numeric(3,0) | | |
fp_last_upd | date | | |
win_indicator | numeric(6,0) | | |
gpr_theme | character varying(6) | | |
comparison_symbol | character(1) | | |
cprd_as_previous | character varying | | |
emp_user_id_as_pcfm | character varying | | |
xtc_id | numeric(10,0) | | |
gpr_oral_care | character(1) | | |
bunit_id | numeric(10,0) | | |
remark_id | numeric(10,0) | | |
win_them_indicator | numeric(6,0) | | |
plr_id | numeric(10,0) | | |
stability_status | character(1) | | |
cmr_frm_tested | character(1) | | |
rprd | numeric(10,0) | | |
like_level_id | numeric(10,0) | | |
lhpr_id | numeric(10,0) | | |
like_perf_lvl_id | numeric(10,0) | | |
lhdncpr_id | numeric(10,0) | | |
like_hdnc_lvl_id | numeric(10,0) | | |
prdgalsts_id | numeric(10,0) | | |
oil_stock_sts | character(1) | | |
archive_status | character(2) | | |
dlz_last_transaction_ts | timestamp(5) with time zone | | |
Indexes:
"clb_flbgpr_pk" PRIMARY KEY, btree (cprd)
"clb_flbgpr_lst_trn" btree (dlz_last_transaction_ts)
Triggers:
clb_flbgpr_ins BEFORE INSERT ON dlz_clb.clb_flbgpr FOR EACH ROW EXECUTE FUNCTION dlz_adm.fct_trg_setup_transaction_ts()
clb_flbgpr_upd BEFORE UPDATE ON dlz_clb.clb_flbgpr FOR EACH ROW WHEN (old.* IS DISTINCT FROM new.*) EXECUTE FUNCTION dlz_adm.fct
_trg_setup_transaction_ts()
Regards
Daniel