After understanding transactions and locking, it is time to focus on writing proper procedures to avoid widespread problems and known issues. The goal is to cover issues that bug many people around the globe. This chapter will introduce some of the most common pitfalls when it comes to writing procedures for PostgreSQL.
The following topics are on our agenda:
- Choosing the right language
- Managing procedures and transactions
- Optimizing procedures for indexing
- Avoiding security problems
- Controlling memory
All of these topics will help you to make sure that your procedures offer high performance at low risk.
One of the cool features of PostgreSQL is the privilege for you to write your stored procedures in almost any language you want. Some languages such as SQL, PL/pgSQL, PL/Perl, PL/Python, and PL/Tcl are shipped along with the core of the system. You can also add other languages such as PL/sh, PL/v8, and many more to PostgreSQL to make sure that you always have the language of your choice at hand when you want to write a procedure.
Tip
The listing at https://wiki.postgresql.org/wiki/PL_Matrix contains an overview of all procedural languages available.
To enable a procedural language, you can call CREATE LANGUAGE.
test=# h CREATE LANGUAGE
Command: CREATE LANGUAGE
Description: define a new procedural language
Syntax:
CREATE [ OR REPLACE ] [ PROCEDURAL ] LANGUAGE name
CREATE [ OR REPLACE ] [ TRUSTED ] [ PROCEDURAL ] LANGUAGE name
HANDLER call_handler [ INLINE inline_handler ] [ VALIDATOR valfunction ]To enable PL/Perl, just call this command:
CREATE LANGUAGE plperl;
If PL/Perl is installed, this will work, and you can write procedures in Perl from now on.
If you decide to add a language, it definitely makes sense to check out the documentation of the language that should be installed and act accordingly.
When deciding on a procedural language, you should keep one thing in mind: you have to decide whether to use a trusted or an untrusted language. What does this actually mean? If you load, for example, Perl in untrusted mode (plperlu), the external language is allowed to do anything. This poses a real security threat because a user writing a procedure can actually delete files, send e-mails, or do any other nasty stuff. Of course, in a secure environment, this is not desirable at all. Therefore, it is better to restrict Perl to the so-called taint mode, which does not provide system calls. It offers only the basics of the language and bans any interaction with the outside world. For procedural languages embedded inside PostgreSQL, this is definitely the way to go. Untrusted Perl should only be used in rare, exceptional cases.
Not all programming languages provide you with such a choice. Languages such as PL/sh and the like are available only in untrusted mode, making them unsuitable for ordinary users who do not have superuser permissions.