RE: Running a Simple Update Statement Fails, Second Time Suceeds. - Mailing list pgsql-general

Type ID doesn’t seem to be existing:

product_master_fdw=> select * from pg_type where oid=196609;

typname | typnamespace | typowner | typlen | typbyval | typtype | typcategory | typispreferred | typisdefined | typdelim | typrelid | typelem | typarray | typinput | typoutput | typreceive | t

ypsend | typmodin | typmodout | typanalyze | typalign | typstorage | typnotnull | typbasetype | typtypmod | typndims | typcollation | typdefaultbin | typdefault | typacl

---------+--------------+----------+--------+----------+---------+-------------+----------------+--------------+----------+----------+---------+----------+----------+-----------+------------+--

-------+----------+-----------+------------+----------+------------+------------+-------------+-----------+----------+--------------+---------------+------------+--------

(0 rows)

Here is simple query, I am running via function call. This statement runs fine as SQL but when put in function as plpgsql it failes with error below, when I ran second times in same session it succeeds:

--

product_master_fdw=> \set VERBOSITY verbose

product_master_fdw=>       

select sddf_update.tps_update_1(p_pres_id_in=>50241::integer,

                          p_last_audit_update_dt_in=>'2019-09-03 12:44:21.356638'::timestamp ,

                          p_audit_update_user_name_tx_in=>'abc@xyz.com'::character varying,

                          p_major_class_name_tx_in=>'TEST0826222'::character varying,

                          p_effective_dt_in=>CURRENT_TIMESTAMP::timestamp

             );

ERROR:  XX000: cache lookup failed for type 196609

CONTEXT:  SQL statement "UPDATE product_history.external_sys_class_code_pres

         SET class_code_id =

                CASE WHEN p_class_code_id_in='0.000000001' THEN  class_code_id  ELSE p_class_code_id_in END ,

             major_classification_cd =

                CASE WHEN p_major_classification_cd_in='.000000001' THEN  major_classification_cd  ELSE p_major_classification_cd_in END ,

             major_classification_name_tx =

                CASE WHEN p_major_class_name_tx_in='0.000000001' THEN  major_classification_name_tx  ELSE p_major_class_name_tx_in END ,

             coverage_short_name_tx =

                CASE WHEN p_coverage_short_name_tx_in='0.000000001' THEN  coverage_short_name_tx  ELSE p_coverage_short_name_tx_in END ,

             coverage_name_tx =

                CASE WHEN p_coverage_name_tx_in='0.000000001' THEN  coverage_name_tx  ELSE p_coverage_name_tx_in END ,

             cdt_source_system_cd =

                CASE WHEN p_cdt_source_system_cd_in='0.000000001' THEN  cdt_source_system_cd  ELSE p_cdt_source_system_cd_in END ,

             cdt_submitting_country_cd =

                CASE WHEN p_cdt_submitting_country_cd_in='0.000000001' THEN  cdt_submitting_country_cd  ELSE p_cdt_submitting_country_cd_in END ,

             cdt_status_cd =

                CASE WHEN p_cdt_status_cd_in='0.000000001' THEN  cdt_status_cd  ELSE p_cdt_status_cd_in END ,

             effective_dt =

                CASE WHEN p_effective_dt_in=TO_DATE('01/01/1600', 'mm/dd/yyyy') THEN  effective_dt  ELSE p_effective_dt_in END ,

             expiration_dt =

                CASE WHEN p_expiration_dt_in=TO_DATE('01/01/1600', 'mm/dd/yyyy') THEN  expiration_dt  ELSE p_expiration_dt_in END ,

             audit_insert_user_name_tx =

                CASE WHEN p_audit_insert_user_name_tx_in='0.000000001' THEN  audit_insert_user_name_tx  ELSE p_audit_insert_user_name_tx_in END ,

             audit_update_dt = CURRENT_TIMESTAMP,

             audit_update_user_name_tx =

                CASE WHEN p_audit_update_user_name_tx_in='0.000000001' THEN  audit_update_user_name_tx  ELSE p_audit_update_user_name_tx_in END ,

             latest_version_in =

                CASE WHEN p_latest_version_in_in='0' THEN  latest_version_in  ELSE p_latest_version_in_in END ,

             delete_in =

                CASE WHEN p_delete_in_in='0' THEN  delete_in  ELSE p_delete_in_in END

       WHERE pres_id = p_pres_id_in

         AND audit_update_dt = p_last_audit_update_dt_in"

PL/pgSQL function px_co_pr_pres_pg.spt_update_1(bigint,timestamp without time zone,timestamp without time zone,character varying,character varying,character varying,character varying,character varying,character varying,character varying,character varying,character varying,character varying,character varying,character varying,timestamp without time zone) line 7 at SQL statement

LOCATION:  getTypeOutputInfo, lsyscache.c:2681

Regards,

Virendra

This message is intended only for the use of the addressee and may contain
information that is PRIVILEGED AND CONFIDENTIAL.

If you are not the intended recipient, you are hereby notified that any
dissemination of this communication is strictly prohibited. If you have
received this communication in error, please erase all copies of the message
and its attachments and notify the sender immediately. Thank you.