2009-04-29

Session variables in postgresql

Consider a trigger that would like to prevent certain operations when executed by a web application, while allowing the operations when performed by a nightly batch job.

One way to solve this is to use a kind of session variables. The trigger lets the operation execute normally only if a session variable is set, which the batch job sets at the start of the execution. Below is an example illustrating one implementation for this in PostgreSQL.

First, we define a kind of namespace for our session variable (a customized option or variable class in postgres lingo) in postgresql.conf:

# comma-separated list of class names:
custom_variable_classes = 'myvariableclass'

Now, consider the following table:

CREATE TABLE my_table (ID INTEGER);
INSERT INTO my_table VALUES (1);

A trigger is to prevent DELETE:s from that table unless the session variable myvariableclass.idontcare is set:

CREATE OR REPLACE FUNCTION prevent_delete_unless_idontcare() RETURNS TRIGGER AS $$
DECLARE
idontcare TEXT;
BEGIN
SELECT INTO idontcare CURRENT_SETTING('myvariableclass.idontcare');
IF (idontcare != 'true') THEN
RAISE EXCEPTION 'Not allowed to delete unless myvariableclass.idontcare is true';
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER prevent_delete_unless_idontcare_trigger
AFTER DELETE ON my_table
FOR EACH ROW EXECUTE PROCEDURE prevent_delete_unless_idontcare();

Now let's see it in action:

fornwall=> DELETE FROM my_table;
ERROR: Not allowed to delete unless myvariableclass.idontcare is true
fornwall=> SELECT SET_CONFIG('myvariableclass.idontcare', 'true', FALSE);
set_config
------------
true
(1 row)

fornwall=> DELETE FROM my_table;
DELETE 1

See postgres documentation for the current_setting and set_config functions.

1 comment:

Anonymous said...

In it all business.