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();