src/test/regress/expected/alter_operator.out - master - githubmirror/postgres

src/test/regress/expected/alter_operator.out
267 строк11 KB
Tom Lane
Extend ALTER OPERATOR to allow setting more optimization attributes.
20 окт 2023, 19:28
20 окт 2023, 19:282b5154b
CREATE FUNCTION alter_op_test_fn(boolean, boolean)
RETURNS boolean AS $$ SELECT NULL::BOOLEAN; $$ LANGUAGE sql IMMUTABLE;
CREATE FUNCTION customcontsel(internal, oid, internal, integer)
RETURNS float8 AS 'contsel' LANGUAGE internal STABLE STRICT;
CREATE OPERATOR === (
    LEFTARG = boolean,
    RIGHTARG = boolean,
    PROCEDURE = alter_op_test_fn,
    COMMUTATOR = ===,
    NEGATOR = !==,
    RESTRICT = customcontsel,
    JOIN = contjoinsel,
    HASHES, MERGES
);
SELECT pg_describe_object(refclassid,refobjid,refobjsubid) as ref, deptype
FROM pg_depend
WHERE classid = 'pg_operator'::regclass AND
      objid = '===(bool,bool)'::regoperator
ORDER BY 1;
                          ref                          | deptype 
-------------------------------------------------------+---------
 function alter_op_test_fn(boolean,boolean)            | n
 function customcontsel(internal,oid,internal,integer) | n
 schema public                                         | n
(3 rows)

--
-- Test resetting and setting restrict and join attributes.
--
ALTER OPERATOR === (boolean, boolean) SET (RESTRICT = NONE);
ALTER OPERATOR === (boolean, boolean) SET (JOIN = NONE);
SELECT oprrest, oprjoin FROM pg_operator WHERE oprname = '==='
  AND oprleft = 'boolean'::regtype AND oprright = 'boolean'::regtype;
 oprrest | oprjoin 
---------+---------
 -       | -
(1 row)

SELECT pg_describe_object(refclassid,refobjid,refobjsubid) as ref, deptype
FROM pg_depend
WHERE classid = 'pg_operator'::regclass AND
      objid = '===(bool,bool)'::regoperator
ORDER BY 1;
                    ref                     | deptype 
--------------------------------------------+---------
 function alter_op_test_fn(boolean,boolean) | n
 schema public                              | n
(2 rows)

ALTER OPERATOR === (boolean, boolean) SET (RESTRICT = contsel);
ALTER OPERATOR === (boolean, boolean) SET (JOIN = contjoinsel);
SELECT oprrest, oprjoin FROM pg_operator WHERE oprname = '==='
  AND oprleft = 'boolean'::regtype AND oprright = 'boolean'::regtype;
 oprrest |   oprjoin   
---------+-------------
 contsel | contjoinsel
(1 row)

SELECT pg_describe_object(refclassid,refobjid,refobjsubid) as ref, deptype
FROM pg_depend
WHERE classid = 'pg_operator'::regclass AND
      objid = '===(bool,bool)'::regoperator
ORDER BY 1;
                    ref                     | deptype 
--------------------------------------------+---------
 function alter_op_test_fn(boolean,boolean) | n
 schema public                              | n
(2 rows)

ALTER OPERATOR === (boolean, boolean) SET (RESTRICT = NONE, JOIN = NONE);
SELECT oprrest, oprjoin FROM pg_operator WHERE oprname = '==='
  AND oprleft = 'boolean'::regtype AND oprright = 'boolean'::regtype;
 oprrest | oprjoin 
---------+---------
 -       | -
(1 row)

SELECT pg_describe_object(refclassid,refobjid,refobjsubid) as ref, deptype
FROM pg_depend
WHERE classid = 'pg_operator'::regclass AND
      objid = '===(bool,bool)'::regoperator
ORDER BY 1;
                    ref                     | deptype 
--------------------------------------------+---------
 function alter_op_test_fn(boolean,boolean) | n
 schema public                              | n
(2 rows)

ALTER OPERATOR === (boolean, boolean) SET (RESTRICT = customcontsel, JOIN = contjoinsel);
SELECT oprrest, oprjoin FROM pg_operator WHERE oprname = '==='
  AND oprleft = 'boolean'::regtype AND oprright = 'boolean'::regtype;
    oprrest    |   oprjoin   
