PostgreSQL

Re: PostgreSQL

Rick — Mon, 15 Feb 2010 17:10:10 -0600

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

Re: PostgreSQL

chas_martel — Thu, 28 Jan 2010 10:47:52 -0600

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.

Re: PostgreSQL

Maurice Makaay — Wed, 27 Jan 2010 16:44:33 -0600

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).

Re: PostgreSQL

domdorn — Wed, 27 Jan 2010 16:20:03 -0600

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.

Re: PostgreSQL

Rick — Thu, 23 Apr 2009 23:51:55 -0500

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)."%'";

Re: PostgreSQL

Rick — Wed, 22 Apr 2009 18:09:39 -0500

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
----

Re: PostgreSQL

Rick — Sun, 19 Apr 2009 20:27:27 -0500

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
----

Re: PostgreSQL

Rick — Sat, 11 Apr 2009 23:14:33 -0500

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.

Re: PostgreSQL

Rick — Sat, 11 Apr 2009 22:54:39 -0500

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);

Re: PostgreSQL

Rick — Sat, 11 Apr 2009 21:51:47 -0500

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().

Re: PostgreSQL

Rick — Sat, 11 Apr 2009 19:25:18 -0500

Quote

You might have to take the default upgrade script from the "scripts" directory in the Phorum distro and modify that for PostgreSQL.

I'm working my way through it now. Lots of fun ;)

Re: PostgreSQL

Maurice Makaay — Sat, 11 Apr 2009 18:36:21 -0500

PostgreSQL does not spell auto incremental functionality different. It uses sequences for handling the auto increments. There is a simple keyword that one can use at table create time ("serial" IIRC), but under the hood this creates a sequence and set the field's default value to nextval(sequence). So yes, you can accomplish the same kind of thing, but it's more than a different spelling alone.

When you are running Phorum 3 using PostgreSQL, then I'm not sure if there is already a conversion script available. You might have to take the default upgrade script from the "scripts" directory in the Phorum distro and modify that for PostgreSQL. Possibly, somebody has such a script lying around already or can tell us to what extend the existing conversion script can be used. I have no experience with that.

Re: PostgreSQL

Rick — Sat, 11 Apr 2009 16:57:23 -0500

I need to convert a phorum 3 site. Am I correct in assuming I'll have to write the conversion software myself?

Re: PostgreSQL

Rick — Sat, 11 Apr 2009 16:29:09 -0500

Quote

postgres doesn't have things like auto_increment

It does have that feature; it's just not spelled the same.

Quote

NOW() functions that mysql also offers are lacking[/quoet]

Such as?

Re: PostgreSQL

chas_martel — Thu, 05 Feb 2009 11:37:49 -0600

Anyone?

Re: PostgreSQL

DarkKlown — Mon, 02 Feb 2009 23:02:06 -0600

