From f8c3d7b49a1c6496eca2203e95248b86c204bbfa Mon Sep 17 00:00:00 2001 From: Tatsuo Ishii Date: Sun, 12 Feb 2023 20:08:08 +0900 Subject: [PATCH] Test: enhance regression 001.load_balance. --- .../001.load_balance/expected/expected1-r | 10 +- .../001.load_balance/expected/expected1-s | 10 +- .../001.load_balance/expected/expected2-r | 4 +- .../001.load_balance/expected/expected2-s | 4 +- .../001.load_balance/expected/expected4-r | 2 - .../001.load_balance/expected/expected5-r | 2 +- .../001.load_balance/expected/expected7-r | 99 +++++++++++++++++++ .../001.load_balance/expected/expected7-s | 88 +++++++++++++++++ .../001.load_balance/expected/expected8-s | 88 +++++++++++++++++ .../tests/001.load_balance/sql/7.sql | 30 ++++++ .../regression/tests/001.load_balance/test.sh | 61 ++++++++++-- 11 files changed, 370 insertions(+), 28 deletions(-) create mode 100644 src/test/regression/tests/001.load_balance/expected/expected7-r create mode 100644 src/test/regression/tests/001.load_balance/expected/expected7-s create mode 100644 src/test/regression/tests/001.load_balance/expected/expected8-s create mode 100644 src/test/regression/tests/001.load_balance/sql/7.sql diff --git a/src/test/regression/tests/001.load_balance/expected/expected1-r b/src/test/regression/tests/001.load_balance/expected/expected1-r index 01c4c4d90..f9bc840ae 100644 --- a/src/test/regression/tests/001.load_balance/expected/expected1-r +++ b/src/test/regression/tests/001.load_balance/expected/expected1-r @@ -1,26 +1,22 @@ 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; +SELECT * FROM t1; -- this load balances NOTICE: DB node id: 1 statement: SELECT * FROM t1; i --- (0 rows) -SELECT f1(1); +SELECT f1(1); -- this does not load balance NOTICE: DB node id: 0 statement: SELECT f1(1); NOTICE: DB node id: 1 statement: SELECT f1(1); f1 @@ -28,7 +24,7 @@ NOTICE: DB node id: 1 statement: SELECT f1(1); 1 (1 row) -SELECT public.f2(1); +SELECT public.f2(1); -- this does not load balance NOTICE: DB node id: 0 statement: SELECT public.f2(1); NOTICE: DB node id: 1 statement: SELECT public.f2(1); f2 diff --git a/src/test/regression/tests/001.load_balance/expected/expected1-s b/src/test/regression/tests/001.load_balance/expected/expected1-s index 4ac8eb99e..029f2a25f 100644 --- a/src/test/regression/tests/001.load_balance/expected/expected1-s +++ b/src/test/regression/tests/001.load_balance/expected/expected1-s @@ -1,29 +1,25 @@ 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; +SELECT * FROM t1; -- this load balances NOTICE: DB node id: 1 statement: SELECT * FROM t1; i --- (0 rows) -SELECT f1(1); +SELECT f1(1); -- this does not load balance NOTICE: DB node id: 0 statement: SELECT f1(1); f1 ---- 1 (1 row) -SELECT public.f2(1); +SELECT public.f2(1); -- this does not load balance NOTICE: DB node id: 0 statement: SELECT public.f2(1); f2 ---- diff --git a/src/test/regression/tests/001.load_balance/expected/expected2-r b/src/test/regression/tests/001.load_balance/expected/expected2-r index fb7aa48f9..922c4cb3b 100644 --- a/src/test/regression/tests/001.load_balance/expected/expected2-r +++ b/src/test/regression/tests/001.load_balance/expected/expected2-r @@ -1,11 +1,11 @@ -SELECT f1(1); +SELECT f1(1); -- this does load balance NOTICE: DB node id: 1 statement: SELECT f1(1); f1 ---- 1 (1 row) -SELECT public.f2(1); +SELECT public.f2(1); -- this does load balance NOTICE: DB node id: 1 statement: SELECT public.f2(1); f2 ---- diff --git a/src/test/regression/tests/001.load_balance/expected/expected2-s b/src/test/regression/tests/001.load_balance/expected/expected2-s index fb7aa48f9..922c4cb3b 100644 --- a/src/test/regression/tests/001.load_balance/expected/expected2-s +++ b/src/test/regression/tests/001.load_balance/expected/expected2-s @@ -1,11 +1,11 @@ -SELECT f1(1); +SELECT f1(1); -- this does load balance NOTICE: DB node id: 1 statement: SELECT f1(1); f1 ---- 1 (1 row) -SELECT public.f2(1); +SELECT public.f2(1); -- this does load balance NOTICE: DB node id: 1 statement: SELECT public.f2(1); f2 ---- diff --git a/src/test/regression/tests/001.load_balance/expected/expected4-r b/src/test/regression/tests/001.load_balance/expected/expected4-r index 25e0a8005..54194344c 100644 --- a/src/test/regression/tests/001.load_balance/expected/expected4-r +++ b/src/test/regression/tests/001.load_balance/expected/expected4-r @@ -1,7 +1,6 @@ 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? @@ -12,4 +11,3 @@ NOTICE: DB node id: 0 statement: SELECT 1; 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 index f2e355922..bee2b5b8e 100644 --- a/src/test/regression/tests/001.load_balance/expected/expected5-r +++ b/src/test/regression/tests/001.load_balance/expected/expected5-r @@ -1,4 +1,4 @@ -SELECT f1(2); +SELECT f1(2); -- this should be sent to all the nodes NOTICE: DB node id: 0 statement: SELECT f1(2); NOTICE: DB node id: 1 statement: SELECT f1(2); f1 diff --git a/src/test/regression/tests/001.load_balance/expected/expected7-r b/src/test/regression/tests/001.load_balance/expected/expected7-r new file mode 100644 index 000000000..4f8963f0e --- /dev/null +++ b/src/test/regression/tests/001.load_balance/expected/expected7-r @@ -0,0 +1,99 @@ +-- ordinary read only SELECT: load balance expected +SELECT 1; +NOTICE: DB node id: 1 statement: SELECT 1; + ?column? +---------- + 1 +(1 row) + +-- multi-statement query including BEGIN +BEGIN\;SELECT 1; +NOTICE: DB node id: 0 statement: BEGIN;SELECT 1; +NOTICE: DB node id: 1 statement: BEGIN;SELECT 1; + ?column? +---------- + 1 +(1 row) + +-- ordinary read only SELECT: load balance expected +SELECT 2; +NOTICE: DB node id: 1 statement: SELECT 2; + ?column? +---------- + 2 +(1 row) + +-- tx started by multi-statement query ends +END; +NOTICE: DB node id: 1 statement: END; +NOTICE: DB node id: 0 statement: END; +-- multi-statement query including BEGIN +BEGIN\;SELECT 1; +NOTICE: DB node id: 0 statement: BEGIN;SELECT 1; +NOTICE: DB node id: 1 statement: BEGIN;SELECT 1; + ?column? +---------- + 1 +(1 row) + +-- SAVEPOINT +SAVEPOINT a; +NOTICE: DB node id: 0 statement: SAVEPOINT a; +NOTICE: DB node id: 1 statement: SAVEPOINT a; +-- PREPARE +PREPARE foo AS SELECT 2; +NOTICE: DB node id: 0 statement: PREPARE foo AS SELECT 2; +NOTICE: DB node id: 1 statement: PREPARE foo AS SELECT 2; +-- EXECUTE +EXECUTE foo; +NOTICE: DB node id: 0 statement: EXECUTE foo; +NOTICE: DB node id: 1 statement: EXECUTE foo; + ?column? +---------- + 2 +(1 row) + +-- DEALLOCATE +DEALLOCATE foo; +NOTICE: DB node id: 0 statement: DEALLOCATE foo; +NOTICE: DB node id: 1 statement: DEALLOCATE foo; +-- ROLLBACK TO +ROLLBACK TO a; +NOTICE: DB node id: 1 statement: ROLLBACK TO a; +NOTICE: DB node id: 0 statement: ROLLBACK TO a; +-- tx started by multi-statement query ends +END; +NOTICE: DB node id: 1 statement: END; +NOTICE: DB node id: 0 statement: END; +-- multi-statement query including BEGIN and ROLLBACK +BEGIN\;SELECT 1\;ROLLBACK; +NOTICE: DB node id: 0 statement: BEGIN;SELECT 1;ROLLBACK; +NOTICE: DB node id: 1 statement: BEGIN;SELECT 1;ROLLBACK; + ?column? +---------- + 1 +(1 row) + +-- ordinary read only SELECT: load balance expected +SELECT 1; +NOTICE: DB node id: 1 statement: SELECT 1; + ?column? +---------- + 1 +(1 row) + +-- multi-statement query including BEGIN and invalid query +BEGIN\;SELECT 1\;FOO; +NOTICE: DB node id: 0 statement: BEGIN;SELECT 1;FOO; +NOTICE: DB node id: 1 statement: BEGIN;SELECT 1;FOO; +ERROR: syntax error at or near "FOO" +LINE 1: BEGIN;SELECT 1;FOO; + ^ +-- ordinary read only SELECT: load balance expected +SELECT 1; +NOTICE: DB node id: 1 statement: SELECT 1; + ?column? +---------- + 1 +(1 row) + diff --git a/src/test/regression/tests/001.load_balance/expected/expected7-s b/src/test/regression/tests/001.load_balance/expected/expected7-s new file mode 100644 index 000000000..30510b4a3 --- /dev/null +++ b/src/test/regression/tests/001.load_balance/expected/expected7-s @@ -0,0 +1,88 @@ +-- ordinary read only SELECT: load balance expected +SELECT 1; +NOTICE: DB node id: 1 statement: SELECT 1; + ?column? +---------- + 1 +(1 row) + +-- multi-statement query including BEGIN +BEGIN\;SELECT 1; +NOTICE: DB node id: 0 statement: BEGIN;SELECT 1; + ?column? +---------- + 1 +(1 row) + +-- ordinary read only SELECT: load balance expected +SELECT 2; +NOTICE: DB node id: 1 statement: SELECT 2; + ?column? +---------- + 2 +(1 row) + +-- tx started by multi-statement query ends +END; +NOTICE: DB node id: 0 statement: END; +-- multi-statement query including BEGIN +BEGIN\;SELECT 1; +NOTICE: DB node id: 0 statement: BEGIN;SELECT 1; + ?column? +---------- + 1 +(1 row) + +-- SAVEPOINT +SAVEPOINT a; +NOTICE: DB node id: 0 statement: SAVEPOINT a; +-- PREPARE +PREPARE foo AS SELECT 2; +NOTICE: DB node id: 0 statement: PREPARE foo AS SELECT 2; +-- EXECUTE +EXECUTE foo; +NOTICE: DB node id: 0 statement: EXECUTE foo; + ?column? +---------- + 2 +(1 row) + +-- DEALLOCATE +DEALLOCATE foo; +NOTICE: DB node id: 0 statement: DEALLOCATE foo; +-- ROLLBACK TO +ROLLBACK TO a; +NOTICE: DB node id: 0 statement: ROLLBACK TO a; +-- tx started by multi-statement query ends +END; +NOTICE: DB node id: 0 statement: END; +-- multi-statement query including BEGIN and ROLLBACK +BEGIN\;SELECT 1\;ROLLBACK; +NOTICE: DB node id: 0 statement: BEGIN;SELECT 1;ROLLBACK; + ?column? +---------- + 1 +(1 row) + +-- ordinary read only SELECT: load balance expected +SELECT 1; +NOTICE: DB node id: 1 statement: SELECT 1; + ?column? +---------- + 1 +(1 row) + +-- multi-statement query including BEGIN and invalid query +BEGIN\;SELECT 1\;FOO; +NOTICE: DB node id: 0 statement: BEGIN;SELECT 1;FOO; +ERROR: syntax error at or near "FOO" +LINE 1: BEGIN;SELECT 1;FOO; + ^ +-- ordinary read only SELECT: load balance expected +SELECT 1; +NOTICE: DB node id: 1 statement: SELECT 1; + ?column? +---------- + 1 +(1 row) + diff --git a/src/test/regression/tests/001.load_balance/expected/expected8-s b/src/test/regression/tests/001.load_balance/expected/expected8-s new file mode 100644 index 000000000..43af8a854 --- /dev/null +++ b/src/test/regression/tests/001.load_balance/expected/expected8-s @@ -0,0 +1,88 @@ +-- ordinary read only SELECT: load balance expected +SELECT 1; +NOTICE: DB node id: 0 statement: SELECT 1; + ?column? +---------- + 1 +(1 row) + +-- multi-statement query including BEGIN +BEGIN\;SELECT 1; +NOTICE: DB node id: 1 statement: BEGIN;SELECT 1; + ?column? +---------- + 1 +(1 row) + +-- ordinary read only SELECT: load balance expected +SELECT 2; +NOTICE: DB node id: 0 statement: SELECT 2; + ?column? +---------- + 2 +(1 row) + +-- tx started by multi-statement query ends +END; +NOTICE: DB node id: 1 statement: END; +-- multi-statement query including BEGIN +BEGIN\;SELECT 1; +NOTICE: DB node id: 1 statement: BEGIN;SELECT 1; + ?column? +---------- + 1 +(1 row) + +-- SAVEPOINT +SAVEPOINT a; +NOTICE: DB node id: 1 statement: SAVEPOINT a; +-- PREPARE +PREPARE foo AS SELECT 2; +NOTICE: DB node id: 1 statement: PREPARE foo AS SELECT 2; +-- EXECUTE +EXECUTE foo; +NOTICE: DB node id: 1 statement: EXECUTE foo; + ?column? +---------- + 2 +(1 row) + +-- DEALLOCATE +DEALLOCATE foo; +NOTICE: DB node id: 1 statement: DEALLOCATE foo; +-- ROLLBACK TO +ROLLBACK TO a; +NOTICE: DB node id: 1 statement: ROLLBACK TO a; +-- tx started by multi-statement query ends +END; +NOTICE: DB node id: 1 statement: END; +-- multi-statement query including BEGIN and ROLLBACK +BEGIN\;SELECT 1\;ROLLBACK; +NOTICE: DB node id: 1 statement: BEGIN;SELECT 1;ROLLBACK; + ?column? +---------- + 1 +(1 row) + +-- ordinary read only SELECT: load balance expected +SELECT 1; +NOTICE: DB node id: 0 statement: SELECT 1; + ?column? +---------- + 1 +(1 row) + +-- multi-statement query including BEGIN and invalid query +BEGIN\;SELECT 1\;FOO; +NOTICE: DB node id: 1 statement: BEGIN;SELECT 1;FOO; +ERROR: syntax error at or near "FOO" +LINE 1: BEGIN;SELECT 1;FOO; + ^ +-- ordinary read only SELECT: load balance expected +SELECT 1; +NOTICE: DB node id: 0 statement: SELECT 1; + ?column? +---------- + 1 +(1 row) + diff --git a/src/test/regression/tests/001.load_balance/sql/7.sql b/src/test/regression/tests/001.load_balance/sql/7.sql new file mode 100644 index 000000000..9e39d2764 --- /dev/null +++ b/src/test/regression/tests/001.load_balance/sql/7.sql @@ -0,0 +1,30 @@ +-- ordinary read only SELECT: load balance expected +SELECT 1; +-- multi-statement query including BEGIN +BEGIN\;SELECT 1; +-- ordinary read only SELECT: load balance expected +SELECT 2; +-- tx started by multi-statement query ends +END; +-- multi-statement query including BEGIN +BEGIN\;SELECT 1; +-- SAVEPOINT +SAVEPOINT a; +-- PREPARE +PREPARE foo AS SELECT 2; +-- EXECUTE +EXECUTE foo; +-- DEALLOCATE +DEALLOCATE foo; +-- ROLLBACK TO +ROLLBACK TO a; +-- tx started by multi-statement query ends +END; +-- multi-statement query including BEGIN and ROLLBACK +BEGIN\;SELECT 1\;ROLLBACK; +-- ordinary read only SELECT: load balance expected +SELECT 1; +-- multi-statement query including BEGIN and invalid query +BEGIN\;SELECT 1\;FOO; +-- ordinary read only SELECT: load balance expected +SELECT 1; diff --git a/src/test/regression/tests/001.load_balance/test.sh b/src/test/regression/tests/001.load_balance/test.sh index 4edd5ff58..29b0a5c05 100755 --- a/src/test/regression/tests/001.load_balance/test.sh +++ b/src/test/regression/tests/001.load_balance/test.sh @@ -5,6 +5,9 @@ source $TESTLIBS TESTDIR=testdir PSQL=$PGBIN/psql +PSQLOPTS="-a -q -X" +PGPOOLBIN=$PGPOOL_INSTALL_DIR/bin +export PGDATABASE=test # sleep time after reload in seconds st=10 @@ -58,7 +61,7 @@ do wait_for_pgpool_startup - $PSQL -ae test > result1 2>&1 < result1 2>&1 < result2 2>&1 < result2 2>&1 < result3 2>&1 < result3 2>&1 < result4 2>&1 < result4 2>&1 <> etc/pgpool.conf ./pgpool_reload sleep $st - $PSQL -ae test > result5 2>&1 < result5 2>&1 < result6 2>&1 < result6 2>&1 <> etc/pgpool.conf + # XXX primary_routing_query_pattern_list does not allow to overwritten. + # So following does not work. + #echo "primary_routing_query_pattern_list = ''" >> etc/pgpool.conf + sed -i '/^primary_routing_query_pattern_list/d' etc/pgpool.conf + #echo "log_min_messages = debug5" >> etc/pgpool.conf + ./shutdownall + ./startall + wait_for_pgpool_startup + $PSQL -c "SHOW POOL_NODES;" test - cd .. + $PSQL $PSQLOPTS < ../sql/7.sql > result7 2>&1 + check_result 7 + + echo "=== test8 started ===" +# ------------------------------------------------------------------------------- +# multi statement queries (swapping primary and standby) +# ------------------------------------------------------------------------------- + if [ $mode = 's' ];then + echo $PGPOOLBIN/pcp_promote_node -w -p $PCP_PORT --switchover 1 + $PGPOOLBIN/pcp_promote_node -w -p $PCP_PORT --switchover 1 + while : + do + wait_for_pgpool_startup + $PSQL -c "SHOW POOL_NODES;" test | grep down + if [ $? != 0 ];then + break + fi + sleep 1 + done + + # Swap the weights. Now backend 0 is the load balance node + echo "backend_weight0 = 1" >> etc/pgpool.conf + echo "backend_weight1 = 0" >> etc/pgpool.conf + ./shutdownall + ./startall + wait_for_pgpool_startup + $PSQL $PSQLOPTS < ../sql/7.sql > result8 2>&1 + check_result 8 + fi + ./shutdownall + cd .. done exit 0 -- 2.39.5