The Future of Perl in PostgreSQL
Pages: 1, 2

The Future

Disclaimer: this section reflects my personal views, so please don't take it as in any way an official position by anyone. Here are some ideas that a few people have discussed. In several cases, there have been patches for them which have either not been of acceptable quality, or arrived too late to make the last release. In other cases, they are just ideas that have floated around.

Prepared Queries

This is a very important item and I am very happy to say that we have added this feature for version 8.2 of PostgreSQL.

PL/Perl does not currently have any way to create or call parameterized queries. This makes creating queries a lot less pleasant than it might be, as the audit example in the first article in this series showed. Prepared queries are not only an excellent device to use for programming clarity and ease, but a major defensive tool to use against SQL injection attacks. Any time you see user input being interpolated into an SQL statement, you know you might be dangerous ground.

You can't always use prepared queries in every possible situation where you might need to interpolate data into SQL, so you always have to be a bit careful. In particular, you can't use them to avoid interpolating table names, because PostgreSQL needs that information in order to plan the query in the first place. Some query types can't be prepared at all. Nevertheless, they are extremely useful and will be an important addition to PL/Perl.

Also, prepared queries can be far more efficient--you only need to call the SQL parser once per preparation, rather than once per execution, so if you are using the same query over and over again this can be a big win. A typical situation for this is when using an INSERT statement to create thousands of new rows.

Support PostgreSQL Polymorphic Types

PostgreSQL has two polymorphic pseudo-types, anyelement and anyarray, that let you write functions that work more or less with any actual arguments. This can save lots of work--you might only have to write one function instead of a whole heap of them. PL/Perl does not currently support these polymorphic types, however. I hope it will in the next release.

OUT and IN OUT Parameters

Version 8.1 of PostgreSQL introduced support for OUT and IN OUT parameters, as set out in the SQL standard. (PL/SQL programmers will be very familiar with these.) However, PL/Perl does not currently implement this, so it is an item on our to-do list.

Named Parameters

PostgreSQL version 8.0 introduced support for named function parameters, but PL/Perl ignores the function name. It would be nice to get the glue code to create lexical aliases for the parameters using their supplied names (if any). There are several wrinkles in this, including differences in what PostgreSQL can use as an identifier and what Perl can use, and possible breakage of legacy applications if we are not careful. But I think it's worth trying, at least.

Complete Translation of Array and Record Types

In general, we have tried to ensure that a Postgres composite (that is, a record) type enters PL/Perl as a hashref and leaves as a hashref, and that an array enters PL/Perl as an arrayref and returns as an arrayref. However, there are a few places where we haven't yet completed this--mainly for arrays and composites nested inside of other composites. We need to complete this so that PL/Perl functions never need to construct or deconstruct array and composite literals.

Provide a Complete DBI Handle to Talk to the Database

We would like to provide a full DBI handle to the PL/Perl user, so that programmers who are familiar with using DBI don't have to learn another API. In fact, this has been done, but it has not been kept completely up to date, and has a license that conflicts with core PostgreSQL. We intend to implement this under PostgreSQL's BSD license and include it in PL/Perl, where we can maintain it as part of the core distribution.

Preload Library Modules and Specified Functions

Currently, there is no way to load database functions or library modules when the PL/Perl interpreter begins. That can mean a significant penalty when first calling a function or loading a library. In certain cases we could dramatically lower this penalty, especially if we could get the postmaster to preload the PL/Perl module and then all the functions and libraries, so as to pay this penalty only once instead of once per connection.

This is probably the most speculative of all of the ideas in this section, and still needs lots of thought and discussion.

Not on my List: Direct Function-to-Function Calls

From time to time people ask why PL/Perl functions cannot call one another directly. There are several technical reasons. First, they have no names. In fact, the only reference to them is in an anonymous hash that the glue code holds, but does not make available to the Perl code. Apart from that, there is no guarantee that the interpreter knows the function at all. When PostgreSQL calls a PL/Perl function, it first checks to see if it has compiled the function. If not, it compiles the function before calling it. We can't do that from inside of PL/Perl--for one thing, we would in any case have to call back to the database to get the source code, and trusted functions can't call eval anyway. This is not an easy problem to solve.

In fact, I am not convinced that it is worth solving. If we had nicely pre-loadable modules, especially if we could make them available safely to trusted code, then applications could move most of their features into modules and leave the directly visible functions as a thin veneer over the libraries. This might well reduce any requirement for direct call capability.

Perhaps someone will have an inspirational moment that shows them how to overcome all the difficulties. Until that happens, I for one think that this is one request that is not worth doing.

Summary

PL/Perl has come a long way in quite a short period. Twelve months or so ago, none of the features shown in this series of articles were present in the stable release of PL/Perl. I hope you will agree that those of us who have worked on it have made great progress. Special credit must go to CommandPrompt, Inc. for the trigger, composite, and initial SPI work; to Abhijit Menon-Sen for fixing our memory problems so well; and to David Fetter for documentation support.

I hope too that I have interested you with some of the directions we might be taking, and that you are motivated to try PL/Perl for yourself.

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 O'Reilly Databases