[GENERAL] Too many LWLocks taken in query using pg_tgrm GIN index
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Too many LWLocks taken in query using pg_tgrm GIN index
Craig Ringer [EMAIL PROTECTED] writes: I've just hit a reproducible error with a query that uses pg_trgm: ERROR: too many LWLocks taken This is absolutely a bug. Please send a complete test case to pgsql-bugs. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] too many LWLocks taken
It appears the errors were caused by table corruption. I've truncated and reloaded some large table (300m entries), and the problem disappeared. Table corruption was probably caused by hardware failure, not by PostgreSQL :) On Fri, 2008-04-04 at 20:15 -0400, Tom Lane wrote: Alex Vinogradovs [EMAIL PROTECTED] writes: Version is 8.2.5 running on FreeBSD 6.2. I can try upgrading to 8.2.7, if you think that would help. Well, an upgrade would be a good idea on general principles, but I doubt it will fix a previously unknown bug. Does the postmaster log show any other odd behavior around these errors? Which process is throwing the error anyway? If it's a regular backend, what query is it executing? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] too many LWLocks taken
Guys, I've got a pretty large database, and since certain time it started giving me too many LWLocks taken when running some batch inserts... Any parameter can be ajusted ? Thanks! Best regards, Alex Vinogradovs -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] too many LWLocks taken
Alex Vinogradovs [EMAIL PROTECTED] writes: I've got a pretty large database, and since certain time it started giving me too many LWLocks taken when running some batch inserts... Any parameter can be ajusted ? Oh really? That's a bug, not something you need to adjust a parameter for. Can you put together a self-contained test case? What PG version is this? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] too many LWLocks taken
Version is 8.2.5 running on FreeBSD 6.2. I can try upgrading to 8.2.7, if you think that would help. I don't think I can make a test case. Database had been up for a year now, with about 2 thousand inherited tables (partitioning) and about 2B rows in all tables... On Fri, 2008-04-04 at 18:58 -0400, Tom Lane wrote: Alex Vinogradovs [EMAIL PROTECTED] writes: I've got a pretty large database, and since certain time it started giving me too many LWLocks taken when running some batch inserts... Any parameter can be ajusted ? Oh really? That's a bug, not something you need to adjust a parameter for. Can you put together a self-contained test case? What PG version is this? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] too many LWLocks taken
Alex Vinogradovs [EMAIL PROTECTED] writes: Version is 8.2.5 running on FreeBSD 6.2. I can try upgrading to 8.2.7, if you think that would help. Well, an upgrade would be a good idea on general principles, but I doubt it will fix a previously unknown bug. Does the postmaster log show any other odd behavior around these errors? Which process is throwing the error anyway? If it's a regular backend, what query is it executing? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
