Running a Simple Update Statement Fails, Second Time Suceeds.

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

Running a Simple Update Statement Fails, Second Time Suceeds.

Kumar, Virendra

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=>'[hidden email]'::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.
Reply | Threaded
Open this post in threaded view
|

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

Kumar, Virendra

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)

 

Regards,

Virendra

 

From: Kumar, Virendra
Sent: Tuesday, September 03, 2019 4:56 PM
To: [hidden email]
Subject: Running a Simple Update Statement Fails, Second Time Suceeds.

 

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=>'[hidden email]'::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.




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.
Reply | Threaded
Open this post in threaded view
|

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

Adrian Klaver-4
In reply to this post by Kumar, Virendra
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=>'[hidden email]'::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
[hidden email]


Reply | Threaded
Open this post in threaded view
|

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

Tom Lane-2
Adrian Klaver <[hidden email]> writes:
> 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.

Also, what PG version is that?  This looks a bit like bug #15913,
but it might be something else.

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

I rather wonder whether I'm even allowed to read this, let alone
answer it.  You do realize that this sort of add-on is completely
silly on a public mailing list?

                        regards, tom lane


Reply | Threaded
Open this post in threaded view
|

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

Kumar, Virendra
Hi Tom,

Sincere apologies for that privacy notice in email, this company policy which I cannot skip.

Adrian,

The function is really simple. I’ll share the code as soon as I can.

Regards,
Virendra

> On Sep 3, 2019, at 5:41 PM, Tom Lane <[hidden email]> wrote:
>
> Adrian Klaver <[hidden email]> writes:
>>> 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.
>
> Also, what PG version is that?  This looks a bit like bug #15913,
> but it might be something else.
>
>>> 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.
>
> I rather wonder whether I'm even allowed to read this, let alone
> answer it.  You do realize that this sort of add-on is completely
> silly on a public mailing list?
>
>            regards, tom lane

________________________________

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.
Reply | Threaded
Open this post in threaded view
|

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

Kumar, Virendra
Here is function code:
--
CREATE OR REPLACE FUNCTION sddf_update.tps_update_1(p_pres_id_in bigint, p_last_audit_update_dt_in timestamp without time zone, OUT p_err_code_out bigint, OUT p_err_mesg_out text, p_expiration_dt_in timestamp without time zone DEFAULT '1600-01-01'::date, p_audit_insert_user_name_tx_in character varying DEFAULT '0.000000001'::character varying, p_audit_update_user_name_tx_in character varying DEFAULT '0.000000001'::character varying, p_latest_version_in_in character varying DEFAULT '0'::character varying, p_delete_in_in character varying DEFAULT '0'::character varying, p_class_code_id_in character varying DEFAULT '0.000000001'::character varying, p_major_classification_cd_in character varying DEFAULT '.000000001'::character varying, p_major_class_name_tx_in character varying DEFAULT '0.000000001'::character varying, p_coverage_short_name_tx_in character varying DEFAULT '0.000000001'::character varying, p_coverage_name_tx_in character varying DEFAULT '0.000000001'::character varying, p_cdt_source_system_cd_in character varying DEFAULT '0.000000001'::character varying, p_cdt_submitting_country_cd_in character varying DEFAULT '0.000000001'::character varying, p_cdt_status_cd_in character varying DEFAULT '0.000000001'::character varying, p_effective_dt_in timestamp without time zone DEFAULT '1600-01-01'::date)
 RETURNS record
 LANGUAGE plpgsql
 SECURITY DEFINER
AS $function$
DECLARE
    ora2pg_rowcount int;
    lv_audit_update_dt   product_owner.external_sys_class_code_pres.audit_update_dt%TYPE;
--lv_audit_update_dt   product_owner.external_sys_class_code_pres.audit_update_dt%TYPE;
BEGIN
      UPDATE product_owner.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;

      GET DIAGNOSTICS ora2pg_rowcount = ROW_COUNT;

      p_err_code_out := 0;
   EXCEPTION
      WHEN SQLSTATE '50001' THEN
         p_err_code_out := -20999;
         p_err_mesg_out :=
               'Record has been modified since last retrieved - Resubmit transaction for parameter(s)  '
            || ' p_pres_id_in  values of which are => '
            || p_pres_id_in;
         RAISE EXCEPTION '%', p_err_mesg_out USING ERRCODE = '45999';
   END;

$function$

Table definition is below:
--
product_master_fdw=# \d product_owner.external_sys_class_code_pres;
                     Table "product_owner.external_sys_class_code_pres"
            Column            |            Type             | Collation | Nullable | Default
------------------------------+-----------------------------+-----------+----------+---------
pres_id                      | bigint                      |           | not null |
major_classification_cd      | character varying(10)       |           | not null |
major_classification_name_tx | character varying(100)      |           | not null |
coverage_short_name_tx       | character varying(50)       |           |          |
coverage_name_tx             | character varying(100)      |           | not null |
cdt_source_system_cd         | character varying(50)       |           | not null |
cdt_status_cd                | character varying(50)       |           | not null |
effective_dt                 | timestamp without time zone |           | not null |
expiration_dt                | timestamp without time zone |           |          |
audit_insert_dt              | timestamp without time zone |           | not null |
audit_insert_user_name_tx    | character varying(50)       |           | not null |
audit_update_dt              | timestamp without time zone |           |          |
audit_update_user_name_tx    | character varying(50)       |           |          |
latest_version_in            | character varying(1)        |           |          |
delete_in                    | character varying(1)        |           |          |
class_code_id                | character varying(50)       |           | not null |
cdt_submitting_country_cd    | character varying(50)       |           | not null |
Indexes:
    "external_sys_class_code_pres_pkey" PRIMARY KEY, btree (pres_id)
    "pres_fk_cdt_submitting_ctry_cd" btree (cdt_submitting_country_cd)
