Subject: Re: Named Operators

From: Gurjeet Singh <[email-masked]>
Date: Thu, 12 Jan 2023 02:59:04 -0800

On Thu, Jan 12, 2023 at 1:49 AM Matthias van de Meent
<[email-masked]> wrote:
>
> On Thu, 12 Jan 2023 at 10:16, Gurjeet Singh <[email-masked]> wrote:
> >
> > Technically correct name of this feature would be Readable Names for
> > Operators, or Pronounceable Names for Operators. But I'd like to call
> > it Named Operators.
> >
> > With this patch in place, the users can name the operators as
> > :some_pronounceable_name: instead of having to choose from the special
> > characters like #^&@.
> > [...]
> >     I think Named Operators will significantly improve the readability
> > of queries.
>
> Couldn't the user better opt to call the functions that implement the
> operator directly if they want more legible operations? So, from your
> example, `SELECT box_add(a, b)` instead of `SELECT a :add_point: b`?

Matter of taste, I guess. But more importantly, defining an operator
gives you many additional features that the planner can use to
optimize your query differently, which it can't do with functions. See
the COMMUTATOR, HASHES, etc. clause in the CREATE OPERATOR command.

https://www.postgresql.org/docs/current/sql-createoperator.html

This proposal is primarily a replacement for the myriad of
hard-to-pronounce operators that users have to memorize. For example,
it'd be nice to have readable names for the PostGIS operators.

https://postgis.net/docs/reference.html#Operators

For someone who's reading/troubleshooting a PostGIS query, when they
encounter operator <<| — in the query for the first time, they'd have
to open up the docs. But if the query used the :strictly_below:
operator, there's no need to switch to docs and lose context.

> I'm -1 on the chosen syntax; :name: shadows common variable
> substitution patterns including those of psql.

Ah, thanks for reminding! Early on when I hadn't written code yet, I
remember discarding colon : as a delimiter choice, precisely because
it is used for using variables in psql, and perhaps in some drivers,
as well. But in the rush of implementing and wrangling code, I forgot
about that argument altogether.

I'll consider using one of the other special characters. Do you have
any suggestions?

Best regards,
Gurjeet
http://Gurje.et

---

From: Matthias van de Meent <[email-masked]>
Date: Thu, 12 Jan 2023 14:55:11 +0100

> the COMMUTATOR, HASHES, etc. clause in the CREATE OPERATOR command.

I see. Wouldn't it be better then to instead make it possible for the
planner to detect the use of the functions used in operators and treat
them as aliases of the operator? Or am I missing something w.r.t.
differences between operator and function invocation?

E.g. indexes on `int8pl(my_bigint, 1)` does not match queries for
`my_bigint + 1` (and vice versa), while they should be able to support
that, as OPERATOR(pg_catalog.+(int8, int8)) 's function is int8pl.

Kind regards,

Matthias van de Meent

---

From: Isaac Morland <[email-masked]>
Date: Thu, 12 Jan 2023 12:02:37 -0500

On Thu, 12 Jan 2023 at 05:59, Gurjeet Singh <[email-masked]> wrote:

> any suggestions?

