Richard Huxton (d...@archonet.com)

What does this give you:
   show default_text_search_config;
I get pg_catalog.english and the same result for the query whether I use:
    to_tsquery('english','database')
or to_tsquery('pg_catalog.english','database')

Could you be picking up a bad "english" configuration (see \dF)?

> However, the following returns zero rows:
>
> select title,author,ts_headline(abstract,to_tsquery('database') from document
> where clob @@ to_tsquery('database')

I take it "clob" matches "abstract"?

> It gets more interesting:
>
> select title,author,ts_headline(abstract,to_tsquery('database') from document
> where clob @@ to_tsquery('english','database')
>
> returns the "correct" result - one row with the expected headline.

Now that *is* strange. ts_headline() works without specifying 'english'
but the actual search works the other way.

> select title,author,ts_headline(abstract,to_tsquery('english','thesaurus')
> from document where clob @@ to_tsquery('english','thesaurus')
>
> also returns the "correct" result.
>
> I suggest that the above indicates a bug somewhere.

Could be - it'd be good to rule out a bad config. You might have an
unexpected list of stopwords or similar.

Let's try:
  SELECT ts_debug('the database and thesaurus');
  SELECT ts_debug('english', 'the database and thesaurus');
  SELECT ts_debug('pg_catalog.english', 'the database and thesaurus');
I'd expect "the", "and" to be stripped out as stopwords and the other
two to get through (database stemmed to "databas").

Well, no-one has been using 8.3 for more than a few weeks, so I don't
think anyone has a lot of experience with it.

I'm getting to_tsquery('database') stemming to "databas" and
to_tsvector(<your text>) gives me the following for that token:

'databas':305,630,642,663,698,719,746,870,872,951,961,974,993,1034,1042,1159,1217,1223,1238,1244,1265,1343,1357,1399,1434,1758,1818,1821,1834,2212,2240,2258,2278,2389,2529

Howard Wilkinson wrote:
> I am working on some upgrades to the MyDNS open source product. I have
> some expertise in MySQL but am not overly familiar with PostgreSQL and
> need some guidance on how to query the schema for the maximum size of
> data a column can hold.

Unless you're after PG-specific stuff, it's probably best to use the
information-schema.

http://www.postgresql.org/docs/8.3/static/information-schema.html

This has a standard layout cross-database, so will help you to extend
you app across other systems. I think it's supported in the latest
version of MySQL too.

Stephen Davies wrote:
> Attached is the "document" in question.
>
> Searches for "norwegian", "thesaurus" and "statement" give good results. A
> search for "database" gives the plain text from the beginning.

Seems OK here - might need to look at your configuration settings.
http://www.postgresql.org/docs/8.3/static/textsearch-debugging.html

I'll make sure I've got a clean setup here and re-run the test.


SELECT ts_headline(t, to_tsquery('database')) FROM tsearch_test;
                                               ts_headline
-------------------------------------------------------------------------------------------------------
<b>database</b> (using a 2 KB page) to a Large File Support (LFS)
<b>database</b> (using an 8 KB page
(1 row)

Hmm - a simple test seems to work OK.

SELECT ts_headline( repeat('apple banana carrot ', 100),
to_tsquery('apple'));
                                                               ts_headline
----------------------------------------------------------------------------------------------------------------------------------------
<b>apple</b> banana carrot <b>apple</b> banana carrot <b>apple</b>
banana carrot <b>apple</b> banana carrot <b>apple</b> banana carrot
(1 row)

It's not just the start of the text either:

SELECT ts_headline( repeat('elephant ', 100) || repeat('apple banana
carrot ', 100), to_tsquery('apple'));
                                                               ts_headline
----------------------------------------------------------------------------------------------------------------------------------------
<b>apple</b> banana carrot <b>apple</b> banana carrot <b>apple</b>
banana carrot <b>apple</b> banana carrot <b>apple</b> banana carrot
(1 row)

Can you provide a piece of text that shows the problem?