looking for help porting a trigger to postgresql

beejeebus's picture


i've created this module:


which uses a trigger to watch the variable table, and i need help porting it to postgresql.

please drop by the issue queue if you can help.


support patch just landed

beejeebus's picture

thanks to steve, we now have postgres support. more reviews welcome.

Watch out for tenary comparisons

richard.broersma's picture

Notice the expressions in the Trigger:

NEW.value != OLD.value

These do not behave as expected if either value is null.




Here is a reference: http://archives.postgresql.org/pgsql-sql/2010-05/msg00027.php

just asked the folk in

beejeebus's picture

just asked the folk in #postgresql how to do a requirements check for plpgsql on the drupal db. seems i need to query pg_catalog.pg_language but i have no clue if its common for the web user to have access to that. anyone here know if the web user can access that on most postgres installs?

yes, for read only

sbuttgereit's picture


Yes. The permissions for SELECT against pg_catalog.pg_language are granted to 'PUBLIC' by default, which on PostgreSQL means granted to everyone by default. Naturally, anything other than SELECT is not granted. So for instance, to do the pseudo-session ID thing in the trigger in the patch I submitted, I call pg_catalog.pg_stat_activity to get the back-end start time for the session. Now pg_stat_activity is a view in the pg_catalog schema, but the permissions on the tables in pg_catalog are designed to allow queries from regular users.... just not changes.


Steven C. Buttgereit
Managing Director
Muse Systems

good news

beejeebus's picture

cool, that's good to hear.

want to have a look at the requirements stuff i added? look about right?

how common is it for a web user account to be able to run db_query('CREATE LANGUAGE plpgsql'); ? perhaps we should run that in hook_requirements, and only error out if it fails?

Can't do that.

sbuttgereit's picture

Sure I can take a look. A typical web user database account would not be able to use the 'CREATE LANGUAGE' command, it requires database superuser privileges. Having said that, most of the PostgreSQL instances I see have been set up with PL/pgSQL installed into one of the so-called template databases as part of the PostgreSQL installation procedure... in PostgreSQL, a new database is a copy of a template database (there are a couple by created by default). So when a new database is created using a template that includes PL/pgSQL, the new database will have the language installed.

Given the relative ubiquity of the language, I think you'll be OK with a simple requirements check. In those cases where the language wasn't installed it's really easy to install and retry. The biggest issue will be some shared hosting providers that don't offer access to a PostgreSQL superuser account... but even then those types of providers will likely be offering MySQL and not PostgreSQL, and if they do offer PostgreSQL, they really, really should have PL/pgSQL installed into their default template anyway.

In the next major release of PostgreSQL (PostgreSQL 9) PL/pgSQL will be installed by default. I know that doesn't help now, but the problem will go away over time.

Steven C. Buttgereit
Managing Director
Muse Systems