I think the problem is that postgres doesn't have things like auto_increment and some of the NOW() functions that mysql also offers are lacking (or aren't seen as important by the psql developers). Also for interger fields you must actually put '0' and not '' like in mysql where it thinks (hey '', that's 0).

After porting over my database and making lots of changes to sql all over the place i kinda moved on, and instead focused efforts at caching query results for the speed boost.

Re: PostgreSQL

chas_martel — Mon, 02 Feb 2009 22:18:50 -0600

OK, here is a follow up. I have attempted to use Postgresql with Phroum and find
that upon trying to create the admin user -right after the tables are made- I get an error
that seems to be saying that fields which are defined as int's can't be set because they
have paren's around the values. Below is the error displayed in my browser.

I am willing to work thru this and/or other Postgresql issues and contribute fixes if warranted. I only ask for a little guidence.

Also, I am working from head as of a few days ago. I have also tried the two other postgresql.php files
from Dan's site, both exhibit the same behaviour.

Thanks,

Here is the warning
======================================================================

Phorum Database Error
Sorry, a Phorum database error occurred.
Please try again later!
Error:
ERROR: invalid input syntax for integer: "" (22P02): UPDATE ph__users SET username = '', password = '', email = '', active = '', admin = '', date_added = '', date_last_active = '', password_temp = '', display_name = '' WHERE user_id = 3
Backtrace:
Function phorum_database_error called at
{path to Phorum}/include/db/postgresql/postgresql.php:168
----
Function phorum_db_interact called at
{path to Phorum}/include/db/postgresql.php:3623
----
Function phorum_db_user_save called at
{path to Phorum}/include/db/postgresql.php:3527
----
Function phorum_db_user_add called at
{path to Phorum}/include/api/user.php:721
----
Function phorum_api_user_save called at
{path to Phorum}/include/admin/install.php:191
----
Function require_once called at
{path to Phorum}/admin.php:101
----

Re: PostgreSQL

Brian Moon — Fri, 19 Dec 2008 23:47:56 -0600

Well, don't worry. As soon as Drizzle is ready, Phorum will have support for it. I am on the mailing list and am sort of a fly on the wall voice for the power users.

Re: PostgreSQL

DarkKlown — Fri, 19 Dec 2008 16:38:17 -0600

adodb is indeed slow but adodb-lite is alot better, also with the php-adodb extension things speed up alot more. I understand your reasoning however, the main reason i choose phorum over other message systems was purely based on how quick it is it.

I'm just starting to get uncomfortable with all the people movements happening at mysql and the rumor they are going to be spending less devel time on the free version of mysql to focus on enterprise. Personally i don't have $3,000 a year to spend on a database (tho the MySQL Query Analyzer in enterprise monitor does look sexy), also table size is starting to become a issue with performance and the stats show postgres to be able to cope so it would be nice to move that way depending on how much work would be required (i'm rather lazy).

I've not really had a play with PDO so i'll have a go with that.

Re: PostgreSQL

Brian Moon — Fri, 19 Dec 2008 13:49:25 -0600

Quote
DarkKlown
Rather than trying to back phorum into just postgresql why not back it into something like adodb/adodblite and let users choose what database they wish to run. As long as the sql is kept strict it should then work in just about anything and then you don't need a dedicated 'postgresql' programmer/supporter you just support the adodb layer? (I'm currently looking at backing phorum into either oracle or postgresql and will probably do that via adodb)

Because we want it to work. Because we want it to work faster than a snail in the snow. Because we want to use SQL that is not super lame-o.

"Standard" SQL is a myth. Even if you use adodb, the SQL you write will either be crippled or will not work on other RDBS. Phorum 3 had LOTS of "if(mysql)" and "if(postgresql)" in it. It was quite nasty.

If you are bound and determined to use something other than native functions, please, oh please, use PDO. PDO will not kill your performance like adodb will. Every line of code you put between your call and mysql_query, pg_query or whatever makes things slower.

See:

[www.phorum.org]
[www.phorum.org]

While I am at it, prepared statements suck too. Just so we get that in there.

Ok, my rant is over now. I feel better.

Re: PostgreSQL

DarkKlown — Thu, 18 Dec 2008 20:23:59 -0600

Rather than trying to back phorum into just postgresql why not back it into something like adodb/adodblite and let users choose what database they wish to run. As long as the sql is kept strict it should then work in just about anything and then you don't need a dedicated 'postgresql' programmer/supporter you just support the adodb layer? (I'm currently looking at backing phorum into either oracle or postgresql and will probably do that via adodb)

Re: PostgreSQL

Brian Moon — Mon, 01 Dec 2008 11:14:13 -0600

Quote
DJ Maze

SET SESSION sql_mode = 'TRADITIONAL,ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO,PIPES_AS_CONCAT'

Or set sql_strict_mode to 1 in the config file.

Re: PostgreSQL

DJ Maze — Sun, 30 Nov 2008 06:23:21 -0600

There's a trick you can use while developing in MySQL and do keep any ISO database into account.

SET SESSION sql_mode = 'TRADITIONAL,ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO,PIPES_AS_CONCAT'

It makes MySQL behave almost like all other databases that run conform ANIS/ISO SQL Standards.

Will setup a testbed here

Re: PostgreSQL

Maurice Makaay — Thu, 25 Sep 2008 16:37:44 -0500

