From c4caeaedc63c2bb2f9bb9f6be6826480aefc9caf Mon Sep 17 00:00:00 2001
From: Bryan Newbold <bnewbold@archive.org>
Date: Mon, 4 Apr 2022 15:27:02 -0700
Subject: sql: fix reingest query missing type on LEFT JOIN; wrap in read-only
 transaction

---
 sql/dump_reingest_spn.sql | 8 +++++++-
 1 file changed, 7 insertions(+), 1 deletion(-)

(limited to 'sql/dump_reingest_spn.sql')

diff --git a/sql/dump_reingest_spn.sql b/sql/dump_reingest_spn.sql
index b0051dd..65a8796 100644
--- a/sql/dump_reingest_spn.sql
+++ b/sql/dump_reingest_spn.sql
@@ -1,7 +1,11 @@
 
+BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE;
+
 COPY (
     SELECT row_to_json(ingest_request.*) FROM ingest_request
-    LEFT JOIN ingest_file_result ON ingest_file_result.base_url = ingest_request.base_url
+    LEFT JOIN ingest_file_result ON
+        ingest_file_result.base_url = ingest_request.base_url
+        AND ingest_file_result.ingest_type = ingest_request.ingest_type
     WHERE
         (ingest_request.ingest_type = 'pdf'
             OR ingest_request.ingest_type = 'html')
@@ -26,3 +30,5 @@ COPY (
         AND ingest_file_result.status != 'spn2-error:network-authentication-required'
         AND ingest_file_result.status != 'spn2-error:unknown'
 ) TO '/srv/sandcrawler/tasks/reingest_spn.rows.json';
+
+ROLLBACK;
-- 
cgit v1.2.3

