Thread: trigger impacting insertion of records

trigger impacting insertion of records

From

Atul Kumar

Date:

06 May 2021, 10:45:12

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.

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

Re: trigger impacting insertion of records

From

Vijaykumar Jain

Date:

06 May 2021, 14:38:01

just simplified, but it works fine for me.

create table example(id int primary key, value text);

create or replace function trg_fn() returns trigger language plpgsql as $$

begin

RAISE NOTICE 'trigger_func(%) called: action = %, when = %, level = %', TG_ARGV[0], TG_OP, TG_WHEN, TG_LEVEL;

RAISE NOTICE 'id=%, value=%', NEW.id, NEW.value;

update example set value=replace(value,'_',' ') where left(value,3) = 'US_';

return new;

end; $$;

create trigger after_insert_trigger after insert ON example for each row execute function trg_fn();

insert into example select x, case when x % 2 = 0 then 'US_' || x::text else x::text end from generate_series(1, 100) x;

NOTICE: trigger_func(<NULL>) called: action = INSERT, when = AFTER, level = ROW

NOTICE: id=99, value=99

NOTICE: Returned 0 rows

NOTICE: trigger_func(<NULL>) called: action = INSERT, when = AFTER, level = ROW

NOTICE: id=100, value=US_100

NOTICE: Returned 0 rows

INSERT 0 100

-- do not see any values with US_, although i inserted 50 of them.

postgres=# select count(*) from example where value like 'US\_%';

count

-------

(1 row)

-- do see 50 "US<space>" values as expected.

postgres=# select count(*) from example where value like 'US %';

count

-------

(1 row)

Can you verify accountnumber field does not have any spaces etc at the beginning.

unless there is some conflicting stuff modifying rows, i think this should be ok.

you can

lock TABLE example IN exclusive mode; -- DO NOT DO IT IF IT IMPACTS ANYTHING IN PRODUCTION

On Thu, 6 May 2021 at 13:15, Atul Kumar <akumar14871@gmail.com> 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.

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

Thanks,

Vijay

Mumbai, India

Re: trigger impacting insertion of records

From

Adrian Klaver

Date:

06 May 2021, 18:56:57

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

Re: trigger impacting insertion of records

From

Atul Kumar

Date:

07 May 2021, 09:37:53

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
>

Re: trigger impacting insertion of records

From

Adrian Klaver

Date:

07 May 2021, 17:57:37

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

Re: trigger impacting insertion of records

From

Michael Lewis

Date:

10 May 2021, 22:16:49

It seems like there is some flaw here. From my reading, on insert of any row, you are updating ALL rows in the same table to just remove an underscore if it matches the pattern of 'US_' at the beginning. That doesn't seem likely to be what you want. I'd think you would want something like the below.

CREATE OR REPLACE FUNCTION bonzipay.ussf_accountnumber_update()
RETURNS trigger
LANGUAGE plpgsql
AS $function$ BEGIN

if( left(NEW.accountnumber,3) = 'US_' ) then

NEW.accountnumber=replace(accountnumber,'_',' ');

RETURN NEW; END; $function$