aboutsummaryrefslogtreecommitdiffstats
path: root/sql
diff options
context:
space:
mode:
authorBryan Newbold <bnewbold@archive.org>2022-04-04 15:27:02 -0700
committerBryan Newbold <bnewbold@archive.org>2022-04-04 15:27:02 -0700
commitc4caeaedc63c2bb2f9bb9f6be6826480aefc9caf (patch)
tree8cbf981a46afc8571ab3cc85f3eab5595afd426b /sql
parent51193896a4958025f1bf045b0ba908ab5d4d0553 (diff)
downloadsandcrawler-c4caeaedc63c2bb2f9bb9f6be6826480aefc9caf.tar.gz
sandcrawler-c4caeaedc63c2bb2f9bb9f6be6826480aefc9caf.zip
sql: fix reingest query missing type on LEFT JOIN; wrap in read-only transaction
Diffstat (limited to 'sql')
-rw-r--r--sql/dump_reingest_bulk.sql8
-rw-r--r--sql/dump_reingest_quarterly.sql7
-rw-r--r--sql/dump_reingest_spn.sql8
-rw-r--r--sql/dump_reingest_weekly.sql7
-rwxr-xr-xsql/reingest_weekly.sh2
5 files changed, 27 insertions, 5 deletions
diff --git a/sql/dump_reingest_bulk.sql b/sql/dump_reingest_bulk.sql
index 403fb20..698db7a 100644
--- a/sql/dump_reingest_bulk.sql
+++ b/sql/dump_reingest_bulk.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')
@@ -23,3 +27,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_bulk_current.rows.json';
+
+ROLLBACK;
diff --git a/sql/dump_reingest_quarterly.sql b/sql/dump_reingest_quarterly.sql
index c425a15..c377bf0 100644
--- a/sql/dump_reingest_quarterly.sql
+++ b/sql/dump_reingest_quarterly.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')
@@ -32,3 +36,4 @@ COPY (
-- AND (ingest_request.ingest_request_source != 'fatcat-changelog'
-- AND ingest_request.ingest_request_source != 'fatcat-ingest')
+ROLLBACK;
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;
diff --git a/sql/dump_reingest_weekly.sql b/sql/dump_reingest_weekly.sql
index e529945..4acec38 100644
--- a/sql/dump_reingest_weekly.sql
+++ b/sql/dump_reingest_weekly.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')
@@ -32,3 +36,4 @@ COPY (
-- AND (ingest_request.ingest_request_source != 'fatcat-changelog'
-- AND ingest_request.ingest_request_source != 'fatcat-ingest')
+ROLLBACK;
diff --git a/sql/reingest_weekly.sh b/sql/reingest_weekly.sh
index 04ce39d..b60bd0e 100755
--- a/sql/reingest_weekly.sh
+++ b/sql/reingest_weekly.sh
@@ -13,7 +13,7 @@ sudo -u sandcrawler pipenv run \
cat /srv/sandcrawler/tasks/reingest_weekly_current.json \
| shuf \
- | head -n60000 \
+ | head -n80000 \
| jq . -c \
| kafkacat -P -b wbgrp-svc263.us.archive.org -t sandcrawler-prod.ingest-file-requests-daily -p -1