PostgreSQL and Drupal conflict

We have been running Drupal for some time now and we have noticed that it generates a very large amount of warnings in our log files.

The warning is in link with improperly formatted strings. PostgreSQL tries to follow the SQL specification to the letter and that means you cannot use the backslash character to escape special character sequences (such as \n for a newline character.)

I knew that in most cases the error was generated because of the function saving a full page or some other content in the cache. In that case, the system includes the characters: \012 and \015 (\n and \r.) That generates a warning in the log file (2 lines.) This is because the cache uses a serialization of the data. Note that many other parts of Drupal use a serialization, so the problem is not limited to the cache. Plus, the fix has to be in the PostgreSQL database file because MySQL is not affected.

The following is our current fix:

File: includes/database.psql.php
Function: _db_query()
Line: Just before $last_result = pg_query($active_db, $query);

  //{m2osw
  // Make sure the query does not generate an error every time
  // there is a backslash in a string
  //
  // Note: I first tried with a preg_replace(). However, very large
  // buffers just fail. There is most certainly a way to make the
  // regular expression work, even with large strings, but I was
  // not trying to debug libregex.so...
  //$query = preg_replace("/'((?:[^']|'')+)'/", "E'\\1'", $query);
  $max = strlen($query);
  $idx = 0;
  $q = '';
  while ($idx < $max) {
    $pos = strpos($query, "'", $idx);
    if ($pos === FALSE) {
      $q .= substr($query, $idx);
      break;
    }
    $q .= substr($query, $idx, $pos - $idx);
    $idx = $pos + 1;
    $s = "'";
    while ($idx < $max) {
      $pos = strpos($query, "'", $idx);
      if ($pos === FALSE) {
        // there is a bug!?
        break;
      }
      $s .= substr($query, $idx, $pos + 1 - $idx);
      $idx = $pos + 1;
      if ($idx >= $max || $query[$idx] != "'") {
        break;
      }
      // single quote in the string
      $s .= "'";
      ++$idx;
    }
    if (strpos($s, '\\') !== FALSE) {
      // Escape required
      $q .= 'E' . $s;
    }
    else {
      $q .= $s;
    }
  }
  $query = $q;
  //m2osw}

This change eliminates the warnings making the PostgreSQL log useful again.

I want to post a patch later, nudge me if you want it and I did not post it yet.

Thank you.
Alexis