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