Add support for postgresql-based full-text search

[vibhi19]

I use search feature of gled fork alot, Is there any way i can use it here on Glitch soc for my self?

[rhaamo]

The search feature depends on additional sql queries and code who depends on those ones. However if you maintain you own repository where you manually patch glitch and then merge it frequently it will works fine. That’s what I’m doing. I can provide an up to date patch if wanted.

[vibhi19]

The search feature depends on additional sql queries and code who depends on those ones. However if you maintain you own repository where you manually patch glitch and then merge it frequently it will works fine. That’s what I’m doing. I can provide an up to date patch if wanted.

That would be awesome. Can i have a link to your fork

[rhaamo]

My fork might have some features you don't want, and I won't be maintaining it indefinitely for some reasons.

You should be able to use https://gist.sigpipe.me/dashie/a84a976a13ff6ae97da494fc7a657d10221e7368 as a patch to apply to glitch-soc, worst case manually applying it isn't that hard.

The only thing is you have to maintain your own fork and merge glitch-soc in it.

Unless someone make a better patch with config option (because the patch override the search using elasticsearch)

[vibhi19]

My fork might have some features you don't want, and I won't be maintaining it indefinitely for some reasons.

You should be able to use https://gist.sigpipe.me/dashie/a84a976a13ff6ae97da494fc7a657d10221e7368 as a patch to apply to glitch-soc, worst case manually applying it isn't that hard.

The only thing is you have to maintain your own fork and merge glitch-soc in it.

Unless someone make a better patch with config option (because the patch override the search using elasticsearch)

Thanks, I am not a dev should i do this?

[vibhi19]

@ThibG Can you add this as opt in?

[ClearlyClaire]

I have to figure out whether this is the solution we want for full-text search, and also how to properly have add it as an option, and include a script to optionally set it up.

[rhaamo]

not sure there is other better way when using postgresql FTS. and it seems to run fine on some instances.

making it optional is basically adding three if/else, look at my diff

and the postgresql part is just running four queries (https://github.com/gled-rs/mastodo/blob/master/FTS_NOES.md)

[vibhi19]

not sure there is other better way when using postgresql FTS. and it seems to run fine on some instances.

making it optional is basically adding three if/else, look at my diff

and the postgresql part is just running four queries (https://github.com/gled-rs/mastodo/blob/master/FTS_NOES.md)

without es hmm that would be so cool. @ThibG you can contact gled maybe he could help

[vibhi19]

@rhaamo Can you update the patch we are working on our Frok. as glitch now support to increase search result. it will be a big help.

Please help me

[rhaamo]

I still haven't updated my instance actually. I don't know if I will do it tonight or later this week.

[rhaamo]

And edit: the PR #1082 is still not merged so that will wait until it gets, or not, into glitch.

[vibhi19]

I am waiting to see if it gonna merged or not, although if its not merged you can add it to your to do list and send me updated patch when you have free time.

Thank You for giving me your time, it means alot to me

[rhaamo]

I've looked at that PR and all it changes is one line. I've updated the patch to not touch that line.

[qnixsynapse]

My fork might have some features you don't want, and I won't be maintaining it indefinitely for some reasons.

You should be able to use https://gist.sigpipe.me/dashie/a84a976a13ff6ae97da494fc7a657d10221e7368 as a patch to apply to glitch-soc, worst case manually applying it isn't that hard.

The only thing is you have to maintain your own fork and merge glitch-soc in it.

Unless someone make a better patch with config option (because the patch override the search using elasticsearch)

@rhaamo Glitch soc with this patch is throwing 500 error. Do you know how to fix that? Elastic Search service is running fine btw.

[rhaamo]

I will update my instance soon.

However we need some logs to know what is going on.

Update: still works for me.

[rhaamo]

Btw I'm not sure FTS can work alongside ElasticSearch, it's one or the other.

[qnixsynapse]

@rhaamo The glitch fork is here. Initially, your patch was failing because some files were already updated.

I'm unable to see any error logs. Everything was fine on the server side. It's only on client side that FTS was throwing a 500 error.

[rhaamo]

Looks fine to me.

This patch assumes you don't enable search in .env because that's for ElasticSearch.

@biswasab have you run the SQL queries documented in https://github.com/gled-rs/mastodo/blob/master/FTS_NOES.md ? Theses are needed to enable Full Text Search on the database.

If the front throw a 500 then the back too, check for .env, sql, and the logs, there should be one in mastodon-web at least.

[qnixsynapse]

@biswasab have you run the SQL queries documented in https://github.com/gled-rs/mastodo/blob/master/FTS_NOES.md ? Theses are needed to enable Full Text Search on the database.

@rhaamo No we didn't since we are using ES. Is that really required?

Found the error

method=GET path=/api/v2/search format=html controller=Api::V2::SearchController action=index 
: SELECT  "statuses".* FROM "statuses" WHERE (tsv @@ plainto_t...
                                              ^
CT  "statuses".* FROM "statuses" WHERE (tsv @@ plainto_tsquery('here')) AND "statuses"."visibility" IN ($1, $2) ORDER BY "stat
d52-a380-4036-abae-2aa9614c20af]
d52-a380-4036-abae-2aa9614c20af] ActiveRecord::StatementInvalid (PG::UndefinedColumn: ERROR:  column "tsv" does not exist
: SELECT  "statuses".* FROM "statuses" WHERE (tsv @@ plainto_t...
                                              ^
CT  "statuses".* FROM "statuses" WHERE (tsv @@ plainto_tsquery('here')) AND "statuses"."visibility" IN ($1, $2) ORDER BY "stat
d52-a380-4036-abae-2aa9614c20af]
d52-a380-4036-abae-2aa9614c20af] app/controllers/api/v2/search_controller.rb:6:in `index'

Looks like we have to run SQL queries!

[rhaamo]

Yes you need the SQL part, it will create the necessary indexes and support for full text search.

By default there is nothing because mastodon doesn't use it.

However the database will growth a bit since it needs to index text. On the other hand you will free the 15Gig of ram used by elastic search :D

[qnixsynapse]

@rhaamo Yes. FIXED it. Thank you so much for your help.

[vibhi19]

@rhaamo Your patch seems to only for database only although Gled fork also add support for ES too, but we are happy with the direct database search.

can you do us one more favor, we want trending hashtag patch from Gled but i dont know how to find the patch, can you help us.