---------------+-------------
 customcontsel | contjoinsel
(1 row)

SELECT pg_describe_object(refclassid,refobjid,refobjsubid) as ref, deptype
FROM pg_depend
WHERE classid = 'pg_operator'::regclass AND
      objid = '===(bool,bool)'::regoperator
ORDER BY 1;
                          ref                          | deptype 
-------------------------------------------------------+---------
 function alter_op_test_fn(boolean,boolean)            | n
 function customcontsel(internal,oid,internal,integer) | n
 schema public                                         | n
(3 rows)

--
-- Test invalid options.
--
ALTER OPERATOR === (boolean, boolean) SET (RESTRICT = non_existent_func);
ERROR:  function non_existent_func(internal, oid, internal, integer) does not exist
ALTER OPERATOR === (boolean, boolean) SET (JOIN = non_existent_func);
ERROR:  function non_existent_func(internal, oid, internal, smallint, internal) does not exist
-- invalid: non-lowercase quoted identifiers
ALTER OPERATOR & (bit, bit) SET ("Restrict" = _int_contsel, "Join" = _int_contjoinsel);
ERROR:  operator attribute "Restrict" not recognized
--
-- Test permission check. Must be owner to ALTER OPERATOR.
--
CREATE USER regress_alter_op_user;
SET SESSION AUTHORIZATION regress_alter_op_user;
ALTER OPERATOR === (boolean, boolean) SET (RESTRICT = NONE);
ERROR:  must be owner of operator ===
RESET SESSION AUTHORIZATION;
--
-- Test setting commutator, negator, merges, and hashes attributes,
-- which can only be set if not already set
--
CREATE FUNCTION alter_op_test_fn_bool_real(boolean, real)
RETURNS boolean AS $$ SELECT NULL::BOOLEAN; $$ LANGUAGE sql IMMUTABLE;
CREATE FUNCTION alter_op_test_fn_real_bool(real, boolean)
RETURNS boolean AS $$ SELECT NULL::BOOLEAN; $$ LANGUAGE sql IMMUTABLE;
-- operator
CREATE OPERATOR === (
    LEFTARG = boolean,
    RIGHTARG = real,
    PROCEDURE = alter_op_test_fn_bool_real
);
-- commutator
CREATE OPERATOR ==== (
    LEFTARG = real,
    RIGHTARG = boolean,
    PROCEDURE = alter_op_test_fn_real_bool
);
-- negator
CREATE OPERATOR !==== (
    LEFTARG = boolean,
    RIGHTARG = real,
    PROCEDURE = alter_op_test_fn_bool_real
);
-- No-op setting already false hashes and merges to false works
ALTER OPERATOR === (boolean, real) SET (MERGES = false);
ALTER OPERATOR === (boolean, real) SET (HASHES = false);
-- Test setting merges and hashes
ALTER OPERATOR === (boolean, real) SET (MERGES);
ALTER OPERATOR === (boolean, real) SET (HASHES);
SELECT oprcanmerge, oprcanhash
FROM pg_operator WHERE oprname = '==='
  AND oprleft = 'boolean'::regtype AND oprright = 'real'::regtype;
 oprcanmerge | oprcanhash 
-------------+------------
 t           | t
(1 row)

-- Test setting commutator
ALTER OPERATOR === (boolean, real) SET (COMMUTATOR = ====);
-- Check that oprcom has been set on both the operator and commutator,
-- that they reference each other, and that the operator used is the existing
-- one we created and not a new shell operator.
SELECT op.oprname AS operator_name, com.oprname AS commutator_name,
  com.oprcode AS commutator_func
  FROM pg_operator op
  INNER JOIN pg_operator com ON (op.oid = com.oprcom AND op.oprcom = com.oid)
  WHERE op.oprname = '==='
  AND op.oprleft = 'boolean'::regtype AND op.oprright = 'real'::regtype;
 operator_name | commutator_name |      commutator_func       
---------------+-----------------+----------------------------
 ===           | ====            | alter_op_test_fn_real_bool
(1 row)

