SQL: Don't DROP the temporary edit_* tables

Simply re-using them by truncating the tables first is faster, and
requires less locking when performing batch edits in one transaction.

1 files changed, 52 insertions, 39 deletions

diff --git a/util/sql/func.sql b/util/sql/func.sql
index ff38f02d..d87450a6 100644
--- a/util/sql/func.sql
+++ b/util/sql/func.sql
@@ -119,13 +119,17 @@ $$ LANGUAGE plpgsql;
 -- create temporary table for generic revision info
 CREATE OR REPLACE FUNCTION edit_revtable(t dbentry_type, i integer) RETURNS void AS $$
 BEGIN
-  CREATE TEMPORARY TABLE edit_revision (
-    type dbentry_type NOT NULL,
-    iid integer,
-    requester integer,
-    ip inet,
-    comments text
-  );
+  BEGIN
+    CREATE TEMPORARY TABLE edit_revision (
+      type dbentry_type NOT NULL,
+      iid integer,
+      requester integer,
+      ip inet,
+      comments text
+    );
+  EXCEPTION WHEN duplicate_table THEN
+    TRUNCATE edit_revision;
+  END;
   INSERT INTO edit_revision (type, iid) VALUES (t, i);
 END;
 $$ LANGUAGE plpgsql;
@@ -178,16 +182,20 @@ $$ LANGUAGE plpgsql;
 CREATE OR REPLACE FUNCTION edit_vn_init(cid integer) RETURNS void AS $$
 BEGIN
   -- create tables, based on existing tables (so that the column types are always synchronised)
