Mercurial > gemma
changeset 4126:52f7264265bb
Bulk-delete tracked entries per table
Especially when many entries in one table are tracked by one
import, this makes declining an import a lot faster.
| author | Tom Gottfried <tom@intevation.de> |
|---|---|
| date | Thu, 01 Aug 2019 17:02:09 +0200 |
| parents | 0f69d256fa12 |
| children | 8c62809ea87e 49ec33a7f954 |
| files | schema/gemma.sql schema/import_tests.sql schema/run_tests.sh schema/tap_tests_data.sql schema/updates/1101/01.improve_del_import.sql schema/version.sql |
| diffstat | 6 files changed, 109 insertions(+), 4 deletions(-) [+] |
line wrap: on
line diff
--- a/schema/gemma.sql Thu Aug 01 16:46:13 2019 +0200 +++ b/schema/gemma.sql Thu Aug 01 17:02:09 2019 +0200 @@ -881,9 +881,13 @@ tmp RECORD; BEGIN FOR tmp IN - SELECT * FROM import.track_imports WHERE import_id = imp_id AND NOT deletion + SELECT relation, array_agg(key) AS keys + FROM import.track_imports + WHERE import_id = imp_id AND NOT deletion + GROUP BY relation LOOP - EXECUTE format('DELETE FROM %s WHERE id = $1', tmp.relation) USING tmp.key; + EXECUTE format('DELETE FROM %s WHERE id = ANY($1)', tmp.relation) + USING tmp.keys; END LOOP; END; $$
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/import_tests.sql Thu Aug 01 17:02:09 2019 +0200 @@ -0,0 +1,81 @@ +-- This is Free Software under GNU Affero General Public License v >= 3.0 +-- without warranty, see README.md and license for details. + +-- SPDX-License-Identifier: AGPL-3.0-or-later +-- License-Filename: LICENSES/AGPL-3.0.txt + +-- Copyright (C) 2019 by via donau +-- – Österreichische Wasserstraßen-Gesellschaft mbH +-- Software engineering by Intevation GmbH + +-- Author(s): +-- * Tom Gottfried <tom@intevation.de> + +-- +-- pgTAP test script for import queue +-- + +\set imp_id 99 +PREPARE insert_gms AS WITH +gms AS ( + INSERT INTO waterway.gauge_measurements ( + location, + measure_date, + country_code, + sender, + language_code, + date_issue, + reference_code, + water_level, + date_info, + source_organization + ) SELECT + ('AT', 'XXX', '00001', 'G0001', 1)::isrs, + t, + 'AT', + 'test', + 'DE', + current_timestamp, + 'ZPG', + 0, + current_timestamp, + 'test' + FROM generate_series( + current_timestamp - '12 h'::interval, + current_timestamp - '6 h'::interval, + '15 min'::interval) AS times (t) + RETURNING id), +imps AS ( + INSERT INTO import.imports (id, kind, username) VALUES ( + $1, 'test', 'test_admin_ro') + RETURNING id) +INSERT INTO import.track_imports (import_id, relation, key) + SELECT imps.id, 'waterway.gauge_measurements', gms.id + FROM imps, gms; + +EXECUTE insert_gms(:imp_id); +EXECUTE insert_gms(:imp_id + 1); + +SELECT ok((SELECT count(*) FROM waterway.gauge_measurements + WHERE id IN(SELECT key + FROM import.track_imports + WHERE import_id IN(:imp_id, :imp_id + 1)) + ) = (SELECT count(*) + FROM import.track_imports WHERE import_id IN(:imp_id, :imp_id + 1)), + 'Tracked entries of test imports exist'); + +SELECT import.del_import(:imp_id); + +SELECT ok(0 = (SELECT count(*) FROM waterway.gauge_measurements + WHERE id IN(SELECT key + FROM import.track_imports + WHERE import_id = :imp_id)), + 'Tracked entries of first test import are deleted'); + +SELECT ok((SELECT count(*) FROM waterway.gauge_measurements + WHERE id IN(SELECT key + FROM import.track_imports + WHERE import_id = :imp_id + 1) + ) = (SELECT count(*) + FROM import.track_imports WHERE import_id = :imp_id + 1), + 'Tracked entries of second test import still exist');
--- a/schema/run_tests.sh Thu Aug 01 16:46:13 2019 +0200 +++ b/schema/run_tests.sh Thu Aug 01 17:02:09 2019 +0200 @@ -80,7 +80,7 @@ -c 'SET client_min_messages TO WARNING' \ -c "DROP ROLE IF EXISTS $TEST_ROLES" \ -f "$BASEDIR"/tap_tests_data.sql \ - -c "SELECT plan(70 + ( + -c "SELECT plan(73 + ( SELECT count(*)::int FROM information_schema.tables WHERE table_schema = 'waterway'))" \ @@ -88,4 +88,5 @@ -f "$BASEDIR"/isrs_tests.sql \ -f "$BASEDIR"/auth_tests.sql \ -f "$BASEDIR"/manage_users_tests.sql \ + -f "$BASEDIR"/import_tests.sql \ -c 'SELECT * FROM finish()'
--- a/schema/tap_tests_data.sql Thu Aug 01 16:46:13 2019 +0200 +++ b/schema/tap_tests_data.sql Thu Aug 01 17:02:09 2019 +0200 @@ -17,6 +17,8 @@ -- INSERT INTO countries VALUES ('AT'), ('RO'), ('DE'); +INSERT INTO language_codes VALUES ('DE'); +INSERT INTO depth_references VALUES ('ZPG'); INSERT INTO users.responsibility_areas VALUES ('AT', ST_geomfromtext('MULTIPOLYGON(((0 0, 0 1, 1 1, 1 0, 0 0)))', 4326)),
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/updates/1101/01.improve_del_import.sql Thu Aug 01 17:02:09 2019 +0200 @@ -0,0 +1,17 @@ +CREATE OR REPLACE FUNCTION import.del_import(imp_id int) RETURNS void AS +$$ +DECLARE + tmp RECORD; +BEGIN + FOR tmp IN + SELECT relation, array_agg(key) AS keys + FROM import.track_imports + WHERE import_id = imp_id AND NOT deletion + GROUP BY relation + LOOP + EXECUTE format('DELETE FROM %s WHERE id = ANY($1)', tmp.relation) + USING tmp.keys; + END LOOP; +END; +$$ +LANGUAGE plpgsql;