-- Cannot set self as negator
ALTER OPERATOR === (boolean, real) SET (NEGATOR = ===);
ERROR:  operator cannot be its own negator
-- Test setting negator
ALTER OPERATOR === (boolean, real) SET (NEGATOR = !====);
-- Check that oprnegate has been set on both the operator and negator,
-- that they reference each other, and that the operator used is the existing
-- one we created and not a new shell operator.
SELECT op.oprname AS operator_name, neg.oprname AS negator_name,
  neg.oprcode AS negator_func
  FROM pg_operator op
  INNER JOIN pg_operator neg ON (op.oid = neg.oprnegate AND op.oprnegate = neg.oid)
  WHERE op.oprname = '==='
  AND op.oprleft = 'boolean'::regtype AND op.oprright = 'real'::regtype;
 operator_name | negator_name |        negator_func        
---------------+--------------+----------------------------
 ===           | !====        | alter_op_test_fn_bool_real
(1 row)

-- Test that no-op set succeeds
ALTER OPERATOR === (boolean, real) SET (NEGATOR = !====);
ALTER OPERATOR === (boolean, real) SET (COMMUTATOR = ====);
ALTER OPERATOR === (boolean, real) SET (MERGES);
ALTER OPERATOR === (boolean, real) SET (HASHES);
-- Check that the final state of the operator is as we expect
SELECT oprcanmerge, oprcanhash,
       pg_describe_object('pg_operator'::regclass, oprcom, 0) AS commutator,
       pg_describe_object('pg_operator'::regclass, oprnegate, 0) AS negator
  FROM pg_operator WHERE oprname = '==='
  AND oprleft = 'boolean'::regtype AND oprright = 'real'::regtype;
 oprcanmerge | oprcanhash |         commutator          |           negator            
-------------+------------+-----------------------------+------------------------------
 t           | t          | operator ====(real,boolean) | operator !====(boolean,real)
(1 row)

-- Cannot change commutator, negator, merges, and hashes when already set
CREATE OPERATOR @= (
    LEFTARG = real,
    RIGHTARG = boolean,
    PROCEDURE = alter_op_test_fn_real_bool
);
CREATE OPERATOR @!= (
    LEFTARG = boolean,
    RIGHTARG = real,
    PROCEDURE = alter_op_test_fn_bool_real
);
ALTER OPERATOR === (boolean, real) SET (COMMUTATOR = @=);
ERROR:  operator attribute "commutator" cannot be changed if it has already been set
ALTER OPERATOR === (boolean, real) SET (NEGATOR = @!=);
ERROR:  operator attribute "negator" cannot be changed if it has already been set
ALTER OPERATOR === (boolean, real) SET (MERGES = false);
ERROR:  operator attribute "merges" cannot be changed if it has already been set
ALTER OPERATOR === (boolean, real) SET (HASHES = false);
ERROR:  operator attribute "hashes" cannot be changed if it has already been set
-- Cannot set an operator that already has a commutator as the commutator
ALTER OPERATOR @=(real, boolean) SET (COMMUTATOR = ===);
ERROR:  commutator operator === is already the commutator of operator ====
-- Cannot set an operator that already has a negator as the negator
ALTER OPERATOR @!=(boolean, real) SET (NEGATOR = ===);
ERROR:  negator operator === is already the negator of operator !====
-- Check no changes made
SELECT oprcanmerge, oprcanhash,
       pg_describe_object('pg_operator'::regclass, oprcom, 0) AS commutator,
       pg_describe_object('pg_operator'::regclass, oprnegate, 0) AS negator
  FROM pg_operator WHERE oprname = '==='
  AND oprleft = 'boolean'::regtype AND oprright = 'real'::regtype;
 oprcanmerge | oprcanhash |         commutator          |           negator            
-------------+------------+-----------------------------+------------------------------
 t           | t          | operator ====(real,boolean) | operator !====(boolean,real)
(1 row)

--
-- Clean up
--
DROP USER regress_alter_op_user;
DROP OPERATOR === (boolean, boolean);
DROP OPERATOR === (boolean, real);
DROP OPERATOR ==== (real, boolean);
DROP OPERATOR !==== (boolean, real);
DROP OPERATOR @= (real, boolean);
DROP OPERATOR @!= (boolean, real);
DROP FUNCTION customcontsel(internal, oid, internal, integer);
DROP FUNCTION alter_op_test_fn(boolean, boolean);
DROP FUNCTION alter_op_test_fn_bool_real(boolean, real);
DROP FUNCTION alter_op_test_fn_real_bool(real, boolean);