Hello,
While testing pg_upgrade we seemed to find an issue related to default value of a column with type bit/varbit.
Below are the steps to reproduce. In this case we added two 'create table' DDLs in the regression database.
Obviously we saw diff after pg_upgrade testing. The pg binaries are based on the code pulled a couple of days ago.
[pguo@host67:/data2/postgres/src/bin/pg_upgrade]$ git diff
diff --git a/src/bin/pg_upgrade/test.sh b/src/bin/pg_upgrade/test.sh
old mode 100644
new mode 100755
index 39983abea1..a41105859e
--- a/src/bin/pg_upgrade/test.sh
+++ b/src/bin/pg_upgrade/test.sh
@@ -188,6 +188,9 @@ if "$MAKE" -C "$oldsrc" installcheck; then
psql -X -d regression -c "$fix_sql;" || psql_fix_sql_status=$?
fi
+ psql -X -d regression -c "CREATE TABLE t111 ( a40 bit varying(5) DEFAULT '1');"
+ psql -X -d regression -c "CREATE TABLE t222 ( a40 bit varying(5) DEFAULT B'1');"
+
pg_dumpall --no-sync -f "$temp_root"/dump1.sql || pg_dumpall1_status=$?
if [ "$newsrc" != "$oldsrc" ]; then
[pguo@host67:/data2/postgres/src/bin/pg_upgrade]$ make check
[pguo@host67:/data2/postgres/src/bin/pg_upgrade]$ diff -du tmp_check/dump1.sql tmp_check/dump2.sql
--- tmp_check/dump1.sql 2018-03-30 16:31:44.329021348 +0800
+++ tmp_check/dump2.sql 2018-03-30 16:31:54.100478202 +0800
@@ -10956,7 +10956,7 @@
--
CREATE TABLE public.t111 (
- a40 bit varying(5) DEFAULT B'1'::bit varying
+ a40 bit varying(5) DEFAULT (B'1'::"bit")::bit varying
);
There is no diff in functionality of the dump SQLs, but it is annoying. The simple patch below could fix this. Thanks.
[pguo@host67:/data2/postgres/src/bin/pg_upgrade]$ git diff
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 2cd54ec33f..ef2ab2d681 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -9389,7 +9389,7 @@ get_const_expr(Const *constval, deparse_context *context, int showtype)
case BITOID:
case VARBITOID:
- appendStringInfo(buf, "B'%s'", extval);
+ appendStringInfo(buf, "'%s'", extval);
break;
case BOOLOID: