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

The Problem

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

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:

• There is a temporary table created by the trigger (only if needed).
• The table should be dropped at commit, at rollback it is dropped automatically.
• This temporary table has just one column and one row – this is the counter.
• The counter is used for storing information about the number of rows updated in current transaction.
• If the number is greater than 4, just rollback the transaction.

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;

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

And 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.

Bookmark It

Related posts:

1. What Is The Real PostgreSQL Triggers Ordering Introduction During the last database training that I led, I...
2. PostgreSQL FOUND Problem FOUND is a global variable that exists in the plpgsql...
3. PostgreSQL Strange Timings A very simple query. PostgreSQL 8.4. I have no idea...
4. How to Ensure There is One Row With a Value The Problem Let’s imagine a business requirement that there should...
5. Random String in PostgreSQL Last time I wrote about the common problem with the...
6. Testing Database – Small Reply On Ovid’s blog I found lately this entry. First of...