PostgreSQL

Finally have the conversion working. The big problem was the SQL call to lastval(). It's not a valid thing to be doing when the insert sets the field that would normally get its value from nextval().

A small correction to include/db/postgresql.php:

@@ -1579,7 +1592,7 @@
$forum_where";
$orderby ="
ORDER BY message_id DESC
- LIMIT $length OFFSET start";
+ LIMIT $length OFFSET $start";
$sql = "SELECT * " . $sqlbody . $orderby;
$rows = phorum_db_interact(DB_RETURN_ASSOCS, $sql);

Quote

All I did was to reverse the order of the statements, so the update is attempted first, if it fails, attempt the insert.

I ran into the same problem and tried your fix. It didn't work. What did work is adding a check for the number of rows. If you didn't update at least one row, do the insert.

Got this trying to move a thread:

Sorry, a Phorum database error occurred.
Please try again later!
Error:
ERROR: syntax error at or near "AS" at character 34 (42601): UPDATE IGNORE dchs_user_newflags AS flags, dchs_messages AS msg SET flags.forum_id = msg.forum_id WHERE flags.message_id = msg.message_id AND flags.message_id IN (17277)
Backtrace:
Function phorum_database_error called at
{path to Phorum}/include/db/postgresql/postgresql.php:180
----
Function phorum_db_interact called at
{path to Phorum}/include/db/postgresql.php:5047
----
Function phorum_db_newflag_update_forum called at
{path to Phorum}/include/db/postgresql.php:2121
----
Function phorum_db_move_thread called at
{path to Phorum}/moderation.php:377
----

Ran into a problem with search today. I'll try to fix it myself. Meanwhile, here's the error page:

Phorum Database Error
Sorry, a Phorum database error occurred.
Please try again later!
Error:
ERROR: invalid input syntax for type boolean: "%half%" (22P02): SELECT * FROM yose5_messages WHERE status= 2 AND forum_id in (1) AND body ilike ('%half%' AND '%dome%') AND datestamp >= 1208905654 ORDER BY datestamp DESC LIMIT 30 OFFSET 0
Backtrace:
Function phorum_database_error called at
{path to Phorum}/include/db/postgresql/postgresql.php:180
----
Function phorum_db_interact called at
{path to Phorum}/include/db/postgresql.php:1767
----
Function phorum_db_search called at
{path to Phorum}/search.php:236
----

Quote
Rick
Ran into a problem with search today. I'll try to fix it myself. Meanwhile, here's the error page:

Phorum Database Error
Sorry, a Phorum database error occurred.
Please try again later!
Error:
ERROR: invalid input syntax for type boolean: "%half%" (22P02): SELECT * FROM yose5_messages WHERE status= 2 AND forum_id in (1) AND body ilike ('%half%' AND '%dome%') AND datestamp >= 1208905654 ORDER BY datestamp DESC LIMIT 30 OFFSET 0
Backtrace:
Function phorum_database_error called at
{path to Phorum}/include/db/postgresql/postgresql.php:180
----
Function phorum_db_interact called at
{path to Phorum}/include/db/postgresql.php:1767
----
Function phorum_db_search called at
{path to Phorum}/search.php:236
----

The fix is just a small change to db/postgresql.php.

Change: $match_str = "('%".implode("%' $condition '%", $tokens)."%')";

To: $match_str = "'%".implode("%' $condition body ilike '%", $tokens)."%'";

hey guys..

whats the current state of the postgresql db-layer?

is it running anywhere stable?
if so, I would suggest to finally try backporting it to the phorum source or create a repo
(e..g on github) to maintain the further development.

We have a repository of our own, so no need to do it on github. If there are developers that wish to maintain the PostgreSQL port of the database layer, then we are happy to provide access to our repository to do so. However, up to now, the PostgreSQL development activity has been rather binary (either on or off).

---

Maurice Makaay
Phorum Development Team
my blog linkedin profile secret sauce

Quote
domdorn
hey guys..

whats the current state of the postgresql db-layer?

is it running anywhere stable?
if so, I would suggest to finally try backporting it to the phorum source or create a repo
(e..g on github) to maintain the further development.

IF you have anything new I would love to test it.

I'm very very much interested in a working postgresql layer. I don't have the resources to take the task on wholesale but am willing to work with others on it.

I have a small amount of PHP/phorum experience and a whole lotta SQL/postgresql experience.

This is what I have so far. I've been using it for almost a year for two forums.