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.
wagnerch
Registered User
Posts: 8
Joined: Sun Jul 22, 2007 7:16 pm

Re: PostgreSQL Fulltext Search

Post by wagnerch »

DrewJensen wrote:So - Tried a similar search on a forum I know uses this patch ( postgresqlforums.com ) with a search ( "vacuum -thresholds" )
It works on my site. If you query vacuum you get 45 results, and if you query vacuum -thresholds you get 42 results. The one problem with the mod is that it doesn't deal with "phrases", words that grouped by quotes. In fact, I outright strip the quotes out. In order to support phrases you would have to split the words apart (as I already do) and then feed it through tsearch2, and then do post-processing on it. Probably similiar to what the native engine does.

I need to spend a bit more time and work on it. I was hoping someone else would do it for me. :)

wagnerch
Registered User
Posts: 8
Joined: Sun Jul 22, 2007 7:16 pm

Re: PostgreSQL Fulltext Search

Post by wagnerch »

I added a new version to the first post that should support 8.3.

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

Re: PostgreSQL Fulltext Search

Post by jmpoure »

wagnerch wrote:I added a new version to the first post that should support 8.3.
Thanks. I am testing your patch.

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

Re: PostgreSQL Fulltext Search

Post by jmpoure »

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 »

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 »

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 »

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 »

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 »

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 »

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 2717 times
fulltext_postgres.patch
Patch to update from previous version.
(10.04 KiB) Downloaded 2508 times

Post Reply