bug comment full text search is disabled - GIN indices in pg 8.2 do not support table scans

Known about:

http://archives.postgresql.org/pgsql-hackers/2007-01/msg01581.php

Is there a workaround for this issue?

<kiko> Is there a workaround for this issue?
<stub> No. We need to revert the indexes or put up with it until it is fixed upstream.

Lowering importance since it happens only using specific search terms as seen by OOPS-523A69 and OOPS-524C471. Meanwhile stub will poke psgsql developer regarding the GIN limitations.

We need to revert. If a fix is forthcoming it won't be around until 8.4 most likely. We need to revert to GIST indexes.

Stuart,
do you plan to revert in the next rollout?

Diogo Matsubara wrote:
> Stuart,
> do you plan to revert in the next rollout?
>

No - rebuilding the indexes will take 6 or 7 hours. It can be done live, but
will cause occasional OOPSes to users for occasional 5 minute periods.

We either need to:
 - Take launchpad down for a long time and do them all at once
 - Schedule regular downtime when stuff like this can be done.
   We can do it it 8 1 hour chunks instead of one long one
 - Do it live.

I'm not sure whose call it is on how we proceed.

--
Stuart Bishop <email address hidden> http://www.canonical.com/
Canonical Ltd. http://www.ubuntu.com/

Talked to kiko about this and he suggested to do it live over two Sundays. Stuart, is that feasible?

It seems to be the less disruptive alternative.

Diogo Matsubara wrote:
> Talked to kiko about this and he suggested to do it live over two
> Sundays. Stuart, is that feasible?
>
> It seems to be the less disruptive alternative.
>

Sure. I'll start this weekend.

--
Stuart Bishop <email address hidden> http://www.canonical.com/
Canonical Ltd. http://www.ubuntu.com/

Reopening as we might have a work around that enables us to use GIN indexes. We need GIN indexes to allow us to start searching in bug comments again, as the GIST indexes are just too slow.

The error occurs when a full scan is needed, and the GIN index type does not support this and fails. The work around is to inspect the query tree of the query to see if it will require a full scan, and if so, return a 'Query too general' error message to the user.

launchpad_prod=# select querytree(ftq('-lt'));
 querytree
-----------
 T
(1 row)

launchpad_prod=# select querytree(ftq('gin (index OR indexes) fail'));
               querytree
----------------------------------------
 'gin' & ( 'index' | 'index' ) & 'fail'
(1 row)

If the querytree() function returns 'T', that query will fail if it is
applied to a GIN index.

So what we need to do is to update BugTaskSet.search() to look before it leaps, in other words? Nice job.

Christian Reis wrote:
> So what we need to do is to update BugTaskSet.search() to look before it
> leaps, in other words? Nice job.

Yes, and any other places that are searching the bug or message full text
indexes. I don't know if the support tracker is searching the comment spool
or not.

We can leave the other full text indexes as GIST for the time being as they
are not having problems.

--
Stuart Bishop <email address hidden> http://www.canonical.com/
Canonical Ltd. http://www.ubuntu.com/

Let's try that workaround!

Bjorn, reassign as necessary.

Is it possible that this bug causes searches to fail also in the bug title and body? I have an open Question #22884 in Launchpad itself about that.

No, this bug doesn't affect searches in the title and body.

Thanks, Björn, my question got answered, too, and I can confirm it has nothing to do with this bug.

This bug has been cited by Stuart on-list as the cause for bug #186262 (whether that makes the latter bug a dupe of this one I'm not sure).

On Mon, Jan 28, 2008 at 08:11:32AM -0000, Graham Binns wrote:
> This bug has been cited by Stuart on-list as the cause for bug #186262
> (whether that makes the latter bug a dupe of this one I'm not sure).

No, it's not a dupe. This bug is about searching comments, while bug
#186262 is about searching in the bug description. I don't think there's
a single fix which will fix both cases.

On 28/01/2008, Björn Tillenius <email address hidden> wrote:
> On Mon, Jan 28, 2008 at 08:11:32AM -0000, Graham Binns wrote:
> > This bug has been cited by Stuart on-list as the cause for bug #186262
> > (whether that makes the latter bug a dupe of this one I'm not sure).
>
> No, it's not a dupe. This bug is about searching comments, while bug
> #186262 is about searching in the bug description. I don't think there's
> a single fix which will fix both cases.

I wonder whether it's worth (and at all possible) to maintain both GIN
and GiST indexes, the latter not including comments, and fall back to
searching the GiST (but not the comments) if the searching the GIN
indexes proves impossible. Then again, this may be a bad idea, if only
because of the complexity involved.

This issue was resolved by reverting. We can switch to GIN indexes with PG 9.0.