My Drupal 6.22 site runs on PostgreSQL 8.x database and httpbl module gives these errors:
Warning: pg_query() [function.pg-query]: Query failed: ERROR: syntax error at or near "REPLACE" LINE 1: REPLACE httpbl (hostname, status, expire) VALUES ('213.164.1... ^ in [.....]/httpdocs/includes/database.pgsql.inc on line 139
The line 677 in httbl.module is causing this problem, since in PostgreSQL there is not "Replace" command:
db_query("REPLACE {httpbl} (hostname, status, expire) VALUES ('%s', %d, %d)", $ip, $status, time() + $offset);
| Comment | File | Size | Author |
|---|---|---|---|
| #6 | httpbl-postgresql-support-807696-6.patch | 1.33 KB | bendiy |
| #2 | httpbl.module.patch | 1.04 KB | alpapan |
Comments
Comment #1
praseodym commentedProblem is that fixing this bug by using two queries instead causes a conflict with #723358: PHP Warning: Duplicate entry <snip> for key 1\nquery: INSERT INTO httpbl (hostname, status, expire) VALUES <snip>. If you can come up with a solution that works on both PostgreSQL and MySQL, it'd be very helpful.
Comment #2
alpapan commenteduse db_lock_table
a
Comment #3
praseodym commentedThanks for the patch. Have you tested this in production? I'm afraid that table locking for common operations like this one will have a severe performance impact.
Comment #4
alpapan commentedMight be a bit better to be a bit slow than not working at all?
Comment #5
praseodym commentedSince this code is executed on every page view, we simply cannot afford waiting for locks. That's simply killing for performance.
Comment #6
bendiy commentedAttached is a new patch that should address this issue for both PostgreSQL and MySQL. It is based on this approach for a PostgreSQL REPLACE:
http://stackoverflow.com/questions/1109061/insert-on-duplicate-update-po...
I see no reason why this would be any slower as a REPLACE is basically doing the same thing.
Comment #7
ben coleman commentedI tested the patch from #6 on both MySQL 5 and PostgreSQL 9.1, and it works on both.
Comment #9
bryrock commentedThis has been committed. Should appear in next 6.x-2.x-dev.