MIME-Version: 1.0
References: 
 <CA+renyUApHgSZF9-nd-a0+OPGharLQLO=mDHcY4_qQ0+noCUVg@mail.gmail.com>
 <a2ee0c1a-5ef0-43ab-897c-b56adb25b34f@illuminatedcomputing.com>
 <333d3886-b737-45c3-93f4-594c96bb405d@eisentraut.org>
 <e8e51e74-10d7-410d-a1d1-8ec681d8436a@eisentraut.org>
 <f4056005-0fb7-417a-bcb3-e704f9aeadc3@illuminatedcomputing.com>
 <6a2247d9-461d-4b0a-b692-f0f3c0fab27f@eisentraut.org>
 <8f995a4c-64e3-40cd-bfed-06b54a280d36@illuminatedcomputing.com>
 <0832f18d-523f-4c15-acb0-338a785150c5@illuminatedcomputing.com>
 <5f0c25de-2c13-4fb5-a4df-a1e03c43cd14@illuminatedcomputing.com>
 <1b45f81d-4e99-4813-a2c6-72bf5fd80980@eisentraut.org>
 <e773bc11-4ac1-40de-bb91-814e02f05b6d@eisentraut.org>
 <3e061027-781d-42c1-b6e8-bafbf3b51033@eisentraut.org>
 <c3af7816-1944-4030-ab73-5b055de8cba3@illuminatedcomputing.com>
 <3b33690e-0d67-47a5-8424-93ac1fed4c26@eisentraut.org>
 <1085d8a3-17a7-4639-afd2-bd6dfa09713d@illuminatedcomputing.com>
 <6ac43c8e-5595-40d9-a039-ede12e7487e1@eisentraut.org>
 <34f9c43a-bad0-482a-a563-a3c202a2b678@illuminatedcomputing.com>
 <CA+renyWEOn33TBQzR4=gDKkOREtBGpDLzQqvyXj8v8ZQ9VWJCw@mail.gmail.com>
In-Reply-To: 
 <CA+renyWEOn33TBQzR4=gDKkOREtBGpDLzQqvyXj8v8ZQ9VWJCw@mail.gmail.com>
From: Robert Haas <robertmhaas@gmail.com>
Date: Fri, 9 May 2025 14:49:57 -0400
Message-ID: 
 <CA+Tgmoaxx6VUvr6werdV235M2aPWp4GG_QpM_MFofbnermixRQ@mail.gmail.com>
Subject: Re: SQL:2011 application time
To: Paul A Jungwirth <pj@illuminatedcomputing.com>
Cc: Peter Eisentraut <peter@eisentraut.org>,
 jian he <jian.universality@gmail.com>,
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>,
 vignesh C <vignesh21@gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CA%2BTgmoaxx6VUvr6werdV235M2aPWp4GG_QpM_MFofbnermixRQ%40mail.gmail.com>
Precedence: bulk

On Thu, May 8, 2025 at 1:36=E2=80=AFPM Paul A Jungwirth
<pj@illuminatedcomputing.com> wrote:
> v51 attached, just rebasing to b560ce7884.

I think these patches would benefit from some work to make them more
understandable for people who don't already know what they're intended
to accomplish. I suspect that's actually a prerequisite to committing
them. I started by opening up v51-0001, and the commit message told me
this:

This new support proc is used by UPDATE/DELETE FOR PORTION OF to
compute leftovers that weren't touched by the UPDATE/DELETE. This
commit defines implementations for ranges and multiranges.

But there is no such thing as FOR PORTION OF just yet, so this is a
forward reference. I had to open v51-0002 to try to figure it out.
Ideally, concepts get introduced before they're used. The rest of the
commit message for v51-0001 reads like this:

The procs return SETOF their input type and work like minus but don't
fail on splits. The results never contain empty elements.

For someone familiar with this work, this probably makes sense, but
I'm not and it doesn't. I don't even understand to what "work like
minus but don't fail on splits" is intended to refer. I mean, is there
some existing minus thing? I don't see it among the GIST_*_PROC
symbols, and I don't know where else I'd go looking.

As I read through the documentation changes, I find that you use terms
like "leftovers" or "leftover row" in various parts of the
documentation that are quite far away from and not obviously linked to
the documentation of FOR PORTION OF. I think that's going to be very
confusing. In other places where we used specialized terms like this,
we often using <xref> or <link> to reference the place where the term
is defined. You don't really quite have a place where that happens,
though, although maybe linking to the documentation of FOR PORTION OF
would be good enough.

The documentation of FOR PORTION OF makes more sense to me for range
types than for multirange types. I gather that if I have a range like
[1,10] and I deleted from 3 to 6, I'm going to end up with two ranges.
I'm not sure whether 3 or 6 are inclusive or exclusive bounds, so I
don't know if I should expect to end up with [1,3) and (6,10] or [1,3]
and [6,10], and I kind of wonder if I should get to say which one I
want, but anyway now I have two records, my original one having been
split. But if [1,10] is a multirange, it's unnecessary to split the
record in two in order to accommodate a deletion: i can just include
both sub-ranges in the original value. But the documentation doesn't
seem to mention this one way or the other: will I still (needlessly?)
create two records, or will I just update the one record I already and
split the range? Since the documentation is already talking about a
feature specific to range and multirange types, it seems like this
kind of stuff should be mentioned.

Hmm. I guess this implies that we never do an update -- it's always a
DELETE followed by zero, one, or two INSERTs. That seems like it's
leaving quite a bit of efficiency on the table, because updating an
existing row in place could potentially be a HOT update.

Is this feature as per the SQL standard? And, assuming yes, does the
SQL standard specify that permission checks should work as you
describe here, or is that something we decided?

--
Robert Haas
EDB: http://www.enterprisedb.com