I have a table with more than 50,000,000 records. The table doesn't have any partition. The table is in PROD.
I need to create a trigger there which will update previous entries flag to false for each upcoming latest entry. Each upcoming latest entry will have true value on flag.
The table has around 1300 inserts per minute. One of the column of that table contain json data as well.
Here is the trigger script:
CREATE TRIGGER trig_updaterow before insert on tab for each row execute procedure updaterow(); CREATE OR REPLACE FUNCTION updaterow() RETURNS trigger AS $$ BEGIN update tab set flag ='0' where s = new.s and p = new.p and c = new.c and m = new.m and e = new.e and flag = '1'; RETURN NEW; END; $$ LANGUAGE plpgsql;
I need to check in case of large number of inserts if the table will going to face any lock because of this trigger. I have tested it will bulk inserts on prod copy. However, still I'm not sure about this. Also, I have observed a strange thing, when I'm inserting a sample 50,000 records via a pgsql loop, it's placing a lock on any other insert(blocking). However, when I'm inserting same number of records manually(manually writing 50,000 inserting statement and run that file in 1 batch)-it's not blocking any new insert. Why is that difference?