pgpool2.git / summary / log / commit / refs
commit 8d58378ff3d3aa9b5b5bdd489d201e1c4229ac60
Author: Bo Peng <pengbo@sraoss.co.jp>
Date: Sun Dec 17 14:30:15 2017 +0000
Fix timestamp data inconsistency by replication mode.
From PostgreSQL10 the column default value such as 'CURRENT_DATE' changes,
Pgpool-II should also rewrite timestamp by the added default values in stead of
"'now'::text::date".
src/rewrite/pool_timestamp.c | 12 ++++++++---
.../tests/002.native_replication/test.sh | 24 ++++++++++++++++++----
2 files changed, 29 insertions(+), 7 deletions(-)
diff --git a/src/rewrite/pool_timestamp.c b/src/rewrite/pool_timestamp.c
index cf975a656..925142893 100644
--- a/src/rewrite/pool_timestamp.c
+++ b/src/rewrite/pool_timestamp.c
@@ -130,7 +130,9 @@ ts_unregister_func(void *data)
static TSRel*
relcache_lookup(TSRewriteContext *ctx)
{
-#define ATTRDEFQUERY "SELECT attname, d.adsrc, coalesce((d.adsrc LIKE '%%now()%%' OR d.adsrc LIKE '%%''now''::text%%')" \
+#define ATTRDEFQUERY "SELECT attname, d.adsrc, coalesce((d.adsrc LIKE '%%now()%%' OR d.adsrc LIKE '%%''now''::text%%' OR" \
+ " d.adsrc LIKE '%%CURRENT_TIMESTAMP%%' OR d.adsrc LIKE '%%CURRENT_TIME%%' OR d.adsrc LIKE '%%CURRENT_DATE%%' OR" \
+ " d.adsrc LIKE '%%LOCALTIME%%' OR d.adsrc LIKE '%%LOCALTIMESTAMP%%')" \
" AND (a.atttypid = 'timestamp'::regtype::oid OR" \
" a.atttypid = 'timestamp with time zone'::regtype::oid OR" \
" a.atttypid = 'date'::regtype::oid OR" \
@@ -142,7 +144,9 @@ relcache_lookup(TSRewriteContext *ctx)
" WHERE c.oid = a.attrelid AND a.attnum >= 1 AND a.attisdropped = 'f' AND c.relname = '%s'" \
" ORDER BY a.attnum"
-#define ATTRDEFQUERY2 "SELECT attname, d.adsrc, coalesce((d.adsrc LIKE '%%now()%%' OR d.adsrc LIKE '%%''now''::text%%')" \
+#define ATTRDEFQUERY2 "SELECT attname, d.adsrc, coalesce((d.adsrc LIKE '%%now()%%' OR d.adsrc LIKE '%%''now''::text%%' OR" \
+ " d.adsrc LIKE '%%CURRENT_TIMESTAMP%%' OR d.adsrc LIKE '%%CURRENT_TIME%%' OR d.adsrc LIKE '%%CURRENT_DATE%%' OR" \
+ " d.adsrc LIKE '%%LOCALTIME%%' OR d.adsrc LIKE '%%LOCALTIMESTAMP%%')" \
" AND (a.atttypid = 'timestamp'::regtype::oid OR" \
" a.atttypid = 'timestamp with time zone'::regtype::oid OR" \
" a.atttypid = 'date'::regtype::oid OR" \
@@ -154,7 +158,9 @@ relcache_lookup(TSRewriteContext *ctx)
" WHERE c.oid = a.attrelid AND a.attnum >= 1 AND a.attisdropped = 'f' AND c.oid = pgpool_regclass('%s')" \
" ORDER BY a.attnum"
-#define ATTRDEFQUERY3 "SELECT attname, d.adsrc, coalesce((d.adsrc LIKE '%%now()%%' OR d.adsrc LIKE '%%''now''::text%%')" \
+#define ATTRDEFQUERY3 "SELECT attname, d.adsrc, coalesce((d.adsrc LIKE '%%now()%%' OR d.adsrc LIKE '%%''now''::text%%' OR" \
+ " d.adsrc LIKE '%%CURRENT_TIMESTAMP%%' OR d.adsrc LIKE '%%CURRENT_TIME%%' OR d.adsrc LIKE '%%CURRENT_DATE%%' OR" \
+ " d.adsrc LIKE '%%LOCALTIME%%' OR d.adsrc LIKE '%%LOCALTIMESTAMP%%')" \
" AND (a.atttypid = 'timestamp'::regtype::oid OR" \
" a.atttypid = 'timestamp with time zone'::regtype::oid OR" \
" a.atttypid = 'date'::regtype::oid OR" \
diff --git a/src/test/regression/tests/002.native_replication/test.sh b/src/test/regression/tests/002.native_replication/test.sh
index 80a17fad4..df85323cd 100755
--- a/src/test/regression/tests/002.native_replication/test.sh
+++ b/src/test/regression/tests/002.native_replication/test.sh
@@ -26,10 +26,18 @@ wait_for_pgpool_startup
$PSQL test <<EOF
CREATE TABLE t1(i SERIAL, j TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP);
+CREATE TABLE t2(i SERIAL, j DATE DEFAULT CURRENT_DATE);
+CREATE TABLE t3(i SERIAL, j TIME WITH TIME ZONE DEFAULT CURRENT_TIME);
+CREATE TABLE t4(i SERIAL, j TIME WITH TIME ZONE DEFAULT LOCALTIME);
+CREATE TABLE t5(i SERIAL, j TIME WITH TIME ZONE DEFAULT LOCALTIMESTAMP);
EOF
cat > pgbench.sql <<EOF
INSERT INTO t1 VALUES (DEFAULT);
+INSERT INTO t2 VALUES (DEFAULT);
+INSERT INTO t3 VALUES (DEFAULT);
+INSERT INTO t4 VALUES (DEFAULT);
+INSERT INTO t5 VALUES (DEFAULT);
EOF
$PGBENCH -i test
@@ -51,11 +59,19 @@ java PgTester 1000 &
wait
$PSQL -p 11002 test <<EOF
-\copy (SELECT * FROM t1 ORDER BY i) to 'dump_t0.txt'
+\copy (SELECT * FROM t1 ORDER BY i) to 'dump_t1_n0.txt'
+\copy (SELECT * FROM t2 ORDER BY i) to 'dump_t2_n0.txt'
+\copy (SELECT * FROM t3 ORDER BY i) to 'dump_t3_n0.txt'
+\copy (SELECT * FROM t4 ORDER BY i) to 'dump_t4_n0.txt'
+\copy (SELECT * FROM t5 ORDER BY i) to 'dump_t5_n0.txt'
EOF
$PSQL -p 11003 test <<EOF
-\copy (SELECT * FROM t1 ORDER BY i) to 'dump_t1.txt'
+\copy (SELECT * FROM t1 ORDER BY i) to 'dump_t1_n1.txt'
+\copy (SELECT * FROM t2 ORDER BY i) to 'dump_t2_n1.txt'
+\copy (SELECT * FROM t3 ORDER BY i) to 'dump_t3_n1.txt'
+\copy (SELECT * FROM t4 ORDER BY i) to 'dump_t4_n1.txt'
+\copy (SELECT * FROM t5 ORDER BY i) to 'dump_t5_n1.txt'
EOF
$PSQL -p 11002 test <<EOF
@@ -67,8 +83,8 @@ $PSQL -p 11003 test <<EOF
EOF
# check if database contents are identical
-cat dump_t0.txt dump_s0.txt > dump0.txt
-cat dump_t1.txt dump_s1.txt > dump1.txt
+cat dump_t1_n0.txt dump_t2_n0.txt dump_t3_n0.txt dump_t4_n0.txt dump_t5_n0.txt dump_s0.txt > dump0.txt
+cat dump_t1_n1.txt dump_t2_n1.txt dump_t3_n1.txt dump_t4_n1.txt dump_t5_n1.txt dump_s1.txt > dump1.txt
diff dump0.txt dump1.txt
if [ $? != 0 ];then
# contents are not identical
[parent: 2b8d6a0139c0]