Controlling per-column updates with deny_updates
Posted Wednesday Jun 23rd, 2010 01:36pm
by Alexey Klyukin | Permalink



One of my favorite features of the upcoming PostgreSQL release is conditional triggers. With minimum efforts one can build per-column triggers by adding a column check into the triggering condition. This functionality is already available with Beta 2 of PostgreSQL 9.0. Alas, PostgreSQL doesn't backport features, in order to gain similar functionality in earlier releases you can use deny_updates.

The deny_updates project on PgFoundry contains PL/Perl and PL/PerlU functions that can be installed as triggers to allow or deny certain types of operations. deny_updates function can block updates to individual table columns. In the example below we'll create 2 tables to model the data for an airport timetable:

CREATE TABLE flights (flight_no TEXT PRIMARY KEY, departure TEXT, arrival TEXT);

CREATE TABLE timetable(flight_no TEXT REFERENCES flights ON UPDATE CASCADE, time TIMESTAMP, status TEXT);

Let's populate them with test data:

INSERT INTO flights VALUES('WU917', 'KBP', 'SIP');

INSERT INTO timetable VALUES('WU917', '2010-06-19 16:50', 'pending');

In practice we don't want a flight number in the timetable to be updated for an already existing departure/arrival time and status. With deny_updates we can easily add this constraint to our model:

CREATE TRIGGER deny_flightno_updates ON UPDATE TO timetable FOR EACH ROW EXECUTE PROCEDURE deny_updates('ALLOW_LIST', time, status);

Now all attempts to update the flight number will be denied:

UPDATE timetable SET flight_no='WU918' WHERE flight_no='WU917';

ERROR: error from Perl function "deny_updates": update of attribute 'flight_no' denied by the trigger deny_flightno_updates at line 79.

The value 'ALLOW_LIST' of the first argument indicates that the rest of the argument list contains columns allowed to be updated, and updates to non-listed columns will be denied. To get the opposite, we should leave the first argument empty. Let's use this form to construct a trigger to disallow changes of departure and arrival airports for an already added flight:

CREATE TRIGGER deny_airport_changes ON UPDATE TO flights FOR EACH ROW EXECUTE PROCEDURE deny_updates('', departure, arrival);

In practice, sometimes a trigger function has to check values of the OLD and NEW tuples to decide on allowing or blocking the triggering operation. deny_updates project provides a function called 'allow_on_condition', which does exactly that:

CREATE TRIGGER lock_cancelled_status BEFORE UPDATE ON timetable FOR EACH ROW EXECUTE PROCEDURE allow_on_condition('%s != ''cancelled''', 'OLD.status');

The first argument of allow_on_condition is a condition, which is evaluated to decide whether the trigger operation should be allowed. The '%s' placeholders are replaced in order with arguments starting from the second, just like in printf. Note that these arguments should be quoted as strings, otherwise PostgreSQL won't recognize them as valid literals due to NEW and OLD prefixes.

The function above forbids changing a flight status once it's set to 'cancelled':

UPDATE timetable SET status='cancelled';

UPDATE 1

UPDATE timetable SET status='in flight';

ERROR: error from Perl function "allow_on_condition": expression SELECT E'cancelled' != 'cancelled' AS result is false, UPDATE is not allowed at line 71.

These functions were created by Command Prompt, Inc for Enova Financial, which kindly decided to open-source them. You can download them and/or leave your feedback at the project's page on PgFoundry

---

blog comments powered by Disqus