PostgreSQL Fulltext Search

[wagnerch]

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]

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

[jmpoure]

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

Thanks. I am testing your patch.

[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 :
This would eliminate empty words like "the" in english, offer better support in future PostgreSQL updates.
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.
Real estate bubble translates "Bulle Immobilière" because bulle (bubble) is feminin gender.
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]

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

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

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?

[Highway of Life]

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

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

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