From 1253a2a0d531f55594109f1d548c56cd1b1fcb59 Mon Sep 17 00:00:00 2001
From: phiresky <phireskyde+git@gmail.com>
Date: Thu, 27 Jul 2023 23:35:53 +0200
Subject: [PATCH] remove performance-problematic and buggy duplicate site
 aggregates (#3732)

---
 .../down.sql                                      | 11 +++++++++++
 .../2023-07-26-222023_site-aggregates-one/up.sql  | 15 +++++++++++++++
 2 files changed, 26 insertions(+)
 create mode 100644 migrations/2023-07-26-222023_site-aggregates-one/down.sql
 create mode 100644 migrations/2023-07-26-222023_site-aggregates-one/up.sql

diff --git a/migrations/2023-07-26-222023_site-aggregates-one/down.sql b/migrations/2023-07-26-222023_site-aggregates-one/down.sql
new file mode 100644
index 00000000..f384785f
--- /dev/null
+++ b/migrations/2023-07-26-222023_site-aggregates-one/down.sql
@@ -0,0 +1,11 @@
+create or replace function site_aggregates_site()
+returns trigger language plpgsql
+as $$
+begin
+  IF (TG_OP = 'INSERT') THEN
+    insert into site_aggregates (site_id) values (NEW.id);
+  ELSIF (TG_OP = 'DELETE') THEN
+    delete from site_aggregates where site_id = OLD.id;
+  END IF;
+  return null;
+end $$;
\ No newline at end of file
diff --git a/migrations/2023-07-26-222023_site-aggregates-one/up.sql b/migrations/2023-07-26-222023_site-aggregates-one/up.sql
new file mode 100644
index 00000000..7ed40316
--- /dev/null
+++ b/migrations/2023-07-26-222023_site-aggregates-one/up.sql
@@ -0,0 +1,15 @@
+create or replace function site_aggregates_site()
+returns trigger language plpgsql
+as $$
+begin
+  -- we only ever want to have a single value in site_aggregate because the site_aggregate triggers update all rows in that table.
+  -- a cleaner check would be to insert it for the local_site but that would break assumptions at least in the tests
+  IF (TG_OP = 'INSERT') AND NOT EXISTS (select id from site_aggregates limit 1) THEN
+    insert into site_aggregates (site_id) values (NEW.id);
+  ELSIF (TG_OP = 'DELETE') THEN
+    delete from site_aggregates where site_id = OLD.id;
+  END IF;
+  return null;
+end $$;
+
+delete from site_aggregates a where not exists (select id from local_site s where s.site_id = a.site_id);
\ No newline at end of file
-- 
2.44.1