Mikkel Høgh

Coding the web since 1999

09 Aug 2010

A tip for using PostgreSQL with Drupal 6

If you are using PostgreSQL for hosting your Drupal sites, you might have noticed a lot of warnings in your logs like these:

Aug  8 18:41:05 s002 postgres[90076]: [5-1] WARNING:  nonstandard use of \\ in a string literal at character 32
Aug  8 18:41:05 s002 postgres[90076]: [5-2] HINT:  Use the escape string syntax for backslashes, e.g., E'\\'.
Aug  8 18:41:05 s002 postgres[90076]: [6-1] WARNING:  nonstandard use of \\ in a string literal at character 122
Aug  8 18:41:05 s002 postgres[90076]: [6-2] HINT:  Use the escape string syntax for backslashes, e.g., E'\\'.

The immediate cause for this is bug #426008 in Drupal core, but the issue stems from the fact that PostgreSQL does not conform exactly to the SQL standard with regards to backslashes in strings. The reasoning behind this and why its going away (as a default setting) in PostgreSQL 9.1 can be read in this excellent blog post by Bruce Momjian.

But how do I fix it?

The good news is that this behavior is configurable. You can set standard_conforming_strings = on in your postgresql.conf and be done with it. This will be the default setting from PostgreSQL 9.1, and hopefully the other applications using your database do not depend on the legacy behavior (if they do, they need fixing).

If that’s not suitable for your setup, there is a few other suggestions in this forum thread.