2

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?

0

The row being updated by the trigger will be locked until the end of the transaction. If the same combination of s,c,p,m,e is inserted by two concurrent transactions, one will block until the other commits or rolls back. If concurrent transactions never touch the same rows, they will not block each other.

via a pgsql loop

What is that? Are you talking about PL/pgSQL? All in one transaction? If two concurrent transactions are inserting 50,000 rows each, it is almost certain that there is at least one overlap between them.

run that file in 1 batch

Again, what is that? psql -f? Is it just a list of insert, or is it wrapped in BEGIN, COMMIT statements?

|improve this answer|||||
  • via a pgsql loop : your understanding is right about that. and batch file - is actually just a list of inserts only. – Shiwangini Shishulkar Oct 9 '19 at 13:16

Not the answer you're looking for? Browse other questions tagged or ask your own question.