What about backticks (`)? They are allowed as operator characters but do
not otherwise appear in the lexical syntax as far as I can tell:
https://www.postgresql.org/docs/current/sql-syntax-lexical.html

---

From: Tom Lane <[email-masked]>
Date: Thu, 12 Jan 2023 12:14:00 -0500

> https://www.postgresql.org/docs/current/sql-syntax-lexical.html

Since they're already allowed as operator characters, you can't
use them for this purpose without breaking existing use-cases.

Even if they were completely unused, I'd be pretty hesitant to
adopt them for this purpose because of the potential confusion
for users coming from mysql.

Pretty much the only available syntax space is curly braces,
and I don't really want to give those up for this either.
(One has to assume that the SQL committee has their eyes
on those too.)

			regards, tom lane

---

From: Vik Fearing <[email-masked]>
Date: Thu, 12 Jan 2023 18:45:09 +0100

> (One has to assume that the SQL committee has their eyes

They are used in row pattern recognition.
-- 
Vik Fearing

---

From: "David G. Johnston" <[email-masked]>
Date: Thu, 12 Jan 2023 11:37:48 -0700

> use them for this purpose without breaking existing use-cases.
IIUC, specifically the fact that an operator is defined to start with one
of those symbols and end at the first non-symbol.  We can't change the
allowed set of non-symbols at this point, without defining something else
to denote the start of an operator.

David J.

---

From: Gurjeet Singh <[email-masked]>
Date: Sat, 14 Jan 2023 06:14:02 -0800

On Thu, Jan 12, 2023 at 5:55 AM Matthias van de Meent
<[email-masked]> wrote:
> > ... defining an operator

Such a feature would be immensely useful in its own right. But it's
also going to be at least 2 orders of magnitude (or more) effort to
implement, and to get accepted in the community. I'm thinking of
changes in planner, catalogs, etc.

On Thu, Jan 12, 2023 at 7:21 AM Tom Lane <[email-masked]> wrote:
> Matthias van de Meent <[email-masked]> writes:
> Yeah, this syntax is DOA because of that.  I think almost
> anything you might invent is going to have conflict risks.

I remember discussing this in a meeting with Joe Conway a few weeks
ago, when this was just a proposal in my head and I was just bouncing
it off him. And I remember pointing out that colons would be a bad
choice because of their use in psql; but for life of me I can't think
of a reason (except temporary memory loss) why I failed to consider
the psql conflict when implementing the feature. If only some test in
`make check` would have pointed out the mistake, I wouldn't have made
this obvious mistake.

> We could probably make it work by allowing the existing OPERATOR
> syntax to take things that look like names as well as operators,
> like
>
>         expr3 OPERATOR(contains_all) expr4
>
> But that's bulky enough that nobody will care to use it.

+1. Although that'd be better for readers than the all-special-char
names, this format is bulky enough that you won't be able to convince
the query writers to bother using it. But if all other efforts fail,
I'll take this format over the cryptic ones any day.

> On the whole I don't see this proposal going anywhere.
> There's too much investment in the existing operator names,
> and too much risk of conflicts if you try to shorten the
> syntax.

I wouldn't give up on the idea, yet :-) See new proposal below.

On Thu, Jan 12, 2023 at 9:14 AM Tom Lane <[email-masked]> wrote:
> > What about backticks (`)?
>

Since when have we started caring for the convenience of users of
other databases?!! /s

> They are used in row pattern recognition.

I was very hopeful of using { }, and hoping that we'd beat the SQL
committee to it, so that they have to choose something else, if we
release this into the wild before them. But it seems that they beat us
to it long ago. (tangent: Reading some blog posts, I have to say I
loved the Row Pattern Recognition feature!)

Considering that there are almost no printable characters left in
1-255 ASCII range for us to choose from, I had to get creative; and I
believe I have found a way to make it work.

Unless the SQL committee has their eyes on a freestanding backslash \
character for something, I believe we can use it as a prefix for Named
Operators. Since the most common use of backslash is for escaping
characters, I believe it would feel natural for the users to use it as
described below.

New scheme for the named operators: \#foo That is, an identifier
prefixed with \# would serve as an operator name. psql considers \ to
be the start of its commands, but it wasn't hard to convince psql to
ignore \# and let it pass through to server.

