Mercurial > gemma
view schema/updates/1104/01.remove_function.sql @ 4158:5466562cca60
Remove utility function with possibly bad performance impact
Since the PostgreSQL planner will call functions used in a filter
condition once per row, even if the function is marked STABLE,
under some circumstances (e.g. the removed usage in an RLS policy),
remove the function from the database completely.
The DEFAULT on users.templates that used the function is unused,
thus remove it as a whole, too.
Recreate the function as a helper for tests in order to minimize
necessary changes there.
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Fri, 02 Aug 2019 16:10:42 +0200 |
parents | |
children |
line wrap: on
line source
DROP POLICY same_country ON import.imports; CREATE POLICY same_country ON import.imports FOR ALL TO waterway_admin USING (EXISTS(SELECT 1 FROM users.list_users lu WHERE lu.username = imports.username)); ALTER TABLE users.templates ALTER COLUMN country DROP DEFAULT; CREATE OR REPLACE FUNCTION users.current_user_area_utm() RETURNS geometry AS $$ DECLARE utm_area geometry; BEGIN SELECT ST_Transform(area::geometry, best_utm(area)) INTO STRICT utm_area FROM users.responsibility_areas WHERE country = (SELECT country FROM users.list_users WHERE username = current_user); RETURN utm_area; END; $$ LANGUAGE plpgsql STABLE PARALLEL SAFE; DROP FUNCTION users.current_user_country();