How to Allow For Max 4 Rows in Update (PostgreSQL)

by Szymon LipiƄski

A question found on the net:

how to restrict users to update only max 4 rows of a table in a transaction.

This looks like a solution to some artificial problem that doesn’t exist, but nevermind, any task should be solved.

The Solution

The solution is simple, there is a trigger on the table. First the table:

create table test (
    id integer
);

and some data:

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

Now the trigger. It is quite simple:

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';
    begin
        raise notice 'Creating table %', tablename_;
        execute 'create temporary table ' || tablename_ || ' (counter integer) on commit drop';
        execute 'insert into ' || tablename_ || ' (counter) values(0)';

        execute 'select counter from ' || tablename_ into counter_;
        raise notice 'Actual value for counter= [%]', counter_;
    exception
        when duplicate_table then
        null;
    end;
    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;

    return new;
end; $$ language plpgsql;

A little bit of testing:

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;

the output:

psql:test.sql:45: NOTICE:  trigger fired
psql:test.sql:45: NOTICE:  Creating table temptable
psql:test.sql:45: NOTICE:  Actual value for counter= [1]
UPDATE 1
psql:test.sql:46: NOTICE:  trigger fired
psql:test.sql:46: NOTICE:  updating
psql:test.sql:46: NOTICE:  Actual value for counter= [2]
UPDATE 1
psql:test.sql:47: NOTICE:  trigger fired
psql:test.sql:47: NOTICE:  updating
psql:test.sql:47: NOTICE:  Actual value for counter= [3]
UPDATE 1
psql:test.sql:48: NOTICE:  trigger fired
psql:test.sql:48: NOTICE:  updating
psql:test.sql:48: NOTICE:  Actual value for counter= [4]
UPDATE 1
psql:test.sql:49: NOTICE:  trigger fired
psql:test.sql:49: NOTICE:  updating
psql:test.sql:49: NOTICE:  Actual value for counter= [5]
psql:test.sql:49: ERROR:  Cannot change more than 4 rows in one trancation

One more test, updating 10 rows in one query:

update test set id = 10 where id <= 10;

and the output:

psql:test.sql:45: NOTICE:  trigger fired
psql:test.sql:45: NOTICE:  Creating table temptable
psql:test.sql:45: NOTICE:  Actual value for counter= [1]
psql:test.sql:45: NOTICE:  trigger fired
psql:test.sql:45: NOTICE:  updating
psql:test.sql:45: NOTICE:  Actual value for counter= [2]
psql:test.sql:45: NOTICE:  trigger fired
psql:test.sql:45: NOTICE:  updating
psql:test.sql:45: NOTICE:  Actual value for counter= [3]
psql:test.sql:45: NOTICE:  trigger fired
psql:test.sql:45: NOTICE:  updating
psql:test.sql:45: NOTICE:  Actual value for counter= [4]
psql:test.sql:45: NOTICE:  trigger fired
psql:test.sql:45: NOTICE:  updating
psql:test.sql:45: NOTICE:  Actual value for counter= [5]
psql:test.sql:45: ERROR:  Cannot change more than 4 rows in one trancation

So it works but I still think that it is a solution for a nonexistent problem, but I don’t know what the problem is.