Horton

Re: SQL:2011 application time

Paul A Jungwirth <pj@illuminatedcomputing.com>

View thread
From: Paul Jungwirth <pj@illuminatedcomputing.com>
To: Peter Eisentraut <peter@eisentraut.org>, jian he <jian.universality@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2024-01-24T22:06:56Z
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

Attachments

On 1/24/24 08:32, Peter Eisentraut wrote:
 > On 18.01.24 04:59, Paul Jungwirth wrote:
 >> Here are new patches consolidating feedback from several emails.
 >
 > I have committed 0001 and 0002 (the primary key support).

Thanks Peter! I noticed the comment on gist_stratnum_btree was out-of-date, so here is a tiny patch 
correcting it.

Also the remaining patches with some updates:

I fixed the dependency issues with PERIODs and their (hidden) GENERATED range columns. This has been 
causing test failures and bugging me since I reordered the patches at PgCon, so I'm glad to finally 
clean it up. The PERIOD should have an INTERNAL dependency on the range column, but then when you 
dropped the table the dependency code thought the whole table was part of the INTERNAL dependency, 
so the drop would fail. The PERIOD patch here fixes the dependency logic. (I guess this is the first 
time a column has been an internal dependency of something.)

I also fixed an error message when you try to change the type of a start/end column used by a 
PERIOD. Previously the error message would complain about the GENERATED column, not the PERIOD, 
which seems confusing. In fact it was non-deterministic, depending on which pg_depend record the 
index returned first.

On 12/6/23 05:22, jian he wrote:
 > tring to the following TODO:
 > // TODO: Need to save context->mtstate->mt_transition_capture? (See
 > comment on ExecInsert)
 >
 > but failed.
 > I also attached the trial, and also added the related test.
 >
 > You can also use the test to check portion update with insert trigger
 > with "referencing old table as old_table new table as new_table"
 > situation.

Thank you for the very helpful test case here. I fixed the issue of not passing along the transition 
table. But there is still more work to do here I think:

- The AFTER INSERT FOR EACH ROW triggers have *both* leftover rows in the NEW table. Now the docs do 
say that for AFTER triggers, a named transition table can see all the changes from the *statement* 
(although that seems pretty weird to me), but the inserts are two *separate* statements. I think the 
SQL:2011 standard is fairly clear about that. So each time the trigger fires we should still get 
just one row in the transition table.

- The AFTER INSERT FOR EACH STATEMENT triggers never fire. That happens outside ExecInsert (in 
ExecModifyTable). In fact there is a bunch of stuff in ExecModifyTable that maybe we need to do when 
we insert leftovers. Do we even need a separate exec node, perhaps wrapping ExecModifyTable? I'm not 
sure that would give us the correct trigger ordering for the triggers on the implicit insert 
statement(s) vs the explicit update/delete statement, so maybe it does all need to be part of the 
single node. But still I think we need to be more careful about memory, especially the per-tuple 
context.

I'll keep working on that, but at least in this round of patches the transition tables aren't 
missing completely.

My plan is still to replace the 'p' amoppurpose operators with just support functions. I want to do 
that next, although as Peter requested I'll also start focusing more narrowly on the foreign key 
patches.

Rebased to 46a0cd4cef.

Yours,

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