Re: Drupal and PostgreSQL - performance issues?

--
Med venlig hilsen,

Mikkel Høgh <mikkel(at)hoegh(dot)org>

On 14/10/2008, at 17.28, Ang Chin Han wrote:

> On Tue, Oct 14, 2008 at 10:05 PM, Martin Gainty
> <mgainty(at)hotmail(dot)com> wrote:
> Yes, it's rather braindead. I'd rather not worry about why, but how'd
> we make Drupal use the PostgreSQL more effectively. In it's current
> form (Drupal 5 and 6), it even issues a regexp for every query, even
> before it hits the database because of some design decisions to use
> user definable table prefix as a workaround to the lack of database
> SCHEMA in MySQL: take the follow snippet as a representative Drupal
> code:
>
> $alias = db_result(db_query("SELECT dst FROM {url_alias} WHERE src =
> '%s' AND language IN('%s', '') ORDER BY language DESC", $path,
> $path_language));
>
> That's one sprintf() and a number of string replace operations to
> replace "{url_alias}" with "url_alias", as well as a number of regexp
> to sanitize the query string.

Yeah, good thing this is all going away in Drupal 6 (or much of it, at
any rate), where we are converting to PHP's PDO abstraction layer.

> Note this comment:
> /*
> * Queries sent to Drupal should wrap all table names in curly
> brackets. This
> * function searches for this syntax and adds Drupal's table prefix
> to all
> * tables, allowing Drupal to coexist with other systems in the same
> database if
> * necessary.
> */
> That's an MySQL-ism for working around legacy hosting sites offering
> only a single MySQL db bogging postgresql down...

Yeah, this is one thing I wouldn't mind if they removed. I have never
used that feature, and will probably never do so. Having multiple
applications in the same database is a mess anyways.

> Also betraying MyISAM heritage (in Drupal pgsql driver):
> /**
> * Lock a table.
> * This function automatically starts a transaction.
> */
> function db_lock_table($table) {
> db_query('BEGIN; LOCK TABLE {'. db_escape_table($table) .'} IN
> EXCLUSIVE MODE');
> }
>
> /**
> * Unlock all locked tables.
> * This function automatically commits a transaction.
> */
> function db_unlock_tables() {
> db_query('COMMIT');
> }

Yeah, sadly, our PostgreSQL driver has not historically been
maintained by someone who "knows what's he's doing". Our current
database maintainer, Larry Garfield, has some knowledge of PostgreSQL,
but he's offered pleas for input a couple of times recently. I hope to
gain some more knowledge to help his efforts.

>
>> MG>From what i've been reading the author doesnt work on drupal
>> anymore
>
> Not when Dries founded a commercial venture selling Drupal hosting
> and services.
> http://acquia.com/products-services/acquia-frequently-asked-questions#driesrole

Well, Dries is still the project lead of Drupal, and he's betting his
companys future on it, so I'm quite sure he's doing the best he can :)
>
>> MG>If we could get access to the php source maybe we could fix
>> this..?

Yeah, there's cvs.drupal.org if you're interested :)
>