pgpool2.git / summary / log / commit / refs
commit b7a2870a87ea54012a67ea758120cfd2528490ac
Author: Toshihiro Kitagawa <kitagawa at sraoss.co.jp>
Date: Fri Jul 01 10:47:14 2011 +0000
Fix insert_lock so that it locks pgpool_catalog.insert_lock because
new PostgreSQL disallows a row lock against the sequence relation.
Add options for compatibility in configure script:
--enable-sequence-lock insert_lock compatible with pgpool-II 3.0 series
(until 3.0.4)
--enable-table-lock insert_lock compatible with pgpool-II 2.2 and 2.3
series
See following thread for more details:
[Pgpool-hackers] Alternative to locking sequences
Makefile.am | 5 +-
Makefile.in | 5 +-
config.h.in | 8 ++
configure | 133 ++++++++++++++++---
configure.in | 26 ++++
doc/pgpool-en.html | 67 ++++++++--
doc/pgpool-ja.html | 33 ++++-
pgpool.conf.sample | 5 +-
pgpool.conf.sample-master-slave | 5 +-
pgpool.conf.sample-replication | 5 +-
pgpool.conf.sample-stream | 5 +-
pool_process_query.c | 285 ++++++++++++++++++++++++++++++++++++++--
sql/insert_lock.sql | 14 ++
13 files changed, 542 insertions(+), 54 deletions(-)
diff --git a/Makefile.am b/Makefile.am
index 352c536e3..e3a00f6c5 100644
--- a/Makefile.am
+++ b/Makefile.am
@@ -38,7 +38,7 @@ DEFS = @DEFS@ \
sysconf_DATA = pgpool.conf.sample pcp.conf.sample pool_hba.conf.sample \
pgpool.conf.sample-replication pgpool.conf.sample-master-slave \
pgpool.conf.sample-stream
-pkgdata_DATA = sql/system_db.sql sample/pgpool.pam
+pkgdata_DATA = sql/insert_lock.sql sql/system_db.sql sample/pgpool.pam
AM_CPPFLAGS = -D_GNU_SOURCE -I @PGSQL_INCLUDE_DIR@
@@ -60,13 +60,14 @@ pgpool.8: pgpool.8.in
sed 's,@sysconfdir\@,$(sysconfdir),g' $? >$@
CLEANFILES = pgpool.8
-EXTRA_DIST = pgpool.8.in sql/system_db.sql sample/pgpool.pam doc/pgpool-ja.html doc/pgpool-en.html \
+EXTRA_DIST = pgpool.8.in sample/pgpool.pam doc/pgpool-ja.html doc/pgpool-en.html \
doc/tutorial-ja.html doc/tutorial-en.html doc/pgpool.css \
doc/where_to_send_queries.pdf doc/where_to_send_queries.odg doc/pgpool-ja.css \
doc/basebackup.sh doc/pgpool_remote_start doc/recovery.conf.sample \
sample/pgpool_remote_start sample/pgpool_recovery sample/pgpool_recovery_pitr \
sample/dist_def_pgbench.sql sample/replicate_def_pgbench.sql \
sql/Makefile \
+ sql/insert_lock.sql sql/system_db.sql \
sql/pgpool-recovery/pgpool-recovery.c \
sql/pgpool-recovery/pgpool-recovery.sql.in sql/pgpool-recovery/Makefile \
sql/pgpool-regclass/pgpool-regclass.c \
diff --git a/Makefile.in b/Makefile.in
index d8ccbdc20..196fe7962 100644
--- a/Makefile.in
+++ b/Makefile.in
@@ -294,7 +294,7 @@ sysconf_DATA = pgpool.conf.sample pcp.conf.sample pool_hba.conf.sample \
pgpool.conf.sample-replication pgpool.conf.sample-master-slave \
pgpool.conf.sample-stream
-pkgdata_DATA = sql/system_db.sql sample/pgpool.pam
+pkgdata_DATA = sql/insert_lock.sql sql/system_db.sql sample/pgpool.pam
AM_CPPFLAGS = -D_GNU_SOURCE -I @PGSQL_INCLUDE_DIR@
# suggested by libtoolize
@@ -305,13 +305,14 @@ pgpool_LDADD = -L@PGSQL_LIB_DIR@ -lpq parser/libsql-parser.a pcp/libpcp.la parse
AM_YFLAGS = -d
man_MANS = pgpool.8
CLEANFILES = pgpool.8
-EXTRA_DIST = pgpool.8.in sql/system_db.sql sample/pgpool.pam doc/pgpool-ja.html doc/pgpool-en.html \
+EXTRA_DIST = pgpool.8.in sample/pgpool.pam doc/pgpool-ja.html doc/pgpool-en.html \
doc/tutorial-ja.html doc/tutorial-en.html doc/pgpool.css \
doc/where_to_send_queries.pdf doc/where_to_send_queries.odg doc/pgpool-ja.css \
doc/basebackup.sh doc/pgpool_remote_start doc/recovery.conf.sample \
sample/pgpool_remote_start sample/pgpool_recovery sample/pgpool_recovery_pitr \
sample/dist_def_pgbench.sql sample/replicate_def_pgbench.sql \
sql/Makefile \
+ sql/insert_lock.sql sql/system_db.sql \
sql/pgpool-recovery/pgpool-recovery.c \
sql/pgpool-recovery/pgpool-recovery.sql.in sql/pgpool-recovery/Makefile \
sql/pgpool-regclass/pgpool-regclass.c \
diff --git a/config.h.in b/config.h.in
index 175f287db..52fc050a8 100644
--- a/config.h.in
+++ b/config.h.in
@@ -292,9 +292,17 @@
/* Use replacement snprintf() functions. */
#undef USE_REPL_SNPRINTF
+/* Define to 1 if you want to use row lock against the sequence table for
+ insert_lock. (--enable-sequence-lock) */
+#undef USE_SEQUENCE_LOCK
+
/* Define to 1 to build with SSL support. (--with-openssl) */
#undef USE_SSL
+/* Define to 1 if you want to use table lock against the target table for
+ insert_lock. (--enable-table-lock) */
+#undef USE_TABLE_LOCK
+
/* Version number of package */
#undef VERSION
diff --git a/configure b/configure
index bc0119b06..c5afefb2d 100755
--- a/configure
+++ b/configure
@@ -917,6 +917,8 @@ with_pgsql_libdir
with_openssl
with_pam
enable_rpath
+enable_sequence_lock
+enable_table_lock
'
ac_precious_vars='build_alias
host_alias
@@ -1567,6 +1569,10 @@ Optional Features:
--disable-float4-byval disable float4 passed by value
--disable-float8-byval disable float8 passed by value
--disable-rpath do not embed shared library search path in executables
+ --enable-sequence-lock insert_lock compatible with pgpool-II 3.0 series
+ (until 3.0.4)
+ --enable-table-lock insert_lock compatible with pgpool-II 2.2 and 2.3
+ series
Optional Packages:
--with-PACKAGE[=ARG] use PACKAGE [ARG=yes]
@@ -4423,13 +4429,13 @@ if test "${lt_cv_nm_interface+set}" = set; then
else
lt_cv_nm_interface="BSD nm"
echo "int some_variable = 0;" > conftest.$ac_ext
- (eval echo "\"\$as_me:4426: $ac_compile\"" >&5)
+ (eval echo "\"\$as_me:4432: $ac_compile\"" >&5)
(eval "$ac_compile" 2>conftest.err)
cat conftest.err >&5
- (eval echo "\"\$as_me:4429: $NM \\\"conftest.$ac_objext\\\"\"" >&5)
+ (eval echo "\"\$as_me:4435: $NM \\\"conftest.$ac_objext\\\"\"" >&5)
(eval "$NM \"conftest.$ac_objext\"" 2>conftest.err > conftest.out)
cat conftest.err >&5
- (eval echo "\"\$as_me:4432: output\"" >&5)
+ (eval echo "\"\$as_me:4438: output\"" >&5)
cat conftest.out >&5
if $GREP 'External.*some_variable' conftest.out > /dev/null; then
lt_cv_nm_interface="MS dumpbin"
@@ -5635,7 +5641,7 @@ ia64-*-hpux*)
;;
*-*-irix6*)
# Find out which ABI we are using.
- echo '#line 5638 "configure"' > conftest.$ac_ext
+ echo '#line 5644 "configure"' > conftest.$ac_ext
if { (eval echo "$as_me:$LINENO: \"$ac_compile\"") >&5
(eval $ac_compile) 2>&5
ac_status=$?
@@ -7463,6 +7469,10 @@ _lt_linker_boilerplate=`cat conftest.err`
$RM -r conftest*
+## CAVEAT EMPTOR:
+## There is no encapsulation within the following macros, do not change
+## the running order or otherwise move them around unless you know exactly
+## what you are doing...
if test -n "$compiler"; then
lt_prog_compiler_no_builtin_flag=
@@ -7488,11 +7498,11 @@ else
-e 's:.*FLAGS}\{0,1\} :&$lt_compiler_flag :; t' \
-e 's: [^ ]*conftest\.: $lt_compiler_flag&:; t' \
-e 's:$: $lt_compiler_flag:'`
- (eval echo "\"\$as_me:7491: $lt_compile\"" >&5)
+ (eval echo "\"\$as_me:7501: $lt_compile\"" >&5)
(eval "$lt_compile" 2>conftest.err)
ac_status=$?
cat conftest.err >&5
- echo "$as_me:7495: \$? = $ac_status" >&5
+ echo "$as_me:7505: \$? = $ac_status" >&5
if (exit $ac_status) && test -s "$ac_outfile"; then
# The compiler can only warn and ignore the option if not recognized
# So say no if there are warnings other than the usual output.
@@ -7827,11 +7837,11 @@ else
-e 's:.*FLAGS}\{0,1\} :&$lt_compiler_flag :; t' \
-e 's: [^ ]*conftest\.: $lt_compiler_flag&:; t' \
-e 's:$: $lt_compiler_flag:'`
- (eval echo "\"\$as_me:7830: $lt_compile\"" >&5)
+ (eval echo "\"\$as_me:7840: $lt_compile\"" >&5)
(eval "$lt_compile" 2>conftest.err)
ac_status=$?
cat conftest.err >&5
- echo "$as_me:7834: \$? = $ac_status" >&5
+ echo "$as_me:7844: \$? = $ac_status" >&5
if (exit $ac_status) && test -s "$ac_outfile"; then
# The compiler can only warn and ignore the option if not recognized
# So say no if there are warnings other than the usual output.
@@ -7932,11 +7942,11 @@ else
-e 's:.*FLAGS}\{0,1\} :&$lt_compiler_flag :; t' \
-e 's: [^ ]*conftest\.: $lt_compiler_flag&:; t' \
-e 's:$: $lt_compiler_flag:'`
- (eval echo "\"\$as_me:7935: $lt_compile\"" >&5)
+ (eval echo "\"\$as_me:7945: $lt_compile\"" >&5)
(eval "$lt_compile" 2>out/conftest.err)
ac_status=$?
cat out/conftest.err >&5
- echo "$as_me:7939: \$? = $ac_status" >&5
+ echo "$as_me:7949: \$? = $ac_status" >&5
if (exit $ac_status) && test -s out/conftest2.$ac_objext
then
# The compiler can only warn and ignore the option if not recognized
@@ -7987,11 +7997,11 @@ else
-e 's:.*FLAGS}\{0,1\} :&$lt_compiler_flag :; t' \
-e 's: [^ ]*conftest\.: $lt_compiler_flag&:; t' \
-e 's:$: $lt_compiler_flag:'`
- (eval echo "\"\$as_me:7990: $lt_compile\"" >&5)
+ (eval echo "\"\$as_me:8000: $lt_compile\"" >&5)
(eval "$lt_compile" 2>out/conftest.err)
ac_status=$?
cat out/conftest.err >&5
- echo "$as_me:7994: \$? = $ac_status" >&5
+ echo "$as_me:8004: \$? = $ac_status" >&5
if (exit $ac_status) && test -s out/conftest2.$ac_objext
then
# The compiler can only warn and ignore the option if not recognized
@@ -9839,13 +9849,10 @@ rm -f core conftest.err conftest.$ac_objext conftest_ipa8_conftest.oo \
# before this can be enabled.
hardcode_into_libs=yes
- # Add ABI-specific directories to the system library path.
- sys_lib_dlsearch_path_spec="/lib64 /usr/lib64 /lib /usr/lib"
-
# Append ld.so.conf contents to the search path
if test -f /etc/ld.so.conf; then
lt_ld_extra=`awk '/^include / { system(sprintf("cd /etc; cat %s 2>/dev/null", \$2)); skip = 1; } { if (!skip) print \$0; skip = 0; }' < /etc/ld.so.conf | $SED -e 's/#.*//;/^[ ]*hwcap[ ]/d;s/[:, ]/ /g;s/=[^=]*$//;s/=[^= ]* / /g;/^$/d' | tr '\n' ' '`
- sys_lib_dlsearch_path_spec="$sys_lib_dlsearch_path_spec $lt_ld_extra"
+ sys_lib_dlsearch_path_spec="/lib /usr/lib $lt_ld_extra"
fi
# We used to test for /lib/ld.so.1 and disable shared libraries on
@@ -10790,7 +10797,7 @@ else
lt_dlunknown=0; lt_dlno_uscore=1; lt_dlneed_uscore=2
lt_status=$lt_dlunknown
cat > conftest.$ac_ext <<_LT_EOF
-#line 10793 "configure"
+#line 10800 "configure"
#include "confdefs.h"
#if HAVE_DLFCN_H
@@ -10886,7 +10893,7 @@ else
lt_dlunknown=0; lt_dlno_uscore=1; lt_dlneed_uscore=2
lt_status=$lt_dlunknown
cat > conftest.$ac_ext <<_LT_EOF
-#line 10889 "configure"
+#line 10896 "configure"
#include "confdefs.h"
#if HAVE_DLFCN_H
@@ -17409,6 +17416,96 @@ else
fi
+# Decide whether to use row lock against the sequence table for insert_lock.
+# This lock method is compatible with pgpool-II 3.0 series(until 3.0.4).
+{ $as_echo "$as_me:$LINENO: checking whether to use row lock against the sequence table for insert_lock" >&5
+$as_echo_n "checking whether to use row lock against the sequence table for insert_lock... " >&6; }
+
+pgac_args="$pgac_args enable_sequence_lock"
+
+# Check whether --enable-sequence-lock was given.
+if test "${enable_sequence_lock+set}" = set; then
+ enableval=$enable_sequence_lock;
+ case $enableval in
+ yes)
+ :
+ ;;
+ no)
+ :
+ ;;
+ *)
+ { { $as_echo "$as_me:$LINENO: error: no argument expected for --enable-sequence-lock option" >&5
+$as_echo "$as_me: error: no argument expected for --enable-sequence-lock option" >&2;}
+ { (exit 1); exit 1; }; }
+ ;;
+ esac
+
+else
+ enable_sequence_lock=no
+
+fi
+
+
+if test "$enable_sequence_lock" = yes && test "$enable_table_lock" = yes ; then
+ { { $as_echo "$as_me:$LINENO: error: --enable-table-lock cannot be enabled at the same time." >&5
+$as_echo "$as_me: error: --enable-table-lock cannot be enabled at the same time." >&2;}
+ { (exit 1); exit 1; }; }
+fi
+if test "$enable_sequence_lock" = yes ; then
+
+cat >>confdefs.h <<\_ACEOF
+#define USE_SEQUENCE_LOCK 1
+_ACEOF
+
+fi
+{ $as_echo "$as_me:$LINENO: result: $enable_sequence_lock" >&5
+$as_echo "$enable_sequence_lock" >&6; }
+
+# Decide whether to use table lock against the target table for insert_lock.
+# This lock method is compatible with pgpool-II 2.2 and 2.3 series.
+{ $as_echo "$as_me:$LINENO: checking whether to use table lock against the target table for insert_lock" >&5
+$as_echo_n "checking whether to use table lock against the target table for insert_lock... " >&6; }
+
+pgac_args="$pgac_args enable_table_lock"
+
+# Check whether --enable-table-lock was given.
+if test "${enable_table_lock+set}" = set; then
+ enableval=$enable_table_lock;
+ case $enableval in
+ yes)
+ :
+ ;;
+ no)
+ :
+ ;;
+ *)
+ { { $as_echo "$as_me:$LINENO: error: no argument expected for --enable-table-lock option" >&5
+$as_echo "$as_me: error: no argument expected for --enable-table-lock option" >&2;}
+ { (exit 1); exit 1; }; }
+ ;;
+ esac
+
+else
+ enable_table_lock=no
+
+fi
+
+
+if test "$enable_table_lock" = yes && test "$enable_sequence_lock" = yes ; then
+ { { $as_echo "$as_me:$LINENO: error: --enable-sequence-lock cannot be enabled at the same time." >&5
+$as_echo "$as_me: error: --enable-sequence-lock cannot be enabled at the same time." >&2;}
+ { (exit 1); exit 1; }; }
+fi
+if test "$enable_table_lock" = yes ; then
+
+cat >>confdefs.h <<\_ACEOF
+#define USE_TABLE_LOCK 1
+_ACEOF
+
+fi
+{ $as_echo "$as_me:$LINENO: result: $enable_table_lock" >&5
+$as_echo "$enable_table_lock" >&6; }
+
ac_config_headers="$ac_config_headers config.h"
diff --git a/configure.in b/configure.in
index d932d690a..394f51321 100644
--- a/configure.in
+++ b/configure.in
@@ -332,6 +332,32 @@ AC_ARG_ENABLE(rpath,
)
AM_CONDITIONAL([enable_rpath], test x$rpath = xyes)
+# Decide whether to use row lock against the sequence table for insert_lock.
+# This lock method is compatible with pgpool-II 3.0 series(until 3.0.4).
+AC_MSG_CHECKING([whether to use row lock against the sequence table for insert_lock])
+PGAC_ARG_BOOL(enable, sequence-lock, no, [insert_lock compatible with pgpool-II 3.0 series (until 3.0.4)])
+if test "$enable_sequence_lock" = yes && test "$enable_table_lock" = yes ; then
+ AC_MSG_ERROR([--enable-table-lock cannot be enabled at the same time.])
+fi
+if test "$enable_sequence_lock" = yes ; then
+ AC_DEFINE([USE_SEQUENCE_LOCK], 1,
+ [Define to 1 if you want to use row lock against the sequence table for insert_lock. (--enable-sequence-lock)])
+fi
+AC_MSG_RESULT([$enable_sequence_lock])
+
+# Decide whether to use table lock against the target table for insert_lock.
+# This lock method is compatible with pgpool-II 2.2 and 2.3 series.
+AC_MSG_CHECKING([whether to use table lock against the target table for insert_lock])
+PGAC_ARG_BOOL(enable, table-lock, no, [insert_lock compatible with pgpool-II 2.2 and 2.3 series])
+if test "$enable_table_lock" = yes && test "$enable_sequence_lock" = yes ; then
+ AC_MSG_ERROR([--enable-sequence-lock cannot be enabled at the same time.])
+fi
+if test "$enable_table_lock" = yes ; then
+ AC_DEFINE([USE_TABLE_LOCK], 1,
+ [Define to 1 if you want to use table lock against the target table for insert_lock. (--enable-table-lock)])
+fi
+AC_MSG_RESULT([$enable_table_lock])
+
AM_CONFIG_HEADER(config.h)
AC_OUTPUT([Makefile parser/Makefile pcp/Makefile])
diff --git a/doc/pgpool-en.html b/doc/pgpool-en.html
index adf971f2b..1681a2e64 100644
--- a/doc/pgpool-en.html
+++ b/doc/pgpool-en.html
@@ -167,6 +167,14 @@ installed. Default value is provided by <code>pg_config</code></li>
<li><code>--with-openssl</code><br/>
pgpool-II binaries will be built with OpenSSL support. OpenSSL support
is disabled by default.</li>
+ <li><code>--enable-sequence-lock</code><br/>
+ Use insert_lock compatible with pgpool-II 3.0 series(until 3.0.4).
+ pgpool-II locks against a row in the sequence table.
+ PostgreSQL 8.2 or later which was released after June 2011 cannot use this lock method.
+ <li><code>--enable-table-lock</code><br/>
+ Use insert_lock compatible with pgpool-II 2.2 and 2.3 series.
+ pgpool-II locks against the insert target table.
+ This lock method is deprecated because it causes a lock conflict with VACUUM.
</ul>
</p>
</dd>
@@ -198,7 +206,7 @@ psql -f pgpool-regclass.sql template1
</pre>
</p>
<p>
-Executing pgpool-regclass.sql should be performed on every database accessed
+Executing pgpool-regclass.sql should be performed on every databases accessed
with pgpool-II.
You do not need to do this for a database created after the
execution of "psql -f pgpool-regclass.sql template1", as this template database
@@ -219,13 +227,37 @@ psql -f pgpool-walrecrunning.sql template1
</pre>
</p>
<p>
-Executing pgpool-walrecrunning.sql should be performed on every database accessed
+Executing pgpool-walrecrunning.sql should be performed on every databases accessed
with pgpool-II.
You do not need to do this for a database created after the
execution of "psql -f pgpool-regclass.sql template1", as this template database
will be cloned to create new databases.
</p>
+<dt>Creating insert_lock table</dt>
+<dd>
+<p>
+If you use insert_lock in replication mode, creating pgpool_catalog.insert_lock
+table for mutual exclusion is strongly recommended.
+Without this, insert_lock works so far. However in that case pgpool-II locks
+against the insert target table. This behavior is same as pgpool-II 2.2 and 2.3
+series. The table lock conflicts with VACUUM. So INSERT processing may be thereby
+kept waiting for a long time.
+</p>
+<p>
+<pre>
+cd pgpool-II-x.x.x/sql
+psql -f insert_lock.sql template1
+</pre>
+</p>
+<p>
+Executing insert_lock.sql should be performed on every databases accessed
+with pgpool-II.
+You do not need to do this for a database created after the execution of
+"psql -f insert_lock.sql template1", as this template database will be
+cloned to create new databases.
+</p>
+
</dl>
<h1>Configuring pgpool-II<a name="config"></a></h1>
@@ -1216,15 +1248,34 @@ made:
executed (if already in transaction, it simply adds LOCK TABLE
....).
</p>
- <p>As of pgpool-II 2.2, it automatically detects if the table has a
- SERIAL columns or not, so only tables having SERIAL columns are
- locked.
+ <p>pgpool-II 2.2 or later, it automatically detects whether the table
+ has a SERIAL columns or not, so it will never lock the table if it does
+ not use SERIAL columns.
</p>
<p>
- pgpool-II 3.0 or later uses a row lock against the sequence
- relation, rather than table lock. This will minimize lock
- conflict with VACUUM (including autovacuum).
+ pgpool-II 3.0 series until 3.0.4 uses a row lock against the sequence
+ relation, rather than table lock. This is intended to minimize lock
+ conflict with VACUUM (including autovacuum). However this will lead to
+ another problem. After transaction wraparound happens, row locking
+ against the sequence relation causes PostgreSQL internal error (more
+ precisely, access error on pg_clog, which keeps transaction status).
+ To prevent this, PostgreSQL core developers decided to disallow row
+ locking against sequences and this will break pgpool-II of course (the
+ "fixed" version of PostgreSQL will be released as 9.0.5,
+ 8.4.9, 8.3.16 and 8.2.22).
</p>
+ <p>
+ pgpool-II 3.0.5 or later uses a row lock against pgpool_catalog.insert_lock
+ table because new PostgreSQL disallows a row lock against the sequence
+ relation. So creating insert_lock table in all databases which are
+ accessed via pgpool-II beforehand is required. See <a href="#install">
+ Creating insert_lock table</a> for more details. If does not
+ exist insert_lock table, pgpool-II locks the insert target
+ table. This behavior is same as pgpool-II 2.2 and 2.3 series.
+ If you want to use insert_lock which is compatible with older
+ releases, you can specify lock method by configure script. See
+ <a href="#install">configure</a> for more details.
+ </p>
<p>
You might want to have a finer (per statement) control:
</p>
diff --git a/doc/pgpool-ja.html b/doc/pgpool-ja.html
index de050b075..792a9f225 100644
--- a/doc/pgpool-ja.html
+++ b/doc/pgpool-ja.html
@@ -147,7 +147,14 @@ PostgreSQL$B$N%/%i%$%"%s%H%i%$%V%i%j$J$I$,%$%s%9%H!<%k$5$l$F$$$k%H%C%W%G%#(B
<li><code>--with-openssl</code><br/>
pgpool-II$B$r(BOpenSSL$B%5%]!<%HIU$G:n@.$7$^$9!#(B
$B%G%U%)%k%H$G$O(BOpenSSL$B%5%]!<%H$OL58z$G$9!#(B</li>
-
+ <li><code>--enable-sequence-lock</code><br/>
+ pgpool-II 3.0$B%7%j!<%:(B(3.0.4$B$^$G(B)$B8_49$N(Binsert_lock$B$r;HMQ$7$^$9!#(B
+ pgpool-II$B$O!"%7!<%1%s%9%F!<%V%k$N9T$KBP$7$F%m%C%/$r9T$$$^$9!#(B
+ $B$3$l$O!"(B2011$BG/(B06$B7n$h$j8e$K%j%j!<%9$5$l$?(BPostgreSQL 8.2$B0J9_$G$O;HMQ$G$-$^$;$s!#(B</li>
+ <li><code>--enable-table-lock</code><br/>
+ pgpool-II 2.2$B$H(B2.3$B%7%j!<%:8_49$N(Binsert_lock$B$r;HMQ$7$^$9!#(B
+ pgpool-II$B$O!"A^F~BP>]$N%F!<%V%k$KBP$7$F%m%C%/$r9T$$$^$9!#(B
+ $B$3$l$O!"%m%C%/$,(BVACUUM$B$H6%9g$9$k$?$aHs?d>)$G$9!#(B</li>
</ul>
</p>
@@ -194,6 +201,21 @@ pgpool-walrecrunning.sql$B$N<B9T$O!"(Bpgpool-II$B7PM3$GMxMQ$9$k%G!<%?%Y!<%9Kh
$B$?$@$7!"(B"psql -f pgpool-walrecrunning.sql template1"$B$r<B9T8e$K:n@.$5$l$?%G!<%?%Y!<%9$G$O<+F0E*$K(Bpgpool-walrecrunning.sql$B$NFbMF$,H?1G$5$l$F$$$k$N$G!"?7$?$K(Bpgpool-walrecrunning.sql$B$r<B9T$9$kI,MW$O$"$j$^$;$s!#(B
</p>
+<dt>insert_lock$B%F!<%V%k$N:n@.(B
+<dd>
+<p>
+$B%l%W%j%1!<%7%g%s%b!<%I$G(Binsert_lock$B$rMxMQ$7$?$$>l9g$O!"GSB>@)8fMQ$N%F!<%V%k(Bpgpool_catalog.insert_lock$B$r:n@.$7$^$9!#(B
+insert_lock$B%F!<%V%k$,B8:_$7$J$/$F$b:#$N$H$3$m(Binsert_lock$B$OF0:n$7$^$9$,!"$=$N>l9g$O!"A^F~BP>]$N%F!<%V%k$KBP$7$F%m%C%/$,9T$o$l$^$9!#$3$l$O(Bpgpool-II 2.2$B$H(B2.3$B%7%j!<%:$NF0:n$HF1$8$G$9!#A^F~BP>]$N%F!<%V%k$KBP$9$k%m%C%/$O!"(BVACUUM$B$H6%9g$7$F(BINSERT$B=hM}$,D9;~4V$,BT$?$5$l$k2DG=@-$,$"$j$^$9!#(B
+$B$7$?$,$C$F!"(Binsert_lock$B%F!<%V%k$r:n@.$9$k$3$H$r$*4+$a$7$^$9!#(B
+$B%F!<%V%k$N:n@.$O!"(Bpgpool-II$B$,%"%/%;%9$9$kM=Dj$N$9$Y$F$N(BPostgreSQL$B%5!<%P$G<B;\$7$F$/$@$5$$!#(B
+<pre>
+cd pgpool-II-x.x.x/sql
+psql -f insert_lock.sql template1
+</pre>
+insert_lock.sql$B$N<B9T$O!"(Bpgpool-II$B7PM3$GMxMQ$9$k%G!<%?%Y!<%9Kh$KI,MW$K$J$j$^$9!#(B
+$B$?$@$7!"(B"psql -f insert_lock.sql template1"$B$r<B9T8e$K:n@.$5$l$?%G!<%?%Y!<%9$G$O<+F0E*$K(Binsert_lock.sql$B$NFbMF$,H?1G$5$l$F$$$k$N$G!"?7$?$K(Binsert_lock.sql$B$r<B9T$9$kI,MW$O$"$j$^$;$s!#(B
+</p>
+
</dl>
<p>
$B0J>e$G%$%s%9%H!<%k$,40N;$7$^$9(B(GNU make$B$,I,MW$J$N$G!"(BSolaris$B$d(BFreeBSD$B$J$I$G$O(B
@@ -1135,10 +1157,15 @@ replicate_select$B!"(Bload_balance_mode$B!"(BSELECT$BLd9g$o$;$,L@<(E*$J%H%i
$B%s$,3+;O$5$l$F$$$k>l9g$O(BLOCK TABLE...$B$@$1$,<B9T$5$l$^$9!K!#(B
</p>
<p>
-$B$J$*!"(Bpgpool-II 3.0$B0J9_$G$O!"BP1~$9$k%7!<%1%s%9%F!<%V%k$KBP$7$F9T%m%C%/$r9T$J$&$3$H$K$h$C$FGSB>@)8f$r9T$J$$$^$9!#$=$l0JA0$N%P!<%8%g%s$HHf$Y$k$H!"(BVACUUM(autovacuum$B$r4^$`(B)$B$H$N%m%C%/6%9g$,$J$/$J$k%a%j%C%H$,$"$j$^$9!#(B
+ pgpool-II 2.2$B0J9_$O!"%F!<%V%k$,(BSERIAL$BNs$r;}$D$+$I$&$+<+F0H=JL$9$k$?$a!"(BSERIAL$BNs$,$J$1$l$P7h$7$F%F!<%V%k$r%m%C%/$7$^$;$s!#(B
+</p>
+<p>
+ pgpool-II 3.0.4$B$^$G$N(B3.0$B%7%j!<%:$O!"BP1~$9$k%7!<%1%s%9%F!<%V%k$KBP$7$F9T%m%C%/$r$+$1$k$3$H$GGSB>@)8f$r9T$J$$$^$9!#$=$l0JA0$N%P!<%8%g%s$HHf$Y$k$H!"(BVACUUM(autovacuum$B$r4^$`(B)$B$H$N%m%C%/6%9g$,$J$/$J$k%a%j%C%H$,$"$j$^$9!#$7$+$7!"$3$l$OB>$NLdBj$r0z$-5/$3$7$^$9!#%H%i%s%6%/%7%g%s<~2s$,5/$-$?8e!"%7!<%1%s%9%F!<%V%k$KBP$9$k9T%m%C%/$O(BPostgreSQL$B$NFbIt%(%i!<(B($B>\:Y$K$O!"%H%i%s%6%/%7%g%s>uBV$rJ];}$9$k(Bpg_clog$B$X$N%"%/%;%9%(%i!<(B)$B$r5/$3$7$^$9!#$3$l$rKI$0$?$a!"(BPostgreSQL$B$N%3%"3+H/<T$O%7!<%1%s%9%F!<%V%k$KBP$9$k9T%m%C%/$r5v2D$7$J$$$3$H$r7hDj$7$^$7$?!#$3$l$O$b$A$m$s(Bpgpool-II$B$rF0:nITG=$K$7$^$9(B($B=$@5$5$l$?(BPostgreSQL$B$O%P!<%8%g%s(B9.0.5, 8.4.9, 8.3.16$B$=$7$F(B8.2.22$B$H$7$F%j%j!<%9$5$l$k$G$7$g$&(B)$B!#(B
+</p>
+<p>
+ pgpool-II 3.0.5$B0J9_$G$O!"?7$7$$(BPostgreSQL$B$,%7!<%1%s%9%F!<%V%k$KBP$9$k%m%C%/$r5v2D$7$J$/$J$C$?$?$a!"(Bpgpool_catalog.insert_lock$B%F!<%V%k$KBP$7$F9T%m%C%/$r$+$1$k$3$H$GGSB>@)8f$r9T$J$$$^$9!#$7$?$,$C$F!"(Bpgpool-II$B7PM3$G%"%/%;%9$9$k$9$Y$F$N%G!<%?%Y!<%9$K(Binsert_lock$B%F!<%V%k$r$"$i$+$8$a:n@.$7$F$*$/I,MW$,$"$j$^$9!#>\:Y$O(B<a href="#install">insert_lock$B%F!<%V%k$N:n@.(B</a>$B$N9`L\$r$4Mw$/$@$5$$!#$b$7!"(Binsert_lock$B%F!<%V%k$,B8:_$7$J$$>l9g$O!"A^F~BP>]$N%F!<%V%k$KBP$7$F%m%C%/$r9T$$$^$9!#$3$l$O!"(Bpgpool-II 2.2$B$H(B2.3$B%7%j!<%:$N(Binsert_lock$B$HF1$8F0:n$G$9!#$^$?!"2a5n$N%P!<%8%g%s$H8_49@-$N$"$k(Binsert_lock$B$r;HMQ$7$?$$>l9g$O!"(Bconfigure$B%9%/%j%W%H$G@_Dj$G$-$^$9!#>\:Y$O(B<a href="#install">configure$B$N<B9T(B</a>$B$N9`L\$r$4Mw2<$5$$!#(B
</p>
<p>
- $B%F!<%V%k$G(BSERIAL$B7?$,;H$o$l$F$$$J$$>l9g$K$O!"%F!<%V%k$N%m%C%/$O9T$J$o$l$^$;$s!#(B
$B$J$*!"$"$^$jI,MW$J$$$+$bCN$l$^$;$s$,!"%3%a%s%H$rMxMQ$7$F!"$3$N5sF0$r(B
$B:Y$+$/@)8f$9$k$3$H$b$G$-$^$9!#(B
</p>
diff --git a/pgpool.conf.sample b/pgpool.conf.sample
index e681b8033..dc8a6d64b 100644
--- a/pgpool.conf.sample
+++ b/pgpool.conf.sample
@@ -173,8 +173,9 @@ replicate_select = off # Replicate SELECT statements
# replicate_select is higher priority than
# load_balance_mode.
-insert_lock = on # Automatically locks a table with INSERT
- # statements to keep SERIAL data consistency
+insert_lock = on # Automatically locks a dummy row or a table
+ # with INSERT statements to keep SERIAL data
+ # consistency
# Without SERIAL, no lock will be issued
lobj_lock_table = '' # When rewriting lo_creat command in
# replication mode, specify table name to
diff --git a/pgpool.conf.sample-master-slave b/pgpool.conf.sample-master-slave
index 2f350ae51..373ea63f4 100644
--- a/pgpool.conf.sample-master-slave
+++ b/pgpool.conf.sample-master-slave
@@ -173,8 +173,9 @@ replicate_select = off # Replicate SELECT statements
# replicate_select is higher priority than
# load_balance_mode.
-insert_lock = off # Automatically locks a table with INSERT
- # statements to keep SERIAL data consistency
+insert_lock = off # Automatically locks a dummy row or a table
+ # with INSERT statements to keep SERIAL data
+ # consistency
# Without SERIAL, no lock will be issued
lobj_lock_table = '' # When rewriting lo_creat command in
# replication mode, specify table name to
diff --git a/pgpool.conf.sample-replication b/pgpool.conf.sample-replication
index d9c5e03ba..459d518a9 100644
--- a/pgpool.conf.sample-replication
+++ b/pgpool.conf.sample-replication
@@ -173,8 +173,9 @@ replicate_select = off # Replicate SELECT statements
# replicate_select is higher priority than
# load_balance_mode.
-insert_lock = on # Automatically locks a table with INSERT
- # statements to keep SERIAL data consistency
+insert_lock = on # Automatically locks a dummy row or a table
+ # with INSERT statements to keep SERIAL data
+ # consistency
# Without SERIAL, no lock will be issued
lobj_lock_table = '' # When rewriting lo_creat command in
# replication mode, specify table name to
diff --git a/pgpool.conf.sample-stream b/pgpool.conf.sample-stream
index 742f97e1d..bdee158fe 100644
--- a/pgpool.conf.sample-stream
+++ b/pgpool.conf.sample-stream
@@ -174,8 +174,9 @@ replicate_select = off # Replicate SELECT statements
# replicate_select is higher priority than
# load_balance_mode.
-insert_lock = off # Automatically locks a table with INSERT
- # statements to keep SERIAL data consistency
+insert_lock = off # Automatically locks a dummy row or a table
+ # with INSERT statements to keep SERIAL data
+ # consistency
# Without SERIAL, no lock will be issued
lobj_lock_table = '' # When rewriting lo_creat command in
# replication mode, specify table name to
diff --git a/pool_process_query.c b/pool_process_query.c
index 3a8a8c706..0d31f7905 100644
--- a/pool_process_query.c
+++ b/pool_process_query.c
@@ -72,8 +72,11 @@ static POOL_STATUS ParallelForwardToFrontend(char kind, POOL_CONNECTION *fronten
static bool is_panic_or_fatal_error(const char *message, int major);
static int detect_error(POOL_CONNECTION *master, char *error_code, int major, char class, bool unread);
static int detect_postmaster_down_error(POOL_CONNECTION *master, int major);
-
static bool is_internal_transaction_needed(Node *node);
+static bool pool_has_insert_lock(void);
+static POOL_STATUS add_lock_target(POOL_CONNECTION *frontend, POOL_CONNECTION_POOL *backend, char* table);
+static bool has_lock_target(POOL_CONNECTION *frontend, POOL_CONNECTION_POOL *backend, char* table, bool for_update);
+static POOL_STATUS insert_oid_into_insert_lock(POOL_CONNECTION *frontend, POOL_CONNECTION_POOL *backend, char* table);
/* timeout sec for pool_check_fd */
static int timeoutsec;
@@ -2296,6 +2299,7 @@ POOL_STATUS do_query(POOL_CONNECTION *backend, char *query, POOL_SELECT_RESULT *
static char nullmap[8192];
unsigned char mask = 0;
+ *result = NULL;
res = malloc(sizeof(*res));
if (!res)
{
@@ -2305,6 +2309,8 @@ POOL_STATUS do_query(POOL_CONNECTION *backend, char *query, POOL_SELECT_RESULT *
rowdesc = malloc(sizeof(*rowdesc));
if (!rowdesc)
{
+ if (res)
+ free(res);
pool_error("pool_query: malloc failed");
return POOL_ERROR;
}
@@ -2624,7 +2630,8 @@ POOL_STATUS do_query(POOL_CONNECTION *backend, char *query, POOL_SELECT_RESULT *
* Return values are:
* 0: lock is not neccessary
* 1: table lock is required
- * 2: row lock is required (SERIAL is used)
+ * 2: row lock against sequence table is required
+ * 3: row lock against insert_lock table is required
*/
int need_insert_lock(POOL_CONNECTION_POOL *backend, char *query, Node *node)
{
@@ -2705,17 +2712,31 @@ int need_insert_lock(POOL_CONNECTION_POOL *backend, char *query, Node *node)
/*
* Search relcache.
*/
+#ifdef USE_TABLE_LOCK
+ result = pool_search_relcache(relcache, backend, str)==0?0:1;
+#elif USE_SEQUENCE_LOCK
result = pool_search_relcache(relcache, backend, str)==0?0:2;
+#else
+ result = pool_search_relcache(relcache, backend, str)==0?0:3;
+#endif
return result;
}
/*
- * Issue LOCK TABLE IN SHARE ROW EXCLUSIVE MODE if lock_kind == 1.
- * Issue row lock if lock_kind == 2.
+ * insert lock to synchronize sequence number
+ * lock_kind are:
+ * 1: Issue LOCK TABLE IN SHARE ROW EXCLUSIVE MODE
+ * 2: Issue row lock against sequence table
+ * 3: Issue row lock against pgpool_catalog.insert_lock table
+ * "lock_kind == 2" is deprecated because PostgreSQL disallows
+ * SELECT FOR UPDATE/SHARE on sequence tables since 2011/06/03.
+ * See following threads for more details:
+ * [HACKERS] pgpool versus sequences
+ * [ADMIN] 'SGT DETAIL: Could not open file "pg_clog/05DC": ...
*/
POOL_STATUS insert_lock(POOL_CONNECTION *frontend, POOL_CONNECTION_POOL *backend, char *query, InsertStmt *node, int lock_kind)
{
- char *table;
+ char *table, *p;
int len = 0;
char qbuf[1024];
POOL_STATUS status;
@@ -2725,10 +2746,16 @@ POOL_STATUS insert_lock(POOL_CONNECTION *frontend, POOL_CONNECTION_POOL *backend
#define SEQUENCETABLEQUERY2 "SELECT d.adsrc 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 = pgpool_regclass('%s') AND d.adsrc ~ 'nextval'"
-#define MAX_SEQ_NAME 128
+/* query to lock a row by only the specified table name without regard to the schema */
+#define ROWLOCKQUERY "SELECT 1 FROM pgpool_catalog.insert_lock WHERE reloid = (SELECT oid FROM pg_catalog.pg_class WHERE relname = '%s' ORDER BY oid LIMIT 1) FOR UPDATE"
+
+#define ROWLOCKQUERY2 "SELECT 1 FROM pgpool_catalog.insert_lock WHERE reloid = pgpool_regclass('%s') FOR UPDATE"
+
+#define MAX_NAME_LEN 128
char *adsrc;
- char seq_rel_name[MAX_SEQ_NAME+1];
+ char seq_rel_name[MAX_NAME_LEN+1];
+ char rel_name[MAX_NAME_LEN+1];
regex_t preg;
size_t nmatch = 2;
regmatch_t pmatch[nmatch];
@@ -2741,14 +2768,29 @@ POOL_STATUS insert_lock(POOL_CONNECTION *frontend, POOL_CONNECTION_POOL *backend
/* get table name */
table = get_insert_command_table_name(node);
+ /* trim quotes */
+ p = table;
+ for (i=0; *p; p++)
+ {
+ if (*p != '"')
+ rel_name[i++] = *p;
+ }
+ rel_name[i] = '\0';
+
/* could not get table name. probably wrong SQL command */
if (table == NULL)
{
return POOL_CONTINUE;
}
+ /* table lock for insert target table? */
+ if (lock_kind == 1)
+ {
+ /* 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)
+ else if (lock_kind == 2)
{
/*
* If relcache does not exist, create it.
@@ -2814,10 +2856,22 @@ POOL_STATUS insert_lock(POOL_CONNECTION *frontend, POOL_CONNECTION_POOL *backend
pool_debug("seq rel name: %s", seq_rel_name);
snprintf(qbuf, sizeof(qbuf), "SELECT 1 FROM %s FOR UPDATE", seq_rel_name);
}
+ /* row lock for insert_lock table? */
else
{
- /* Issue lock table command */
- snprintf(qbuf, sizeof(qbuf), "LOCK TABLE %s IN SHARE ROW EXCLUSIVE MODE", table);
+ if (pool_has_insert_lock())
+ {
+ if (pool_has_pgpool_regclass())
+ snprintf(qbuf, sizeof(qbuf), ROWLOCKQUERY2, rel_name);
+ else
+ snprintf(qbuf, sizeof(qbuf), ROWLOCKQUERY, rel_name);
+ }
+ else
+ {
+ /* issue lock table command if insert_lock table does not exist */
+ lock_kind = 1;
+ snprintf(qbuf, sizeof(qbuf), "LOCK TABLE %s IN SHARE ROW EXCLUSIVE MODE", table);
+ }
}
per_node_statement_log(backend, MASTER_NODE_ID, qbuf);
@@ -2827,13 +2881,57 @@ POOL_STATUS insert_lock(POOL_CONNECTION *frontend, POOL_CONNECTION_POOL *backend
status = do_command(frontend, MASTER(backend), qbuf, MAJOR(backend), MASTER_CONNECTION(backend)->pid,
MASTER_CONNECTION(backend)->key, 0);
}
+ else if (lock_kind == 2)
+ {
+ POOL_SELECT_RESULT *result;
+ status = do_query(MASTER(backend), qbuf, &result, MAJOR(backend));
+ if (result)
+ free_select_result(result);
+ }
else
{
POOL_SELECT_RESULT *result;
+ /* issue row lock command */
status = do_query(MASTER(backend), qbuf, &result, MAJOR(backend));
+ if (status == POOL_CONTINUE)
+ {
+ /* does oid exist in insert_lock table? */
+ if (result && result->numrows == 0)
+ {
+ free_select_result(result);
+ result = NULL;
+
+ /* insert a lock target row into insert_lock table */
+ status = add_lock_target(frontend, backend, rel_name);
+ if (status == POOL_CONTINUE)
+ {
+ per_node_statement_log(backend, MASTER_NODE_ID, qbuf);
+
+ /* issue row lock command */
+ status = do_query(MASTER(backend), qbuf, &result, MAJOR(backend));
+ if (status == POOL_CONTINUE)
+ {
+ if (!(result && result->data[0] && !strcmp(result->data[0], "1")))
+ status = POOL_ERROR;
+ }
+ }
+ }
+ }
+
if (result)
free_select_result(result);
+
+ if (status != POOL_CONTINUE)
+ {
+ /* try to lock table finally, if row lock failed */
+ lock_kind = 1;
+ 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 (status == POOL_END)
{
return POOL_END;
@@ -2850,16 +2948,16 @@ POOL_STATUS insert_lock(POOL_CONNECTION *frontend, POOL_CONNECTION_POOL *backend
MASTER_CONNECTION(backend)->pid, MASTER_CONNECTION(backend)->key, 0);
else
{
- per_node_statement_log(backend, i, qbuf);
-
if (lock_kind == 1)
{
+ per_node_statement_log(backend, i, qbuf);
status = do_command(frontend, CONNECTION(backend, i), qbuf, PROTO_MAJOR_V3,
MASTER_CONNECTION(backend)->pid, MASTER_CONNECTION(backend)->key, 0);
}
- else
+ else if (lock_kind == 2)
{
POOL_SELECT_RESULT *result;
+ per_node_statement_log(backend, i, qbuf);
status = do_query(CONNECTION(backend,i), qbuf, &result, MAJOR(backend));
if (result)
free_select_result(result);
@@ -2876,6 +2974,167 @@ POOL_STATUS insert_lock(POOL_CONNECTION *frontend, POOL_CONNECTION_POOL *backend
return POOL_CONTINUE;
}
+/*
+ * Judge if we have insert_lock table or not.
+ */
+static bool pool_has_insert_lock(void)
+{
+/*
+ * Query to know if insert_lock table exists.
+ */
+#define HASINSERT_LOCKQUERY "SELECT count(*) FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON (c.relnamespace = n.oid) WHERE nspname = 'pgpool_catalog' AND relname = '%s'"
+ bool result;
+ static POOL_RELCACHE *relcache;
+ POOL_CONNECTION_POOL *backend;
+
+ backend = pool_get_session_context()->backend;
+
+ if (!relcache)
+ {
+ relcache = pool_create_relcache(32, HASINSERT_LOCKQUERY,
+ int_register_func, int_unregister_func,
+ false);
+ if (relcache == NULL)
+ {
+ pool_error("pool_has_insert_lock: pool_create_relcache error");
+ return false;
+ }
+ }
+
+ result = pool_search_relcache(relcache, backend, "insert_lock")==0?0:1;
+ return result;
+}
+
+/*
+ * Insert a lock target row into insert_lock table.
+ * This function is called after the transaction has been started.
+ * Protocol is V3 only.
+ * Return POOL_CONTINUE if the row is inserted successfully
+ * or the row already exists, the others return POOL_ERROR.
+ */
+static POOL_STATUS add_lock_target(POOL_CONNECTION *frontend, POOL_CONNECTION_POOL *backend, char* table)
+{
+ POOL_STATUS status;
+
+ /* lock the row where reloid is 0 to avoid "duplicate key violates..." error when insert new oid */
+ if (!has_lock_target(frontend, backend, NULL, true))
+ {
+ pool_log("add_lock_target: could not lock the row where reloid is 0");
+
+ per_node_statement_log(backend, MASTER_NODE_ID, "LOCK TABLE pgpool_catalog.insert_lock IN SHARE ROW EXCLUSIVE MODE");
+ status = do_command(frontend, MASTER(backend), "LOCK TABLE pgpool_catalog.insert_lock IN SHARE ROW EXCLUSIVE MODE",
+ PROTO_MAJOR_V3, MASTER_CONNECTION(backend)->pid, MASTER_CONNECTION(backend)->key, 0);
+ if (status == POOL_CONTINUE)
+ {
+ if (has_lock_target(frontend, backend, NULL, false))
+ {
+ pool_debug("add_lock_target: reloid 0 already exists in insert_lock table");
+ }
+ else
+ {
+ if (insert_oid_into_insert_lock(frontend, backend, NULL) != POOL_CONTINUE)
+ {
+ pool_log("add_lock_target: could not insert 0 into insert_lock table");
+ return POOL_ERROR;
+ }
+ }
+ }
+ else
+ {
+ return POOL_ERROR;
+ }
+ }
+
+ /* does insert_lock table contain the oid of the table? */
+ if (has_lock_target(frontend, backend, table, false))
+ {
+ pool_debug("add_lock_target: \"%s\" oid already exists in insert_lock table", table);
+ return POOL_CONTINUE;
+ }
+
+ /* insert the oid of the table into insert_lock table */
+ if (insert_oid_into_insert_lock(frontend, backend, table) != POOL_CONTINUE)
+ {
+ pool_log("add_lock_target: could not insert \"%s\" oid into insert_lock table", table);
+ return POOL_ERROR;
+ }
+
+ return POOL_CONTINUE;
+}
+
+/*
+ * Judge if insert_lock table contains the oid of the specified table or not.
+ * If the table name is NULL, this function checks whether oid 0 exists.
+ * If lock is true, this function locks the row of the table oid.
+ */
+static bool has_lock_target(POOL_CONNECTION *frontend,
+ POOL_CONNECTION_POOL *backend,
+ char* table, bool lock)
+{
+ char *suffix;
+ char qbuf[QUERY_STRING_BUFFER_LEN];
+ POOL_STATUS status;
+ POOL_SELECT_RESULT *result;
+
+ suffix = lock ? " FOR UPDATE" : "";
+
+ if (table)
+ {
+ if (pool_has_pgpool_regclass())
+ snprintf(qbuf, sizeof(qbuf), "SELECT 1 FROM pgpool_catalog.insert_lock WHERE reloid = pgpool_regclass('%s')%s", table, suffix);
+ else
+ snprintf(qbuf, sizeof(qbuf), "SELECT 1 FROM pgpool_catalog.insert_lock WHERE reloid = (SELECT oid FROM pg_catalog.pg_class WHERE relname = '%s' ORDER BY oid LIMIT 1)%s", table, suffix);
+ }
+ else
+ {
+ snprintf(qbuf, sizeof(qbuf), "SELECT 1 FROM pgpool_catalog.insert_lock WHERE reloid = 0%s", suffix);
+ }
+
+ per_node_statement_log(backend, MASTER_NODE_ID, qbuf);
+ status = do_query(MASTER(backend), qbuf, &result, MAJOR(backend));
+ if (status == POOL_CONTINUE)
+ {
+ if (result && result->data[0] && !strcmp(result->data[0], "1"))
+ {
+ free_select_result(result);
+ return true;
+ }
+ }
+
+ if (result)
+ free_select_result(result);
+
+ return false;
+}
+
+/*
+ * Insert the oid of the specified table into insert_lock table.
+ */
+static POOL_STATUS insert_oid_into_insert_lock(POOL_CONNECTION *frontend,
+ POOL_CONNECTION_POOL *backend,
+ char* table)
+{
+ char qbuf[QUERY_STRING_BUFFER_LEN];
+ POOL_STATUS status;
+
+ if (table)
+ {
+ if (pool_has_pgpool_regclass())
+ snprintf(qbuf, sizeof(qbuf), "INSERT INTO pgpool_catalog.insert_lock VALUES (pgpool_regclass('%s'))", table);
+ else
+ snprintf(qbuf, sizeof(qbuf), "INSERT INTO pgpool_catalog.insert_lock SELECT oid FROM pg_catalog.pg_class WHERE relname = '%s' ORDER BY oid LIMIT 1", table);
+ }
+ else
+ {
+ snprintf(qbuf, sizeof(qbuf), "INSERT INTO pgpool_catalog.insert_lock VALUES (0)");
+ }
+
+ per_node_statement_log(backend, MASTER_NODE_ID, qbuf);
+ status = do_command(frontend, MASTER(backend), qbuf, PROTO_MAJOR_V3,
+ MASTER_CONNECTION(backend)->pid, MASTER_CONNECTION(backend)->key, 0);
+ return status;
+}
+
bool is_partition_table(POOL_CONNECTION_POOL *backend, Node *node)
{
DistDefInfo *info = NULL;
diff --git a/sql/insert_lock.sql b/sql/insert_lock.sql
new file mode 100644
index 000000000..c2fe3d44f
--- /dev/null
+++ b/sql/insert_lock.sql
@@ -0,0 +1,14 @@
+-- $Header$
+
+DROP TABLE pgpool_catalog.insert_lock;
+
+CREATE SCHEMA pgpool_catalog;
+CREATE TABLE pgpool_catalog.insert_lock(reloid OID PRIMARY KEY);
+
+-- this row is used as the row lock target when pgpool inserts new oid
+INSERT INTO pgpool_catalog.insert_lock VALUES (0);
+
+-- allow "SELECT ... FOR UPDATE" and "INSERT ..." to all roles
+GRANT SELECT ON pgpool_catalog.insert_lock TO PUBLIC;
+GRANT UPDATE ON pgpool_catalog.insert_lock TO PUBLIC;
+GRANT INSERT ON pgpool_catalog.insert_lock TO PUBLIC;
[parent: dbd6dab42ba4]