count number of rows to be affected before update in trigger

Question

Hi,

I want to know number of rows that will be affected by UPDATE query in BEFORE per statement trigger . Is that possible?

The problem is that i want to allow only queries that will update up to 4 rows. If affected rows count is 5 or more i want to raise error.

I don't want to do this in code because i need this check on db level. Is this at all possible?

Thanks in advance for any clues on that

Answer 1

I've never worked with postgresql, so my answer may not apply. In SQL Server, your trigger can call a stored procedure which would do one of two things:

1. Perform a SELECT COUNT(*) to determine the number of records that will be affected by the UPDATE, and then only execute the UPDATE if the count is 4 or less
2. Perform the UPDATE within a transaction, and only commit the transaction if the returned number of rows affected is 4 or less

No. 1 is timing vulnerable (the number of records affected by the UPDATE may change between the COUNT(*) check and the actual UPDATE. No. 2 is pretty inefficient, if there are many cases where the number of rows updated is greater than 4.

Answer 2

PostgreSQL has two types of triggers: row and statement triggers. Row triggers only work within the context of a row so you can't use those. Unfortunately, "before" statement triggers don't see what kind of change is about to take place so I don't believe you can use those, either.

Based on that, I would say it's unlikely you'll be able to build that kind of protection into the database using triggers, not unless you don't mind using an "after" trigger and rolling back the transaction if the condition isn't satisfied. Wouldn't mind being proved wrong. :)

Answer 3

Have a look at using Serializable Isolation Level. I believe this will give you a consistent view of the database data within your transaction. Then you can use option #1 that MusiGenesis mentioned, without the timing vulnerability. Test it of course to validate.

Answer 4

Write a function that updates the rows for you or performs a rollback. Sorry for poor style formatting.

create function update_max(varchar, int) RETURNS void AS $BODY$

DECLARE

sql ALIAS FOR $1; max ALIAS FOR $2; rcount INT;

BEGIN

EXECUTE sql; GET DIAGNOSTICS rcount = ROW_COUNT;

IF rcount > max THEN

   --ROLLBACK;
   RAISE EXCEPTION 'Too much rows affected (%).', rcount;

END IF;

--COMMIT;

END;

$BODY$ LANGUAGE plpgsql

Then call it like

select update_t2('update t1 set id=id+10 where id <4', 3); where the first param ist your sql-Statement and the 2nd your max rows.

Answer 5

I've created something like this:

begin;

create table test (
    id integer
);

insert into test(id) select generate_series(1,100);


create or replace function trg_check_max_4_updated_records() 
returns trigger as $$
declare
    counter_ integer := 0;
    tablename_ text := 'temptable';
begin
    raise notice 'trigger fired';
    select count(42) into counter_ 
        from pg_catalog.pg_tables where tablename = tablename_;
    if counter_ = 0 then
        raise notice 'Creating table %', tablename_;
        execute 'create temporary table ' || tablename_ || ' (counter integer) on commit drop';
        execute 'insert into ' || tablename_ || ' (counter) values(1)';

        execute 'select counter from ' || tablename_ into counter_;
        raise notice 'Actual value for counter= [%]', counter_;
    else
        execute 'select counter from ' || tablename_ into counter_;
        execute 'update ' || tablename_ || ' set counter = counter + 1';
        raise notice 'updating';
        execute 'select counter from ' || tablename_ into counter_;
        raise notice 'Actual value for counter= [%]', counter_;

        if counter_ > 4 then
            raise exception 'Cannot change more than 4 rows in one trancation';
        end if;

    end if;
    return new;
end; $$ language plpgsql;


create trigger trg_bu_test before 
  update on test 
  for each row
  execute procedure trg_check_max_4_updated_records();

update test set id = 10 where id <= 1;
update test set id = 10 where id <= 2;
update test set id = 10 where id <= 3;
update test set id = 10 where id <= 4;
update test set id = 10 where id <= 5;

rollback;

The main idea is to have a trigger on 'before update for each row' that creates (if necessary) a temporary table (that is dropped at the end of transaction). In this table there is just one row with one value, that is the number of updated rows in current transaction. For each update the value is incremented. If the value is bigger than 4, the transaction is stopped.

But I think that this is a wrong solution for your problem. What's a problem to run such wrong query that you've written about, twice, so you'll have 8 rows changed. What about deletion rows or truncating them?

Answer 6

Simon had a good idea but his implementation is unnecessarily complicated. This is my proposition:

create or replace function trg_check_max_4()                
returns trigger as $$
begin
        perform true from pg_class 
                where relname='check_max_4' and relnamespace=pg_my_temp_schema(); 
        if not FOUND then
                create temporary table check_max_4 
                        (value int check (value<=4)) 
                        on commit drop;
                insert into check_max_4 values (0); 
        end if;

        update check_max_4 set value=value+1; 
        return new;
end; $$ language plpgsql;