Opened 10 years ago

Last modified 5 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@… 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 Ramiro Morales)

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)

fts-pstgres-oracle-mssql.patch (1.8 KB) - added by Ronny Pfannschmidt <ronny.pfannschmidt@…> 10 years ago.
fts-pstgres-oracle-mssql-corrected.patch (1.8 KB) - added by djangoproject.com@… 9 years ago.
Corrected patch
fts-pstgres.patch (711 bytes) - added by rachid 9 years ago.
corrected Patch (only for postgres)

Download all attachments as: .zip

Change History (41)

Changed 10 years ago by Ronny Pfannschmidt <ronny.pfannschmidt@…>

comment:1 Changed 10 years ago by Simon G. <dev@…>

Summary: [patch] experimental fts support for postgres, oracle and mssql[patch] experimental fulltext search support for postgres, oracle and mssql
Triage Stage: UnreviewedDesign decision needed

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.

comment:2 Changed 10 years ago by anonymous

Cc: sam@… added

comment:3 Changed 9 years ago by djangoproject.com@…

Typo in patch: plainto_tsquery in psycoph2/base.py should be to_tsquery
Corrected patch attached.

Changed 9 years ago by djangoproject.com@…

Corrected patch

comment:4 Changed 9 years ago by James Bennett

#4676 was a duplicate, and has an alternate implementation for PostgreSQL.

comment:5 Changed 9 years ago by djangoproject.com@…

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 Changed 9 years ago by Jacob

Component: MetasystemDatabase wrapper
Needs documentation: set
Needs tests: set
Patch needs improvement: set
Triage Stage: Design decision neededAccepted

comment:7 Changed 9 years ago by anonymous

Cc: johann.queuniet@… added

comment:8 Changed 9 years ago by Jacob

Reporter: changed from Ronny Pfannschmidt <ronny.pfannschmidt@…> to Ronny Pfannschmidt

comment:9 Changed 9 years ago by anonymous

Postgres 8.3 now has full-text searching. Not sure if this changes anything here or not...

comment:10 in reply to:  9 Changed 9 years ago by anonymous

Replying to anonymous:

Postgres 8.3 now has full-text searching. Not sure if this changes anything here or not...

http://www.postgresql.org/docs/8.3/static/textsearch.html

comment:11 Changed 9 years ago by Thomas Güttler

Cc: hv@… added

comment:12 Changed 9 years ago by Thomas Güttler

Both patches (last from june 2006) are broken since [5957]

comment:13 Changed 9 years ago by Thomas Güttler

Sorry, I meant june 2007 (The date format of trac is hard to read for germans ...)

Changed 9 years ago by rachid

Attachment: fts-pstgres.patch added

corrected Patch (only for postgres)

comment:14 Changed 9 years ago by rachid

Cc: rboumart@… 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 Changed 8 years ago by Dan Fairs

Cc: dan.fairs@… added

comment:16 Changed 8 years ago by anonymous

Cc: twidi@… added

comment:17 Changed 8 years ago by Matt Boersma

Cc: Matt Boersma added

comment:18 Changed 8 years ago by Matt Boersma

Keywords: oracle fulltext search added

comment:19 Changed 8 years ago by Ramiro Morales

Description: modified (diff)

(description)

comment:20 Changed 8 years ago by Justin Lilly

Cc: justinlilly@… added

comment:21 Changed 6 years ago by Łukasz Rekucki

Severity: normalNormal
Type: enhancementNew feature

comment:22 Changed 5 years ago by Aymeric Augustin

UI/UX: unset

Change UI/UX from NULL to False.

comment:23 Changed 5 years ago by Aymeric Augustin

Easy pickings: unset

Change Easy pickings from NULL to False.

comment:24 Changed 4 years ago by Stefano Crosta <stefano@…>

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 Changed 4 years ago by James Pic

Cc: James Pic added

comment:26 Changed 4 years ago by Robin

Cc: robinchew@… added

comment:27 Changed 4 years ago by Andrei Antoukh

Cc: niwi@… 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 Changed 4 years ago by Sergey Maranchuk

Cc: slav0nic@… added

comment:29 Changed 3 years ago by Apostolis Bessas

Cc: mpessas@… added

comment:30 Changed 3 years ago by Apostolis Bessas

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:

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 Changed 3 years ago by Marc Tamlyn

Owner: changed from nobody to Marc Tamlyn
Status: newassigned

comment:32 Changed 2 years ago by Malik A. Rumi

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 Changed 2 years ago by Dan Loewenherz

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.

comment:34 Changed 21 months ago by Tim Graham

Summary: [patch] experimental fulltext search support for postgres, oracle and mssqlfull text search support for postgres, oracle and mssql

comment:35 Changed 18 months ago by Tomi Kyöstilä

Cc: tomi.kyostila@… added

comment:36 Changed 16 months ago by Marc Tamlyn

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:37 Changed 5 months ago by Marc Tamlyn <marc.tamlyn@…>

In 2d877da8:

Refs #3254 -- Added full text search to contrib.postgres.

Adds a reasonably feature complete implementation of full text search
using the built in PostgreSQL engine. It uses public APIs from
Expression and Lookup.

With thanks to Tim Graham, Simon Charettes, Josh Smeaton, Mikey Ariel
and many others for their advice and review. Particular thanks also go
to the supporters of the contrib.postgres kickstarter.

comment:38 Changed 5 months ago by Tim Graham

Has patch: unset
Needs documentation: unset
Needs tests: unset
Owner: Marc Tamlyn deleted
Patch needs improvement: unset
Status: assignednew
Note: See TracTickets for help on using tickets.
Back to Top