MIME-Version: 1.0
References: <CABwTF4VVuacLV+coERacdDxUXg5tDMHZxvgH5+ek0UZvoq386g@mail.gmail.com>
 <CAEze2WjeYjcf-bm_4gggK7PQ+9fQQ6=hzGeYxTRY9r+D-BPt8g@mail.gmail.com>
 <CABwTF4Uh3FUK6_9mj_aszciLt-CU0eYKiCdaL1nXShjxaHhbqQ@mail.gmail.com>
 <CAMsGm5d3mBF=PQiHKV0ptXNiMW34aZ3u=44ams7ObWbZYfsRLQ@mail.gmail.com>
 <1097504.1673543640@sss.pgh.pa.us> <847c18d4-4c76-1cec-3177-53e62104afd4@postgresfriends.org>
In-Reply-To: <847c18d4-4c76-1cec-3177-53e62104afd4@postgresfriends.org>
From: Gurjeet Singh <gurjeet@singh.im>
Date: Sat, 14 Jan 2023 06:14:02 -0800
Message-ID: <CABwTF4VEGbnZAiC8LX_MTc0Yw+co0g-T+KNt0SwdMtPkiHsJLA@mail.gmail.com>
Subject: Re: Named Operators
To: Vik Fearing <vik@postgresfriends.org>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, Isaac Morland <isaac.morland@gmail.com>, 
	Matthias van de Meent <boekewurm+postgres@gmail.com>, 
	Postgres Hackers <pgsql-hackers@postgresql.org>
Content-Type: multipart/mixed; boundary="000000000000454f5a05f239f795"
Archived-At: <https://www.postgresql.org/message-id/CABwTF4VEGbnZAiC8LX_MTc0Yw%2Bco0g-T%2BKNt0SwdMtPkiHsJLA%40mail.gmail.com>
Precedence: bulk

--000000000000454f5a05f239f795
Content-Type: text/plain; charset="UTF-8"

