[OSSTEST PATCH 0/7] Fixes for postgresql etc. upgrade

[OSSTEST PATCH 0/7] Fixes for postgresql etc. upgrade

[Ian Jackson]

Evidently it has been a while since I last ran this script since
it really didn't work at all.  Fix it.

Ian Jackson (7):
  mg-schema-test-database: posgtres compat: psql transactions
  mg-schema-test-database: Be slightly more defensive
  mg-schema-test-database: posgtres compat: Cope with schema name
  mg-schema-test-database: GetOpt compat: work around arg bundling bug
  mg-schema-test-database: posgtres compat: Sequences
  mg-schema-test-database: Fix following host_lifecycle
  mg-schema-test-database: posgtres compat: tidy messages

 mg-schema-create        |  2 +-
 mg-schema-test-database | 41 +++++++++++++++++++++++++----------------
 2 files changed, 26 insertions(+), 17 deletions(-)

-- 
2.20.1

[OSSTEST PATCH 1/7] mg-schema-test-database: posgtres compat: psql transactions

[Ian Jackson]

Now (postgresql 11):

 * psql transaction behaviour has changed so that now we want to
   use the -1 option.  This obviates a few BEGIN and COMMITs.

 * SET implicitly starts a transaction and DROP and CREATE DATABASE
   aren't transactional and now complain if they are run in a
   transaction.  So we must add COMMIT after SET.

Signed-off-by: Ian Jackson <iwj@xenproject.org>
---
 mg-schema-create        |  2 +-
 mg-schema-test-database | 12 +++++-------
 2 files changed, 6 insertions(+), 8 deletions(-)

diff --git a/mg-schema-create b/mg-schema-create
index 9bb3040a..3a344db4 100755
--- a/mg-schema-create
+++ b/mg-schema-create
@@ -89,7 +89,7 @@ fi
 
 $progress "Populating database..."
 
-$(get_psql_cmd) $quietopt -f schema/initial.sql
+$(get_psql_cmd) $quietopt -1 -f schema/initial.sql
 
 if $do_updates; then
     ./mg-schema-update $quietopt apply-all "${updates[@]}"
diff --git a/mg-schema-test-database b/mg-schema-test-database
index dab536c5..e69b9881 100755
--- a/mg-schema-test-database
+++ b/mg-schema-test-database
@@ -132,7 +132,7 @@ psql_query () {
 }
 
 psql_do_cmd () {
-	echo "$(get_psql_cmd) ${OSSTEST_DEBUG:+-e -a}" 
+	echo "$(get_psql_cmd) ${OSSTEST_DEBUG:+-e -a}" -1
 }
 
 psql_do () {
@@ -411,7 +411,9 @@ END
 	#---------- create test db ----------
 
 	psql_do <<END
+		COMMIT;
 		CREATE DATABASE $dbname;
+		BEGIN;
 END
 	withtest ./mg-schema-create -q --no-updates
 
@@ -449,12 +451,10 @@ END
 
 	for task in $tasks; do
 		psql_do <<END
-			BEGIN;
 			$(make_xdbref_task $dbname 'borrowed for test db' \
 				$task "$username@$nodename")
 			UPDATE resources SET owntaskid = $(borrowtaskid $task)
 				WHERE owntaskid=$task;
-			COMMIT;
 END
 	done
 
@@ -466,7 +466,6 @@ END
 
 	cat >>$t.import <<END
 		\o $t.import-output
-		BEGIN;
 		SET CONSTRAINTS ALL DEFERRED;
 END
 
@@ -500,7 +499,6 @@ END
 			WHERE owntaskid != $(borrowtaskid $task)
 			  AND owntaskid != $(taskid magic shared)
 			  AND owntaskid != $(taskid magic preparing);
-		COMMIT;
 END
 
 	printf "Copy..."
@@ -519,11 +517,9 @@ END
 
 	for task in $tasks; do
 		(withtest psql_do <<END
-			BEGIN;
 			UPDATE resources
 				SET owntaskid = $(taskid magic idle)
 				WHERE owntaskid = $(borrowtaskid $task);
-			COMMIT;
 END
                 )
 	done
@@ -551,7 +547,9 @@ drop)
 
 	psql_do <<END
                 SET client_min_messages = WARNING;
+		COMMIT;
 		DROP DATABASE IF EXISTS $dbname;
+		BEGIN;
 		UPDATE resources
 			SET owntaskid = CAST(tasks.refinfo AS INTEGER)
 			FROM tasks
-- 
2.20.1

[OSSTEST PATCH 2/7] mg-schema-test-database: Be slightly more defensive

[Ian Jackson]

Signed-off-by: Ian Jackson <iwj@xenproject.org>
---
 mg-schema-test-database | 3 ++-
 1 file changed, 2 insertions(+), 1 deletion(-)

