Postgres Pro
Downloads
Home   >   mailing lists

Re: Wrong results with postgres_fdw and merge anti join from RHEL 7.9 to RHEL 8.7 - Mailing list pgsql-bugs

From Daniel Westermann (DWE)
Subject Re: Wrong results with postgres_fdw and merge anti join from RHEL 7.9 to RHEL 8.7
Date
Msg-id GV0P278MB04193FBC28A1744C6945B81ED2909@GV0P278MB0419.CHEP278.PROD.OUTLOOK.COM
Whole thread Raw
In response to 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>)
Responses Re: Wrong results with postgres_fdw and merge anti join from RHEL 7.9 to RHEL 8.7
List pgsql-bugs
Tree view
>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


pgsql-bugs by date:

Previous
From: Tom Lane
Date:
Subject: Re: Wrong results with postgres_fdw and merge anti join from RHEL 7.9 to RHEL 8.7
Next
From: Tom Lane
Date:
Subject: Re: Wrong results with postgres_fdw and merge anti join from RHEL 7.9 to RHEL 8.7