Horton

Re: SQL:2011 application time

Robert Haas <robertmhaas@gmail.com>

View thread
From: Robert Haas <robertmhaas@gmail.com>
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>
Date: 2025-05-09T18:49:57Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Rename gist stratnum support function

  2. Remove support for temporal RESTRICT foreign keys

  3. Cache NO ACTION foreign keys separately from RESTRICT foreign keys

  4. Fix NO ACTION temporal foreign keys when the referenced endpoints change

  5. Improve whitespace in without_overlaps test

  6. Tests for logical replication with temporal keys

  7. Support for GiST in get_equal_strategy_number()

  8. Make the conditions in IsIndexUsableForReplicaIdentityFull() more explicit

  9. Replace get_equal_strategy_number_for_am() by get_equal_strategy_number()

  10. Improve internal logical replication error for missing equality strategy

  11. Simplify IsIndexUsableForReplicaIdentityFull()

  12. Fix ALTER TABLE / REPLICA IDENTITY for temporal tables

  13. doc: Update pg_constraint.conexclop docs for WITHOUT OVERLAPS

  14. doc: Add PERIOD to ALTER TABLE reference docs

  15. doc: Add WITHOUT OVERLAPS to ALTER TABLE reference docs

  16. Add temporal FOREIGN KEY contraints

  17. Add temporal PRIMARY KEY and UNIQUE constraints

  18. Add stratnum GiST support function

  19. Avoid crashing when a JIT-inlined backend function throws an error.

  20. Revert temporal primary keys and foreign keys

  21. Fix ON CONFLICT DO NOTHING/UPDATE for temporal indexes

  22. Add test for REPLICA IDENTITY with a temporal key

  23. Use half-open interval notation in without_overlaps tests

  24. Use daterange and YMD in without_overlaps tests instead of tsrange.

  25. Rename pg_constraint.conwithoutoverlaps to conperiod

  26. Fix comment on gist_stratnum_btree

  27. Add missing TAP test name

  28. Improve error handling of HMAC computations

  29. Rename functions to avoid future conflicts

On Thu, May 8, 2025 at 1:36 PM 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