Re: trigger impacting insertion of records - Mailing list pgsql-general

hi,

The data is inserting using some json sript which is working fine in
our stating server and inserting complete records.

But in production data insertion is slow and after some insertion it
just abort somehow.

DB logs are given below:


#PostGreSQL: idle^^2021-05-06 18:06:09
PDT^^bonzipay^^usmliv1^^127.0.0.1(38435)^^60949111.16a5^^2021-05-06
18:00:01 PDTLOG:  statement: BEGIN
#PostGreSQL: idle in transaction^^2021-05-06 18:06:09
PDT^^bonzipay^^usmliv1^^127.0.0.1(38435)^^60949111.16a5^^2021-05-06
18:00:01 PDTLOG:  statement: DEALLOCATE pdo_stmt_000000d6
#PostGreSQL: INSERT^^2021-05-06 18:06:09
PDT^^bonzipay^^usmliv1^^127.0.0.1(38435)^^60949111.16a5^^2021-05-06
18:00:01 PDTLOG:  execute pdo_stmt_000000d7: insert into
bonzipay.bp_ach_trans_response
(payment_pastransid,payment_status,code,site,accountnumber,amount,bankaccountnumber,accountype,TranId)

                                 select
payment_pastransid,'SETTLED',code
,site,accountnumber,amount,bankaccountnumber,accountype,TranId from
bonzipay.bp_ach_trans

                                 where payment_status ='PROCESS' and
accountnumber='USR=647376' and bankaccountnumber='3027469304'

                                 and amount='6000' and accountype='22'
order by 1 desc limit 1
#PostGreSQL: idle in transaction^^2021-05-06 18:06:10
PDT^^bonzipay^^usmliv1^^127.0.0.1(38435)^^60949111.16a5^^2021-05-06
18:00:01 PDTLOG:  statement: COMMIT
#PostGreSQL: idle^^2021-05-06 18:06:10
PDT^^bonzipay^^usmliv1^^127.0.0.1(38435)^^60949111.16a5^^2021-05-06
18:00:01 PDTLOG:  statement: BEGIN
#PostGreSQL: idle in transaction^^2021-05-06 18:06:10
PDT^^bonzipay^^usmliv1^^127.0.0.1(38435)^^60949111.16a5^^2021-05-06
18:00:01 PDTLOG:  statement: DEALLOCATE pdo_stmt_000000d7
#PostGreSQL: UPDATE^^2021-05-06 18:06:10
PDT^^bonzipay^^usmliv1^^127.0.0.1(38435)^^60949111.16a5^^2021-05-06
18:00:01 PDTLOG:  execute pdo_stmt_000000d8: UPDATE
bonzipay.BP_ACH_TRANS SET payment_status='SETTLED' where
payment_pastransid= (select payment_pastransid from
bonzipay.BP_ACH_TRANS where payment_status ='PROCESS' and
accountnumber='USR=647376' and

                         amount='6000'and
bankaccountnumber='3027469304' and accountype='22' order by 1 desc
limit 1)
                                                                                                
                                                                                                
#PostGreSQL: idle in transaction^^2021-05-06 18:06:12
PDT^^bonzipay^^usmliv1^^127.0.0.1(38435)^^60949111.16a5^^2021-05-06
18:00:01 PDTLOG:  unexpected EOF on client connection with an open
transaction




Please suggest the solution to troubleshoot it more.




Regards,
Atul





On 5/6/21, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
> On 5/6/21 12:45 AM, Atul Kumar wrote:
>> Hi,
>>
>> I have simple table having structure like given below:
>>
>> \d bp_ach_trans
>>                                            Table "bonzipay.bp_ach_trans"
>>         Column       |          Type          |
>>      Modifiers
>> --------------------+------------------------+-------------------------------------------------------------------
>> bptransid          | integer                | not null default
>> nextval('bp_ach_trans_bptransid_seq1'::regclass)
>>
>> filename           | character varying(50)  |
>>   payment_status     | character varying(30)  |
>>   settledate         | character varying(15)  |
>>   payment_pastransid | bigint                 |
>>   tname              | character varying(250) |
>>   code               | character varying(5)   |
>>   error_txt          | character varying(200) |
>>   routingnumber      | character varying(15)  |
>>   tracenumber        | character varying(10)  |
>>   accountnumber      | character varying(15)  |
>>   bankaccountnumber  | character varying(17)  |
>>   type               | character varying(1)   |
>>   amount             | numeric                |
>>   site               | character varying(30)  |
>>   accountype         | character varying(2)   |
>>   tranid             | character varying(15)  |
>>
>> Triggers:
>>      ins_ussf_rec AFTER INSERT ON bp_ach_trans FOR EACH ROW EXECUTE
>> PROCEDURE ussf_accountnumber_update()
>>
>>
>>
>> the function definition is like below:
>>
>> CREATE OR REPLACE FUNCTION bonzipay.ussf_accountnumber_update()
>>   RETURNS trigger
>>   LANGUAGE plpgsql
>> AS $function$ BEGIN update bonzipay.bp_ach_trans set
>> accountnumber=replace(accountnumber,'_',' ') where
>> left(accountnumber,3) = 'US_'; RETURN NEW; END; $function$
>>
>>
>> my query is:
>>
>> when I am inserting around 1000 records in the table having
>> accountnumber not having value 'US_', I am getting only 300 records
>> insertion. remaining around 700 values are not getting inserted.
>
> How are you determining this?
>
> How are you doing the INSERT?
>
> Does the Postgres log show any errors when the INSERTs are done?
>
>>
>> why this strange behavior is happening, as I am not inserting any
>> record having value 'US_' even after that all records are not
>> inserting.
>>
>> Any suggestions are welcome.
>>
>>
>>
>> Regards,
>> Atul
>>
>>
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
>