Re: Running a Simple Update Statement Fails, Second Time Suceeds.

On 9/3/19 1:56 PM, Kumar, Virendra wrote:
> 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:

We will need to see the function definition.

> 
> --
> 
> 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.


-- 
Adrian Klaver
adrian.klaver@aklaver.com