-  CREATE TEMPORARY TABLE edit_vn (LIKE vn_rev INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
-  ALTER TABLE edit_vn DROP COLUMN id;
-  ALTER TABLE edit_vn DROP COLUMN vid;
-  CREATE TEMPORARY TABLE edit_vn_anime (LIKE vn_anime INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
-  ALTER TABLE edit_vn_anime DROP COLUMN vid;
-  CREATE TEMPORARY TABLE edit_vn_relations (LIKE vn_relations INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
-  ALTER TABLE edit_vn_relations DROP COLUMN vid1;
-  ALTER TABLE edit_vn_relations RENAME COLUMN vid2 TO vid;
-  CREATE TEMPORARY TABLE edit_vn_screenshots (LIKE vn_screenshots INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
-  ALTER TABLE edit_vn_screenshots DROP COLUMN vid;
+  BEGIN
+    CREATE TEMPORARY TABLE edit_vn (LIKE vn_rev INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
+    ALTER TABLE edit_vn DROP COLUMN id;
+    ALTER TABLE edit_vn DROP COLUMN vid;
+    CREATE TEMPORARY TABLE edit_vn_anime (LIKE vn_anime INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
+    ALTER TABLE edit_vn_anime DROP COLUMN vid;
+    CREATE TEMPORARY TABLE edit_vn_relations (LIKE vn_relations INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
+    ALTER TABLE edit_vn_relations DROP COLUMN vid1;
+    ALTER TABLE edit_vn_relations RENAME COLUMN vid2 TO vid;
+    CREATE TEMPORARY TABLE edit_vn_screenshots (LIKE vn_screenshots INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
+    ALTER TABLE edit_vn_screenshots DROP COLUMN vid;
+  EXCEPTION WHEN duplicate_table THEN
+    TRUNCATE edit_vn, edit_vn_anime, edit_vn_relations, edit_vn_screenshots;
+  END;
   -- new VN, load defaults
   IF cid IS NULL THEN
     PERFORM edit_revtable('v', NULL);
@@ -218,7 +226,6 @@ BEGIN
   INSERT INTO vn_relations SELECT r.cid, vid, relation FROM edit_vn_relations;
   INSERT INTO vn_screenshots SELECT r.cid, scr, nsfw, rid FROM edit_vn_screenshots;
   UPDATE vn SET latest = r.cid WHERE id = r.iid;
-  DROP TABLE edit_revision, edit_vn, edit_vn_anime, edit_vn_relations, edit_vn_screenshots;
   RETURN r;
 END;
 $$ LANGUAGE plpgsql;
@@ -228,19 +235,23 @@ $$ LANGUAGE plpgsql;
 CREATE OR REPLACE FUNCTION edit_release_init(cid integer) RETURNS void AS $$
 BEGIN
   -- temp. tables
-  CREATE TEMPORARY TABLE edit_release (LIKE releases_rev INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
-  ALTER TABLE edit_release DROP COLUMN id;
-  ALTER TABLE edit_release DROP COLUMN rid;
-  CREATE TEMPORARY TABLE edit_release_lang (LIKE releases_lang INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
-  ALTER TABLE edit_release_lang DROP COLUMN rid;
-  CREATE TEMPORARY TABLE edit_release_media (LIKE releases_media INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
-  ALTER TABLE edit_release_media DROP COLUMN rid;
-  CREATE TEMPORARY TABLE edit_release_platforms (LIKE releases_platforms INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
-  ALTER TABLE edit_release_platforms DROP COLUMN rid;
-  CREATE TEMPORARY TABLE edit_release_producers (LIKE releases_producers INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
-  ALTER TABLE edit_release_producers DROP COLUMN rid;
-  CREATE TEMPORARY TABLE edit_release_vn (LIKE releases_vn INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
-  ALTER TABLE edit_release_vn DROP COLUMN rid;
+  BEGIN
+    CREATE TEMPORARY TABLE edit_release (LIKE releases_rev INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
+    ALTER TABLE edit_release DROP COLUMN id;
+    ALTER TABLE edit_release DROP COLUMN rid;
+    CREATE TEMPORARY TABLE edit_release_lang (LIKE releases_lang INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
+    ALTER TABLE edit_release_lang DROP COLUMN rid;
+    CREATE TEMPORARY TABLE edit_release_media (LIKE releases_media INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
+    ALTER TABLE edit_release_media DROP COLUMN rid;
+    CREATE TEMPORARY TABLE edit_release_platforms (LIKE releases_platforms INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
+    ALTER TABLE edit_release_platforms DROP COLUMN rid;
+    CREATE TEMPORARY TABLE edit_release_producers (LIKE releases_producers INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
+    ALTER TABLE edit_release_producers DROP COLUMN rid;
+    CREATE TEMPORARY TABLE edit_release_vn (LIKE releases_vn INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
+    ALTER TABLE edit_release_vn DROP COLUMN rid;
+  EXCEPTION WHEN duplicate_table THEN
+    TRUNCATE edit_release, edit_release_lang, edit_release_media, edit_release_platforms, edit_release_producers, edit_release_vn;
+  END;
   -- new release
   IF cid IS NULL THEN
     PERFORM edit_revtable('r', NULL);
@@ -277,7 +288,6 @@ BEGIN
   INSERT INTO releases_producers SELECT pid, r.cid, developer, publisher FROM edit_release_producers;
   INSERT INTO releases_vn SELECT r.cid, vid FROM edit_release_vn;
   UPDATE releases SET latest = r.cid WHERE id = r.iid;
-  DROP TABLE edit_revision, edit_release, edit_release_lang, edit_release_media, edit_release_platforms, edit_release_producers, edit_release_vn;
   RETURN r;
 END;
 $$ LANGUAGE plpgsql;
@@ -286,12 +296,16 @@ $$ LANGUAGE plpgsql;
 
 CREATE OR REPLACE FUNCTION edit_producer_init(cid integer) RETURNS void AS $$
 BEGIN
-  CREATE TEMPORARY TABLE edit_producer (LIKE producers_rev INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
-  ALTER TABLE edit_producer DROP COLUMN id;
-  ALTER TABLE edit_producer DROP COLUMN pid;
-  CREATE TEMPORARY TABLE edit_producer_relations (LIKE producers_relations INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
-  ALTER TABLE edit_producer_relations DROP COLUMN pid1;
-  ALTER TABLE edit_producer_relations RENAME COLUMN pid2 TO pid;
+  BEGIN
+    CREATE TEMPORARY TABLE edit_producer (LIKE producers_rev INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
+    ALTER TABLE edit_producer DROP COLUMN id;
+    ALTER TABLE edit_producer DROP COLUMN pid;
+    CREATE TEMPORARY TABLE edit_producer_relations (LIKE producers_relations INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
+    ALTER TABLE edit_producer_relations DROP COLUMN pid1;
+    ALTER TABLE edit_producer_relations RENAME COLUMN pid2 TO pid;
+  EXCEPTION WHEN duplicate_table THEN
+    TRUNCATE edit_producer, edit_producer_relations;
+  END;
   -- new producer
   IF cid IS NULL THEN
     PERFORM edit_revtable('p', NULL);
@@ -318,7 +332,6 @@ BEGIN
   INSERT INTO producers_rev SELECT r.cid, r.iid, type, name, original, website, lang, "desc", alias, l_wp FROM edit_producer;
   INSERT INTO producers_relations SELECT r.cid, pid, relation FROM edit_producer_relations;
   UPDATE producers SET latest = r.cid WHERE id = r.iid;
-  DROP TABLE edit_revision, edit_producer, edit_producer_relations;
   RETURN r;
 END;
 $$ LANGUAGE plpgsql;