how many times did you (or somebody in your environment) did something like this? update (or delete) without proper “where"?

it happened at least couple of times to me.

of course – using transactions solved the problem. but – on the other hand – if i can't trust myself to add proper where, how can i trust myself to add “begin"?

so, is there no hope?

are we doomed to always repeat the same mistake, and spend uncountable hours on recovering from damaged tables?

fortunatelly there is a help.

if you have a table that is very precious (can be recovered but it would take a long time, or damage will instantly kill some other service – like email server), you can forbid multi-row updates/deletes.

of course – it will not protect you from all kind of disasters and mistakes, but at the very least – missing “where" will not do any harm.

how to do it?

let's create test table:

CREATE TABLE test AS
    SELECT i AS id, 'password for: ' || i AS pass
    FROM generate_series(1,100) i;

if you dont know what it does, here's a sample (table has 100 rows, here i'll show just first 10):

# select * from test order by id asc limit 10;

ok. now, let's check it multi-row updates work (they should, so it's just sanity-check):

# UPDATE test SET pass = 'xx';
UPDATE 100
# SELECT pass, COUNT(*) FROM test GROUP BY pass;
 pass | COUNT
------+-------
 xx   |   100
(1 ROW)

ok., so now let's try to avoid the multi-row updates and deletes.

we will need 2 triggers. both in pl/perl (if you dont like pl/perl or can't use it on your server, dont stop reading, there is another solution below).

first, will create/reset counter of updated/removed rows at the beginning of update/remove:

CREATE OR REPLACE FUNCTION zero_counter()
    RETURNS TRIGGER
    LANGUAGE plperl
    AS
$BODY$
    $_SHARED{'count'} = 0;
    RETURN;
$BODY$;
CREATE TRIGGER zero_counter BEFORE UPDATE OR DELETE ON TEST FOR EACH STATEMENT EXECUTE PROCEDURE zero_counter();

CREATE OR REPLACE FUNCTION limit_modifications()
    RETURNS TRIGGER
    LANGUAGE plperl
    AS
$BODY$
    $_SHARED{'count'}++;
    IF ($_SHARED{'count'} > 1 ) {
        my $msg = sprintf("%s of more than 1 row is forbidden.", $_TD->{'event'});
        die $msg;
    }
    RETURN undef;
$BODY$;
CREATE TRIGGER limit_modifications BEFORE UPDATE OR DELETE ON TEST FOR EACH ROW EXECUTE PROCEDURE limit_modifications();

now. what happens when i do: UPDATE test SET pass = ‘qq'?

first, zero_counter is called (BEFORE STATEMENT), which sets “count" to 0 (in order to prevent using count from previous statements.

then, for every ROW (BEFORE ROW) limit_modifications trigger is called, which:

– increments number of modified rows
– checks if current modifications count is greater than 1. if yes – die()'s – which in pgsql means – raises exsception, and breaks transaction.

how does it work?

# UPDATE test SET pass = 'qq';
ERROR:  error FROM Perl TRIGGER FUNCTION: UPDATE OF more than 1 ROW IS forbidden. at line 5.

multi-row update is correctly blocked.

# UPDATE test SET pass = 'qq' WHERE id = 10;
UPDATE 1

single-row update works without any problems.

# DELETE FROM test;
ERROR:  error FROM Perl TRIGGER FUNCTION: DELETE OF more than 1 ROW IS forbidden. at line 5.

multi-row delete breaks.

# DELETE FROM test WHERE id = 50;
DELETE 1

single-row delete works as a charm.

so basically – table is now protected. of course it might happen that for whatever reason you absolutely need to modify multiple rows.

what to do then?

simple, temporarily block the limit_modifications trigger:

# ALTER TABLE test DISABLE TRIGGER limit_modifications;
ALTER TABLE
# UPDATE test SET pass = 'ee';
UPDATE 99
# ALTER TABLE test ENABLE TRIGGER limit_modifications;
ALTER TABLE

ok,

so now, let's assume you are perlhater. you dont have pl/perl and will never have. you find perl impossible to read, write or use.

what do you do? besides lobotomy?

let's use pl/pgsql.

unfortunatelly pl/pgsql doesn't have equivalent of %_SHARED. we can theoretically use some side table for this purpose, but i'd rather use something else. a trick that AndrewSN (from freenode/#postgresql) showed.

in postgresql.conf we have to find “custom_variable_classes" option, and add new class. let's do it that way:

custom_variable_classes = 'limits'

now we need to reload postgresql.conf – pg_ctl reload.

this means we can have new naming space in settings.

so, now, let's use it in pl/pgsql triggers:

CREATE OR REPLACE FUNCTION zero_counter()
    RETURNS TRIGGER
    LANGUAGE plpgsql
    AS
$BODY$
DECLARE
BEGIN
    PERFORM set_config('limits.test', '0', TRUE);
    IF TG_OP = 'UPDATE' THEN
        RETURN NEW;
    END IF;
    RETURN OLD;
END;
$BODY$;
CREATE TRIGGER zero_counter BEFORE UPDATE OR DELETE ON TEST FOR EACH STATEMENT EXECUTE PROCEDURE zero_counter();

CREATE OR REPLACE FUNCTION limit_modifications()
    RETURNS TRIGGER
    LANGUAGE plpgsql
    AS
$BODY$
DECLARE
    i INT4;
BEGIN
    i := current_setting('limits.test')::INT4 + 1;
    PERFORM set_config('limits.test', i::TEXT, TRUE);
    IF i > 1 THEN
        RAISE EXCEPTION '% of more than 1 row is forbidden.', TG_OP;
    END IF;
    IF TG_OP = 'UPDATE' THEN
        RETURN NEW;
    END IF;
    RETURN OLD;
END;
$BODY$;
CREATE TRIGGER limit_modifications BEFORE UPDATE OR DELETE ON TEST FOR EACH ROW EXECUTE PROCEDURE limit_modifications();

and let's check if it works:

# UPDATE test SET pass = 'qq';
ERROR:  UPDATE OF more than 1 ROW IS forbidden.

# UPDATE test SET pass = 'qq' WHERE id = 10;
UPDATE 1

# DELETE FROM test;
ERROR:  DELETE OF more than 1 ROW IS forbidden.

# DELETE FROM test WHERE id = 50;
DELETE 1

nice. works.

of course you can use limits other than 1 🙂

at the end – this is not a protection against malicious users. this only protects from simple user errors. nothing more. and nothing less.