Re: SQL:2011 Application Time Update & Delete

On Fri, Aug 29, 2025 at 6:03 AM Paul A Jungwirth
<pj(at)illuminatedcomputing(dot)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(at)illuminatedcomputing(dot)com