Mercurial > gemma

annotate schema/updates/1105/01.improve_rls_performance.sql @ 4161:64cd18281c76

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Improve performance of row level security policies Using constraint_column_usage instead of key_column_usage makes the query twice as fast. I did not explore why. Let's just take it. Using 'EXISTS(... WHERE ... = value) is often more efficient than value IN(...) since it allows the inner query to be executed only up to the point where it turns out to return more than nothing with filtering directly in place.
author Tom Gottfried <tom@intevation.de>
date Fri, 02 Aug 2019 17:14:13 +0200
parents
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
4161
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1 CREATE OR REPLACE FUNCTION import.is_new_key(
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
2 tablename varchar,
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
3 kv anyelement)
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
4 RETURNS boolean
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
5 AS $$
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
6 DECLARE columnname varchar;
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
7 DECLARE ret boolean;
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
8 BEGIN
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
9 columnname = (SELECT column_name
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
10 FROM information_schema.constraint_column_usage k
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
11 JOIN information_schema.table_constraints USING (constraint_name)
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
12 WHERE k.table_name = tablename and constraint_type = 'PRIMARY KEY');
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
13 EXECUTE format('SELECT NOT EXISTS(SELECT 1 FROM import.%I WHERE %I = $1)',
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
14 tablename, columnname)
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
15 INTO ret
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
16 USING kv;
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
17 RETURN ret;
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
18 END;
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
19 $$
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
20 LANGUAGE plpgsql
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
21 SECURITY DEFINER
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
22 STABLE PARALLEL SAFE;
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
23
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
24 DROP POLICY parent_allowed ON import.import_logs;
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
25 CREATE POLICY parent_allowed ON import.import_logs
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
26 FOR ALL TO waterway_admin
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
27 USING (EXISTS(SELECT 1 FROM import.imports WHERE id = import_id))
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
28 WITH CHECK (import.is_new_key('imports', import_id)
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
29 OR EXISTS(SELECT 1 FROM import.imports WHERE id = import_id));
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
30
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
31 DROP POLICY parent_allowed ON import.track_imports;
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
32 CREATE POLICY parent_allowed ON import.track_imports
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
33 FOR ALL TO waterway_admin
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
34 USING (EXISTS(SELECT 1 FROM import.imports WHERE id = import_id))
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
35 WITH CHECK (import.is_new_key('imports', import_id)
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
36 OR EXISTS(SELECT 1 FROM import.imports WHERE id = import_id));
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
37
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
38 DROP POLICY import_configuration_policy ON import.import_configuration;
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
39 CREATE POLICY import_configuration_policy ON import.import_configuration
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
40 FOR ALL TO waterway_admin
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
41 USING (EXISTS(SELECT 1 FROM users.list_users lu
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
42 WHERE lu.username = import_configuration.username));
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
43
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
44 DROP POLICY parent_allowed ON import.import_configuration_attributes;
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
45 CREATE POLICY parent_allowed ON import.import_configuration_attributes
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
46 FOR ALL TO waterway_admin
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
47 USING (EXISTS(SELECT 1 FROM import.import_configuration
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
48 WHERE id = import_configuration_id))
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
49 WITH CHECK (
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
50 import.is_new_key('import_configuration', import_configuration_id)
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
51 OR EXISTS(SELECT 1 FROM import.import_configuration
64cd18281c76 Improve performance of row level security policies
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
52 WHERE id = import_configuration_id));