Horton
Menu

Re: SQL:2011 Application Time Update & Delete

Paul A Jungwirth <pj@illuminatedcomputing.com>

View thread
From: Paul A Jungwirth <pj@illuminatedcomputing.com>
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, Robert Haas <robertmhaas@gmail.com>, Peter Eisentraut <peter@eisentraut.org>
Date: 2025-09-25T04:05:41Z
Lists: pgsql-hackers

Attachments

On Fri, Aug 29, 2025 at 6:03 AM Paul A Jungwirth
<pj@illuminatedcomputing.com> wrote:
> I'm still working on the PERIOD DDL, but that doesn't have to go in at
> the same time. The tricky part is ALTER TABLE ADD PERIOD, where I need
> to wait until the add-columns pass to see the start/end columns'
> type/etc, but then in that same pass I need to add a generated range
> column. If I add the column in a later pass, I get a failure, e.g.
> "cannot ALTER TABLE "pt" because it is being used by active queries in
> this session". This only appeared with recent(ish) NOT NULL work. I
> think the solution is to avoid holding a relcache entry longer than
> needed, but I haven't had a chance to locate the issue yet.

Here is another update, now with working PERIOD DDL. I also fixed some
new post-rebase problems causing CI to fail.

There is a detailed wiki page attached to the commitfest entry. To
summarize the patches here:

- Four documentation patches adding a new chapter introducing temporal
concepts. This are split out by topic: primary key + unique
constraints, foreign keys, PERIODs, and UPDATE/DELETE FOR PORTION OF.
- Two patches adding UPDATE/DELETE FOR PORTION OF. (I broke out the
helper functions that compute temporal leftovers.)
- Some patches adding CASCADE/SET NULL/SET DEFAULT to temporal foreign
keys. Once you have UPDATE/DELETE FOR PORTION OF, these are easy. You
do need to know the FOR PORTION OF bounds though, so one of the
patches adds that to the TriggerData struct.
- A patch to add the same bounds info to PL/pgSQL trigger variables.
- A patch to add PERIOD DDL support, based on hidden GENERATED
rangetype columns.

Rebased to d96c854dfc.

Yours,

-- 
Paul              ~{:-)
pj@illuminatedcomputing.com

Commits

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

  1. Make ExecForPortionOfLeftovers() obey SRF protocol.

  2. Add isolation tests for UPDATE/DELETE FOR PORTION OF

  3. Add UPDATE/DELETE FOR PORTION OF

  4. Record range constructor functions in pg_range

  5. Add range_minus_multi and multirange_minus_multi functions

  6. doc: Add section for temporal tables

  7. Add assertion check for WAL receiver state during stream-archive transition