Hi! Imagine that I need to use custom operators for one of the table fields in a "simple case" and let these operators not be in a default scheme. As far as I understand, the only way to specify the operator in this case is to use the search path.
In the example below, this corresponds to column "v". The "h" column has
been created
using "normal case" and should match the "v". And it works as expected.
=================================================================================
CREATE SCHEMA sch1;
CREATE SCHEMA sch2;
CREATE TYPE public.aga AS (x integer);
CREATE FUNCTION sch1.cmp(i public.aga, j public.aga) RETURNS boolean
LANGUAGE sql IMMUTABLE
AS $$
SELECT $1.x < $2.x;
$$;
CREATE FUNCTION sch2.cmp(i public.aga, j public.aga) RETURNS boolean
LANGUAGE sql IMMUTABLE
AS $$
SELECT $1.x > $2.x;
$$;
CREATE OPERATOR sch1.= (
FUNCTION = sch1.cmp,
LEFTARG = public.aga,
RIGHTARG = public.aga,
COMMUTATOR = OPERATOR(sch1.=)
);
CREATE OPERATOR sch2.= (
FUNCTION = sch2.cmp,
LEFTARG = public.aga,
RIGHTARG = public.aga,
COMMUTATOR = OPERATOR(sch2.=)
);
SET search_path = sch1;
CREATE TABLE public.tab1 (
v public.aga,
g integer GENERATED ALWAYS AS (
CASE v
WHEN ROW(1)::public.aga THEN 1
WHEN ROW(2)::public.aga THEN 2
ELSE NULL::integer
END) STORED,
h integer GENERATED ALWAYS AS (
CASE
WHEN (v OPERATOR(sch1.=) ROW(1)::public.aga) THEN 1
WHEN (v OPERATOR(sch1.=) ROW(2)::public.aga) THEN 2
ELSE NULL::integer
END) STORED
);
INSERT INTO public.tab1(v) VALUES (ROW(0)), (ROW(1)), (ROW(2)), (ROW(3));
SET search_path = sch2;
CREATE TABLE public.tab2 (
v public.aga,
g integer GENERATED ALWAYS AS (
CASE v
WHEN ROW(1)::public.aga THEN 1
WHEN ROW(2)::public.aga THEN 2
ELSE NULL::integer
END) STORED,
h integer GENERATED ALWAYS AS (
CASE
WHEN (v OPERATOR(sch2.=) ROW(1)::public.aga) THEN 1
WHEN (v OPERATOR(sch2.=) ROW(2)::public.aga) THEN 2
ELSE NULL::integer
END) STORED
);
INSERT INTO public.tab2(v) VALUES (ROW(0)), (ROW(1)), (ROW(2)), (ROW(3));
...
# postgres tab1:
# (0)|1|1
# (1)|2|2
# (2)| |
# (3)| |
# postgres tab2:
# (0)| |
# (1)| |
# (2)|1|1
# (3)|1|1
...
=================================================================================
But after dump/restore, this will be converted to the:
# foo tab1:
# (0)| |1
# (1)|1|2
# (2)|2|
# (3)| |
# foo tab2:
# (0)| |
# (1)|1|
# (2)|2|1
# (3)| |1
This point is partially addressed in the documentation [0]. It turns out
that a "simple case" cannot
be used in this situation. However, for the user, the situation is not
always clear. At the same
time, Postgres stores the right operators in pg_attrdef but lacks the
ability to generate an
equivalent construct for restoration after dump.
This leads me to the following questions.
1) Can this behaviour, in the case described above, when after dump and
recovery we receive
different data, be considered correct?
2) Should the documentation explicitly state that for types with custom
operators, using
"simple case" can lead to problems after upgrade?
3) Maybe some warning for the user would be useful? In the sense of, "Hey,
I won't be able to
restore such a construction after dumping."
4) Does it make sense to extend the "simple case" grammar so that it can
accept a custom
operator? Something like:
CASE expression [OPERATOR(schema.operator)]
WHEN [OPERATOR(schema.operator)] value THEN result
[WHEN ...]
[ELSE result]
END
I would be very glad to hear your opinions on these issues.
[0]
https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PATH
--
Best regards,
Maxim Orlov.
0001-Add-pg_dump-007_operator-test.patch
Description: Binary data
