Home > mailing lists

FW: Query execution failure - Mailing list pgsql-bugs

From	Pete Storer
Subject	FW: Query execution failure
Date	January 26, 2023 01:17:07
Msg-id	BL0PR05MB66283C57D72E321591AE4EB1F3CE9@BL0PR05MB6628.namprd05.prod.outlook.com Whole thread Raw
In response to	Query execution failure (Pete Storer <Pete.Storer@sas.com>)
Responses	Re: FW: Query execution failure (Tom Lane <tgl@sss.pgh.pa.us>)
List	pgsql-bugs

Tree view

Resending this with all the text in the body of the email rather than as a Word attachment.

PostgreSQL Bug Report

Overview

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

This occurs ONLY in the Dev environment; the query executes successfully in our Production environment, although it returns zero rows.

The query

Here is the query that is causing the problem:

SELECT

dk.deployment_id,

dk.deployment_key_txt,

dp.name as deployment_nm,

dp.tenant as deployment_tenant_nm,

infc.name as deployment_cluster_nm,

icv.description as cluster_kubernetes_version_id,

infc.nodes as cluster_node_no,

infc.ram as cluster_ram_no,

infc.cpu as cluster_cpu_no,

infc.disk as cluster_disk_no,

dp.deploy_description as infrastructure_desc,

CASE

WHEN infrq.multi_master = 1 THEN 'Y'

else 'N'

END

AS HA_multi_master_flg,

infrq.cluster_label as cluster_environment_desc,

dl.deploy_time as deployment_deploy_tm,

dl.order_number as order_no_id,

ob.order_id as order_id,

dp.server as deployment_host_nm,

to_timestamp(CAST(dl."timestamp" AS double precision)) as deployment_created_dttm,

dl.promotion_stage as promotion_stage_cd,

se.ship_event_nm as ship_event_nm,

'UDANEXT' as source_system_cd

FROM foreign_udanext.deployments dp

LEFT OUTER JOIN foreign_udanext.deployment_log dl ON (dl.deploy_id = dp.id)

LEFT OUTER JOIN foreign_udanext.infrastructure_tenant inft ON (inft.name=dp.tenant)

JOIN cqm_meta.deployment_key dk ON (concat_ws('|',dl.deploy_id, dl.task_id,'UDANEXT') = dk.deployment_key_txt)

-- JOIN sel_meta_key dk ON concat_ws('|',dl.deploy_id, dl.task_id,'UDANEXT') = dk.deployment_key_txt

LEFT OUTER JOIN foreign_udanext.infrastructure_cluster infc ON (infc.tenant_id=inft.id)

LEFT OUTER JOIN foreign_udanext.infrastructure_cluster_request infrq ON (infrq.id=infc.request_id)

LEFT OUTER JOIN foreign_udanext.infrastructure_cluster_version icv ON (infc.k8s_version_id=icv.id)

LEFT OUTER JOIN main.ship_event se ON (se.ship_event_nm = substring(dp.shipevent from 6 for 5))

inner join main.order_base ob on ob.order_no = dl.order_number

WHERE dk.deployment_key_txt NOT IN

(SELECT source_system_deployment_id

FROM main.deployment_base db)

The intent is to use this SELECT within an INSERT statement that will add the result data into a table, but first we have to have the SELECT work appropriately.

The error

ERROR: mergejoin input data is out of order

SQL state: XX000

This error message is not documented anywhere that I can find, and it only occurs in Dev, not Prod.

Suspected issue

We believe that this error is caused by one of two conditions:

Postgres is assuming an incorrect sort order on data returned from a substring function; and/or
The number of joins is causing intermediate data to be cached, and available cache memory is being exhausted.

Reasons for error suspicions

For (1), the error is suspected because if the line containing the substring is commented out, along with it’s associated join, the query runs successfully.

Substring:

LEFT OUTER JOIN main.ship_event se ON (se.ship_event_nm = substring(dp.shipevent from 6 for 5))

Query parameter (part of select):

se.ship_event_nm as ship_event_nm,

The reason we suspect this is first, because the query runs if this join and column selected is commented out of the query. Second, the error message, “mergejoin input data is out of order”, leads us to suspect that it is expecting the shipevent data to be in a particular order, and it is not because it is a substring that begins with the 6^th character of the string in the column.

What argues against this being the issue is that fact that the query executes properly in Prod, which is running on the same version of Postgres as Dev.

For error (2), the suspicion is based on the log output. The full log will be attached to this bug report, but here’s the relevant subset of the log: