pgpool2.git / summary / log / commit / refs
commit 1cb4d4d75a3563aecf6a9e8a86ec309bb77725dc
Author: Tatsuo Ishii <ishii at sraoss.co.jp>
Date: Thu Aug 19 09:25:40 2010 +0000
When insert_lock is enabled and the table includes SERIAL data type,
issue row lock on the sequence table. Before we issues table
lock. Problem is, the table lock conflicts with auto vacuum and
sometimes caused excessive lock waiting.
Remove unnused global variable start_internal_transaction
Remove #ifdef out comments in pool_session_context.h
NEWS | 27 +++++++++++++----
doc/pgpool-ja.html | 9 ++++--
pool_process_query.c | 78 ++++++++++++++++++++++++++++++++++++++++++--------
pool_proto_modules.c | 10 +++----
pool_proto_modules.h | 4 +--
pool_relcache.c | 25 ++++++++++++++++
pool_relcache.h | 2 ++
pool_session_context.h | 46 -----------------------------
8 files changed, 128 insertions(+), 73 deletions(-)
diff --git a/NEWS b/NEWS
index d3d7381e4..319ad3670 100644
--- a/NEWS
+++ b/NEWS
@@ -2,7 +2,7 @@
* Version 3.0
- This version is the first version of pgpool-II 3.0 series.
+ This is the first version of pgpool-II 3.0 series.
That is, a "major version up" from 2.2 or 2.3 series.
The biggest news is, this version adapts to PostgreSQL 9.0's new
@@ -13,15 +13,15 @@
- SELECTs in explicit transactions can be load balanced
- In extended protocol, PARSE/BIND/DESCRIBE messages are sent to
- the node which execute EXECUTE message. This will reduce lock
- contention.
+ the node which execute EXECUTE message, not all node. This
+ will reduce lock contentions.
- Auto start of transaction happens only when it needed.
- Temporary tables can be used safely.
- - SELECT calling functions possibly write to database executes
- on master(primary)
+ - SELECT which calls functions possibly write to database
+ executes on master(primary)
Also many new features are added and major refactoring has been
made to the internal structure of pgpoo-II. For example, in
@@ -45,6 +45,14 @@
replication delay in master/slave/streaming replication
mode(Tatsuo)
+ - When insert_lock is enabled and the table includes SERIAL data
+ type, issue row lock on the sequence table. Before we issues
+ table lock. Problem is, the table lock conflicts with auto
+ vacuum and sometimes caused excessive lock waiting(Tatsuo)
+
+ - Add support for more "SHOW" commands: pool_nodes,
+ pool_processes, and pool_version(Guillaume Lelarge)
+
- Backend process id and whether frontend connects to this
connection pool or not are added to pcp_proc_info's
output(Tatsuo)
@@ -57,6 +65,9 @@
are added to register functions those do not or do write to
database(Tatsuo)
+ - In master/slave mode, SELECTs to system catalogs executes only
+ on master/primary(Tatsuo)
+
- In master/slave mode, SELECTs to temporary table executes only
on master/primary(Tatsuo)
@@ -102,6 +113,12 @@
* Bug fixes
+ - Do not rewrite statement which accesses columns having now()
+ etc. as the default value but the data type are not timestamp
+ etc. Otherwise we have an error in DMLs(Tatsuo)
+
+ - Fix timestamp rewriting not to omit schema qualification(Tatsuo)
+
- Fix bug with timeout handling in pcp commands(Tatsuo)
- Fix SSL hang when large amount of data transfered(Tatsuo)
diff --git a/doc/pgpool-ja.html b/doc/pgpool-ja.html
index 9da4b517a..6408411bd 100644
--- a/doc/pgpool-ja.html
+++ b/doc/pgpool-ja.html
@@ -8,7 +8,7 @@
<body>
<!-- hhmts start -->
-Last modified: Wed Aug 18 08:43:08 JST 2010
+Last modified: Thu Aug 19 18:16:20 JST 2010
<!-- hhmts end -->
<body bgcolor="#ffffff">
@@ -935,7 +935,7 @@ pgpool-II 3.0$B$h$jA0$N%P!<%8%g%s$G$O!"8GDj$G(Bnextval$B$H(Bsetval$B$,=q$-9
</p>
<pre>
white_function_list = ''
-black_function_list = 'upper,lower'
+black_function_list = 'nextval,setval'
</pre>
<a name="replicate_select"></a>
@@ -3726,6 +3726,11 @@ pgpool-II$B$O4pK\E*$K$O(Bmaster/slave mode$B$H$7$FF0:n$7$^$9$,!"$=$N:]$K(B "
</ul>
+ <li>insert_lock$B$,M-8z$G!"%F!<%V%k$K%7%j%"%k7?$,4^$^$l$F$$$k>l9g!"%F!<%V%k%m%C%/$G$O$J$/!"3:Ev%7!<%1%s%9$r9T%m%C%/$9$k$h$&$K$7$^$7$?!#(B
+$B0JA0$O%F!<%V%k%m%C%/$r$7$F$$$^$7$?$,!"(Bauto vacuum$B$H%m%C%/$,>WFM$7$?$j$7$F@-G=$,Dc2<$9$kLdBj$,$"$j$^$7$?(B(Tatsuo)
+
+ <li>$B?7$7$$(B"SHOW"$B%3%^%s%I$,DI2C$5$l$^$7$?!#$9$J$o$A!"(Bpool_nodes, pool_processes, pool_version$B$G$9(B(Guillaume Lelarge)
+
<li>pcp_proc_info$B%3%^%s%I$N=PNO7k2L$K!"(BPostgreSQL$B%P%C%/%(%s%I%W%m%;%9$N%W%m%;%9(BID$B$H%U%m%s%H%(%s%I$+$i$N@\B3$,$"$k$+$I$&$+$,DI2C$5$l$^$7$?(B(Tatsuo)
<li>$B4X?t8F$S=P$7$rH<$&(BSELECT$B$r@)8f$9$k@_Dj9`L\(Bwhite_function_list$B$H(Bback_function_list$B$,DI2C$5$l$^$7$?(B(Tatsuo)
diff --git a/pool_process_query.c b/pool_process_query.c
index 6c3079dab..2514f9822 100644
--- a/pool_process_query.c
+++ b/pool_process_query.c
@@ -1446,7 +1446,6 @@ void reset_variables(void)
if(pool_get_session_context())
pool_unset_query_in_progress();
- internal_transaction_started = 0;
receive_extended_begin = 0;
}
@@ -2736,12 +2735,16 @@ POOL_STATUS do_query(POOL_CONNECTION *backend, char *query, POOL_SELECT_RESULT *
/*
* Judge if we need to lock the table
* to keep SERIAL consistency among servers
+ * Return values are:
+ * 0: lock is not neccessary
+ * 1: table lock is required
+ * 2: row lock is required (SERIAL is used)
*/
int need_insert_lock(POOL_CONNECTION_POOL *backend, char *query, Node *node)
{
/*
* Query to know if the target table has SERIAL column or not.
- * This query is valid through PostgreSQL 7.3 to 8.3.
+ * This query is valid through PostgreSQL 7.3 or higher.
*/
#define NEXTVALQUERY "SELECT count(*) FROM pg_catalog.pg_attrdef AS d, pg_catalog.pg_class AS c WHERE d.adrelid = c.oid AND d.adsrc ~ 'nextval' AND c.oid = '%s'::regclass::oid"
@@ -2803,21 +2806,30 @@ int need_insert_lock(POOL_CONNECTION_POOL *backend, char *query, Node *node)
/*
* Search relcache.
*/
- result = pool_search_relcache(relcache, backend, str)==0?0:1;
+ result = pool_search_relcache(relcache, backend, str)==0?0:2;
return result;
}
/*
- * if a transaction has not already started, start a new one.
- * issue LOCK TABLE IN SHARE ROW EXCLUSIVE MODE
+ * If a transaction has not already started, start a new one.
+ * issue LOCK TABLE IN SHARE ROW EXCLUSIVE MODE if lock_kind == 1.
+ * Issue row lock if lock_kind == 2.
*/
-POOL_STATUS insert_lock(POOL_CONNECTION *frontend, POOL_CONNECTION_POOL *backend, char *query, InsertStmt *node)
+POOL_STATUS insert_lock(POOL_CONNECTION *frontend, POOL_CONNECTION_POOL *backend, char *query, InsertStmt *node, int lock_kind)
{
char *table;
char qbuf[1024];
POOL_STATUS status;
int i, deadlock_detected = 0;
+#define SEQUENCETABLEQUERY "SELECT attname FROM pg_catalog.pg_class c, pg_catalog.pg_attribute a LEFT JOIN pg_catalog.pg_attrdef d ON (a.attrelid = d.adrelid AND a.attnum = d.adnum) WHERE c.oid = a.attrelid AND a.attnum >= 1 AND a.attisdropped = 'f' AND c.oid = '%s'::regclass::oid AND d.adsrc ~ 'nextval'"
+
+#define MAX_SEQ_NAME 128
+
+ char *atrname;
+ char seq_rel_name[MAX_SEQ_NAME+1];
+ static POOL_RELCACHE *relcache;
+
/* insert_lock can be used in V3 only */
if (MAJOR(backend) != PROTO_MAJOR_V3)
return POOL_CONTINUE;
@@ -2831,16 +2843,59 @@ POOL_STATUS insert_lock(POOL_CONNECTION *frontend, POOL_CONNECTION_POOL *backend
return POOL_CONTINUE;
}
- /* issue lock table command */
- snprintf(qbuf, sizeof(qbuf), "LOCK TABLE %s IN SHARE ROW EXCLUSIVE MODE", table);
+ /* row lock for sequence table? */
+ if (lock_kind == 2)
+ {
+ /*
+ * If relcache does not exist, create it.
+ */
+ if (!relcache)
+ {
+ relcache = pool_create_relcache(32, SEQUENCETABLEQUERY,
+ string_register_func, string_unregister_func,
+ false);
+ if (relcache == NULL)
+ {
+ pool_error("insert_lock: pool_create_relcache error");
+ return false;
+ }
+ }
+
+ /*
+ * Search relcache.
+ */
+ atrname = pool_search_relcache(relcache, backend, table);
+ if (atrname == NULL)
+ {
+ /* 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);
+ }
+ else
+ {
+ /* Issue lock table command */
+ snprintf(qbuf, sizeof(qbuf), "LOCK TABLE %s IN SHARE ROW EXCLUSIVE MODE", table);
+ }
per_node_statement_log(backend, MASTER_NODE_ID, qbuf);
- status = do_command(frontend, MASTER(backend), qbuf, MAJOR(backend), MASTER_CONNECTION(backend)->pid,
- MASTER_CONNECTION(backend)->key, 0);
+ if (lock_kind == 1)
+ {
+ status = do_command(frontend, MASTER(backend), qbuf, MAJOR(backend), MASTER_CONNECTION(backend)->pid,
+ MASTER_CONNECTION(backend)->key, 0);
+ }
+ else
+ {
+ POOL_SELECT_RESULT *result;
+ status = do_query(MASTER(backend), qbuf, &result, MAJOR(backend));
+ if (result)
+ free_select_result(result);
+ }
if (status == POOL_END)
{
- internal_transaction_started = 0;
return POOL_END;
}
else if (status == POOL_DEADLOCK)
@@ -2859,7 +2914,6 @@ POOL_STATUS insert_lock(POOL_CONNECTION *frontend, POOL_CONNECTION_POOL *backend
if (status != POOL_CONTINUE)
{
- internal_transaction_started = 0;
return POOL_END;
}
}
diff --git a/pool_proto_modules.c b/pool_proto_modules.c
index e8c0b5413..0e286c132 100644
--- a/pool_proto_modules.c
+++ b/pool_proto_modules.c
@@ -56,8 +56,6 @@
#include "pool_query_context.h"
#include "pool_lobj.h"
-int internal_transaction_started; /* to issue table lock command a transaction
- has been started internally */
char *copy_table = NULL; /* copy table name */
char *copy_schema = NULL; /* copy table name */
char copy_delimiter; /* copy delimiter char */
@@ -114,6 +112,7 @@ POOL_STATUS SimpleQuery(POOL_CONNECTION *frontend,
Node *node = NULL;
POOL_STATUS status;
char *string;
+ int lock_kind;
POOL_SESSION_CONTEXT *session_context;
POOL_QUERY_CONTEXT *query_context;
@@ -313,10 +312,11 @@ POOL_STATUS SimpleQuery(POOL_CONNECTION *frontend,
return POOL_END;
/* check if need lock */
- if (need_insert_lock(backend, contents, node))
+ lock_kind = need_insert_lock(backend, contents, node);
+ if (lock_kind)
{
/* if so, issue lock command */
- status = insert_lock(frontend, backend, contents, (InsertStmt *)node);
+ status = insert_lock(frontend, backend, contents, (InsertStmt *)node, lock_kind);
if (status != POOL_CONTINUE)
{
free_parser();
@@ -715,7 +715,7 @@ POOL_STATUS Parse(POOL_CONNECTION *frontend, POOL_CONNECTION_POOL *backend,
if (insert_stmt_with_lock)
{
/* start a transaction if needed and lock the table */
- status = insert_lock(frontend, backend, stmt, (InsertStmt *)query_context->parse_tree);
+ status = insert_lock(frontend, backend, stmt, (InsertStmt *)query_context->parse_tree, insert_stmt_with_lock);
if (status != POOL_CONTINUE)
{
/* free_parser(); */
diff --git a/pool_proto_modules.h b/pool_proto_modules.h
index 1d239f412..71fcf0e37 100644
--- a/pool_proto_modules.h
+++ b/pool_proto_modules.h
@@ -37,8 +37,6 @@
#define SPECIFIED_ERROR 1
#define POOL_ERROR_QUERY "send invalid query from pgpool to abort transaction"
-extern int internal_transaction_started; /* to issue table lock command a transaction
- has been started internally */
extern char *copy_table; /* copy table name */
extern char *copy_schema; /* copy table name */
extern char copy_delimiter; /* copy delimiter char */
@@ -159,7 +157,7 @@ extern int is_commit_query(Node *node);
extern int is_strict_query(Node *node); /* returns non 0 if this is strict query */
extern int load_balance_enabled(POOL_CONNECTION_POOL *backend, Node* node, char *sql);
extern int need_insert_lock(POOL_CONNECTION_POOL *backend, char *query, Node *node);
-extern POOL_STATUS insert_lock(POOL_CONNECTION *frontend, POOL_CONNECTION_POOL *backend, char *query, InsertStmt *node);
+extern POOL_STATUS insert_lock(POOL_CONNECTION *frontend, POOL_CONNECTION_POOL *backend, char *query, InsertStmt *node, int lock_kind);
extern char *parse_copy_data(char *buf, int len, char delimiter, int col_id);
extern int check_copy_from_stdin(Node *node); /* returns non 0 if this is a COPY FROM STDIN */
extern void query_ps_status(char *query, POOL_CONNECTION_POOL *backend); /* show ps status */
diff --git a/pool_relcache.c b/pool_relcache.c
index cdee4325a..6445da172 100644
--- a/pool_relcache.c
+++ b/pool_relcache.c
@@ -225,3 +225,28 @@ void *int_unregister_func(void *data)
/* Nothing to do since no memory was allocated */
return NULL;
}
+
+void *string_register_func(POOL_SELECT_RESULT *res)
+{
+ char *ret = NULL;
+ int i;
+
+ if (res->numrows == 0)
+ return NULL;
+
+ for (i = 0; i < res->numrows; i++)
+ {
+ ret = malloc(strlen(res->data[i])+1);
+ if (ret)
+ ret = strdup(res->data[i]);
+ break;
+ }
+
+ return (void *)ret;
+}
+
+void *string_unregister_func(void *data)
+{
+ free(data);
+ return data;
+}
diff --git a/pool_relcache.h b/pool_relcache.h
index 2c1590ff4..174d3543c 100644
--- a/pool_relcache.h
+++ b/pool_relcache.h
@@ -70,5 +70,7 @@ extern void pool_discard_relcache(POOL_RELCACHE *relcache);
extern void *pool_search_relcache(POOL_RELCACHE *relcache, POOL_CONNECTION_POOL *backend, char *table);
extern void *int_register_func(POOL_SELECT_RESULT *res);
extern void *int_unregister_func(void *data);
+extern void *string_register_func(POOL_SELECT_RESULT *res);
+extern void *string_unregister_func(void *data);
#endif /* POOL_RELCACHE_H */
diff --git a/pool_session_context.h b/pool_session_context.h
index c922c4b72..f90705346 100644
--- a/pool_session_context.h
+++ b/pool_session_context.h
@@ -167,52 +167,6 @@ typedef struct {
* If true, we are executing reset query list.
*/
bool reset_context;
-
-#ifdef NOT_USED
-/* We need to override these gotchas... */
- int internal_transaction_started; /* to issue table lock command a transaction
- has been started internally */
- int mismatch_ntuples; /* number of updated tuples */
- char *copy_table = NULL; /* copy table name */
- char *copy_schema = NULL; /* copy table name */
- char copy_delimiter; /* copy delimiter char */
- char *copy_null = NULL; /* copy null string */
-
-/* non 0 if "BEGIN" query with extended query protocol received */
- int receive_extended_begin = 0;
-
-/*
- * Non 0 if allow to close internal transaction. This variable was
- * introduced on 2008/4/3 not to close an internal transaction when
- * Sync message is received after receiving Parse message. This hack
- * is for PHP-PDO.
- */
- static int allow_close_transaction = 1;
-
- PreparedStatementList prepared_list; /* prepared statement name list */
-
- int is_select_pgcatalog = 0;
- int is_select_for_update = 0; /* 1 if SELECT INTO or SELECT FOR UPDATE */
- bool is_parallel_table = false;
-
-/*
- * last query string sent to simpleQuery()
- */
- char query_string_buffer[QUERY_STRING_BUFFER_LEN];
-
-/*
- * query string produced by nodeToString() in simpleQuery().
- * this variable only usefull when enable_query_cache is true.
- */
- char *parsed_query = NULL;
-
-
- int load_balance_node_id; /* selected load balance node id */
- bool received_write_query; /* have we recived a write query in this transaction? */
- bool send_ready_for_query; /* ok to send ReadyForQuery */
- LOAD_BALANCE_STATUS load_balance_status[MAX_NUM_BACKENDS]; /* to remember which DB node is selected for load balancing */
-#endif
-
} POOL_SESSION_CONTEXT;
extern void pool_init_session_context(POOL_CONNECTION *frontend, POOL_CONNECTION_POOL *backend);
[parent: 50e6c5e32ae3]