On Fri, Feb 11, 2011 at 11:28 PM, Tatsuo Ishii <[email protected]> wrote: > I think this is a known bug with pgpool-II 3.0.x. > > What actually following SELECT: > > SELECT 1 FROM "keywords"_id_seq FOR UPDATE > > being interpreted by PostgreSQL is: > > SELECT 1 FROM keywords AS _id_seq FOR UPDATE > > which tries to obtain row locks for "all" rows in table keywords, > which will take very long time if the table has many rows. > > Please wait 3.0.2 (will be released in the next week) or grab > V3_0_STABLE HEAD from CVS. Or you could apply included patches which > is being committed to V3_0_STABLE. > -- > Tatsuo Ishii > SRA OSS, Inc. Japan > English: http://www.sraoss.co.jp/index_en.php > Japanese: http://www.sraoss.co.jp > >> Hi Everyone, >> >> My configuration is fairly simple. I have a server running FC-14, >> pgpool-II-3.0.1, and postgresql-server 9.0.2, and another server >> running FreeBSD with postgresql-server 9.0.2. These servers are >> configured with replication via pgpool, with the FC14 machine as the >> primary. >> >> The problem I am having, is that an insert done via pgpool takes an >> extremely long time, hours, and I have yet to see one complete. The >> insert itself is simple: >> >> INSERT INTO keywords (keyword) VALUES ('jacktest1'); >> >> This query works perfectly and quickly when executed via psql directly >> to either back-end node, but through pgpool is where the problem >> arises. If I run: >> >> SELECT * FROM pg_stat_activity; >> >> Via psql on the primary (not through pgpool), I see this: >> >> 16387 | kwds | 11951 | 16386 | dbadmin | psql | >> 127.0.0.1 | 50337 | 2011-02-12 00:09:39.029072+00 | 2011-02-12 >> 00:10:05.014054+00 | 2011-02-12 00:10:05.231779+00 | f | SELECT >> 1 FROM "keywords"_id_seq FOR UPDATE >> >> This is interesting, because the column name is actually >> keywords_id_seq, not "keywords"_id_seq, so I am wondering if pgpool is >> somehow mangling the column name, or if this is normal. Furthermore, >> I believe insert_lock is supposed to be set to true in order to deal >> with SERIAL data types during inserts. I have set this to true as >> recommended. >> >> The secondary server has an established session, but it is idle. >> >> I have attached strace to both the insert and select processes, and >> they are active (not hung). I have run this with pgpool in debug >> mode, and it only floods the logs with messages like this: >> >> 2011-02-12 00:06:13 DEBUG: pid 10355: do_query: kind: D >> >> Until I eventually ctrl-c my session in psql. I have not seen any >> other errors in either pgsql or pgpool logs. Please let me know if >> you need any further information, or if you have any ideas. Thanks >> for taking the time to read this mess. >> >> -Jack >> _______________________________________________ >> Pgpool-general mailing list >> [email protected] >> http://pgfoundry.org/mailman/listinfo/pgpool-general >
Thank you Tatsuo! My pgpool installation now works perfectly :) _______________________________________________ Pgpool-general mailing list [email protected] http://pgfoundry.org/mailman/listinfo/pgpool-general