Normally, an error makes the system stop, since errors should never occur. But in the core of the database layer, a system is used to let the core function (phorum_db_interact()) ignore certain errors. This way, the main db layer code can tell the interact function to ignore duplicate key errors (which would be needed for doing the settings updates or inserts like they are implemented now).

My guess is that the functionality for ignoring duplicate key errors is not implemented correctly (maybe it's using an incorrect error code to check against for this case). The file in which this is implemented, would be include/db/postgresql/postgresql.php.

Reversing the order might work, but it is incorrect. Two concurrent Phorum processes might run into a race condition:

 process 1           process 2

 TRY UPDATE
     |
     |               TRY UPDATE
     v                    |
UPDATE FAILS              |
     |                    v
     |              UPDATE FAILS
     v                    |
 TRY INSERT               |
     |                    v
     |                TRY INSERT
     v                    |
 INSERT OK - - - - - - - >| duplicate key error
                          v
                     INSERT FAILS

When doing it the other way around, it is a safe operation:

 process 1           process 2

 TRY INSERT
     |
     |               TRY INSERT
     v                    |
  INSERT OK - - - - - - ->| duplicate key error
                          v
                    INSERT FAILS
                          |
                          |
                          v
                      TRY UPDATE
                          |
                          |
                          v
                      UPDATE OK

Re: PostgreSQL

nefarious — Thu, 25 Sep 2008 16:27:45 -0500

I too just went through the pain of getting postgresql to work with Phorum. Using the above I was able to get it working. Like MythMonk mentioned things seemed to be working but the admin section settings weren't saving. What I found was that Dan's postgresql.php file has an error in: phorum_db_update_settings() function (include/db/postgresql.php is the file). The code attempts an insert initially and if an error is detected, it then attempts an update. I found that once the settings exist, the insert throws and error, but it doesn't make it back to phorum_db_update_settings() so it merrily completes (without calling the update). All I did was to reverse the order of the statements, so the update is attempted first, if it fails, attempt the insert. This seems to be working. I haven't had time to figure out why the error didn't make it to the caller.

Mike

Re: PostgreSQL

MythMonk — Sat, 13 Sep 2008 21:09:24 -0500

Yeah, all you have to do is unpack the the archive from Dan's site into your Phorum install, and create your config.php appropriately.

One thing I ran across while setting up a production site with this today, was that some of the admin stuff is broken - ie, everything under the "Global Settings" header on the admin page won't save. I'm working on this issue right now. If I get it working satisfactorily, I'll post updates.

Re: PostgreSQL

chas_martel — Wed, 03 Sep 2008 10:14:51 -0500

Quote
MythMonk
Other than that, so far it's working with Phorum 5.2.8.

Can I ask a "hopefully" simple question? Do you just get the postgresql specific files from
head and place them into the appropriate directories of a 5.2.8 install?

Re: PostgreSQL

Dan Langille — Sun, 10 Aug 2008 16:23:17 -0500

Like Maurice said, someone has to step up.

I did some work quite some time ago, but I don't have the time to keep it up to date.

Re: PostgreSQL

Maurice Makaay — Sun, 10 Aug 2008 07:36:16 -0500

If a postgresql/php developer stands up and can provide code and full time support, then I see no reason why it could not be a part of the core Phorum releases. Right now it is based on drive-by support, so there is nobody working on it full time.

If one of the developers would switch to postgresql, then this would change things of course, but right now we all are using MySQL 5. The most likely dev to switch to PostgreSQL would be me, since I do a lot of my corporate database development in PostgreSQL. However, up to now I haven't seen a real need for PostgreSQL on my live Phorum installs.

Re: PostgreSQL

chas_martel — Sat, 09 Aug 2008 21:52:40 -0500

Hello,

I have a question about postgresql support. I notice in this thread there is
a comment that postgresql works in 5.2.8, there is also a note about a bug
fix related to the port that the DB might use. BUT, I look into SVN trunk
and see the lastest version is 7 months old. What is up with that?
Where is the lates postgresql stuff?

Also, will postgresql support ever be official? That is will it ever be
in a mainline release?

Thanks to the community for all the hard work. I've spent the last month
really learning about phorum and look forward to switching from phpBB.