From 5e45211a64149b3c659b90ff2de6fa982a5a93ed Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Sat, 4 May 2024 14:17:33 +0200 Subject: Adding upstream version 15.5. Signed-off-by: Daniel Baumann --- contrib/seg/sql/security.sql | 32 ++++++ contrib/seg/sql/seg.sql | 240 +++++++++++++++++++++++++++++++++++++++++++ 2 files changed, 272 insertions(+) create mode 100644 contrib/seg/sql/security.sql create mode 100644 contrib/seg/sql/seg.sql (limited to 'contrib/seg/sql') diff --git a/contrib/seg/sql/security.sql b/contrib/seg/sql/security.sql new file mode 100644 index 0000000..7dfbbaa --- /dev/null +++ b/contrib/seg/sql/security.sql @@ -0,0 +1,32 @@ +-- +-- Test extension script protection against search path overriding +-- + +CREATE ROLE regress_seg_role; +SELECT current_database() AS datname \gset +GRANT CREATE ON DATABASE :"datname" TO regress_seg_role; +SET ROLE regress_seg_role; +CREATE SCHEMA regress_seg_schema; + +CREATE FUNCTION regress_seg_schema.exfun(i int) RETURNS int AS $$ +BEGIN + CREATE EXTENSION seg VERSION '1.2'; + + CREATE FUNCTION regress_seg_schema.compare(oid, regclass) RETURNS boolean AS + 'BEGIN RAISE EXCEPTION ''overloaded compare() called by %'', current_user; END;' LANGUAGE plpgsql; + + CREATE OPERATOR = (LEFTARG = oid, RIGHTARG = regclass, PROCEDURE = regress_seg_schema.compare); + + ALTER EXTENSION seg UPDATE TO '1.3'; + + RETURN i; +END; $$ LANGUAGE plpgsql; + +CREATE SCHEMA test_schema +CREATE TABLE t(i int) PARTITION BY RANGE (i) +CREATE TABLE p1 PARTITION OF t FOR VALUES FROM (1) TO (regress_seg_schema.exfun(2)); + +DROP SCHEMA test_schema CASCADE; +RESET ROLE; +DROP OWNED BY regress_seg_role; +DROP ROLE regress_seg_role; diff --git a/contrib/seg/sql/seg.sql b/contrib/seg/sql/seg.sql new file mode 100644 index 0000000..a027d4d --- /dev/null +++ b/contrib/seg/sql/seg.sql @@ -0,0 +1,240 @@ +-- +-- Test seg datatype +-- + +CREATE EXTENSION seg; + +-- Check whether any of our opclasses fail amvalidate +SELECT amname, opcname +FROM pg_opclass opc LEFT JOIN pg_am am ON am.oid = opcmethod +WHERE opc.oid >= 16384 AND NOT amvalidate(opc.oid); + +-- +-- testing the input and output functions +-- + +-- Any number +SELECT '1'::seg AS seg; +SELECT '-1'::seg AS seg; +SELECT '1.0'::seg AS seg; +SELECT '-1.0'::seg AS seg; +SELECT '1e7'::seg AS seg; +SELECT '-1e7'::seg AS seg; +SELECT '1.0e7'::seg AS seg; +SELECT '-1.0e7'::seg AS seg; +SELECT '1e+7'::seg AS seg; +SELECT '-1e+7'::seg AS seg; +SELECT '1.0e+7'::seg AS seg; +SELECT '-1.0e+7'::seg AS seg; +SELECT '1e-7'::seg AS seg; +SELECT '-1e-7'::seg AS seg; +SELECT '1.0e-7'::seg AS seg; +SELECT '-1.0e-7'::seg AS seg; +SELECT '2e-6'::seg AS seg; +SELECT '2e-5'::seg AS seg; +SELECT '2e-4'::seg AS seg; +SELECT '2e-3'::seg AS seg; +SELECT '2e-2'::seg AS seg; +SELECT '2e-1'::seg AS seg; +SELECT '2e-0'::seg AS seg; +SELECT '2e+0'::seg AS seg; +SELECT '2e+1'::seg AS seg; +SELECT '2e+2'::seg AS seg; +SELECT '2e+3'::seg AS seg; +SELECT '2e+4'::seg AS seg; +SELECT '2e+5'::seg AS seg; +SELECT '2e+6'::seg AS seg; + + +-- Significant digits preserved +SELECT '1'::seg AS seg; +SELECT '1.0'::seg AS seg; +SELECT '1.00'::seg AS seg; +SELECT '1.000'::seg AS seg; +SELECT '1.0000'::seg AS seg; +SELECT '1.00000'::seg AS seg; +SELECT '1.000000'::seg AS seg; +SELECT '0.000000120'::seg AS seg; +SELECT '3.400e5'::seg AS seg; + +-- Digits truncated +SELECT '12.34567890123456'::seg AS seg; + +-- Same, with a very long input +SELECT '12.3456789012345600000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000'::seg AS seg; + +-- Numbers with certainty indicators +SELECT '~6.5'::seg AS seg; +SELECT '<6.5'::seg AS seg; +SELECT '>6.5'::seg AS seg; +SELECT '~ 6.5'::seg AS seg; +SELECT '< 6.5'::seg AS seg; +SELECT '> 6.5'::seg AS seg; + +-- Open intervals +SELECT '0..'::seg AS seg; +SELECT '0...'::seg AS seg; +SELECT '0 ..'::seg AS seg; +SELECT '0 ...'::seg AS seg; +SELECT '..0'::seg AS seg; +SELECT '...0'::seg AS seg; +SELECT '.. 0'::seg AS seg; +SELECT '... 0'::seg AS seg; + +-- Finite intervals +SELECT '0 .. 1'::seg AS seg; +SELECT '-1 .. 0'::seg AS seg; +SELECT '-1 .. 1'::seg AS seg; + +-- (+/-) intervals +SELECT '0(+-)1'::seg AS seg; +SELECT '0(+-)1.0'::seg AS seg; +SELECT '1.0(+-)0.005'::seg AS seg; +SELECT '101(+-)1'::seg AS seg; +-- incorrect number of significant digits in 99.0: +SELECT '100(+-)1'::seg AS seg; + +-- invalid input +SELECT ''::seg AS seg; +SELECT 'ABC'::seg AS seg; +SELECT '1ABC'::seg AS seg; +SELECT '1.'::seg AS seg; +SELECT '1.....'::seg AS seg; +SELECT '.1'::seg AS seg; +SELECT '1..2.'::seg AS seg; +SELECT '1 e7'::seg AS seg; +SELECT '1e700'::seg AS seg; + +-- +-- testing the operators +-- + +-- equality/inequality: +-- +SELECT '24 .. 33.20'::seg = '24 .. 33.20'::seg AS bool; +SELECT '24 .. 33.20'::seg = '24 .. 33.21'::seg AS bool; +SELECT '24 .. 33.20'::seg != '24 .. 33.20'::seg AS bool; +SELECT '24 .. 33.20'::seg != '24 .. 33.21'::seg AS bool; + +-- overlap +-- +SELECT '1'::seg && '1'::seg AS bool; +SELECT '1'::seg && '2'::seg AS bool; +SELECT '0 ..'::seg && '0 ..'::seg AS bool; +SELECT '0 .. 1'::seg && '0 .. 1'::seg AS bool; +SELECT '..0'::seg && '0..'::seg AS bool; +SELECT '-1 .. 0.1'::seg && '0 .. 1'::seg AS bool; +SELECT '-1 .. 0'::seg && '0 .. 1'::seg AS bool; +SELECT '-1 .. -0.0001'::seg && '0 .. 1'::seg AS bool; +SELECT '0 ..'::seg && '1'::seg AS bool; +SELECT '0 .. 1'::seg && '1'::seg AS bool; +SELECT '0 .. 1'::seg && '2'::seg AS bool; +SELECT '0 .. 2'::seg && '1'::seg AS bool; +SELECT '1'::seg && '0 .. 1'::seg AS bool; +SELECT '2'::seg && '0 .. 1'::seg AS bool; +SELECT '1'::seg && '0 .. 2'::seg AS bool; + +-- overlap on the left +-- +SELECT '1'::seg &< '0'::seg AS bool; +SELECT '1'::seg &< '1'::seg AS bool; +SELECT '1'::seg &< '2'::seg AS bool; +SELECT '0 .. 1'::seg &< '0'::seg AS bool; +SELECT '0 .. 1'::seg &< '1'::seg AS bool; +SELECT '0 .. 1'::seg &< '2'::seg AS bool; +SELECT '0 .. 1'::seg &< '0 .. 0.5'::seg AS bool; +SELECT '0 .. 1'::seg &< '0 .. 1'::seg AS bool; +SELECT '0 .. 1'::seg &< '0 .. 2'::seg AS bool; +SELECT '0 .. 1'::seg &< '1 .. 2'::seg AS bool; +SELECT '0 .. 1'::seg &< '2 .. 3'::seg AS bool; + +-- overlap on the right +-- +SELECT '0'::seg &> '1'::seg AS bool; +SELECT '1'::seg &> '1'::seg AS bool; +SELECT '2'::seg &> '1'::seg AS bool; +SELECT '0'::seg &> '0 .. 1'::seg AS bool; +SELECT '1'::seg &> '0 .. 1'::seg AS bool; +SELECT '2'::seg &> '0 .. 1'::seg AS bool; +SELECT '0 .. 0.5'::seg &> '0 .. 1'::seg AS bool; +SELECT '0 .. 1'::seg &> '0 .. 1'::seg AS bool; +SELECT '0 .. 2'::seg &> '0 .. 2'::seg AS bool; +SELECT '1 .. 2'::seg &> '0 .. 1'::seg AS bool; +SELECT '2 .. 3'::seg &> '0 .. 1'::seg AS bool; + +-- left +-- +SELECT '1'::seg << '0'::seg AS bool; +SELECT '1'::seg << '1'::seg AS bool; +SELECT '1'::seg << '2'::seg AS bool; +SELECT '0 .. 1'::seg << '0'::seg AS bool; +SELECT '0 .. 1'::seg << '1'::seg AS bool; +SELECT '0 .. 1'::seg << '2'::seg AS bool; +SELECT '0 .. 1'::seg << '0 .. 0.5'::seg AS bool; +SELECT '0 .. 1'::seg << '0 .. 1'::seg AS bool; +SELECT '0 .. 1'::seg << '0 .. 2'::seg AS bool; +SELECT '0 .. 1'::seg << '1 .. 2'::seg AS bool; +SELECT '0 .. 1'::seg << '2 .. 3'::seg AS bool; + +-- right +-- +SELECT '0'::seg >> '1'::seg AS bool; +SELECT '1'::seg >> '1'::seg AS bool; +SELECT '2'::seg >> '1'::seg AS bool; +SELECT '0'::seg >> '0 .. 1'::seg AS bool; +SELECT '1'::seg >> '0 .. 1'::seg AS bool; +SELECT '2'::seg >> '0 .. 1'::seg AS bool; +SELECT '0 .. 0.5'::seg >> '0 .. 1'::seg AS bool; +SELECT '0 .. 1'::seg >> '0 .. 1'::seg AS bool; +SELECT '0 .. 2'::seg >> '0 .. 2'::seg AS bool; +SELECT '1 .. 2'::seg >> '0 .. 1'::seg AS bool; +SELECT '2 .. 3'::seg >> '0 .. 1'::seg AS bool; + + +-- "contained in" (the left value belongs within the interval specified in the right value): +-- +SELECT '0'::seg <@ '0'::seg AS bool; +SELECT '0'::seg <@ '0 ..'::seg AS bool; +SELECT '0'::seg <@ '.. 0'::seg AS bool; +SELECT '0'::seg <@ '-1 .. 1'::seg AS bool; +SELECT '0'::seg <@ '-1 .. 1'::seg AS bool; +SELECT '-1'::seg <@ '-1 .. 1'::seg AS bool; +SELECT '1'::seg <@ '-1 .. 1'::seg AS bool; +SELECT '-1 .. 1'::seg <@ '-1 .. 1'::seg AS bool; + +-- "contains" (the left value contains the interval specified in the right value): +-- +SELECT '0'::seg @> '0'::seg AS bool; +SELECT '0 .. '::seg <@ '0'::seg AS bool; +SELECT '.. 0'::seg <@ '0'::seg AS bool; +SELECT '-1 .. 1'::seg <@ '0'::seg AS bool; +SELECT '0'::seg <@ '-1 .. 1'::seg AS bool; +SELECT '-1'::seg <@ '-1 .. 1'::seg AS bool; +SELECT '1'::seg <@ '-1 .. 1'::seg AS bool; + +-- Load some example data and build the index +-- +CREATE TABLE test_seg (s seg); + +\copy test_seg from 'data/test_seg.data' + +CREATE INDEX test_seg_ix ON test_seg USING gist (s); + +SET enable_indexscan = false; +EXPLAIN (COSTS OFF) +SELECT count(*) FROM test_seg WHERE s @> '11..11.3'; +SELECT count(*) FROM test_seg WHERE s @> '11..11.3'; +RESET enable_indexscan; + +SET enable_bitmapscan = false; +EXPLAIN (COSTS OFF) +SELECT count(*) FROM test_seg WHERE s @> '11..11.3'; +SELECT count(*) FROM test_seg WHERE s @> '11..11.3'; +RESET enable_bitmapscan; + +-- Test sorting +SELECT * FROM test_seg WHERE s @> '11..11.3' GROUP BY s; + +-- Test functions +SELECT seg_lower(s), seg_center(s), seg_upper(s) +FROM test_seg WHERE s @> '11.2..11.3' OR s IS NULL ORDER BY s; -- cgit v1.2.3