Horton
Menu

Thread

  1. Issue with custom operator in simple case

    Maxim Orlov <orlovmg@gmail.com> — 2025-06-23T15:40:31Z

    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.

  2. Re: Issue with custom operator in simple case

    Tom Lane <tgl@sss.pgh.pa.us> — 2025-06-23T16:28:42Z

    Maxim Orlov <orlovmg@gmail.com> writes:
> 1) Can this behaviour, in the case described above, when after dump and
> recovery we receive different data, be considered correct?

It's undesirable, for sure.

> 4) Does it make sense to extend the "simple case" grammar so that it can
> accept a custom operator?

This has been discussed before, see e.g. [1][2].  Unfortunately CASE
is just the tip of the iceberg, there are several SQL constructs that
are equally underspecified.  Fixing them all looks messy, and it would
cause dumps to be even less portable than they are now.  So nobody's
stepped up to tackle the issue.

			regards, tom lane

[1] https://www.postgresql.org/message-id/flat/20141009200031.25464.53769%40wrigleys.postgresql.org
[2] https://www.postgresql.org/message-id/flat/10492.1531515255%40sss.pgh.pa.us#8755318d9b16ec32296398f0893a44d7

  3. Re: Issue with custom operator in simple case

    Maxim Orlov <orlovmg@gmail.com> — 2025-06-25T08:42:29Z

    On Mon, 23 Jun 2025 at 19:28, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Maxim Orlov <orlovmg@gmail.com> writes:
> > 1) Can this behaviour, in the case described above, when after dump and
> > recovery we receive different data, be considered correct?
>
> It's undesirable, for sure.
>
> > 4) Does it make sense to extend the "simple case" grammar so that it can
> > accept a custom operator?
>
> This has been discussed before, see e.g. [1][2].  Unfortunately CASE
>
> [1]
> https://www.postgresql.org/message-id/flat/20141009200031.25464.53769%40wrigleys.postgresql.org
> [2]
> https://www.postgresql.org/message-id/flat/10492.1531515255%40sss.pgh.pa.us#8755318d9b16ec32296398f0893a44d7


Thank you for pointing this out. I'll have to read those discussions.


-- 
Best regards,
Maxim Orlov.

  4. Re: Issue with custom operator in simple case

    Maxim Orlov <orlovmg@gmail.com> — 2025-06-30T15:49:20Z

    AFAICS, we have the following problem constructions:
================================
a IS DISTINCT FROM b
a IS NOT DISTINCT FROM b
a IN (...)
a NOT IN (...)
CASE a WHEN b THEN ... ELSE d END
NULLIF(a, b)
JOIN USING / NATURAL JOIN
================================

As a quick recap, the following options are available to us.
1) Add the "USING operator" clause.
Rejected due to:
* Non-standard syntax.
* ruleutils.c could not safely convert this back to the source text.
* In case "IS DISTINCT FROM" on composite types, using only the eq operator
is maybe not enough.

2) Using default btree opclass/opfamily operators.
Rejected due to:
 * Adding yet another way of selecting an operator to the
existingfunc_select_candidate and opfamily
   seems too complicated and not safe.
 * May not work in some cases.

3) CVE-2018-1058 revert.
Rejected due to obvious reasons.

In my humble opinion, the best way to solve an issue is (1). Whether it's
even worth it. Although it
uses non-standard syntax, it does not break the existing one.

-- 
Best regards,
Maxim Orlov.

  5. Re: Issue with custom operator in simple case

    David G. Johnston <david.g.johnston@gmail.com> — 2025-06-30T16:52:07Z

    On Mon, Jun 30, 2025 at 8:49 AM Maxim Orlov <orlovmg@gmail.com> wrote:

> 3) CVE-2018-1058 revert.
> Rejected due to obvious reasons.
>
>
Not revert but maybe try again at convincing people that DBAs should be
given agency here by opting out of a security system that breaks
functioning code without providing, in reality, any immediate security
benefit.

David J.