pgpool2.git / summary / log / commit / refs
commit 14bbb5afa58b5d57cf6bb77ee31e06f804c6a0cb
Author: Tatsuo Ishii <ishii@sraoss.co.jp>
Date: Tue Feb 07 10:39:37 2023 +0000
Test: simplify 001.load_balance.
Commit 85ce8523 allows to use expected/result style tests. This
commit applies it to 001.load_balance. The expected files are under
"expected" directory. Suffix "-s" is for streaming replication mode
and "-r" is for native replication and snapshot isolation mode.
.../tests/001.load_balance/expected/expected1-r | 38 ++++
.../tests/001.load_balance/expected/expected1-s | 32 ++++
.../tests/001.load_balance/expected/expected2-r | 14 ++
.../tests/001.load_balance/expected/expected2-s | 14 ++
.../tests/001.load_balance/expected/expected3-r | 40 +++++
.../tests/001.load_balance/expected/expected3-s | 40 +++++
.../tests/001.load_balance/expected/expected4-r | 15 ++
.../tests/001.load_balance/expected/expected5-r | 8 +
.../tests/001.load_balance/expected/expected6-r | 16 ++
.../tests/001.load_balance/expected/expected6-s | 14 ++
src/test/regression/tests/001.load_balance/test.sh | 199 +++++++--------------
11 files changed, 297 insertions(+), 133 deletions(-)
diff --git a/src/test/regression/tests/001.load_balance/expected/expected1-r b/src/test/regression/tests/001.load_balance/expected/expected1-r
new file mode 100644
index 000000000..01c4c4d90
--- /dev/null
+++ b/src/test/regression/tests/001.load_balance/expected/expected1-r
@@ -0,0 +1,38 @@
+CREATE TABLE t1(i INTEGER);
+NOTICE: DB node id: 0 statement: CREATE TABLE t1(i INTEGER);
+NOTICE: DB node id: 1 statement: CREATE TABLE t1(i INTEGER);
+CREATE TABLE
+CREATE TABLE t2(i INTEGER);
+NOTICE: DB node id: 0 statement: CREATE TABLE t2(i INTEGER);
+NOTICE: DB node id: 1 statement: CREATE TABLE t2(i INTEGER);
+CREATE TABLE
+CREATE FUNCTION f1(INTEGER) returns INTEGER AS 'SELECT $1' LANGUAGE SQL;
+NOTICE: DB node id: 0 statement: CREATE FUNCTION f1(INTEGER) returns INTEGER AS 'SELECT $1' LANGUAGE SQL;
+NOTICE: DB node id: 1 statement: CREATE FUNCTION f1(INTEGER) returns INTEGER AS 'SELECT $1' LANGUAGE SQL;
+CREATE FUNCTION
+CREATE FUNCTION f2(INTEGER) returns INTEGER AS 'SELECT $1' LANGUAGE SQL;
+NOTICE: DB node id: 0 statement: CREATE FUNCTION f2(INTEGER) returns INTEGER AS 'SELECT $1' LANGUAGE SQL;
+NOTICE: DB node id: 1 statement: CREATE FUNCTION f2(INTEGER) returns INTEGER AS 'SELECT $1' LANGUAGE SQL;
+CREATE FUNCTION
+SELECT * FROM t1;
+NOTICE: DB node id: 1 statement: SELECT * FROM t1;
+ i
+---
+(0 rows)
+
+SELECT f1(1);
+NOTICE: DB node id: 0 statement: SELECT f1(1);
+NOTICE: DB node id: 1 statement: SELECT f1(1);
+ f1
+----
+ 1
+(1 row)
+
+SELECT public.f2(1);
+NOTICE: DB node id: 0 statement: SELECT public.f2(1);
+NOTICE: DB node id: 1 statement: SELECT public.f2(1);
+ f2
+----
+ 1
+(1 row)
+
diff --git a/src/test/regression/tests/001.load_balance/expected/expected1-s b/src/test/regression/tests/001.load_balance/expected/expected1-s
new file mode 100644
index 000000000..4ac8eb99e
--- /dev/null
+++ b/src/test/regression/tests/001.load_balance/expected/expected1-s
@@ -0,0 +1,32 @@
+CREATE TABLE t1(i INTEGER);
+NOTICE: DB node id: 0 statement: CREATE TABLE t1(i INTEGER);
+CREATE TABLE
+CREATE TABLE t2(i INTEGER);
+NOTICE: DB node id: 0 statement: CREATE TABLE t2(i INTEGER);
+CREATE TABLE
+CREATE FUNCTION f1(INTEGER) returns INTEGER AS 'SELECT $1' LANGUAGE SQL;
+NOTICE: DB node id: 0 statement: CREATE FUNCTION f1(INTEGER) returns INTEGER AS 'SELECT $1' LANGUAGE SQL;
+CREATE FUNCTION
+CREATE FUNCTION f2(INTEGER) returns INTEGER AS 'SELECT $1' LANGUAGE SQL;
+NOTICE: DB node id: 0 statement: CREATE FUNCTION f2(INTEGER) returns INTEGER AS 'SELECT $1' LANGUAGE SQL;
+CREATE FUNCTION
+SELECT * FROM t1;
+NOTICE: DB node id: 1 statement: SELECT * FROM t1;
+ i
+---
+(0 rows)
+
+SELECT f1(1);
+NOTICE: DB node id: 0 statement: SELECT f1(1);
+ f1
+----
+ 1
+(1 row)
+
+SELECT public.f2(1);
+NOTICE: DB node id: 0 statement: SELECT public.f2(1);
+ f2
+----
+ 1
+(1 row)
+
diff --git a/src/test/regression/tests/001.load_balance/expected/expected2-r b/src/test/regression/tests/001.load_balance/expected/expected2-r
new file mode 100644
index 000000000..fb7aa48f9
--- /dev/null
+++ b/src/test/regression/tests/001.load_balance/expected/expected2-r
@@ -0,0 +1,14 @@
+SELECT f1(1);
+NOTICE: DB node id: 1 statement: SELECT f1(1);
+ f1
+----
+ 1
+(1 row)
+
+SELECT public.f2(1);
+NOTICE: DB node id: 1 statement: SELECT public.f2(1);
+ f2
+----
+ 1
+(1 row)
+
diff --git a/src/test/regression/tests/001.load_balance/expected/expected2-s b/src/test/regression/tests/001.load_balance/expected/expected2-s
new file mode 100644
index 000000000..fb7aa48f9
--- /dev/null
+++ b/src/test/regression/tests/001.load_balance/expected/expected2-s
@@ -0,0 +1,14 @@
+SELECT f1(1);
+NOTICE: DB node id: 1 statement: SELECT f1(1);
+ f1
+----
+ 1
+(1 row)
+
+SELECT public.f2(1);
+NOTICE: DB node id: 1 statement: SELECT public.f2(1);
+ f2
+----
+ 1
+(1 row)
+
diff --git a/src/test/regression/tests/001.load_balance/expected/expected3-r b/src/test/regression/tests/001.load_balance/expected/expected3-r
new file mode 100644
index 000000000..faa48800b
--- /dev/null
+++ b/src/test/regression/tests/001.load_balance/expected/expected3-r
@@ -0,0 +1,40 @@
+SELECT * FROM t1;
+NOTICE: DB node id: 1 statement: SELECT * FROM t1;
+ i
+---
+(0 rows)
+
+SELECT 'a';
+NOTICE: DB node id: 1 statement: SELECT 'a';
+ ?column?
+----------
+ a
+(1 row)
+
+SELECT 1;
+NOTICE: DB node id: 1 statement: SELECT 1;
+ ?column?
+----------
+ 1
+(1 row)
+
+SELECT ';';
+NOTICE: DB node id: 1 statement: SELECT ';';
+ ?column?
+----------
+ ;
+(1 row)
+
+SELECT * FROM t2;
+NOTICE: DB node id: 1 statement: SELECT * FROM t2;
+ i
+---
+(0 rows)
+
+SELECT f1(1);
+NOTICE: DB node id: 1 statement: SELECT f1(1);
+ f1
+----
+ 1
+(1 row)
+
diff --git a/src/test/regression/tests/001.load_balance/expected/expected3-s b/src/test/regression/tests/001.load_balance/expected/expected3-s
new file mode 100644
index 000000000..006f33fa8
--- /dev/null
+++ b/src/test/regression/tests/001.load_balance/expected/expected3-s
@@ -0,0 +1,40 @@
+SELECT * FROM t1;
+NOTICE: DB node id: 0 statement: SELECT * FROM t1;
+ i
+---
+(0 rows)
+
+SELECT 'a';
+NOTICE: DB node id: 0 statement: SELECT 'a';
+ ?column?
+----------
+ a
+(1 row)
+
+SELECT 1;
+NOTICE: DB node id: 0 statement: SELECT 1;
+ ?column?
+----------
+ 1
+(1 row)
+
+SELECT ';';
+NOTICE: DB node id: 0 statement: SELECT ';';
+ ?column?
+----------
+ ;
+(1 row)
+
+SELECT * FROM t2;
+NOTICE: DB node id: 0 statement: SELECT * FROM t2;
+ i
+---
+(0 rows)
+
+SELECT f1(1);
+NOTICE: DB node id: 0 statement: SELECT f1(1);
+ f1
+----
+ 1
+(1 row)
+
diff --git a/src/test/regression/tests/001.load_balance/expected/expected4-r b/src/test/regression/tests/001.load_balance/expected/expected4-r
new file mode 100644
index 000000000..25e0a8005
--- /dev/null
+++ b/src/test/regression/tests/001.load_balance/expected/expected4-r
@@ -0,0 +1,15 @@
+BEGIN;
+NOTICE: DB node id: 0 statement: BEGIN;
+NOTICE: DB node id: 1 statement: BEGIN;
+BEGIN
+SELECT 1;
+NOTICE: DB node id: 0 statement: SELECT 1;
+ ?column?
+----------
+ 1
+(1 row)
+
+END;
+NOTICE: DB node id: 1 statement: END;
+NOTICE: DB node id: 0 statement: END;
+COMMIT
diff --git a/src/test/regression/tests/001.load_balance/expected/expected5-r b/src/test/regression/tests/001.load_balance/expected/expected5-r
new file mode 100644
index 000000000..f2e355922
--- /dev/null
+++ b/src/test/regression/tests/001.load_balance/expected/expected5-r
@@ -0,0 +1,8 @@
+SELECT f1(2);
+NOTICE: DB node id: 0 statement: SELECT f1(2);
+NOTICE: DB node id: 1 statement: SELECT f1(2);
+ f1
+----
+ 2
+(1 row)
+
diff --git a/src/test/regression/tests/001.load_balance/expected/expected6-r b/src/test/regression/tests/001.load_balance/expected/expected6-r
new file mode 100644
index 000000000..4ed86119a
--- /dev/null
+++ b/src/test/regression/tests/001.load_balance/expected/expected6-r
@@ -0,0 +1,16 @@
+SELECT f1(1);
+NOTICE: DB node id: 0 statement: SELECT f1(1);
+NOTICE: DB node id: 1 statement: SELECT f1(1);
+ f1
+----
+ 1
+(1 row)
+
+SELECT public.f2(1);
+NOTICE: DB node id: 0 statement: SELECT public.f2(1);
+NOTICE: DB node id: 1 statement: SELECT public.f2(1);
+ f2
+----
+ 1
+(1 row)
+
diff --git a/src/test/regression/tests/001.load_balance/expected/expected6-s b/src/test/regression/tests/001.load_balance/expected/expected6-s
new file mode 100644
index 000000000..bc50fecee
--- /dev/null
+++ b/src/test/regression/tests/001.load_balance/expected/expected6-s
@@ -0,0 +1,14 @@
+SELECT f1(1);
+NOTICE: DB node id: 0 statement: SELECT f1(1);
+ f1
+----
+ 1
+(1 row)
+
+SELECT public.f2(1);
+NOTICE: DB node id: 0 statement: SELECT public.f2(1);
+ f2
+----
+ 1
+(1 row)
+
diff --git a/src/test/regression/tests/001.load_balance/test.sh b/src/test/regression/tests/001.load_balance/test.sh
index fc396f149..4edd5ff58 100755
--- a/src/test/regression/tests/001.load_balance/test.sh
+++ b/src/test/regression/tests/001.load_balance/test.sh
@@ -9,6 +9,22 @@ PSQL=$PGBIN/psql
# sleep time after reload in seconds
st=10
+# function to check the result
+# argument is test case number.
+function check_result
+{
+ diff -c ../expected/expected$1$suffix result$1
+ if [ $? = 0 ];then
+ echo "test$1 succeeded."
+ else
+ echo "test$1 failed."
+ ./shutdownall
+ exit 1
+ fi
+}
+
+# main test script
+
for mode in s r i
do
rm -fr $TESTDIR
@@ -22,9 +38,19 @@ do
source ./bashrc.ports
+ # set expected file suffix
+ if [ $mode = 's' ];then
+ suffix='-s'
+ else
+ suffix='-r'
+ fi
+
+ echo "=== test1 started ==="
+
echo "backend_weight0 = 0" >> etc/pgpool.conf
echo "backend_weight1 = 1" >> etc/pgpool.conf
echo "write_function_list = 'f1,public.f2'" >> etc/pgpool.conf
+ echo "notice_per_node_statement = on" >> etc/pgpool.conf
./startall
@@ -32,7 +58,7 @@ do
wait_for_pgpool_startup
- $PSQL test <<EOF
+ $PSQL -ae test > result1 2>&1 <<EOF
CREATE TABLE t1(i INTEGER);
CREATE TABLE t2(i INTEGER);
CREATE FUNCTION f1(INTEGER) returns INTEGER AS 'SELECT \$1' LANGUAGE SQL;
@@ -41,69 +67,30 @@ SELECT * FROM t1; -- this load balances
SELECT f1(1); -- this does not load balance
SELECT public.f2(1); -- this does not load balance
EOF
+ check_result 1
-# check if simple load balance worked
- fgrep "SELECT * FROM t1;" log/pgpool.log |grep "DB node id: 1">/dev/null 2>&1
- if [ $? != 0 ];then
- # expected result not found
- echo fail: select is sent to zero-weight node.
- ./shutdownall
- exit 1
- fi
- echo ok: load balance works.
-
-# check if write function list worked
- fgrep "SELECT f1(1);" log/pgpool.log |grep "DB node id: 0">/dev/null 2>&1
- if [ $? != 0 ];then
- # expected result not found
- echo fail: write function is sent to node 1.
- ./shutdownall
- exit 1
- fi
- fgrep "SELECT public.f2(1);" log/pgpool.log |grep "DB node id: 0">/dev/null 2>&1
- if [ $? != 0 ];then
- # expected result not found
- echo fail: write function is sent to node 1.
- ./shutdownall
- exit 1
- fi
- echo ok: write function list works.
-
+ echo "=== test2 started ==="
+ # check if read_only function list works
echo "read_only_function_list = 'f1,public.f2'" >> etc/pgpool.conf
echo "write_function_list = ''" >> etc/pgpool.conf
./pgpool_reload
sleep $st
- $PSQL test <<EOF
+ $PSQL -ae test > result2 2>&1 <<EOF
SELECT f1(1); -- this does load balance
SELECT public.f2(1); -- this does load balance
EOF
+ check_result 2
-# check if read_only function list worked
- fgrep "SELECT f1(1);" log/pgpool.log |grep "DB node id: 1">/dev/null 2>&1
- if [ $? != 0 ];then
- # expected result not found
- echo fail: read_only function is sent to zero-weight node.
- ./shutdownall
- exit 1
- fi
- fgrep "SELECT public.f2(1);" log/pgpool.log |grep "DB node id: 1">/dev/null 2>&1
- if [ $? != 0 ];then
- # expected result not found
- echo fail: read_only function is sent to zero-weight node.
- ./shutdownall
- exit 1
- fi
- echo ok: read_only function list works.
-
-# check if primary routing query pattern list worked
+ echo "=== test3 started ==="
+ # check if primary routing query pattern list worked
./shutdownall
echo "primary_routing_query_pattern_list = 'SELECT \'a\'\;;SELECT 1\;;SELECT \'\;\'\;;SELECT \* FROM t1\;;^.*t2.*\;$;^.*f1.*$'" >> etc/pgpool.conf
./startall
wait_for_pgpool_startup
-queries=`cat << EOF
+ $PSQL -ae test > result3 2>&1 <<EOF
SELECT * FROM t1;
SELECT 'a';
SELECT 1;
@@ -111,36 +98,15 @@ SELECT ';';
SELECT * FROM t2;
SELECT f1(1);
EOF
-`
- echo "$queries" | while read query; do
- $PSQL test -c "$query"
-
- # If native replication mode, all queries are sent to primary node only.
- # If query match both primary_routing_query_pattern_list and read_only_function_list,
- # read_only_function_list will be ignored, and query is sent to primary node only.
- #
- # If replication node, all queries are load-balanced.
- if [[ $mode = "s" ]];then
- node_id=0
- else
- node_id=1
- fi
- fgrep "${query}" log/pgpool.log | grep "DB node id: "`echo $node_id` > /dev/null 2>&1
-
- if [ $? != 0 ];then
- # expected result not found
- echo "fail: primary routing query: ${query} is load-balanced."
- ./shutdownall
- exit 1
- fi
- done
-
- if [ $? -eq 1 ]; then
- exit 1
- fi
- echo ok: primary routing query pattern list works.
-
- # check if statement level load balance worked
+ # If streaming replication mode, all queries are sent to primary node only.
+ # If query match both primary_routing_query_pattern_list and read_only_function_list,
+ # read_only_function_list will be ignored, and query is sent to primary node only.
+ #
+ # If replication node, all queries are load-balanced.
+ check_result 3
+
+ echo "=== test4 started ==="
+ # check if statement level load balance works
./shutdownall
echo "read_only_function_list = ''" >> etc/pgpool.conf
echo "write_function_list = ''" >> etc/pgpool.conf
@@ -158,94 +124,61 @@ EOF
n=`grep "selecting load balance node" log/pgpool.log | wc -l`
if [ $n != 3 ]; then
# expected result not found
- echo "fail: statement level load balance doesn't work."
- ./shutdownall
- exit 1
+ echo "fail: statement level load balance doesn't work."
+ ./shutdownall
+ exit 1
fi
echo ok: statement level load balance works.
# in replication mode if load_balance_mode = off, SELECT query inside
# an explicit transaction should be sent to main node only.
if [ $mode = "r" ];then
- ./shutdownall
- echo "load_balance_mode = off" >> etc/pgpool.conf
- ./startall
- wait_for_pgpool_startup
+ ./shutdownall
+ echo "load_balance_mode = off" >> etc/pgpool.conf
+ ./startall
+ wait_for_pgpool_startup
- $PSQL test <<EOF
+ $PSQL -ae test > result4 2>&1 <<EOF
BEGIN;
SELECT 1;
END;
EOF
+ check_result 4
- ok=0
- fgrep "SELECT 1;" log/pgpool.log |grep "DB node id: 0">/dev/null 2>&1
- if [ $? = 0 ];then
- fgrep "SELECT 1;" log/pgpool.log |grep "DB node id: 1">/dev/null 2>&1
- if [ $? != 0 ];then
- # the SELECT should not be executed on node 1
- echo ok: select is sent to only main node when not load-balanced.
- ok=1
- fi
- # the SELECT should be executed on node 0
- fi
+ echo "=== test5 started ==="
# in replication mode if load_balance_mode = off, SELECT query
# including writing function should be sent to all the nodes.
# per [pgpool-general: 2221].
- echo "write_function_list = 'f1'" >> etc/pgpool.conf
- echo "read_only_function_list = ''" >> etc/pgpool.conf
- ./pgpool_reload
- sleep $st
- $PSQL test <<EOF
+ echo "write_function_list = 'f1'" >> etc/pgpool.conf
+ echo "read_only_function_list = ''" >> etc/pgpool.conf
+ ./pgpool_reload
+ sleep $st
+ $PSQL -ae test > result5 2>&1 <<EOF
SELECT f1(2); -- this should be sent to all the nodes
EOF
- fgrep "SELECT f1(2);" log/pgpool.log |grep "DB node id: 0">/dev/null 2>&1
- if [ $? = 0 ];then
- fgrep "SELECT f1(2);" log/pgpool.log |grep "DB node id: 1">/dev/null 2>&1
- if [ $? = 0 ];then
- # the SELECT should be executed on node 0 & 1
- echo ok: write function is sent to all nodes.
- ok=`expr $ok + 1`
- fi
- # the SELECT should be executed on node 0
- fi
-
- if [ $ok != 2 ];then
- exit 1;
- fi
+ check_result 5
fi
+ echo "=== test6 started ==="
# -------------------------------------------------------------------------------
# check the case when write_function_list and read_only_function_list are both empty.
# In this case pg_proc.provolatile is checked. If it is 'v' (volatile), then the
# function is regarded doing writes.
+# Since f1() and f2() were declared without volatility property, they are regarded
+# as volatile functions.
# -------------------------------------------------------------------------------
+ echo "load_balance_mode = on" >> etc/pgpool.conf
echo "write_function_list = ''" >> etc/pgpool.conf
echo "read_only_function_list = ''" >> etc/pgpool.conf
./pgpool_reload
sleep $st
- $PSQL test <<EOF
+ $PSQL -ae test > result6 2>&1 <<EOF
SELECT f1(1);
SELECT public.f2(1);
EOF
-
- fgrep "SELECT f1(1);" log/pgpool.log |grep "DB node id: 0">/dev/null 2>&1
- if [ $? != 0 ];then
- # expected result not found
- echo fail: volatile function is sent to node 1.
- ./shutdownall
- exit 1
- fi
- fgrep "SELECT public.f2(1);" log/pgpool.log |grep "DB node id: 0">/dev/null 2>&1
- if [ $? != 0 ];then
- # expected result not found
- echo fail: volatile function is sent to node 1.
- ./shutdownall
- exit 1
- fi
- echo ok: volatile function check works.
+ check_result 6
./shutdownall
[parent: 85ce852329c0]