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

Re: PostgreSQL Fulltext Search

Post by jmpoure » Thu Jun 05, 2008 9:18 am

Good patch as usual. Works on our 500.000 posts forum.

Could you comment on your technical choices?
Why do you let Php parse messages instead of using PostgreSQL full-text parsing function ?

Basically, PostgreSQL full-text search documentation explains how parsing queries work:
SELECT plainto_tsquery('english', 'The Fat Rats');
plainto_tsquery
-----------------
'fat' & 'rat'
Using PostgreSQL parsing functions seem more straigthforward :
:arrow: This would eliminate empty words like "the" in english, offer better support in future PostgreSQL updates.
:arrow: Also, I suspect a better support on PostgreSQL side for accentuated languages.

Let me give an example:

In French, real-estate is "immobilier". Immobilier is a noun, but it can also be an adjective.
:arrow: Real estate bubble translates "Bulle Immobilière" because bulle (bubble) is feminin gender.
:arrow: Real estate crash translates "Krach immobilier" because krach (crash) is masculin gender.

So how does PostgreSQL handle search ?
SELECT plainto_tsquery('french', 'krach immobilier');
'krach' & 'immobili'
SELECT plainto_tsquery('french', 'bulle immobilière');
'bull' & 'immobili'
So in the case of 'immobilier', PostgreSQL will seach for all occurences (immobilier, immobilière, immobilières, immobiliers), in masculin, feminin, plurar forms using "immobili". For this PostgreSQL has some grammar knowledge and is able to use dictionaries.

I tried to look for "immobilier" on our forum. It only returns matches for "immobilier" and not "immobilière".
In the case of my forum, I probably loose 50% search matches, which is enough to decide to let PostgreSQL do the parsing work.

In my mind, the very minimum would be to pass the initial seach query to PostgreSQL using plainto_tsquery and then trim the result removing ' and &.
Then you can remove a lot of your code on the Php parsing side and let PostgreSQL do the job.
The most simple the patch, the better ;)

What is your point of view?

Otherwize, this patch should be integrated into PhpBB next release.

I really recommend this patch to any PostgreSQL 8.3 user with medium to very large forum.
It really cuts down CPU usage as PostgreSQL does most processing itself.

Thanks for your work.

Kind regards,
Jean-Michel

jmpoure
Registered User
Posts: 81
Joined: Sun Mar 11, 2007 9:42 am

Re: PostgreSQL Fulltext Search

Post by jmpoure » Mon Mar 09, 2009 11:23 am

Is full-text search for PostgreSQL part of Phpbb3 main release?

system11
Registered User
Posts: 1
Joined: Mon Jun 22, 2009 8:02 am

Re: PostgreSQL Fulltext Search

Post by system11 » Mon Jun 22, 2009 8:19 am

This might be just what I needed - our board is projected to take 24 hours to rebuild the search index with native phpBB search, I wish it was in the main distribution - I'll try it out later.

we3b
Registered User
Posts: 3
Joined: Tue Jan 27, 2009 10:18 pm

Re: PostgreSQL Fulltext Search

Post by we3b » Fri Jan 08, 2010 5:17 pm

After update phpBB to 3.0.6 I have some errors with keyword_search() function, for example:

Code: Select all

SQL ERROR [ postgres ]

ERROR: syntax error at end of input at character 258 []

SQL

SELECT p.post_id FROM posts p WHERE (to_tsvector ('english', p.post_subject) @@ to_tsquery ('english', 'something to search ') OR to_tsvector ('english', p.post_text) @@ to_tsquery ('english', 'something to search')) ORDER BY p.post_time DESC LIMIT 250 OFFSET Array

BACKTRACE

FILE: includes/db/postgres.php
LINE: 177
CALL: dbal->sql_error()

FILE: includes/db/postgres.php
LINE: 232
CALL: dbal_postgres->sql_query()

FILE: includes/db/dbal.php
LINE: 170
CALL: dbal_postgres->_sql_query_limit()

FILE: includes/search/fulltext_postgres.php
LINE: 476
CALL: dbal->sql_query_limit()

FILE: search.php
LINE: 490
CALL: fulltext_postgres->keyword_search()
I have got PostgreSQL 8.4.2. Does anyone have the same problem? Any tip to fix this? Pages with search results like unanswered and unread posts work correctly. I think it might be a problem with $tmp_sql_match variable. Am I right?

User avatar
Highway of Life
Registered User
Posts: 1399
Joined: Tue Feb 08, 2005 10:18 pm
Location: I'd love to change the World, but they won't give me the Source Code
Contact:

Re: PostgreSQL Fulltext Search

Post by Highway of Life » Fri Jan 08, 2010 9:13 pm

Have you tried asking for support over at the main boards?
http://www.phpbb.com/community/viewforum.php?f=46
Image

we3b
Registered User
Posts: 3
Joined: Tue Jan 27, 2009 10:18 pm

Re: PostgreSQL Fulltext Search

Post by we3b » Sat Jan 09, 2010 11:46 am

Not yet. I've tried to solve this problem oneself. Currently without any results. I'll install phpBB 3.0.5 and check on it, to be 100% sure.

we3b
Registered User
Posts: 3
Joined: Tue Jan 27, 2009 10:18 pm

Re: PostgreSQL Fulltext Search

Post by we3b » Wed Feb 10, 2010 10:31 am

The problem was in keyword_search() function. It has been expecting one parameter more - $author_name and $start becomes an array, not integer as should be.

Version 1.49
Updated search by username. Now it works :)
Attachments
fulltext_postgres.zip
Updated file.
(6.32 KiB) Downloaded 1872 times
fulltext_postgres.patch
Patch to update from previous version.
(10.04 KiB) Downloaded 1730 times

Oleg
Posts: 1150
Joined: Tue Feb 23, 2010 2:38 am
Contact:

Re: PostgreSQL Fulltext Search

Post by Oleg » Tue Mar 16, 2010 9:04 am

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: Karlsruhe, Germany
Contact:

Re: PostgreSQL Fulltext Search

Post by naderman » Tue Mar 16, 2010 3:52 pm

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 » Tue May 25, 2010 1:55 pm

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.

Post Reply