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

From Vijaykumar Jain
Subject Re: trigger impacting insertion of records
Date
Msg-id CAM+6J94chzo9ZtucFyGt6ms-TigGRaNqwi2QEVL3QtM8g132AA@mail.gmail.com
Whole thread Raw
In response to trigger impacting insertion of records  (Atul Kumar <akumar14871@gmail.com>)
List pgsql-general

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
-------
     0
(1 row)

-- do see 50 "US<space>" values as expected.
postgres=# select count(*) from example where value like 'US %';
 count
-------
    50
(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

pgsql-general by date:

Previous
From: Jian He
Date:
Subject: Chain Hashing
Next
From: Pavel Luzanov
Date:
Subject: Re: Strange behavior of function date_trunc