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(at)xyz(dot)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(dot)klaver(at)aklaver(dot)com