Opened 18 years ago
Last modified 19 months ago
#3254 new New feature
full text search support for postgres, oracle and mssql
Reported by: | Ronny Pfannschmidt | Owned by: | |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | |
Severity: | Normal | Keywords: | oracle fulltext search postgresql postgres mysql |
Cc: | sam@…, johann.queuniet@…, hv@…, rboumart@…, dan.fairs@…, twidi@…, Matt Boersma, justinlilly@…, James Pic, robinchew@…, niwi@…, slav0nic@…, mpessas@…, tomi.kyostila@…, Matt Goldman | Triage Stage: | Accepted |
Has patch: | no | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description (last modified by )
I didn't like it was missing.
oracle, mssql : add a FulltextIndex for the Fields
postgresql: for each FulltextField add a extra tsvector Field named like "%(fieldname)_tsv"
Problems with sqlite fts:
- they stated a match statement may only be used once in a query
- it needs a very specific syntax to search multiple fields
- i have no idea how to get that into the ORM
Attachments (3)
Change History (46)
by , 18 years ago
Attachment: | fts-pstgres-oracle-mssql.patch added |
---|
comment:1 by , 18 years ago
Summary: | [patch] experimental fts support for postgres, oracle and mssql → [patch] experimental fulltext search support for postgres, oracle and mssql |
---|---|
Triage Stage: | Unreviewed → Design decision needed |
comment:2 by , 18 years ago
Cc: | added |
---|
comment:3 by , 17 years ago
Typo in patch: plainto_tsquery
in psycoph2/base.py should be to_tsquery
Corrected patch attached.
comment:4 by , 17 years ago
#4676 was a duplicate, and has an alternate implementation for PostgreSQL.
comment:5 by , 17 years ago
Patches attached to this ticket do not work correctly with PostgreSQL, please use patch http://code.djangoproject.com/attachment/ticket/4676/sql.diff in ticket #4676 (which also describes the correct database set-up by the way).
comment:6 by , 17 years ago
Component: | Metasystem → Database wrapper |
---|---|
Needs documentation: | set |
Needs tests: | set |
Patch needs improvement: | set |
Triage Stage: | Design decision needed → Accepted |
comment:7 by , 17 years ago
Cc: | added |
---|
comment:8 by , 17 years ago
Reporter: | changed from | to
---|
follow-up: 10 comment:9 by , 17 years ago
Postgres 8.3 now has full-text searching. Not sure if this changes anything here or not...
comment:10 by , 17 years ago
Replying to anonymous:
Postgres 8.3 now has full-text searching. Not sure if this changes anything here or not...
comment:11 by , 17 years ago
Cc: | added |
---|
comment:13 by , 17 years ago
Sorry, I meant june 2007 (The date format of trac is hard to read for germans ...)
comment:14 by , 17 years ago
Cc: | added |
---|
I added a Patch against 7231. It is only for postgres. But i think it would be straight forward for the other backend.
comment:15 by , 16 years ago
Cc: | added |
---|
comment:16 by , 16 years ago
Cc: | added |
---|
comment:17 by , 16 years ago
Cc: | added |
---|
comment:18 by , 16 years ago
Keywords: | oracle fulltext search added |
---|
comment:20 by , 16 years ago
Cc: | added |
---|
comment:21 by , 14 years ago
Severity: | normal → Normal |
---|---|
Type: | enhancement → New feature |
comment:24 by , 12 years ago
can this be useful? http://barryp.org/blog/entries/postgresql-full-text-search-django/
There's a special "search" method for MySQL but still nothing for PostgreSQL... any specific reason? This interesting ticket seems just abandoned!
comment:25 by , 12 years ago
Cc: | added |
---|
comment:26 by , 12 years ago
Cc: | added |
---|
comment:27 by , 12 years ago
Cc: | added |
---|
If in the future, are going to implement support for postgresql text search, you can have this as a reference implementation:
https://github.com/niwibe/djorm-ext-pgfulltext/blob/master/djorm_pgfulltext/models.py
comment:28 by , 12 years ago
Cc: | added |
---|
comment:29 by , 11 years ago
Cc: | added |
---|
comment:30 by , 11 years ago
This is definitely something I would like to see in Django and could help with (see https://github.com/mpessas/django-pg-extensions for another package that supports FTS in PostgreSQL).
Here is a suggestion as how this could be implmented for PostgreSQL:
- Add two extra options in TextField, use_fts=True and fts_config (see http://www.postgresql.org/docs/9.3/static/textsearch-intro.html#TEXTSEARCH-INTRO-CONFIGURATIONS), with 'simple' being the default value.
- When the use_fts option is set, create an index (see http://www.postgresql.org/docs/9.3/static/textsearch-tables.html#TEXTSEARCH-TABLES-INDEX). The main reasons to prefer an index to a column for the tsvector representation are that this is simpler to set up (no need for columns that are not exposed in Django and no need for triggers or methods to update the column) and that, even if it is a bit slower, most people would not mind; if they care for performance, they can do it manually or use Solr etc. See http://www.postgresql.org/docs/9.3/static/textsearch-tables.html for the details of the two approaches.
- The user can do a fts query by using the
search
field lookup.
I haven't looked into fts for some time now, so I might be forgetting something. In any case, how does this proposal sound?
comment:31 by , 11 years ago
Owner: | changed from | to
---|---|
Status: | new → assigned |
comment:32 by , 10 years ago
I was just reading in the documentation that full text search is only available for MySQL, and came over here to find out why. What can I do to help light a fire under this project?
What are people doing in the meantime? Solr? Haystack?
comment:33 by , 10 years ago
Keywords: | postgresql postgres mysql added |
---|
I'd be happy to work on this ticket. I just implemented a very flexible FTS integration with custom fields, and built in support for migrations to automatically create the indices. If it's ok, I'd like to take the reigns on this if no one else has time to.
follow-up: 39 comment:34 by , 10 years ago
Summary: | [patch] experimental fulltext search support for postgres, oracle and mssql → full text search support for postgres, oracle and mssql |
---|
comment:35 by , 10 years ago
Cc: | added |
---|
comment:36 by , 10 years ago
The first parts of a FTS implementation in contrib.postgres are available for review at https://github.com/django/django/pull/4726, as is my plan of attack for the extent of features I wish to implement.
The approach is different to that used by previous implementations of full text search with postgres, staying fairly close to the SQL structures and making heavy use of the new expressions and lookups. As yet I have not researched the potential for similar functionality in other databases, but I expect at least parts of the contrib.postgres implementation to be highly postgres specific. It may be there is a nice generic search API with a cross database set of features which could be implemented. If this is the case though, it will likely make heavy use of the code written for postgres on that backend at least. I don't intend to work on a generic solution at this point.
comment:38 by , 9 years ago
Has patch: | unset |
---|---|
Needs documentation: | unset |
Needs tests: | unset |
Owner: | removed |
Patch needs improvement: | unset |
Status: | assigned → new |
comment:39 by , 5 years ago
Replying to Tim Graham: Is anyone working on the full text search support for oracle?
comment:42 by , 3 years ago
Is this ticket still open just for Oracle full-text search support?
- I'm not sure if the SQLite criticisms in the original description still stand — I see various projects making good use of FTS on SQLite.
- Wagtail just added a Search backend for everything except Oracle as part of GSoC 2021 https://wagtail.io/blog/gsoc-2021-wrapup/ — Perhaps there are things to learn from there.
- It would still be good to have
SearchVector
&co or similar available on the other DBs than PostgreSQL.
comment:43 by , 19 months ago
Cc: | added |
---|
This is a very simple little patch - if accepted it'll need some docs. Shouldn't be too hard to implement MySQL fulltext searching too, but this will require a special index to be created.