July 27th, 2007 by depesz | Tags: | 10 comments »
Did it help? If yes - maybe you can help me?

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;

id pass
1 password for: 1
2 password for: 2
3 password for: 3
4 password for: 4
5 password for: 5
6 password for: 6
7 password for: 7
8 password for: 8
9 password for: 9
10 password for: 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.

  1. 10 comments

  2. Jul 27, 2007

    Excellent piece of code… thanks. By the way: sed ‘s/then/than/’; 🙂

  3. Jul 27, 2007

    thanks – i think i fixed then/than issue.

  4. # Jakob
    Jul 30, 2007

    This is exactly what I expected to find out after reading the title o.us poetry. Thanks for informative article

  5. Jul 30, 2007

    Depesz: tribute to morr’s mailsystem disaster ? 😉

  6. Jul 31, 2007

    @krolik:
    not really. if it’s a tribute, then it’s rather tribute to some anonymous (for me) soul from “tbg” which was setting “vacation” lately.

  7. Jul 31, 2007

    I haven’t tested that technique yet, but it looks as if it would block transactions trying to update just one row in multiple tables to which the triggers are bound.

    For example, if you bind these triggers to 3 tables (in order to protect them from mass-updates), and initiate a transaction, calling:
    BEGIN;
    UPDATE table1 SET field1 = val1 WHERE id = 1;
    UPDATE table2 SET field2 = val2 WHERE id = 2;
    UPDATE table3 SET field3 = val3 WHERE id = 3;
    COMMIT;

    I would expect it to die since it uses a shared counter for the trigger function. It would probably be better to create a hash-table to store one counter per table, and use the table-name as a hash key so you are preventing this.

  8. Jul 31, 2007

    @Xavier:
    no, it doesn’t block the transaction. nor it was ever supposed to do so.
    it is to block multi-row-updates. not multi-row-updateing-transactions.

    if you want to block something like this, it’s doable – let me know, i’ll write it. on the other hand – i don’t really see the point of blocking such transactions.

  9. Jul 31, 2007

    No, I don’t _want_ to do that, I’m just saying that $_SHARED{‘count’} will reference to the same variable across all three tables’ UPDATE statements, and so it could be a problem.

  10. Aug 1, 2007

    @Xavier:
    that’s why there is before statement trigger.

    i really think it would be good if you just tried the code. the problem you’re describing doesn’t exist.

  1. 1 Trackback(s)

  2. Aug 17, 2007: </depesz> » Blog Archive » rownum anyone? cumulative sum in one query?

Leave a comment