Posted by Anonymous on April 3, 2010 at 9:12am
hi,
i've created this module:
http://drupal.org/project/settings_audit_log
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.
Comments
support patch just landed
thanks to steve, we now have postgres support. more reviews welcome.
Watch out for tenary comparisons
Notice the expressions in the Trigger:
NEW.value != OLD.value
These do not behave as expected if either value is null.
Use:
NEW.value IS DISTINCT FROM OLD.value
instead.
Here is a reference: http://archives.postgresql.org/pgsql-sql/2010-05/msg00027.php
just asked the folk in
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
Hi--
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.
Thanks,
Steve
Steven C. Buttgereit
Managing Director
Muse Systems
www.musesystems.com
good news
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.
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
www.musesystems.com