diff --git a/mg-schema-test-database b/mg-schema-test-database
index e69b9881..b49fbecf 100755
--- a/mg-schema-test-database
+++ b/mg-schema-test-database
@@ -381,7 +381,8 @@ END
 			print "sequences+=\" $1\"\n";
 		} elsif (m/^CREATE TABLE (\w+)/) {
 			$table=$1;
-		} elsif (m/^\s*flight\s+integer/) {
+		} elsif (m/^\s*flight\s+integer\s/) {
+			die unless $table;
 			print "ftables+=\" $table\"\n";
 		} elsif ($table && m/^\)\;$/) {
 			print "tables+=\" $table\"\n";
-- 
2.20.1

[OSSTEST PATCH 3/7] mg-schema-test-database: posgtres compat: Cope with schema name

[Ian Jackson]

The dump now (postgresql 11) contains lots of "public." which made
these regexps not match.

Signed-off-by: Ian Jackson <iwj@xenproject.org>
---
 mg-schema-test-database | 5 +++--
 1 file changed, 3 insertions(+), 2 deletions(-)

diff --git a/mg-schema-test-database b/mg-schema-test-database
index b49fbecf..b5906d70 100755
--- a/mg-schema-test-database
+++ b/mg-schema-test-database
@@ -377,9 +377,10 @@ END
 
 	# What tables are there ?
 	perl -ne <$t.schema >$t.tablevars '
-		if (m/^CREATE SEQUENCE (\w+)/) {
+		BEGIN { $sch_re = qr{(?:public\.)?}; }
+		if (m/^CREATE SEQUENCE $sch_re(\w+)/) {
 			print "sequences+=\" $1\"\n";
-		} elsif (m/^CREATE TABLE (\w+)/) {
+		} elsif (m/^CREATE TABLE $sch_re(\w+)\s+/) {
 			$table=$1;
 		} elsif (m/^\s*flight\s+integer\s/) {
 			die unless $table;
-- 
2.20.1

[OSSTEST PATCH 4/7] mg-schema-test-database: GetOpt compat: work around arg bundling bug

[Ian Jackson]

With -qff it now complains, daftly, that -q doesn't take a value.

Signed-off-by: Ian Jackson <iwj@xenproject.org>
---
 mg-schema-test-database | 2 +-
 1 file changed, 1 insertion(+), 1 deletion(-)

diff --git a/mg-schema-test-database b/mg-schema-test-database
index b5906d70..26e35c5b 100755
--- a/mg-schema-test-database
+++ b/mg-schema-test-database
@@ -419,7 +419,7 @@ END
 END
 	withtest ./mg-schema-create -q --no-updates
 
-	withtest ./mg-schema-update -qff apply $wantupdates
+	withtest ./mg-schema-update -q -f -f apply $wantupdates
 
         printf " (seqs)"
         seq_alters=""
-- 
2.20.1

[OSSTEST PATCH 5/7] mg-schema-test-database: posgtres compat: Sequences

[Ian Jackson]

Now (postgresql 11) start_value is in a different table.  We don't
really care about it very much and mostly care about the last value,
so we fix that (and launder the for-db comparison dumps).

Signed-off-by: Ian Jackson <iwj@xenproject.org>
---
 mg-schema-test-database | 16 ++++++++++++----
 1 file changed, 12 insertions(+), 4 deletions(-)

diff --git a/mg-schema-test-database b/mg-schema-test-database
index 26e35c5b..eab0746e 100755
--- a/mg-schema-test-database
+++ b/mg-schema-test-database
@@ -425,16 +425,16 @@ END
         seq_alters=""
         for seq in $sequences; do
         	orgseqval=$(psql_query <<END
-			SELECT start_value || ' ' || last_value FROM $seq
+			SELECT s.last_value FROM $seq s
 END
                 )
-                read orgstart orglast <<END
+                read orglast <<END
 $orgseqval
 END
 		newlast=$(( 10000 * (2 + $orglast / 10000) ))
                 seq_alters+="
 			ALTER SEQUENCE $seq
-				START WITH $orgstart
+				START WITH $newlast
 				RESTART WITH $newlast;
 "
         done
@@ -447,7 +447,15 @@ END
 
 	# Schema should now be identical to main DB
 	$(withtest get_pgdump_cmd) -s -O -x >$t.schema.created
-	diff -u $t.schema.orig $t.schema.created
+
+	for wh in orig created; do
+		perl -pe '
+			next unless m{^CREATE SEQUENCE }..m{^\)};
+			$_ = "" if m/^\s*START WITH \d+$/;
+		' <$t.schema.$wh >$t.schema.$wh-laund
+	done
+
+	diff -u $t.schema.orig-laund $t.schema.created-laund
 
 	#---------- mark resources that we are going to borrow ----------
 
-- 
2.20.1

[OSSTEST PATCH 6/7] mg-schema-test-database: Fix following host_lifecycle

[Ian Jackson]

This data doesn't really survive, since the tasks are all mangled.
Rather than trying to transpose it, just don't transfer it for now.

Signed-off-by: Ian Jackson <iwj@xenproject.org>
---
 mg-schema-test-database | 1 +
 1 file changed, 1 insertion(+)

diff --git a/mg-schema-test-database b/mg-schema-test-database
index eab0746e..df83b65a 100755
--- a/mg-schema-test-database
+++ b/mg-schema-test-database
@@ -482,6 +482,7 @@ END
 	for table in $tables; do
 		case $table in
 		schema_updates)	continue ;;
+		host_lifecycle)	continue ;;
 		esac
 		case " $ftables " in
 		*" $table "*)
-- 
2.20.1

[OSSTEST PATCH 7/7] mg-schema-test-database: posgtres compat: tidy messages

[Ian Jackson]

Now (postgresql 11) this prints a bunch of COPY notices.  Suppress
them with a -q.

Signed-off-by: Ian Jackson <iwj@xenproject.org>
---
 mg-schema-test-database | 2 +-
 1 file changed, 1 insertion(+), 1 deletion(-)

diff --git a/mg-schema-test-database b/mg-schema-test-database
index df83b65a..357646bb 100755
--- a/mg-schema-test-database
+++ b/mg-schema-test-database
@@ -515,7 +515,7 @@ END
 	printf "Copy..."
 
 	printf "export..."
-	$(psql_do_cmd) -f $t.export
+	$(psql_do_cmd) -q -f $t.export
 
 	printf "import..."
 	$(withtest psql_do_cmd) -f $t.import
-- 
2.20.1