Thread: Query execution failure

Query execution failure

From

Pete Storer

Date:

25 January 2023, 22:58:37

I am encountering a strange error when trying to execute a complex SELECT query in Postgres. All of the details of this error are documented in the attachment.

I hope you can help me to resolve this error.

Thanks very much.

Pete Storer

Sr Data Architect

Enterprise Performance and Data Architecture - EPD

Tel: + 1 919 531 5745

SAS | 100 Campus Drive | Cary, NC 27513

DID YOU KNOW?
SAS IS THE NO. 1 AI AND ADVANCED ANALYTICS SOFTWARE PLATFORM.

Attachment

Re: Query execution failure

From

Christophe Pettus

Date:

25 January 2023, 23:20:10

> On Jan 25, 2023, at 11:58, Pete Storer <Pete.Storer@sas.com> wrote:
>
> I am encountering a strange error when trying to execute a complex SELECT query in Postgres. All of the details of
thiserror are documented in the attachment. 

Without opining on the bug itself, you will likely get a much better response if you put the details in plain text,
ratherthan as a .docx attachment.  Many systems strip attachments, and many more people have a (justifiable) allergy to
openingattachments from an unknown source.

FW: Query execution failure

From

Pete Storer

Date:

26 January 2023, 01:17:07

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: