PostgreSQL Fulltext Search

Discuss features as they are added to the new version. Give us your feedback. Don't post bug reports, feature requests, support questions or suggestions here.
Forum rules
Discuss features as they are added to the new version. Give us your feedback. Don't post bug reports, feature requests, support questions or suggestions here. Feature requests are closed.
Oleg
Posts: 1150
Joined: Tue Feb 23, 2010 2:38 am
Contact:

Re: PostgreSQL Fulltext Search

Post by Oleg »

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

User avatar
naderman
Consultant
Posts: 1727
Joined: Sun Jan 11, 2004 2:11 am
Location: Berlin, Germany
Contact:

Re: PostgreSQL Fulltext Search

Post by 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?

User avatar
bantu
3.0 Release Manager
3.0 Release Manager
Posts: 557
Joined: Thu Sep 07, 2006 11:22 am
Location: Karlsruhe, Germany
Contact:

Re: PostgreSQL Fulltext Search

Post by 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
Posts: 1150
Joined: Tue Feb 23, 2010 2:38 am
Contact:

Re: PostgreSQL Fulltext Search

Post by 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
Posts: 1150
Joined: Tue Feb 23, 2010 2:38 am
Contact:

Re: PostgreSQL Fulltext Search

Post by 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
Registered User
Posts: 81
Joined: Sun Mar 11, 2007 9:42 am

Re: PostgreSQL Fulltext Search

Post by 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
Registered User
Posts: 1
Joined: Wed Oct 28, 2009 10:26 pm

Re: PostgreSQL Fulltext Search

Post by 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
Registered User
Posts: 1
Joined: Thu Jul 21, 2011 11:48 am

Re: PostgreSQL Fulltext Search

Post by 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
Posts: 1150
Joined: Tue Feb 23, 2010 2:38 am
Contact:

Re: PostgreSQL Fulltext Search

Post by 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
Registered User
Posts: 1
Joined: Mon Apr 30, 2012 11:30 am

Re: PostgreSQL Fulltext Search

Post by 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)!

Post Reply