Referenced by:
    TABLE "product_owner.cov_category_detail_class_prcc" CONSTRAINT "prcc_fk_pres_detail_class" FOREIGN KEY (pres_id) REFERENCES product_owner.external_sys_class_code_pres(pres_id) DEFERRABLE
    TABLE "product_owner.ext_class_prod_class_map_prcm" CONSTRAINT "prcm_fk_pres_class_code" FOREIGN KEY (pres_id) REFERENCES product_owner.external_sys_class_code_pres(pres_id) DEFERRABLE NOT VALID
    TABLE "product_owner.ext_class_prod_cvg_map_prcv" CONSTRAINT "prcv_fk_pres_id" FOREIGN KEY (pres_id) REFERENCES product_owner.external_sys_class_code_pres(pres_id) DEFERRABLE
    TABLE "product_owner.external_prod_dtl_presd" CONSTRAINT "presd_fk_pres_id" FOREIGN KEY (pres_id) REFERENCES product_owner.external_sys_class_code_pres(pres_id) DEFERRABLE



Regards,
Virendra
-----Original Message-----
From: Kumar, Virendra
Sent: Tuesday, September 03, 2019 6:09 PM
To: Tom Lane
Cc: Adrian Klaver; [hidden email]
Subject: Re: Running a Simple Update Statement Fails, Second Time Suceeds.

Hi Tom,

Sincere apologies for that privacy notice in email, this company policy which I cannot skip.

Adrian,

The function is really simple. I’ll share the code as soon as I can.

Regards,
Virendra

> On Sep 3, 2019, at 5:41 PM, Tom Lane <[hidden email]> wrote:
>
> Adrian Klaver <[hidden email]> writes:
>>> 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.
>
> Also, what PG version is that?  This looks a bit like bug #15913,
> but it might be something else.
>
>>> 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.
>
> I rather wonder whether I'm even allowed to read this, let alone
> answer it.  You do realize that this sort of add-on is completely
> silly on a public mailing list?
>
>            regards, tom lane

________________________________

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.

________________________________

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.
Reply | Threaded
Open this post in threaded view
|

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

Tom Lane-2
"Kumar, Virendra" <[hidden email]> writes:
> Here is function code:

Hm, nothing very exciting in there.  But the known reasons for this
type of error involve something changing a table rowtype that the
function uses (that is, ALTER TABLE ADD COLUMN or the like).  Or
dropping/recreating such a table altogether.  Is there any part
of your workflow that redefines the type of
product_owner.external_sys_class_code_pres.audit_update_dt or
product_owner.external_sys_class_code_pres ?

Also, you still didn't tell us the server's version.

                        regards, tom lane


Reply | Threaded
Open this post in threaded view
|

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

Adrian Klaver-4
In reply to this post by Kumar, Virendra
On 9/4/19 8:02 AM, Kumar, Virendra wrote:
> Here is function code:

>
> Table definition is below:
> --
> product_master_fdw=# \d product_owner.external_sys_class_code_pres;
>                       Table "product_owner.external_sys_class_code_pres"
>              Column            |            Type             | Collation | Nullable | Default

I see ora2pg in the function and product_master_fdw above.

Is this a foreign table?

--
Adrian Klaver
[hidden email]


Reply | Threaded
Open this post in threaded view
|

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

Kumar, Virendra
In reply to this post by Tom Lane-2

Just got confirmation from developer that they are not modifying any type.
We are on 11.5 RHEL.

Regards,
Virendra

-----Original Message-----
From: Tom Lane [mailto:[hidden email]]
Sent: Wednesday, September 04, 2019 11:17 AM
To: Kumar, Virendra
Cc: Adrian Klaver; [hidden email]
Subject: Re: Running a Simple Update Statement Fails, Second Time Suceeds.

"Kumar, Virendra" <[hidden email]> writes:
> Here is function code:

Hm, nothing very exciting in there.  But the known reasons for this
type of error involve something changing a table rowtype that the
function uses (that is, ALTER TABLE ADD COLUMN or the like).  Or
dropping/recreating such a table altogether.  Is there any part
of your workflow that redefines the type of
product_owner.external_sys_class_code_pres.audit_update_dt or
product_owner.external_sys_class_code_pres ?

Also, you still didn't tell us the server's version.

regards, tom lane

________________________________

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.


Reply | Threaded
Open this post in threaded view
|

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

Tom Lane-2
"Kumar, Virendra" <[hidden email]> writes:
> Just got confirmation from developer that they are not modifying any type.
> We are on 11.5 RHEL.

Interesting.  Can you exhibit a self-contained test case?

                        regards, tom lane