Using Perl in PostgreSQL
Pages: 1, 2

Writing Triggers in PL/Perl

Up until PostgreSQL version 8.0, functions like the one above were just about all you could do with PL/Perl. They're useful, but not world-shattering. Version 8.0 gave PL/Perl a major shot in the arm, and more good stuff is coming in version 8.1. From version 8.0, you can do these cool new things:

• Create event handlers (a.k.a. triggers).
• Call back to the database from within PL/Perl, either to fetch rows or perform some action.
• Return a set of values, rather than just one value.
• Return composite types, not just simple types.
• Persist data between function calls and share that data with other functions within the same session.

Triggers can be extremely useful. Users of PostgreSQL and commercial high-end database systems have long used them to solve all sorts of problems that otherwise the client might have to handle (and often unreliably). One common use is for auditing changes. The audit.sql file accompanying this article contains an example of an account transaction table with an attached trigger that logs every change to the table. The output from the script looks like this:

  mod   | user_id |              ts               |                   new_txn                   |                   old_txn                   
--------+---------+-------------------------------+---------------------------------------------+---------------------------------------------
 INSERT |      10 | 2005-07-17 18:50:28.991595-04 | (1,1,1000.00,"initial 'deposit'")           | 
 UPDATE |      10 | 2005-07-17 18:50:29.051915-04 | (1,1,1000.00,"modified: initial 'deposit'") | (1,1,1000.00,"initial 'deposit'")
 DELETE |      10 | 2005-07-17 18:50:29.05932-04  |                                             | (1,1,1000.00,"modified: initial 'deposit'")
(3 rows)

Although there are no transactions in the transactions table, this contains a complete record, or audit trail, of everything that has been done to the table. In particular, it logs what happened, who did it, and when they did it. It doesn't matter what SQL they used; whatever they did to change the transactions table in any way is in the log. This is the sort of accountability that auditors and lawyers love, and it is very easy to achieve with triggers.

There is quite a lot to notice in this very simple example, especially if you usually use a database that doesn't provide all of these features. First, notice that everything goes into a schema called accts. A SQL schema is a namespace, and names of objects only have to be unique within a schema. Moreover, dropping a schema with cascading drops everything inside of the schema, so cleaning up is easy.

Next, notice that the declaration of the function current_sysid() is VOLATILE rather than IMMUTABLE. This is because its value does not depend only on its arguments, and furthermore, its value could potentially change within a single table scan via SECURITY DEFINER (a sort of setuid mechanism).

There is nothing very remarkable about the txn table, but the txn_audit table looks very odd--the new and old fields are of type accts.txn! This uses a new feature in PostgreSQL version 8.0 that allows tables to contain fields of opposite types. Because the name of a table is also the name of a corresponding composite type, you can use a table name as the type of a field, and that field will take exactly the same type of record as the table. This neat arrangement means that no matter how complex the base table, the audit table needs only these five fields. Without this facility, the schema would have to duplicate all of the fields in the base table twice!

No matter what the language, the trigger must be a function that takes no arguments and returns the special type trigger. Trigger functions in PL/Perl receive a special object called $_TD that contains lots of useful information about what caused it to be called. Of particular interest in this function are the event type that causes the trigger to fire and the values of the old and new data. Other interesting values in other circumstances include the table being modified, whether the trigger is called before or after the update, and any arguments passed to it by the trigger setup. The documentation contains a full list. Most of the work of the function consists of constructing an SQL statement to populate the audit table. It takes special care to escape the details fields, both to avoid upsetting the SQL parser and to avoid SQL injection attacks. Ideally, the trigger could use a prepared query instead of much of this, but that feature likely won't arrive until PostgreSQL 8.2.

After constructing the SQL query, the code uses another feature that is new in version 8.0--the ability to call back to the database to perform some action. spi_exec_query() does the work and returns a hash with some information, including a status field. If this is the expected SPI_INSERT_OK value, great! If not, the code returns the value SKIP to Postgres, telling it not to perform the modification to the txn table at all. If the transaction cannot be logged, the table modification should not happen. Another option is to modify the values in $_TD->{new} and return MODIFY. This would have caused PostgreSQL to use the modified value in the insert or update, rather than the value it originally intended to use. Instead of doing either of these things, the code returns undef, which tells PostgreSQL that it can simply proceed with modifying the table, having logged the modification.

Finally, the code calls CREATE TRIGGER to tie the newly created function to all of the events on the txn table.

Conclusion

Triggers are an immensely powerful and useful database tool, and I have only scratched the surface here. Being able to write them in Perl means that if you already know Perl, you don't have to learn a new language just so you can use them. So you get productive in much less time, and with greater comfort.

Part two of this series will look at the other new features of PL/Perl that are available in version 8.0 of PostgreSQL, and part three will look at what is coming in version 8.1 and beyond.

Andrew Dunstan works for a small consulting and software company in the Triangle area of North Carolina, and contributes to PostgreSQL as an enthusiastic hobbyist as well as a sometime professional user.

Return to the Databases DevCenter

Comments on this article

Showing messages 1 through 3 of 3.

• correction
  2007-01-24 13:50:08  oicu [View]
  
  
  The line in the palindrome function that reads:
  
  my ($canonical = lc $arg) =~ s/\s+//g;
  
  should read:
  
  my $canonical = lc $arg;
  $canonical =~ s/\\s+//g;
  
  Thanks to Tom Laudeman for pointing out the error.

• Performance
  2005-11-15 02:20:12  brunorc [View]
  
  
  How about performance? I've built a realtime billing system on the top of PostgreSQL 7.4, but PL/PGSQL triggers were so slow, I had to rewrite everything in C. The speed gain was really big - C triggers were about 20-30 times faster. So, how fast is Perl?
• Performance
  2005-11-15 05:55:04  oicu [View]
  
  
  
  I have not benchmarked it. C triggers are probably always going to outperform anything else, not least because the language handlers for other languages need to perform some glue operations. My instict would be that perl might perform better than PL/PGSQL, and it might not, but in any case is still likely to be slower than C.
  
  That said, perl performance is adequate for many purposes. And even if you find you eventually need to write your triggers in C, it might work well to prototype in perl.
  
  I would be interested to see any test results.