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
Index: pool_process_query.c
===================================================================
RCS file: /cvsroot/pgpool/pgpool-II/pool_process_query.c,v
retrieving revision 1.249
retrieving revision 1.249.2.1
diff -c -r1.249 -r1.249.2.1
*** pool_process_query.c 6 Sep 2010 05:03:21 -0000 1.249
--- pool_process_query.c 14 Nov 2010 11:22:23 -0000 1.249.2.1
***************
*** 1,6 ****
/* -*-pgsql-c-*- */
/*
! * $Header: /cvsroot/pgpool/pgpool-II/pool_process_query.c,v 1.249 2010/09/06 05:03:21 t-ishii Exp $
*
* pgpool: a language independent connection pool server for PostgreSQL
* written by Tatsuo Ishii
--- 1,6 ----
/* -*-pgsql-c-*- */
/*
! * $Header: /cvsroot/pgpool/pgpool-II/pool_process_query.c,v 1.249.2.1 2010/11/14 11:22:23 t-ishii Exp $
*
* pgpool: a language independent connection pool server for PostgreSQL
* written by Tatsuo Ishii
***************
*** 2720,2725 ****
--- 2720,2726 ----
POOL_STATUS insert_lock(POOL_CONNECTION *frontend, POOL_CONNECTION_POOL *backend, char *query, InsertStmt *node, int lock_kind)
{
char *table;
+ int len;
char qbuf[1024];
POOL_STATUS status;
int i, deadlock_detected = 0;
***************
*** 2732,2737 ****
--- 2733,2739 ----
char *atrname;
char seq_rel_name[MAX_SEQ_NAME+1];
+ char stripped_table_name[MAX_SEQ_NAME+1];
static POOL_RELCACHE *relcache;
/* insert_lock can be used in V3 only */
***************
*** 2785,2791 ****
/* could not get attribute namme */
return POOL_CONTINUE;
}
! snprintf(seq_rel_name, MAX_SEQ_NAME, "%s_%s_seq", table, atrname);
pool_debug("seq rel name:%s", seq_rel_name);
snprintf(qbuf, sizeof(qbuf), "SELECT 1 FROM %s FOR UPDATE", seq_rel_name);
}
--- 2787,2803 ----
/* could not get attribute namme */
return POOL_CONTINUE;
}
!
! /* strip head and tail double quotes from table name */
! if (*table == '"')
! table++;
! len = strlen(table);
! if (*(table + len -1) == '"')
! len--;
! strncpy(stripped_table_name, table, len);
! *(stripped_table_name + len) = '\0';
!
! snprintf(seq_rel_name, MAX_SEQ_NAME, "\"%s_%s_seq\"", stripped_table_name, atrname);
pool_debug("seq rel name:%s", seq_rel_name);
snprintf(qbuf, sizeof(qbuf), "SELECT 1 FROM %s FOR UPDATE", seq_rel_name);
}
_______________________________________________
Pgpool-general mailing list
[email protected]
http://pgfoundry.org/mailman/listinfo/pgpool-general
