[dak/master] use temporary table when syncing `external_files` table

[Ansgar Burchardt <ansgar@debian.org>]

This should keep the WAL smaller as we no longer delete and rewrite
the full table.
---
 config/debian/daily.functions        |  2 +-
 scripts/debian/update-external-files | 49 ++++++++++++++++++++++++++++++++++++
 2 files changed, 50 insertions(+), 1 deletion(-)
 create mode 100644 scripts/debian/update-external-files

diff --git a/config/debian/daily.functions b/config/debian/daily.functions
index 40b4e869..fa0bd82b 100644
--- a/config/debian/daily.functions
+++ b/config/debian/daily.functions
@@ -13,7 +13,7 @@ function wnppbugs() {
 function pushfilesdb() {
     log "Pushing files table to security"
     # The key over there should have the following set for the ssh key:
-    #  command="/usr/bin/xzcat | /usr/bin/psql -1 -c 'DELETE FROM external_files; COPY external_files (id, filename, size, md5sum, last_used, sha1sum, sha256sum, created, modified) FROM STDIN' obscurity"
+    #  command="/srv/security-master.debian.org/dak/scripts/debian/update-external-files --update"
     psql -c 'COPY files (id, filename, size, md5sum, last_used, sha1sum, sha256sum, created, modified) TO STDOUT' projectb | \
         xz -3 | \
         ssh -o BatchMode=yes -o ConnectTimeout=30 -o SetupTimeout=30 -2 \
diff --git a/scripts/debian/update-external-files b/scripts/debian/update-external-files
new file mode 100644
index 00000000..8ae7f13e
--- /dev/null
+++ b/scripts/debian/update-external-files
@@ -0,0 +1,49 @@
+#! /bin/bash
+
+# Copyright (C) 2017, Ansgar Burchardt <ansgar@debian.org>
+#
+# This program is free software; you can redistribute it and/or
+# modify it under the terms of the GNU General Public License as
+# published by the Free Software Foundation; version 2.
+#
+# This program is distributed in the hope that it will be useful, but
+# WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
+# General Public License for more details.
+#
+# You should have received a copy of the GNU General Public License
+# along with this program; if not, write to the Free Software
+# Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
+
+set -e
+set -u
+set -o pipefail
+
+usage() {
+  cat >&2 <<-EOF
+	usage: $0 --update
+	Update `external_files` table with data from standard input.
+
+	The input should be the xz-compressed output of the
+	    COPY files (id, filename, size, md5sum, last_used, sha1sum,
+            sha256sum, created, modified) TO STDOUT
+	query.  See config/debian/daily.functions's pushfilesdb.
+	EOF
+  exit ${1:-0}
+}
+
+if [ "$#" -eq 0 ]; then
+  usage
+fi
+
+if [ "$#" -ne 1 -o "${1}" != "--update" ]; then
+  usage 1
+fi
+
+/usr/bin/xzcat |
+  /usr/bin/psql -1 -d obscurity -c '
+    CREATE TEMPORARY TABLE external_files_tmp AS SELECT * FROM external_files WITH NO DATA;
+    COPY external_files_tmp (id, filename, size, md5sum, last_used, sha1sum, sha256sum, created, modified) FROM STDIN;
+    DELETE FROM external_files WHERE id NOT IN (SELECT id FROM external_files_tmp);
+    INSERT INTO external_files SELECT * FROM external_files_tmp WHERE id NOT IN (SELECT id FROM external_files);
+    '
-- 
2.11.0