I agree that an identifier _surrounded_ by the same token (e.g. #foo#)
or the pairing token (e.g. {foo}) looks better aesthetically, so I am
okay with any of the following variations of the scheme, as well:

\#foo\#  (tested; works)
\#foo#   (not tested; reduces ident length by 1)

We can choose a different character, instead of #. Perhaps \{foo} !

Attached is the v2 patch that supports \#foo style Named Operators.
Following is the SQL snippet to see what the usage looks like.

create operator \#add_point
    (function = box_add, leftarg = box, rightarg = point);
create table test(a box);
insert into test values('((0,0),(1,1))'), ('((0,0),(2,1))');
select a as original, a \#add_point '(1,1)' as modified from test;
drop operator \#add_point(box, point);

Although we have never done it before, but  by using backslash we
might be able to define new custom token types as well, if needed.

For those interested, I have couple of different branches with
named_operators* prefix in my Git fork [1] where I'm trying different
combinations.

[1]: https://github.com/gurjeet/postgres/branches

Best regards,
Gurjeet
http://Gurje.et

---

From: Gurjeet Singh <[email-masked]>
Date: Fri, 20 Jan 2023 09:16:42 -0800

> We can choose a different character, instead of #. Perhaps \{foo} !

Please find attached the patch that uses \{foo} styled Named
Operators. This is in line with Tom's reluctant hint at possibly using
curly braces as delimiter characters. Since the curly braces are used
by the SQL Specification for row pattern recognition, this patch
proposes escaping the first of the curly braces.

We can get rid of the leading backslash, if (a) we're confident that
SQL committee will not use curly braces anywhere else, and (b) if
we're confident that if/when Postgres supports Row Pattern Recognition
feature, we'll be able to treat curly braces inside the PATTERN clause
specially. Since both of those conditions are unlikely, I think we
must settle for the escaped-first-curly-brace style for the naming our
operators.

Keeping with the previous posts, here's a sample SQL script showing
what the proposed syntax will look like in action. Personally, I
prefer the \#foo style, since the \# prefix stands out among the text,
better than \{..} does, and because # character is a better signal of
an operator than {.

create operator \{add_point}
    (function = box_add, leftarg = box, rightarg = point);
create table test(a box);
insert into test values('((0,0),(1,1))'), ('((0,0),(2,1))');
select a as original, a \{add_point} '(1,1)' as modified from test;
drop operator \{add_point}(box, point);

Best regards,
Gurjeet
http://Gurje.et

---

From: Peter Eisentraut <[email-masked]>
Date: Fri, 27 Jan 2023 16:26:01 +0100

> that, as OPERATOR(pg_catalog.+(int8, int8)) 's function is int8pl.

I have been thinking about something like this for a long time. 
Basically, we would merge pg_proc and pg_operator internally.  Then, all 
the special treatment for operators would also be available to 
two-argument functions.

---

From: Matthias van de Meent <[email-masked]>
Date: Fri, 27 Jan 2023 16:34:52 +0100

> the special treatment for operators would also be available to

And single-argument functions in case of prefix operators, right?

---

From: Peter Eisentraut <[email-masked]>
Date: Tue, 31 Jan 2023 11:21:12 +0100

> And single-argument functions in case of prefix operators, right?

Right.

(The removal of postfix operators is helpful to remove ambiguity here.)

---

From: Tom Lane <[email-masked]>
Date: Wed, 08 Feb 2023 10:57:57 -0500

> the special treatment for operators would also be available to 

I had a thought about this ...

I do not think this proposal is going anywhere as-written.
There seems very little chance that we can invent a syntax that
is concise, non-ugly, and not likely to get blindsided by future
SQL spec extensions.  Even if we were sure that, say, "{foo}"
was safe from spec interference, the syntax "a {foo} b" has
exactly nothing to recommend it compared to "foo(a,b)".
It's not shorter, it's not standard, it won't help any pre-existing
queries, and it can't use function-call features such as named
arguments.

As Matthias said, what we actually need is for the planner to be able
to optimize function calls on the same basis as operators.  We should
tackle that directly rather than inventing new syntax.

We could go after that by inventing a bunch of new function properties
to parallel operator properties, but there is a simpler way: just
teach the planner to look to see if a function call is a call of the
underlying function of some operator, and if so treat it like that
operator.  Right now that'd be an expensive lookup, but we could
remove that objection with an index on pg_operator.oprcode or a
single new field in pg_proc.

This approach does have a couple of shortcomings:

* You still have to invent an operator name, even if you never
plan to use it in queries.  This is just cosmetic though.
It's not going to matter if the operator name is long or looks like
line noise, if you only need to use it a few times in setup DDL.

* We could not extend this to support index functions with more than
two arguments, a request we've heard once in awhile in the past.
Our answer to that so far has been "make a function/operator with
one indexed argument and one composite-type argument", which is a
bit of an ugly workaround but seems to be serviceable enough.

On the whole I don't think these shortcomings are big enough
to justify all the work that would be involved in attaching
operator-like optimization information directly to functions.
(To mention just one nontrivial stumbling block: do you really
want to invent "shell functions" similar to the shell-operator
hack?  If not, how are you going to handle declaration of
commutator pairs?)

In the long run this might lead to thinking of pg_operator as
an extension of pg_proc in the same way that pg_aggregate is.
But we have not unified pg_aggregate into pg_proc, and I don't
think anyone wants to, because pg_proc rows are undesirably
wide already.  There's a similar objection to attaching
optimization fields directly to pg_proc.

You could imagine some follow-on internal cleanup like trying
to unify FuncExpr and OpExpr into a single node type (carrying
a function OID and optionally an operator OID).  But that need
not have any user-visible impact either; it'd mainly be good
for eliminating a lot of near-duplicate code.

			regards, tom lane

---

From: Daniel Gustafsson <[email-masked]>
Date: Tue, 4 Jul 2023 15:11:37 +0200

> On 8 Feb 2023, at 16:57, Tom Lane <[email-masked]> wrote:

> I do not think this proposal is going anywhere as-written.

Reading this thread, it seems there is concensus against this proposal in its
current form, and no updated patch has been presented, so I will mark this as
Returned with Feedback.  Please feel free to resubmit to a future CF when there
is renewed interest in working on this.

--
Daniel Gustafsson