Thread: Full text searching?

[I have looked in the archives, I just can't find anything useful in there.]

I have largish table of text files (about 10K or so each), which I
need to make searchable in the normal keyword search style. What is
the current best way of doing this, and is there any movement on
putting a proper indexing method in place. I've looked at the fti stuff
in contrib: how well does this work? I don't really like the look of it.

If the current method isn't adequate I'll look at implementing a proper
solution in-house.

Colman

I chose to use a 3rd party product (Verity) for full text indexing even
though I would prefer to use PostgreSQL for fti.
It's probably cheaper to buy a indexing server, than to develop a new
one.

I use Cold Fusion and it has Verity full text indexing built in, so it
was the most natural choice for me.

Poul L. Christiansen

Colman Reilly wrote:
>
> [I have looked in the archives, I just can't find anything useful in there.]
>
> I have largish table of text files (about 10K or so each), which I
> need to make searchable in the normal keyword search style. What is
> the current best way of doing this, and is there any movement on
> putting a proper indexing method in place. I've looked at the fti stuff
> in contrib: how well does this work? I don't really like the look of it.
>
> If the current method isn't adequate I'll look at implementing a proper
> solution in-house.
>
> Colman

"Poul L. Christiansen" <poulc@cs.auc.dk> writes:

> I chose to use a 3rd party product (Verity) for full text indexing even
> though I would prefer to use PostgreSQL for fti.

How is the support for Danish in Verity now ?
I used Verity indirectly earlier as a plugin in to Sybase. The nice thing
about such an integration where that I could do joins between the different
Verity indexes and my regular tables.

It would have been nice if PostgreSQL had some sort of extension mechanism
to interface against external servers. Maybe an API for creating proxy
tables, so a join you do against a proxy table really forwards the request
to another service.

Then it should be doable to integrate PostgreSQL with your search engine of
choice.

> It's probably cheaper to buy a indexing server, than to develop a new
> one.

Yup.

Regards,
    Gunnar

Gunnar R|nning wrote:
>
> "Poul L. Christiansen" <poulc@cs.auc.dk> writes:
>
> > I chose to use a 3rd party product (Verity) for full text indexing even
> > though I would prefer to use PostgreSQL for fti.
>
> How is the support for Danish in Verity now ?

I convert the characters - e.g. ø will be converted to ø When I
do a search, i just replace the search string the same way. The only
problem that could arise is when people search for the word "slash", but
I guess you could avoid searching ø somehow.

> I used Verity indirectly earlier as a plugin in to Sybase. The nice thing
> about such an integration where that I could do joins between the different
> Verity indexes and my regular tables.
>
> It would have been nice if PostgreSQL had some sort of extension mechanism
> to interface against external servers. Maybe an API for creating proxy
> tables, so a join you do against a proxy table really forwards the request
> to another service.

I'd love to have an index type directly in PostegreSQL called fti which
give us full text indexing just as easily as a standard B-tree index.

When 7.1 is out, all it needs is fti to make it equal in features to MS
SQL Server ;)

Poul L. Christiansen

"Poul L. Christiansen" <poulc@cs.auc.dk> writes:

>
> I convert the characters - e.g. ø will be converted to ø When I
> do a search, i just replace the search string the same way. The only
> problem that could arise is when people search for the word "slash", but
> I guess you could avoid searching ø somehow.

I was more thinking in the terms of language and not characters. Matching
things like "biler" to "bil", or "pjolter" and "drink". E.g. synonym
searches and knowledge of singular/plural forms, etc.

regards,

    Gunnar

Hi all,

Before waiting and native fti included into the PostgreSQL distribution,
and if you are interested into full text search you can take a look at these
perl scripts they do most of works that you want.

Here are the features :

    - Apply to any database.
    - Incremental indexing.
    - Multiple table and field indexing.
    - Multilanguage indexing.
    - Multiple table and field search.
    - Multilanguage search.

There's also documentation :-)

You will find a sample script with all use you can do with these scripts.

I think most of you can podify them to match exactly their needs.

You can find it at

    http://www.adelys.com/fti/

Regards

Gilles DAROLD.

Hi -
    I'm using the AuthPG apache authentication module
(authpg.sourceforge.net) to authenticate against postgres 7.0.2 and am
getting some strange errors.  The end user is repeatedly prompted for
their username/password.

I used to think it was because I only had 32 backends running, but I'm
watching logs right now and am getting the following in apache's logs.

NOTICE:  AbortTransaction and not in in-progress state

However, if I reload it will sometimes start working again and I can
continue as normal...

thanks!

-philip

Philip Hallstrom <philip@adhesivemedia.com> writes:
> NOTICE:  AbortTransaction and not in in-progress state

IIRC, 7.0.3 fixes some problems that could lead to that message.
If you can still reproduce this with 7.0.3, it'd be interesting
to turn on query logging (-d2 to postmaster) and see what sequence
of events provokes it.

            regards, tom lane

I've upgraded to 7.0.3 and am logging.  I'll let you know if I see it
again.

btw - do I need to send any signal to postmaster to let it know I've
rotated the log file?

btw2 - as far as upgrading goes, that was probably the quickest/easiest
I've every done.  wahoo!

On Sat, 11 Nov 2000, Tom Lane wrote:

> Philip Hallstrom <philip@adhesivemedia.com> writes:
> > NOTICE:  AbortTransaction and not in in-progress state
>
> IIRC, 7.0.3 fixes some problems that could lead to that message.
> If you can still reproduce this with 7.0.3, it'd be interesting
> to turn on query logging (-d2 to postmaster) and see what sequence
> of events provokes it.
>
>             regards, tom lane
>

Philip Hallstrom <philip@adhesivemedia.com> writes:
> btw - do I need to send any signal to postmaster to let it know I've
> rotated the log file?

AFAIK we don't really support log rotation currently --- you have to
stop/restart the postmaster and direct its stdout/stderr into a new
file :-(.  There is support for logging via syslog, though, if you
compile with the option for it.

            regards, tom lane