RE: FW: Query execution failure - Mailing list pgsql-bugs

Thanks for the quick response, Tom.

To clarify, the DDL for the two local tables and the primary foreign table are below. I am also including the EXPLAIN
VERBOSEoutput for this query. 

I'm not sure how to identify which index may be corrupt, if that is the cause. Regarding a different sort order between
tables,if that were the case, wouldn't this query fail in Production as it does in our Development environment? They
areidentical with respect to indices and sort order, and they both point to the same foreign table, which resides on a
MySQLdatabase. 

Let me know if this additional information helps. Thanks.

__________________________________________

CREATE TABLE IF NOT EXISTS main.ship_event
(
    ship_event_nm character varying(20) COLLATE pg_catalog."default" NOT NULL,
    available_for_ship_dt date,
    last_build_dt date,
    sign_off_dt date,
    commit_to_ship_dt date,
    l10n_file_freeze_dt date,
    dev_test_infra_ready_dt date,
    builds_stop_dt date,
    localization_ready_dt date,
    source_system_cd character varying(20) COLLATE pg_catalog."default" NOT NULL,
    updated_dttm timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_by_id character varying(50) COLLATE pg_catalog."default" NOT NULL DEFAULT CURRENT_USER,
    CONSTRAINT ship_event_pk PRIMARY KEY (ship_event_nm),
    CONSTRAINT ship_event_fk01 FOREIGN KEY (source_system_cd)
        REFERENCES cqm_meta.source_system (source_system_cd) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS main.ship_event
    OWNER to cqmadmin;

CREATE TABLE IF NOT EXISTS main.deployment_base
(
    deployment_id integer NOT NULL DEFAULT nextval('main.deployment_base_deployment_id_seq'::regclass),
    source_system_deployment_id text COLLATE pg_catalog."default" NOT NULL,
    deployment_nm character varying(100) COLLATE pg_catalog."default",
    deployment_tenant_nm character varying(100) COLLATE pg_catalog."default",
    deployment_cluster_nm character varying(100) COLLATE pg_catalog."default",
    cluster_kubernetes_version_id character varying(100) COLLATE pg_catalog."default",
    cluster_node_no smallint,
    cluster_ram_no smallint,
    cluster_cpu_no smallint,
    cluster_disk_no smallint,
    infrastructure_desc text COLLATE pg_catalog."default",
    ha_multi_master_flg character(1) COLLATE pg_catalog."default",
    cluster_environment_desc character varying(100) COLLATE pg_catalog."default",
    build_level_cd character varying(50) COLLATE pg_catalog."default",
    deployment_deploy_tm integer,
    deployment_ulid_cd character varying(120) COLLATE pg_catalog."default",
    order_id integer,
    k8s_namespace_nm character varying(100) COLLATE pg_catalog."default",
    k8s_kubeconfig_url_txt character varying(200) COLLATE pg_catalog."default",
    k8s_kubeconfig_data jsonb,
    k8s_failed_pods_cnt integer,
    k8s_client_version_no character varying(20) COLLATE pg_catalog."default",
    k8s_server_version_no character varying(20) COLLATE pg_catalog."default",
    k8s_cluster_type_cd character varying(20) COLLATE pg_catalog."default",
    k8s_ingress_controller_type_cd character varying(20) COLLATE pg_catalog."default",
    k8s_os_type_nm character varying(20) COLLATE pg_catalog."default",
    k8s_os_version_no character varying(20) COLLATE pg_catalog."default",
    deployment_host_nm character varying(120) COLLATE pg_catalog."default",
    promotion_stage_cd character varying(120) COLLATE pg_catalog."default",
    ship_event_nm character varying(20) COLLATE pg_catalog."default",
    deployment_uuid_cd character varying(120) COLLATE pg_catalog."default",
    manifest_commit_id character varying(120) COLLATE pg_catalog."default",
    orderable_ary_nm character varying[] COLLATE pg_catalog."default",
    deployment_deploy_dttm timestamp with time zone,
    source_system_cd character varying(20) COLLATE pg_catalog."default",
    cruise_worker_id character varying(50) COLLATE pg_catalog."default",
    cluster_info_json_doc jsonb,
    cas_ready_cd character varying(10) COLLATE pg_catalog."default",
    all_pods_alive_flg boolean,
    oauth_ready_flg boolean,
    deployment_label character varying(50) COLLATE pg_catalog."default",
    sas_conn_ready_flg boolean,
    sas_login_ready_flg boolean,
    postgres_version_no character varying(20) COLLATE pg_catalog."default",
    postgres_type_cd character varying(10) COLLATE pg_catalog."default",
    cadence_id integer,
    deployment_test_flg boolean,
    deployment_type_nm character varying(20) COLLATE pg_catalog."default",
    cloud_provider_nm character varying(50) COLLATE pg_catalog."default",
    storage_type_nm character varying(50) COLLATE pg_catalog."default",
    sas_base_url_txt character varying(200) COLLATE pg_catalog."default",
    embedded_ldap_flg boolean,
    cas_host_nm character varying(100) COLLATE pg_catalog."default",
    cas_mode_cd character varying(10) COLLATE pg_catalog."default",
    cas_port_no character varying(10) COLLATE pg_catalog."default",
    cas_battery_results_cd character varying(10) COLLATE pg_catalog."default",
    cas_server_type_cd character varying(10) COLLATE pg_catalog."default",
    cas_backup_flg boolean,
    node_instance_type character varying(100) COLLATE pg_catalog."default",
    viya4_ark_json_doc jsonb,
    performance_json_doc jsonb,
    astrolabe_json_doc jsonb,
    viya4_env_valid_json_doc jsonb,
    updated_dttm timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_by_id character varying(50) COLLATE pg_catalog."default" NOT NULL DEFAULT CURRENT_USER,
    CONSTRAINT deployment_base_pk PRIMARY KEY (deployment_id),
    CONSTRAINT deployment_base_ux01 UNIQUE (source_system_deployment_id, source_system_cd),
    CONSTRAINT deployment_base_fk01 FOREIGN KEY (ship_event_nm)
        REFERENCES main.ship_event (ship_event_nm) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT deployment_base_fk02 FOREIGN KEY (source_system_cd)
        REFERENCES cqm_meta.source_system (source_system_cd) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT deployment_base_fk03 FOREIGN KEY (order_id)
        REFERENCES main.order_base (order_id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)

TABLESPACE pg_default;

Query explain

QUERY PLAN
Hash Join  (cost=182208.88..4862218.44 rows=10656250 width=1555)
  Output: dk.deployment_id, dk.deployment_key_txt, dp.name, dp.tenant, infc.name, icv.description, infc.nodes,
infc.ram,infc.cpu, infc.disk, dp.deploy_description, CASE WHEN (infrq.multi_master = 1) THEN 'Y'::text ELSE 'N'::text
END,infrq.cluster_label, dl.deploy_time, dl.order_number, ob.order_id, dp.server, to_timestamp((dl."timestamp")::double
precision),dl.promotion_stage, se.ship_event_nm, 'UDANEXT'::text 
  Hash Cond: ((dl.order_number)::text = (ob.order_no)::text)
  ->  Merge Left Join  (cost=14682.01..174631.01 rows=10656250 width=1599)
        Output: dp.name, dp.tenant, dp.deploy_description, dp.server, dl.deploy_time, dl.order_number, dl."timestamp",
dl.promotion_stage,dk.deployment_id, dk.deployment_key_txt, infc.name, infc.nodes, infc.ram, infc.cpu, infc.disk,
infrq.multi_master,infrq.cluster_label, icv.description, se.ship_event_nm 
        Merge Cond: ((dp.tenant)::text = (inft.name)::text)
        ->  Sort  (cost=14667.01..14709.63 rows=17050 width=927)
              Output: dp.name, dp.tenant, dp.deploy_description, dp.server, dl.deploy_time, dl.order_number,
dl."timestamp",dl.promotion_stage, dk.deployment_id, dk.deployment_key_txt, se.ship_event_nm 
              Sort Key: dp.tenant
              ->  Merge Join  (cost=13165.12..13468.61 rows=17050 width=927)
                    Output: dp.name, dp.tenant, dp.deploy_description, dp.server, dl.deploy_time, dl.order_number,
dl."timestamp",dl.promotion_stage, dk.deployment_id, dk.deployment_key_txt, se.ship_event_nm 
                    Merge Cond: (dk.deployment_key_txt = (concat_ws('|'::text, dl.deploy_id, dl.task_id, 'UDANEXT')))
                    ->  Sort  (cost=12778.41..12780.12 rows=682 width=36)
                          Output: dk.deployment_id, dk.deployment_key_txt
                          Sort Key: dk.deployment_key_txt
                          ->  Foreign Scan on cqm_meta.deployment_key dk  (cost=12691.97..12746.31 rows=682 width=36)
                                Output: dk.deployment_id, dk.deployment_key_txt
                                Filter: (NOT (hashed SubPlan 1))
                                Remote SQL: SELECT deployment_id, deployment_key_txt FROM cqm_meta.deployment_key
                                SubPlan 1
                                  ->  Index Only Scan using deployment_base_ux01 on main.deployment_base db
(cost=0.42..11840.60rows=300546 width=20) 
                                        Output: db.source_system_deployment_id
                    ->  Sort  (cost=386.70..399.20 rows=5000 width=899)
                          Output: dp.name, dp.tenant, dp.deploy_description, dp.server, dl.deploy_time,
dl.order_number,dl."timestamp", dl.promotion_stage, dl.deploy_id, dl.task_id, se.ship_event_nm, (concat_ws('|'::text,
dl.deploy_id,dl.task_id, 'UDANEXT')) 
                          Sort Key: (concat_ws('|'::text, dl.deploy_id, dl.task_id, 'UDANEXT'))
                          ->  Hash Left Join  (cost=45.69..79.51 rows=5000 width=899)
                                Output: dp.name, dp.tenant, dp.deploy_description, dp.server, dl.deploy_time,
dl.order_number,dl."timestamp", dl.promotion_stage, dl.deploy_id, dl.task_id, se.ship_event_nm, concat_ws('|'::text,
dl.deploy_id,dl.task_id, 'UDANEXT') 
                                Inner Unique: true
                                Hash Cond: (SUBSTRING(dp.shipevent FROM 6 FOR 5) = (se.ship_event_nm)::text)
                                ->  Foreign Scan  (cost=15.00..35.00 rows=5000 width=960)
                                      Output: dp.name, dp.tenant, dp.deploy_description, dp.server, dp.shipevent,
dl.deploy_time,dl.order_number, dl."timestamp", dl.promotion_stage, dl.deploy_id, dl.task_id 
                                      Relations: (udanext.deployments dp) INNER JOIN (udanext.deployment_log dl)
                                      Remote server startup cost: 25
                                      Remote query: SELECT r1.`name`, r1.`tenant`, r1.`deploy_description`,
r1.`server`,r1.`shipevent`, r2.`deploy_time`, r2.`order_number`, r2.`timestamp`, r2.`promotion_stage`, r2.`deploy_id`,
r2.`task_id`FROM (`udanext`.`deployments` r1 INNER JOIN `udanext`.`deployment_log` r2 ON (((r1.`id` =
r2.`deploy_id`))))ORDER BY r2.`order_number` IS NULL, r2.`order_number` ASC 
                                ->  Hash  (cost=19.75..19.75 rows=875 width=7)
                                      Output: se.ship_event_nm
                                      ->  Seq Scan on main.ship_event se  (cost=0.00..19.75 rows=875 width=7)
                                            Output: se.ship_event_nm
        ->  Materialize  (cost=15.00..347.50 rows=125000 width=890)
              Output: inft.name, infc.name, infc.nodes, infc.ram, infc.cpu, infc.disk, infrq.multi_master,
infrq.cluster_label,icv.description 
              ->  Foreign Scan  (cost=15.00..35.00 rows=125000 width=890)
                    Output: inft.name, infc.name, infc.nodes, infc.ram, infc.cpu, infc.disk, infrq.multi_master,
infrq.cluster_label,icv.description 
                    Relations: (((udanext.infrastructure_tenant inft) LEFT JOIN (udanext.infrastructure_cluster infc))
LEFTJOIN (udanext.infrastructure_cluster_request infrq)) LEFT JOIN (udanext.infrastructure_cluster_version icv) 
                    Remote server startup cost: 25
                    Remote query: SELECT r4.`name`, r8.`name`, r8.`nodes`, r8.`ram`, r8.`cpu`, r8.`disk`,
r10.`multi_master`,r10.`cluster_label`, r12.`description` FROM (((`udanext`.`infrastructure_tenant` r4 LEFT JOIN
`udanext`.`infrastructure_cluster`r8 ON (((r8.`tenant_id` = r4.`id`)))) LEFT JOIN
`udanext`.`infrastructure_cluster_request`r10 ON (((r10.`id` = r8.`request_id`)))) LEFT JOIN
`udanext`.`infrastructure_cluster_version`r12 ON (((r8.`k8s_version_id` = r12.`id`)))) ORDER BY r4.`name` IS NULL,
r4.`name`ASC 
  ->  Hash  (cost=113073.72..113073.72 rows=3132572 width=11)
        Output: ob.order_id, ob.order_no
        ->  Seq Scan on main.order_base ob  (cost=0.00..113073.72 rows=3132572 width=11)
              Output: ob.order_id, ob.order_no
Query Identifier: -8672584825060546873


CREATE FOREIGN TABLE IF NOT EXISTS foreign_udanext.deployments(
    id integer NOT NULL,
    "timestamp" character varying(50) NULL COLLATE pg_catalog."default",
    status character varying(15) NULL COLLATE pg_catalog."default",
    prod_code character varying(10) NULL COLLATE pg_catalog."default",
    shipevent character varying(25) NULL COLLATE pg_catalog."default",
    user_id character varying(15) NULL COLLATE pg_catalog."default",
    name character varying(100) NULL COLLATE pg_catalog."default",
    env_type character varying(25) NULL COLLATE pg_catalog."default",
    tenant character varying(100) NULL COLLATE pg_catalog."default",
    deploy_type character varying(25) NULL COLLATE pg_catalog."default",
    host_code character varying(5) NULL COLLATE pg_catalog."default",
    cas_controller character varying(50) NULL COLLATE pg_catalog."default",
    contacts character varying(400) NULL COLLATE pg_catalog."default",
    deploy_description text NULL COLLATE pg_catalog."default",
    server character varying(100) NULL COLLATE pg_catalog."default",
    vm_instance_name character varying(50) NULL COLLATE pg_catalog."default",
    vm_instance_size character varying(25) NULL COLLATE pg_catalog."default",
    race_base_image character varying(25) NULL COLLATE pg_catalog."default",
    race_gold_image character varying(25) NULL COLLATE pg_catalog."default",
    race_label character varying(50) NULL COLLATE pg_catalog."default",
    load_image smallint NULL,
    vm_instance_os character varying(200) NULL COLLATE pg_catalog."default",
    topology character varying(25) NULL COLLATE pg_catalog."default",
    tenant_region character varying(25) NULL COLLATE pg_catalog."default",
    vm_volume_size integer NULL,
    multi_nic_enabled smallint NULL,
    service_mesh_type integer NOT NULL,
    orchestration_format_id integer NULL,
    database_type integer NOT NULL,
    cas_id integer NULL
)
    SERVER udanext_prod_server
    OPTIONS (dbname 'udanext', table_name 'deployments');


-----Original Message-----
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Thursday, January 26, 2023 10:24 AM
To: Pete Storer <Pete.Storer@sas.com>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: FW: Query execution failure

[You don't often get email from tgl@sss.pgh.pa.us. Learn why this is important at
https://aka.ms/LearnAboutSenderIdentification] 

EXTERNAL

Pete Storer <Pete.Storer@sas.com> writes:
> When trying to run a complex join of eight tables - including 6
> foreign tables - the query runs for a few seconds and then terminates
> with an error message
> ERROR: mergejoin input data is out of order

You have not shown us the table definitions, nor an EXPLAIN for the query, but this'd indicate that the sort ordering
ofone or more tables is not like that of the others.  Likely causes of this include 

* corrupt index, if an indexscan is being used instead of an explicit
  sort

* foreign server has a different idea of the locale's sort details than
  the local server does (or than some other foreign server, if the
  query touches more than one)

If the join key columns are all numeric then it's almost surely a corrupt index somewhere.  But if you are joining on
stringkeys then you are subject to the whims of the locale's sort order, and that is a mess, particularly for non-ASCII
data. Not only might it be different on different platforms, but even on the same platform it can change over time
(withthe effect of making indexes on string columns corrupt). 
There's some details and advice here:


https://nam02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwiki.postgresql.org%2Fwiki%2FLocale_data_changes&data=05%7C01%7CPete.Storer%40sas.com%7C69ba399db52343407f0c08daffb15836%7Cb1c14d5c362545b3a4309552373a0c2f%7C0%7C0%7C638103434451258989%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=7nBPCQY16PdlQy1tmPnURFk3LMSL%2FosNsrZhM0eEwvo%3D&reserved=0

                        regards, tom lane