On Thu, Jan 12, 2023 at 5:55 AM Matthias van de Meent
<boekewurm+postgres@gmail.com> wrote:
> On Thu, 12 Jan 2023 at 11:59, Gurjeet Singh <gurjeet@singh.im> wrote:
> > ... 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.
>
> 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.

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 <tgl@sss.pgh.pa.us> wrote:
> Matthias van de Meent <boekewurm+postgres@gmail.com> writes:
> > I'm -1 on the chosen syntax; :name: shadows common variable
> > substitution patterns including those of psql.
>
> 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 <tgl@sss.pgh.pa.us> wrote:
> Isaac Morland <isaac.morland@gmail.com> writes:
> > What about backticks (`)?
>
> 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.

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

> 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.)

On Thu, Jan 12, 2023 at 9:45 AM Vik Fearing <vik@postgresfriends.org> wrote:
> 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

--000000000000454f5a05f239f795
Content-Type: application/octet-stream; name="named_operators_v2.patch"
Content-Disposition: attachment; filename="named_operators_v2.patch"
Content-Transfer-Encoding: base64
Content-ID: <f_lcw0zcen0>
X-Attachment-Id: f_lcw0zcen0

ZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL2NhdGFsb2cvcGdfb3BlcmF0b3IuYyBiL3NyYy9iYWNr
ZW5kL2NhdGFsb2cvcGdfb3BlcmF0b3IuYwppbmRleCAxMDE3ZjJlZWQxLi5jNWI4NTYyY2I1IDEw
MDY0NAotLS0gYS9zcmMvYmFja2VuZC9jYXRhbG9nL3BnX29wZXJhdG9yLmMKKysrIGIvc3JjL2Jh
Y2tlbmQvY2F0YWxvZy9wZ19vcGVyYXRvci5jCkBAIC0zMSw2ICszMSw3IEBACiAjaW5jbHVkZSAi
Y2F0YWxvZy9wZ190eXBlLmgiCiAjaW5jbHVkZSAibWlzY2FkbWluLmgiCiAjaW5jbHVkZSAicGFy
c2VyL3BhcnNlX29wZXIuaCIKKyNpbmNsdWRlICJwYXJzZXIvc2NhbnN1cC5oIgogI2luY2x1ZGUg
InV0aWxzL2FjbC5oIgogI2luY2x1ZGUgInV0aWxzL2J1aWx0aW5zLmgiCiAjaW5jbHVkZSAidXRp
bHMvbHN5c2NhY2hlLmgiCkBAIC03OSw2ICs4MCwxMCBAQCB2YWxpZE9wZXJhdG9yTmFtZShjb25z
dCBjaGFyICpuYW1lKQogCWlmIChsZW4gPT0gMCB8fCBsZW4gPj0gTkFNRURBVEFMRU4pCiAJCXJl
dHVybiBmYWxzZTsKIAorCS8qIElzIHRoaXMgYSBOYW1lZCBPcGVyYXRvcj8gKi8KKwlpZiAodmFs
aWROYW1lZE9wZXJhdG9yKG5hbWUpKQorCQlyZXR1cm4gdHJ1ZTsKKwogCS8qIENhbid0IGNvbnRh
aW4gYW55IGludmFsaWQgY2hhcmFjdGVycyAqLwogCS8qIFRlc3Qgc3RyaW5nIGhlcmUgc2hvdWxk
IG1hdGNoIG9wX2NoYXJzIGluIHNjYW4ubCAqLwogCWlmIChzdHJzcG4obmFtZSwgIn4hQCNeJnxg
PystKi8lPD49IikgIT0gbGVuKQpkaWZmIC0tZ2l0IGEvc3JjL2JhY2tlbmQvcGFyc2VyL3NjYW4u
bCBiL3NyYy9iYWNrZW5kL3BhcnNlci9zY2FuLmwKaW5kZXggZGI4YjBmZThlYi4uZmEyODJkZGU0
ZCAxMDA2NDQKLS0tIGEvc3JjL2JhY2tlbmQvcGFyc2VyL3NjYW4ubAorKysgYi9zcmMvYmFja2Vu
ZC9wYXJzZXIvc2Nhbi5sCkBAIC0zNzksNiArMzc5LDE1IEBAIHNlbGYJCQlbLCgpXFtcXS47XDpc
K1wtXCpcL1wlXF5cPFw+XD1dCiBvcF9jaGFycwkJW1x+XCFcQFwjXF5cJlx8XGBcP1wrXC1cKlwv
XCVcPFw+XD1dCiBvcGVyYXRvcgkJe29wX2NoYXJzfSsKIAorLyoKKyAqIE5hbWVkIE9wZXJhdG9y
cywgZS5nLiBcI2ZvbworICoKKyAqIHtuYW1lZG9wZmFpbGVkfSBpcyBhbiBlcnJvciBydWxlIHRv
IGF2b2lkIHNjYW5uZXIgYmFja3VwIHdoZW4KKyAqIHtuYW1lZG9wfSBmYWlscyB0byBtYXRjaCBp
dHMgdHJhaWxpbmcge2lkZW50aWZpZXJ9LgorICovCituYW1lZG9wCQkJXFxcI3tpZGVudGlmaWVy
fQorbmFtZWRvcGZhaWxlZAlcXFwjCisKIC8qCiAgKiBOdW1iZXJzCiAgKgpAQCAtNzY4LDYgKzc3
NywxOCBAQCBvdGhlcgkJCS4KIAkJCQl9CiA8eGRvbHE+PDxFT0Y+Pgl7IHl5ZXJyb3IoInVudGVy
bWluYXRlZCBkb2xsYXItcXVvdGVkIHN0cmluZyIpOyB9CiAKK3tuYW1lZG9wfQkJeworCQkJCQlT
RVRfWVlMTE9DKCk7CisJCQkJCWlmICh5eWxlbmcgPj0gTkFNRURBVEFMRU4pCisJCQkJCQl5eWVy
cm9yKCJvcGVyYXRvciBuYW1lIHRvbyBsb25nIik7CisJCQkJCXl5bHZhbC0+c3RyID0gZG93bmNh
c2VfaWRlbnRpZmllcih5eXRleHQsIHl5bGVuZywgZmFsc2UsIGZhbHNlKTsKKwkJCQkJcmV0dXJu
IE9wOworCQkJCX0KKwore25hbWVkb3BmYWlsZWR9CXsKKwkJCQkJeXllcnJvcigidW5leHBlY3Rl
ZCB0b2tlbiIpOworCQkJCX0KKwoge3hkc3RhcnR9CQl7CiAJCQkJCVNFVF9ZWUxMT0MoKTsKIAkJ
CQkJQkVHSU4oeGQpOwpkaWZmIC0tZ2l0IGEvc3JjL2JhY2tlbmQvcGFyc2VyL3NjYW5zdXAuYyBi
L3NyYy9iYWNrZW5kL3BhcnNlci9zY2Fuc3VwLmMKaW5kZXggNjAyMTA4YTQwZi4uZGI5NGE5Mjdm
NyAxMDA2NDQKLS0tIGEvc3JjL2JhY2tlbmQvcGFyc2VyL3NjYW5zdXAuYworKysgYi9zcmMvYmFj
a2VuZC9wYXJzZXIvc2NhbnN1cC5jCkBAIC0xMjUsMyArMTI1LDY1IEBAIHNjYW5uZXJfaXNzcGFj
ZShjaGFyIGNoKQogCQlyZXR1cm4gdHJ1ZTsKIAlyZXR1cm4gZmFsc2U7CiB9CisKKy8qCisgKiB2
YWxpZE5hbWVkT3BlcmF0b3IoKSAtLSByZXR1cm4gdHJ1ZSBpZiBuYW1lIGFkaGVyZXMgdG8gdGhl
IHNjYW5uZXIgcnVsZQorICoge25hbWVkb3B9CisgKi8KK2Jvb2wKK3ZhbGlkTmFtZWRPcGVyYXRv
cihjb25zdCBjaGFyICpuYW1lKQoreworCXNpemVfdAlsZW4gPSBzdHJsZW4obmFtZSk7CisJYm9v
bAl2YWxpZF9pZGVudGlmaWVyOworCisJaWYgKGxlbiA8IDMgfHwgbGVuID49IE5BTUVEQVRBTEVO
KQorCSAgIHJldHVybiBmYWxzZTsKKworCWlmIChuYW1lWzBdICE9ICdcXCcgfHwgbmFtZVsxXSAh
PSAnIycpCisJCXJldHVybiBmYWxzZTsKKworCS8vIERpc3JlZ2FyZCB0aGUgZGVsaW1pdGVycwor
CXZhbGlkX2lkZW50aWZpZXIgPSB2YWxpZElkZW50aWZpZXIobmFtZSArIDIpOworCisJcmV0dXJu
IHZhbGlkX2lkZW50aWZpZXI7Cit9CisKKy8qCisgKiB2YWxpZElkZW50aWZpZXIoKSAtLSByZXR1
cm4gdHJ1ZSBpZiBuYW1lIGFkaGVyZXMgdG8gdGhlIHNjYW5uZXIgcnVsZQorICoge2lkZW50aWZp
ZXJ9CisgKgorICogTm90ZTogdGhpcyBmdW5jdGlvbiBkb2VzIG5vdCBjaGVjayBpZiB0aGUgaWRl
bnRpZmllciBsZW5ndGgKKyAqIGlzIGxlc3MgdGhhbiBOQU1FREFUQUxFTi4KKyAqLworYm9vbAor
dmFsaWRJZGVudGlmaWVyKGNvbnN0IGNoYXIgKm5hbWUpCit7CisJdWludDgJYzsKKwlzaXplX3QJ
aSwgbGVuID0gc3RybGVuKG5hbWUpOworCisJLy8gUmVqZWN0IGlmIGZpcnN0IGNoYXJhY3RlciBp
cyBub3QgcGFydCBvZiBpZGVudF9zdGFydAorCWMgPSBuYW1lWzBdOworCWlmICggIShjID09ICdf
JworCQl8fCAoYyA+PSdBJyAmJiBjIDw9ICdaJykKKwkJfHwgKGMgPj0nYScgJiYgYyA8PSAneicp
CisJCXx8IChjID49IDAyMDAgJiYgYyA8PSAwMzc3KSkpCisJeworCQlyZXR1cm4gZmFsc2U7CisJ
fQorCisJLy8gUmVqZWN0IGlmIG90aGVyIGNoYXJhY3RlcnMgYXJlIG5vdCBwYXJ0IG9mIGlkZW50
X2NvbnQKKwlmb3IgKGkgPSAxOyBpIDwgbGVuOyArK2kpCisJeworCQljID0gbmFtZVtpXTsKKwkJ
aWYgKCAhKGMgPT0gJ18nIHx8IGMgPT0gJyQnCisJCQl8fCAoYyA+PSdBJyAmJiBjIDw9ICdaJykK
KwkJCXx8IChjID49J2EnICYmIGMgPD0gJ3onKQorCQkJfHwgKGMgPj0nMCcgJiYgYyA8PSAnOScp
CisJCQl8fCAoYyA+PSAwMjAwICYmIGMgPD0gMDM3NykpKQorCQl7CisJCQlyZXR1cm4gZmFsc2U7
CisJCX0KKwl9CisKKwlyZXR1cm4gdHJ1ZTsKK30KZGlmZiAtLWdpdCBhL3NyYy9mZV91dGlscy9w
c3Fsc2Nhbi5sIGIvc3JjL2ZlX3V0aWxzL3BzcWxzY2FuLmwKaW5kZXggYWU1MzFlYzI0MC4uOGVj
OTU3MmQ1ZSAxMDA2NDQKLS0tIGEvc3JjL2ZlX3V0aWxzL3BzcWxzY2FuLmwKKysrIGIvc3JjL2Zl
X3V0aWxzL3BzcWxzY2FuLmwKQEAgLTMxNyw2ICszMTcsMTUgQEAgc2VsZgkJCVssKClcW1xdLjtc
OlwrXC1cKlwvXCVcXlw8XD5cPV0KIG9wX2NoYXJzCQlbXH5cIVxAXCNcXlwmXHxcYFw/XCtcLVwq
XC9cJVw8XD5cPV0KIG9wZXJhdG9yCQl7b3BfY2hhcnN9KwogCisvKgorICogTmFtZWQgT3BlcmF0
b3JzLCBlLmcuIFwjZm9vCisgKgorICoge25hbWVkb3BmYWlsZWR9IGlzIGFuIGVycm9yIHJ1bGUg
dG8gYXZvaWQgc2Nhbm5lciBiYWNrdXAgd2hlbgorICoge25hbWVkb3B9IGZhaWxzIHRvIG1hdGNo
IGl0cyB0cmFpbGluZyB7aWRlbnRpZmllcn0uCisgKi8KK25hbWVkb3AJCQlcXFwje2lkZW50aWZp
ZXJ9CituYW1lZG9wZmFpbGVkCVxcXCMKKwogLyoKICAqIE51bWJlcnMKICAqCkBAIC01NzAsNiAr
NTc5LDE0IEBAIG90aGVyCQkJLgogCQkJCQlFQ0hPOwogCQkJCX0KIAore25hbWVkb3B9CQl7CisJ
CQkJCUVDSE87CisJCQkJfQorCit7bmFtZWRvcGZhaWxlZH0JeworCQkJCQlFQ0hPOworCQkJCX0K
Kwoge3hkc3RhcnR9CQl7CiAJCQkJCUJFR0lOKHhkKTsKIAkJCQkJRUNITzsKZGlmZiAtLWdpdCBh
L3NyYy9pbmNsdWRlL3BhcnNlci9zY2Fuc3VwLmggYi9zcmMvaW5jbHVkZS9wYXJzZXIvc2NhbnN1
cC5oCmluZGV4IGZmNjUyMjRiZjYuLjBmNmFmZjhiNDQgMTAwNjQ0Ci0tLSBhL3NyYy9pbmNsdWRl
L3BhcnNlci9zY2Fuc3VwLmgKKysrIGIvc3JjL2luY2x1ZGUvcGFyc2VyL3NjYW5zdXAuaApAQCAt
MjQsNCArMjQsNyBAQCBleHRlcm4gdm9pZCB0cnVuY2F0ZV9pZGVudGlmaWVyKGNoYXIgKmlkZW50
LCBpbnQgbGVuLCBib29sIHdhcm4pOwogCiBleHRlcm4gYm9vbCBzY2FubmVyX2lzc3BhY2UoY2hh
ciBjaCk7CiAKK2V4dGVybiBib29sIHZhbGlkTmFtZWRPcGVyYXRvcihjb25zdCBjaGFyICpuYW1l
KTsKK2V4dGVybiBib29sIHZhbGlkSWRlbnRpZmllcihjb25zdCBjaGFyICpuYW1lKTsKKwogI2Vu
ZGlmCQkJCQkJCS8qIFNDQU5TVVBfSCAqLwo=
--000000000000454f5a05f239f795--