Re: trigger impacting insertion of records

On 5/6/21 11:37 PM, Atul Kumar wrote:
> 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
> 

The formatting of the above makes it hard to follow. Also trying to 
figure out why there are log lines at '2021-05-06
18:00:01' following those at '2021-05-06 18:06:09'.  If I am following 
it looks like a connection is timing out/being disconnected.

Where is the production server located relative to the process running 
the script, same machine, same local network or a remote network?

Also to be complete what Postgres version(though I suspect 9.5)?

> 
> 
> 
> Please suggest the solution to troubleshoot it more.
> 
> 
> 
> 
> Regards,
> Atul
> 
> 
> 
> 



-- 
Adrian Klaver
adrian.klaver@aklaver.com