PostgreSQL Fulltext Search

[Oleg]

What is required in order for postgresql fulltext search to be accepted into phpbb proper?

[naderman]

Well the patch itself would need to have a few formatting issues solved. Other than that I don't really see anything standing in its way. We should probably check that it really works on all supported PHP versions etc., phpBB with PostgreSQL is generally not tested enough. Some database unit tests would certainly be nice, but not a requirement I would say. And then the question is, is there any good reason to include this in 3.0, and not to add it in 3.1?

[bantu]

Yes, would be cool if we could get this into 3.1.x.

Or even 3.0.x, but only if it doesn't change anything in the architecture and works as a plugin.

[Oleg]

I took the patches posted in this topic, committed them sequentially and then fixed obvious style issues (per my reformatter).

Remaining to be done:

- Readme file needs to be moved into acp help text
- Sql file needs to be moved in the code somewhere, possibly similar to how mysql native search does its setup
- Setup and functionality needs to be tested on the 6 or 7 postgresql versions phpbb supports
- Consider how this code would survive upgrading postgres from e.g. 8.0 to 8.4

http://github.com/p/phpbb3/compare/deve ... ext-search

[Oleg]

PostgreSQL 8.2 has full text search in the tsearch2 module.

PostgreSQL 8.3 integrates tsearch2 into the core: "TSearch2, our cutting-edge full text search tool, has been fully integrated into the core code, and also has a cleaner API." [15]

- http://www.wikivs.com/wiki/MySQL_vs_Pos ... d_Indexing

[jmpoure]

Many thanks! Our website runs Phpbb3 with full-text search patch and works like a charm. This is PostgreSQL 9.0. The database is 3,7 Go (postgreSQL text dump). We have more than 1.000.000 messages. Will report back after we upgrade using GIT head. All queries run in 3/4 ms. I hope that you can integrate this patch in PhpBB core soon.

[TolicH]

There's small mistake with database version detection. To get work on PostgreSQL 9.0 apply this patch:

Code: Select all

--- a/fulltext_postgres.php       2011-06-03 02:56:49.886233637 +0400
+++ b/fulltext_postgres.php       2011-06-03 02:57:51.657165419 +0400
@@ -60,7 +60,7 @@
                if ($db->sql_layer == 'postgres')
                {
                        $pgsql_version = explode('.', substr($db->sql_server_info(), 10));
-                       if ($pgsql_version[0] >= 8 && $pgsql_version[1] >= 3)
+                       if ($pgsql_version[0] > 8 || ($pgsql_version[0] == 8 && $pgsql_version[1] >= 3))
                        {
                                $this->tsearch_builtin = true;
                        }

[mithnae]

As of PostgreSQL 9.0 there should be an explicit cast to regname when providing to_tsvector() and to_tsquery() function with FTS configuration name as in:

Code: Select all

                $tmp_sql_match[] = "to_tsvector ('" . $db->sql_escape($config['fulltext_postgres_ts_name']) . "'::regname, " . $sql_match_column . ") @@ to_tsquery ('" . $db->sql_escape($config['fulltext_postgres_ts_name']) . "'::regname, '" . $db->sql_escape($this->tsearch_query) . "')";

Otherwise there could be a problem with using indices.

[Oleg]

According to http://www.postgresql.org/docs/9.1/stat ... ation.html we might need to require postgresql 8.3 for the full text search.

[l3on]

Hi!

WIth postgres 9.1 and phpBB 3.0.10 full_text seems works great. The only change I did was a "s/regname/regconfig/g " on the file.

Thanks a lot, you saved our life (4M of posts, 500K topics, 150K users, 4,5GB)!