18943: Migrate created_at indexes to (created_at, uuid)

[arvados.git] / services / api / db / structure.sql

diff --git a/services/api/db/structure.sql b/services/api/db/structure.sql
index da9959593c0a28853831d3cc6bd464be28bdbe07..d874bce9405a6b2311d91642199801d008500a18 100644 (file)
--- a/services/api/db/structure.sql
+++ b/services/api/db/structure.sql
@@ -190,6 +190,24 @@ case (edges.edge_id = perm_edge_id)
 $$;
 
 
+--
+-- Name: project_subtree_with_is_frozen(character varying, boolean); Type: FUNCTION; Schema: public; Owner: -
+--
+
+CREATE FUNCTION public.project_subtree_with_is_frozen(starting_uuid character varying, starting_is_frozen boolean) RETURNS TABLE(uuid character varying, is_frozen boolean)
+    LANGUAGE sql STABLE
+    AS $$
+WITH RECURSIVE
+  project_subtree(uuid, is_frozen) as (
+    values (starting_uuid, starting_is_frozen)
+    union
+    select groups.uuid, project_subtree.is_frozen or groups.frozen_by_uuid is not null
+      from groups join project_subtree on (groups.owner_uuid = project_subtree.uuid)
+  )
+  select uuid, is_frozen from project_subtree;
+$$;
+
+
 --
 -- Name: project_subtree_with_trash_at(character varying, timestamp without time zone); Type: FUNCTION; Schema: public; Owner: -
 --
@@ -548,6 +566,15 @@ CREATE SEQUENCE public.containers_id_seq
 ALTER SEQUENCE public.containers_id_seq OWNED BY public.containers.id;
 
 
+--
+-- Name: frozen_groups; Type: TABLE; Schema: public; Owner: -
+--
+
+CREATE TABLE public.frozen_groups (
+    uuid character varying
+);
+
+
 --
 -- Name: groups; Type: TABLE; Schema: public; Owner: -
 --
@@ -567,7 +594,8 @@ CREATE TABLE public.groups (
     trash_at timestamp without time zone,
     is_trashed boolean DEFAULT false NOT NULL,
     delete_at timestamp without time zone,
-    properties jsonb DEFAULT '{}'::jsonb
+    properties jsonb DEFAULT '{}'::jsonb,
+    frozen_by_uuid character varying
 );
 
 
@@ -1775,7 +1803,7 @@ CREATE INDEX group_index_on_properties ON public.groups USING gin (properties);
 -- Name: groups_search_index; Type: INDEX; Schema: public; Owner: -
 --
 
-CREATE INDEX groups_search_index ON public.groups USING btree (uuid, owner_uuid, modified_by_client_uuid, modified_by_user_uuid, name, group_class);
+CREATE INDEX groups_search_index ON public.groups USING btree (uuid, owner_uuid, modified_by_client_uuid, modified_by_user_uuid, name, group_class, frozen_by_uuid);
 
 
 --
@@ -1877,10 +1905,10 @@ CREATE UNIQUE INDEX index_authorized_keys_on_uuid ON public.authorized_keys USIN
 
 
 --
--- Name: index_collections_on_created_at; Type: INDEX; Schema: public; Owner: -
+-- Name: index_collections_on_created_at_and_uuid; Type: INDEX; Schema: public; Owner: -
 --
 
-CREATE INDEX index_collections_on_created_at ON public.collections USING btree (created_at);
+CREATE INDEX index_collections_on_created_at_and_uuid ON public.collections USING btree (created_at, uuid);
 
 
 --
@@ -2059,10 +2087,17 @@ CREATE UNIQUE INDEX index_containers_on_uuid ON public.containers USING btree (u
 
 
 --
--- Name: index_groups_on_created_at; Type: INDEX; Schema: public; Owner: -
+-- Name: index_frozen_groups_on_uuid; Type: INDEX; Schema: public; Owner: -
+--
+
+CREATE UNIQUE INDEX index_frozen_groups_on_uuid ON public.frozen_groups USING btree (uuid);
+
+
+--
+-- Name: index_groups_on_created_at_and_uuid; Type: INDEX; Schema: public; Owner: -
 --
 
-CREATE INDEX index_groups_on_created_at ON public.groups USING btree (created_at);
+CREATE INDEX index_groups_on_created_at_and_uuid ON public.groups USING btree (created_at, uuid);
 
 
 --
@@ -2325,10 +2360,10 @@ CREATE UNIQUE INDEX index_keep_services_on_uuid ON public.keep_services USING bt
 
 
 --
--- Name: index_links_on_created_at; Type: INDEX; Schema: public; Owner: -
+-- Name: index_links_on_created_at_and_uuid; Type: INDEX; Schema: public; Owner: -
 --
 
-CREATE INDEX index_links_on_created_at ON public.links USING btree (created_at);
+CREATE INDEX index_links_on_created_at_and_uuid ON public.links USING btree (created_at, uuid);
 
 
 --
@@ -2388,10 +2423,10 @@ CREATE UNIQUE INDEX index_links_on_uuid ON public.links USING btree (uuid);
 
 
 --
--- Name: index_logs_on_created_at; Type: INDEX; Schema: public; Owner: -
+-- Name: index_logs_on_created_at_and_uuid; Type: INDEX; Schema: public; Owner: -
 --
 
-CREATE INDEX index_logs_on_created_at ON public.logs USING btree (created_at);
+CREATE INDEX index_logs_on_created_at_and_uuid ON public.logs USING btree (created_at, uuid);
 
 
 --
@@ -2654,10 +2689,10 @@ CREATE UNIQUE INDEX index_trashed_groups_on_group_uuid ON public.trashed_groups
 
 
 --
--- Name: index_users_on_created_at; Type: INDEX; Schema: public; Owner: -
+-- Name: index_users_on_created_at_and_uuid; Type: INDEX; Schema: public; Owner: -
 --
 
-CREATE INDEX index_users_on_created_at ON public.users USING btree (created_at);
+CREATE INDEX index_users_on_created_at_and_uuid ON public.users USING btree (created_at, uuid);
 
 
 --
@@ -3147,6 +3182,10 @@ INSERT INTO "schema_migrations" (version) VALUES
 ('20210126183521'),
 ('20210621204455'),
 ('20210816191509'),
-('20211027154300');
+('20211027154300'),
+('20220224203102'),
+('20220301155729'),
+('20220303204419'),
+('20220401153101');