Full Text Indexing

"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:

I'm playing around with the Full Text Indexing module, and I notice that
it's case-sensitive. This seems to be pretty useless to me - especially for
my application. I wonder if there'd be any objections to me modifying it to
be case-insensitive. Or at least be configurable either way...

Seems like a good idea, but make it configurable.

Also, the fti.pl that comes with the contrib seems to be using an outdated
version of CPAN's Pg.pm.

It hasn't been touched in awhile, so feel free to update it. BTW,
someone ought to look at bringing src/interfaces/perl5 into sync with
the CPAN version, too. Or possibly we should stop distributing that
altogether, if the CPAN copy is being maintained?

regards, tom lane

"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:

I'm playing around with the Full Text Indexing module, and I notice that
it's case-sensitive. This seems to be pretty useless to me -

especially for

my application. I wonder if there'd be any objections to me

modifying it to

be case-insensitive. Or at least be configurable either way...

Seems like a good idea, but make it configurable.

I actually came up with another way of solving the problem.

The FTI table has two columns: (string, id). The code needs to do two
things; delete all strings for an id, and join to the main table based on
the id.

The docs for FTI recommend indexing (string, id). This is poor as the
delete based on id does a sequential scan, although the join seems to be
able to use the index (as long was you have a where string ~ '^something').

I indexed as follows:

-- Functional index that lets us do case-insensitivity without hacking
fti.so
CREATE INDEX fti_string_idx ON fti_table(lower(string));

-- Index on id to allow fast deletes
CREATE INDEX fti_id_idx ON fti_table(id);

That seems to be a good solution to me - it allows case-insensitivity, fast
deletion and fast joining.

Also, the fti.pl that comes with the contrib seems to be using

an outdated

version of CPAN's Pg.pm.

It hasn't been touched in awhile, so feel free to update it. BTW,
someone ought to look at bringing src/interfaces/perl5 into sync with
the CPAN version, too. Or possibly we should stop distributing that
altogether, if the CPAN copy is being maintained?

I'll have a look someday maybe, but I'll try to get this
harder-than-expected ADD CONSTRAINT UNIQUE/PRIMARY patch off my hands first.

Chris

Doh - sorry about these hideously late posts. I think my mail queue has
been clogged up for a while!

Chris