Mercurial > gemma
changeset 4431:8fcabb6f971e
Fix operator support functions
Operators used in an operator class should not be based on SQL functions,
which might be inlined, thus preventing index usage based on the
operator.
Schema qualify functions called inside functions to make the outer
function work independendly from search_path setting. This makes
it possible to use the operators using the 'OPERATOR(<schema>.<opname>)'
syntax. Most importantly, it fixes the usage of isrs_diff() during
autovacuum.
| author | Tom Gottfried <tom@intevation.de> |
|---|---|
| date | Wed, 18 Sep 2019 17:26:28 +0200 |
| parents | 86c89824aab4 |
| children | aaab0ebde623 |
| files | schema/isrs.sql schema/isrs_tests.sql schema/run_tests.sh schema/updates/1201/01.fix_support_functions.sql schema/version.sql |
| diffstat | 5 files changed, 77 insertions(+), 13 deletions(-) [+] |
line wrap: on
line diff
--- a/schema/isrs.sql Wed Sep 18 16:36:06 2019 +0200 +++ b/schema/isrs.sql Wed Sep 18 17:26:28 2019 +0200 @@ -58,32 +58,42 @@ CREATE FUNCTION isrslt(a isrs, b isrs) RETURNS boolean AS $$ - SELECT isrs_cmp(a, b) < 0 -$$ LANGUAGE sql +BEGIN + RETURN public.isrs_cmp(a, b) < 0; +END; +$$ LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE; CREATE FUNCTION isrsle(a isrs, b isrs) RETURNS boolean AS $$ - SELECT isrs_cmp(a, b) <= 0 -$$ LANGUAGE sql +BEGIN + RETURN public.isrs_cmp(a, b) <= 0; +END; +$$ LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE; CREATE FUNCTION isrseq(a isrs, b isrs) RETURNS boolean AS $$ - SELECT isrs_cmp(a, b) = 0 -$$ LANGUAGE sql +BEGIN + RETURN public.isrs_cmp(a, b) = 0; +END; +$$ LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE; CREATE FUNCTION isrsge(a isrs, b isrs) RETURNS boolean AS $$ - SELECT isrs_cmp(a, b) >= 0 -$$ LANGUAGE sql +BEGIN + RETURN public.isrs_cmp(a, b) >= 0; +END; +$$ LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE; CREATE FUNCTION isrsgt(a isrs, b isrs) RETURNS boolean AS $$ - SELECT isrs_cmp(a, b) > 0 -$$ LANGUAGE sql +BEGIN + RETURN public.isrs_cmp(a, b) > 0; +END; +$$ LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE; CREATE OPERATOR <~ ( @@ -131,7 +141,7 @@ CREATE FUNCTION isrs_diff(a isrs, b isrs) RETURNS double precision AS $$ - SELECT CAST(isrs_cmp(a, b) AS double precision) + SELECT CAST(public.isrs_cmp(a, b) AS double precision) $$ LANGUAGE sql IMMUTABLE PARALLEL SAFE;
--- a/schema/isrs_tests.sql Wed Sep 18 16:36:06 2019 +0200 +++ b/schema/isrs_tests.sql Wed Sep 18 17:26:28 2019 +0200 @@ -81,6 +81,15 @@ isrs_fromText('ATXXX000000000006570')), 'isrsrange: Overlap depends on hectometre'); +SELECT lives_ok($$ + SET search_path TO ''; + SELECT public.isrs_diff( + CAST('(AT,XXX,00000,00000,0)' as public.isrs), + CAST('(AT,XXX,00000,00000,1)' as public.isrs)); + RESET search_path; + $$, + 'Support function runs with empty search path (as during autovacuum)'); + -- -- Geometry processing --
--- a/schema/run_tests.sh Wed Sep 18 16:36:06 2019 +0200 +++ b/schema/run_tests.sh Wed Sep 18 17:26:28 2019 +0200 @@ -80,7 +80,7 @@ -c 'SET client_min_messages TO WARNING' \ -c "DROP ROLE IF EXISTS $TEST_ROLES" \ -f "$BASEDIR"/tap_tests_data.sql \ - -c "SELECT plan(74 + ( + -c "SELECT plan(75 + ( SELECT count(*)::int FROM information_schema.tables WHERE table_schema = 'waterway'))" \
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/updates/1201/01.fix_support_functions.sql Wed Sep 18 17:26:28 2019 +0200 @@ -0,0 +1,45 @@ +CREATE OR REPLACE FUNCTION isrslt(a isrs, b isrs) RETURNS boolean +AS $$ +BEGIN + RETURN public.isrs_cmp(a, b) < 0; +END; +$$ LANGUAGE plpgsql + IMMUTABLE PARALLEL SAFE; + +CREATE OR REPLACE FUNCTION isrsle(a isrs, b isrs) RETURNS boolean +AS $$ +BEGIN + RETURN public.isrs_cmp(a, b) <= 0; +END; +$$ LANGUAGE plpgsql + IMMUTABLE PARALLEL SAFE; + +CREATE OR REPLACE FUNCTION isrseq(a isrs, b isrs) RETURNS boolean +AS $$ +BEGIN + RETURN public.isrs_cmp(a, b) = 0; +END; +$$ LANGUAGE plpgsql + IMMUTABLE PARALLEL SAFE; + +CREATE OR REPLACE FUNCTION isrsge(a isrs, b isrs) RETURNS boolean +AS $$ +BEGIN + RETURN public.isrs_cmp(a, b) >= 0; +END; +$$ LANGUAGE plpgsql + IMMUTABLE PARALLEL SAFE; + +CREATE OR REPLACE FUNCTION isrsgt(a isrs, b isrs) RETURNS boolean +AS $$ +BEGIN + RETURN public.isrs_cmp(a, b) > 0; +END; +$$ LANGUAGE plpgsql + IMMUTABLE PARALLEL SAFE; + +CREATE OR REPLACE FUNCTION isrs_diff(a isrs, b isrs) RETURNS double precision +AS $$ + SELECT CAST(public.isrs_cmp(a, b) AS double precision) +$$ LANGUAGE sql + IMMUTABLE PARALLEL SAFE;