Skip to main content
Database Administrators

Return to Question

Post Closed as "Needs details or clarity" by mustaccio, Colin 't Hart, Michael Green, John K. N., LowlyDBA - John M
added 344 characters in body
Source Link
Shiwangini
Shiwangini
  • 362
  • 7
  • 23

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?

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.

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?

edited title
Link
McNets
McNets
  • 23.9k
  • 11
  • 50
  • 89

Trigger working on 5 crore+more than 50 million records

Format code to make it more readable.
Source Link
McNets
McNets
  • 23.9k
  • 11
  • 50
  • 89

I'm using postgresql 11. I have a table with 50000000+more than 50,000,000 records.The 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

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.

I'm using postgresql 11. I have a table with 50000000+ 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.

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.

Source Link
Shiwangini
Shiwangini
  • 362
  • 7
  • 23