Too many LWLocks taken in query using pg_tgrm & GIN index

From: Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>
To: PostgreSQL <pgsql-general(at)postgresql(dot)org>
Subject: Too many LWLocks taken in query using pg_tgrm & GIN index
Date: 2008-04-21 05:49:26
Message-ID: 480C2AE6.2010707@postnewspapers.com.au
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi

I've just hit a reproducible error with a query that uses pg_trgm:

ERROR: too many LWLocks taken

I'm using PostgreSQL 8.3.1 .

The database is the one generated by the script I posted recently for
reading .po files. It's tiny, with only 7000 records in the table being
queried to produce this error. It's also easily generated using that script.

I'm encountering the error with the following query, which seeks to find
similar looking messages:

SELECT a.message, b.message FROM po_message a, po_message b
WHERE a.id <> b.id AND a.message % b.message
ORDER BY similarity(a.message,b.message) desc;

The query is intended to find similar looking messages as a test to make
sure the po reader script isn't inserting multiple slightly different
versions of a message from different po files.

If I remove the ORDER BY clause the error still occurs. The error does
not occur if I remove the '%' operator.

The table in question has a GIN index on the `message' column. If I drop
that index and replace it with a GIST index, the issue no longer occurs.

Dropping the index and recreating it as GIN again causes the problem to
reappear, so it wasn't a corrupt index.

Here's the query plan for the query without the ORDER BY:

EXPLAIN SELECT a.message, b.message FROM po_message a, po_message b
WHERE a.id <> b.id AND a.message % b.message;

QUERY PLAN
-------------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..8259.37 rows=59436 width=94)
Join Filter: (a.id <> b.id)
-> Seq Scan on po_message a (cost=0.00..161.10 rows=7710 width=51)
-> Index Scan using po_message_trigrm_idx on po_message b
(cost=0.00..0.93 rows=8 width=51)
Index Cond: (a.message % b.message)
Filter: (a.message % b.message)
(6 rows)

This isn't an issue for me, but I thought I should post it in case it
does indicate a bug lurking somewhere, like some kind of lock leak in
the tgrm gin index code. Ideas?

--
Craig Ringer

Responses

Browse pgsql-general by date

  From Date Subject
Next Message laser 2008-04-21 06:04:04 PostgreSQL over HTTP/REST interface
Previous Message Tom Lane 2008-04-21 05:03:21 Re: Too many LWLocks taken in query using pg_tgrm & GIN index