Message reported in the Greenplum log : THD000,ERROR,"Lookup of object 3.142.1.0;25.1.0 in cache failed",

Products

• VMware Tanzu Data Suite
• Greenplum
• VMware Tanzu Greenplum
• VMware Tanzu Data Suite

Issue/Introduction

The following message is reported frequently in the master log of the Greenplum database:

2024-08-21 18:08:03.023167 BST,"gpadmin","gpadmin",p4990,th1875691648,"[local]",,2024-08-21 18:04:58 BST,0,con189897,cmd64,seg-1,,,,sx1,"LOG","00000","statement: select xml_data from xml_table ;",,,,,,"select xml_data from xml_table ;",0,,"postgres.c",1667,
2024-08-21 18:08:09.771828 BST,"gpadmin","gpadmin",p4990,th1875691648,"[local]",,2024-08-21 18:04:58 BST,0,con189897,cmd66,seg-1,,,,sx1,"LOG","00000","statement: select xml_data::text from xml_table ;",,,,,,"select xml_data::text from xml_table ;",0,,"postgres.c",1667,
2024-08-21 18:08:09.772010 BST,"gpadmin","gpadmin",p4990,th1875691648,"[local]",,2024-08-21 18:04:58 BST,0,con189897,cmd66,seg-1,,,,sx1,"LOG","00000","2024-08-21 18:08:09:770618 BST,THD000,ERROR,""Lookup of object 3.142.1.0;25.1.0 in cache failed"",
2024-08-21 18:08:09:770950 BST,THD000,ERROR,""Lookup of object 3.142.1.0;25.1.0 in cache failed"",
",,,,,,"select xml_data::text from xml_table ;",0,,"COptTasks.cpp",271,

Cause

Example that cause the message, is to cast data from an XML type column to text:

CREATE TABLE xml_data (id int, data xml) DISTRIBUTED BY(id);
INSERT INTO xml_data VALUES (1, '<info1>Some information text</info1>'::xml);

SELECT data::text from xml_data;

Check the log file for the message:

2024-08-21 18:19:13.208066 BST,"gpadmin","gpadmin",p5916,th1875691648,"[local]",,2024-08-21 18:11:17 BST,0,con189898,cmd14,seg-1,,,,sx1,"LOG","00000","statement: CREATE TABLE xml_data (id int, data xml) DISTRIBUTED BY(id);",,,,,,"CREATE TABLE xml_data (id int, data xml) DISTRIBUTED BY(id);",0,,"postgres.c",1667,
2024-08-21 18:19:13.279127 BST,"gpadmin","gpadmin",p5916,th1875691648,"[local]",,2024-08-21 18:11:17 BST,0,con189898,cmd15,seg-1,,,,sx1,"LOG","00000","statement: INSERT INTO xml_data VALUES (1, '<info1>Some information text</info1>'::xml);",,,,,,"INSERT INTO xml_data VALUES (1, '<info1>Some information text</info1>'::xml);",0,,"postgres.c",1667,
2024-08-21 18:19:16.681177 BST,"gpadmin","gpadmin",p5916,th1875691648,"[local]",,2024-08-21 18:11:17 BST,0,con189898,cmd17,seg-1,,,,sx1,"LOG","00000","statement: SELECT data::text from xml_data;",,,,,,"SELECT data::text from xml_data;",0,,"postgres.c",1667,
2024-08-21 18:19:16.691463 BST,"gpadmin","gpadmin",p5916,th1875691648,"[local]",,2024-08-21 18:11:17 BST,0,con189898,cmd17,seg-1,,,,sx1,"LOG","00000","2024-08-21 18:19:16:690725 BST,THD000,ERROR,""Lookup of object 3.142.1.0;25.1.0 in cache failed"",
2024-08-21 18:19:16:690870 BST,THD000,ERROR,""Lookup of object 3.142.1.0;25.1.0 in cache failed"",
",,,,,,"SELECT data::text from xml_data;",0,,"COptTasks.cpp",271,

The data from the SELECT is returned successfully.

Resolution

This can occur when "optimizer=on", which is the default setting, and the Pivotal Optimizer (GPORCA) cannot generate a plan for the query and it falls back to the Postgres query optimizer.

The message is not an error and not causing any issues for users running the query. It is just for information.

The message can be ignored.