Re: SQL:2011 application time

jian he <jian.universality@gmail.com>

From: jian he <jian.universality@gmail.com>

To: Paul Jungwirth <pj@illuminatedcomputing.com>

Cc: Vik Fearing <vik@postgresfriends.org>, Peter Eisentraut <peter@eisentraut.org>, Corey Huinker <corey.huinker@gmail.com>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>

Date: 2023-09-08T09:35:10Z

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 →

Rename gist stratnum support function
- 32edf732e8dc 18.0 landed
Remove support for temporal RESTRICT foreign keys
- b83e8a2ca2eb 18.0 landed
Cache NO ACTION foreign keys separately from RESTRICT foreign keys
- 9926f854d077 18.0 landed
Fix NO ACTION temporal foreign keys when the referenced endpoints change
- 1772d554b089 18.0 landed
Improve whitespace in without_overlaps test
- 888d4523f0c2 18.0 landed
Tests for logical replication with temporal keys
- 939b0908c87a 18.0 landed
Support for GiST in get_equal_strategy_number()
- 74edabce7a33 18.0 landed
Make the conditions in IsIndexUsableForReplicaIdentityFull() more explicit
- 13544e790ef8 18.0 landed
Replace get_equal_strategy_number_for_am() by get_equal_strategy_number()
- a2a475b011cf 18.0 landed
Improve internal logical replication error for missing equality strategy
- 321c287351f7 18.0 landed
Simplify IsIndexUsableForReplicaIdentityFull()
- 7727049e8f66 18.0 landed
Fix ALTER TABLE / REPLICA IDENTITY for temporal tables
- 79b575d3bc09 18.0 landed
doc: Update pg_constraint.conexclop docs for WITHOUT OVERLAPS
- f683ba0867da 18.0 landed
doc: Add PERIOD to ALTER TABLE reference docs
- d56af4c882e2 18.0 landed
doc: Add WITHOUT OVERLAPS to ALTER TABLE reference docs
- bf621059500b 18.0 landed
Add temporal FOREIGN KEY contraints
- 89f908a6d0ac 18.0 landed
- 34768ee36165 17.0 landed
Add temporal PRIMARY KEY and UNIQUE constraints
- fc0438b4e805 18.0 landed
- 46a0cd4cefb4 17.0 landed
Add stratnum GiST support function
- 7406ab623fee 18.0 landed
- 6db4598fcb82 17.0 landed
Avoid crashing when a JIT-inlined backend function throws an error.
- 5d6c64d29097 17.0 cited
Revert temporal primary keys and foreign keys
- 8aee330af55d 17.0 landed
Fix ON CONFLICT DO NOTHING/UPDATE for temporal indexes
- 144c2ce0cc75 17.0 landed
Add test for REPLICA IDENTITY with a temporal key
- 482e108cd38d 17.0 landed
Use half-open interval notation in without_overlaps tests
- 5577a71fb0cc 17.0 landed
Use daterange and YMD in without_overlaps tests instead of tsrange.
- a88c800deb6f 17.0 landed
Rename pg_constraint.conwithoutoverlaps to conperiod
- 030e10ff1a36 17.0 landed
Fix comment on gist_stratnum_btree
- 86232a49a437 17.0 landed
Add missing TAP test name
- 1ab763fc22ad 16.0 cited
Improve error handling of HMAC computations
- 5513dc6a304d 15.0 cited
Rename functions to avoid future conflicts
- ee419607381d 15.0 landed

hi.
the following script makes the server crash (Segmentation fault).

create schema test;
set search_path to test;
DROP TABLE  IF EXISTS temporal_rng;
CREATE TABLE temporal_rng (id int4range, valid_at daterange);
ALTER TABLE temporal_rng
ADD CONSTRAINT temporal_rng_pk
PRIMARY KEY (id, valid_at WITHOUT OVERLAPS);

INSERT INTO temporal_rng VALUES
    ('[11,11]', daterange('2018-01-01', '2020-01-01')),
    ('[11,11]', daterange('2020-01-01', '2021-01-01')),
    ('[20,20]', daterange('2018-01-01', '2020-01-01')),
    ('[20,20]', daterange('2020-01-01', '2021-01-01'));

DROP TABLE  IF EXISTS temporal_fk_rng2rng;

CREATE TABLE temporal_fk_rng2rng (
id int4range,
valid_at tsrange,
parent_id int4range,
CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
);
---------------------------------------------------------------
BEGIN;
ALTER TABLE temporal_fk_rng2rng DROP CONSTRAINT IF EXISTS
temporal_fk_rng2rng_fk;
ALTER TABLE temporal_fk_rng2rng
  ALTER COLUMN parent_id SET DEFAULT '[-1,-1]',
  ALTER COLUMN valid_at SET DEFAULT tsrange('2018-01-01', '2019-11-11');

ALTER TABLE temporal_fk_rng2rng
ADD CONSTRAINT temporal_fk_rng2rng_fk
FOREIGN KEY (parent_id, PERIOD valid_at)
REFERENCES temporal_rng
        on update set DEFAULT
        on delete set DEFAULT;
---------------------------------------------------------gdb related
info:---------------
(gdb) continue
Continuing.

Program received signal SIGSEGV, Segmentation fault.
FindFKComparisonOperators (fkconstraint=0x556450100bd8,
tab=0x55644ff8f570, i=1, fkattnum=0x7ffeb3286ba0,
old_check_ok=0x7ffeb3286b11, old_pfeqop_item=0x7ffeb3286b28,
pktype=3912, fktype=3908, opclass=10078, is_temporal=true,
for_overlaps=true, pfeqopOut=0x7ffeb3286da4, ppeqopOut=0x7ffeb3286e24,
ffeqopOut=0x7ffeb3286ea4) at
../../Desktop/pg_sources/main/postgres/src/backend/commands/tablecmds.c:11582
11582                           pkattr_name = strVal(fkconstraint->pk_period);
(gdb) where
#0  FindFKComparisonOperators (fkconstraint=0x556450100bd8,
tab=0x55644ff8f570, i=1,
    fkattnum=0x7ffeb3286ba0, old_check_ok=0x7ffeb3286b11,
old_pfeqop_item=0x7ffeb3286b28, pktype=3912,
    fktype=3908, opclass=10078, is_temporal=true, for_overlaps=true,
pfeqopOut=0x7ffeb3286da4,
    ppeqopOut=0x7ffeb3286e24, ffeqopOut=0x7ffeb3286ea4)
    at ../../Desktop/pg_sources/main/postgres/src/backend/commands/tablecmds.c:11582
#1  0x000055644e53875a in ATAddForeignKeyConstraint
(wqueue=0x7ffeb3287118, tab=0x55644ff8f570,
    rel=0x7fb2dc124430, fkconstraint=0x556450100bd8, recurse=true,
recursing=false, lockmode=6)
    at ../../Desktop/pg_sources/main/postgres/src/backend/commands/tablecmds.c:10395
#2  0x000055644e536cc2 in ATExecAddConstraint (wqueue=0x7ffeb3287118,
tab=0x55644ff8f570,
    rel=0x7fb2dc124430, newConstraint=0x556450100bd8, recurse=true,
is_readd=false, lockmode=6)
    at ../../Desktop/pg_sources/main/postgres/src/backend/commands/tablecmds.c:9948
#3  0x000055644e528eaa in ATExecCmd (wqueue=0x7ffeb3287118,
tab=0x55644ff8f570, cmd=0x5564500fae48,
    lockmode=6, cur_pass=10, context=0x7ffeb3287310)
    at ../../Desktop/pg_sources/main/postgres/src/backend/commands/tablecmds.c:5711
#4  0x000055644e5283f6 in ATRewriteCatalogs (wqueue=0x7ffeb3287118,
lockmode=6, context=0x7ffeb3287310)
    at ../../Desktop/pg_sources/main/postgres/src/backend/commands/tablecmds.c:5569
#5  0x000055644e527031 in ATController (parsetree=0x55645000e228,
rel=0x7fb2dc124430,
    cmds=0x55645000e1d8, recurse=true, lockmode=6, context=0x7ffeb3287310)
    at ../../Desktop/pg_sources/main/postgres/src/backend/commands/tablecmds.c:5136
#6  0x000055644e526a9d in AlterTable (stmt=0x55645000e228, lockmode=6,
context=0x7ffeb3287310)
    at ../../Desktop/pg_sources/main/postgres/src/backend/commands/tablecmds.c:4789
#7  0x000055644e92eb65 in ProcessUtilitySlow (pstate=0x55644ff8f460,
pstmt=0x55645000e2d8,
--Type <RET> for more, q to quit, c to continue without paging--
    55645000d330 "ALTER TABLE temporal_fk_rng2rng\n\tADD CONSTRAINT
temporal_fk_rng2rng_fk\n\tFOREIGN KEY (parent_id, PERIOD
valid_at)\n\tREFERENCES temporal_rng\n        on update set DEFAULT \n
       on delete set DEFAULT;", context=PROCESS_UTILITY_TOPLEVEL,
params=0x0, queryEnv=0x0,
    dest=0x55645000e698, qc=0x7ffeb3287970)
    at ../../Desktop/pg_sources/main/postgres/src/backend/tcop/utility.c:1329
#8  0x000055644e92e24c in standard_ProcessUtility (pstmt=0x55645000e2d8,
    queryString=0x55645000d330 "ALTER TABLE temporal_fk_rng2rng\n\tADD
CONSTRAINT temporal_fk_rng2rng_fk\n\tFOREIGN KEY (parent_id, PERIOD
valid_at)\n\tREFERENCES temporal_rng\n        on update set DEFAULT \n
       on delete set DEFAULT;", readOnlyTree=false,
context=PROCESS_UTILITY_TOPLEVEL, params=0x0,
    queryEnv=0x0, dest=0x55645000e698, qc=0x7ffeb3287970)
    at ../../Desktop/pg_sources/main/postgres/src/backend/tcop/utility.c:1078
#9  0x000055644e92c921 in ProcessUtility (pstmt=0x55645000e2d8,
    queryString=0x55645000d330 "ALTER TABLE temporal_fk_rng2rng\n\tADD
CONSTRAINT temporal_fk_rng2rng_fk\n\tFOREIGN KEY (parent_id, PERIOD
valid_at)\n\tREFERENCES temporal_rng\n        on update set DEFAULT \n
       on delete set DEFAULT;", readOnlyTree=false,
context=PROCESS_UTILITY_TOPLEVEL, params=0x0,
    queryEnv=0x0, dest=0x55645000e698, qc=0x7ffeb3287970)
    at ../../Desktop/pg_sources/main/postgres/src/backend/tcop/utility.c:530
#10 0x000055644e92a83e in PortalRunUtility (portal=0x5564500a9840,
pstmt=0x55645000e2d8,
    isTopLevel=true, setHoldSnapshot=false, dest=0x55645000e698,
qc=0x7ffeb3287970)
    at ../../Desktop/pg_sources/main/postgres/src/backend/tcop/pquery.c:1158
#11 0x000055644e92abdb in PortalRunMulti (portal=0x5564500a9840,
isTopLevel=true,
    setHoldSnapshot=false, dest=0x55645000e698,
altdest=0x55645000e698, qc=0x7ffeb3287970)
    at ../../Desktop/pg_sources/main/postgres/src/backend/tcop/pquery.c:1315
#12 0x000055644e929b53 in PortalRun (portal=0x5564500a9840,
count=9223372036854775807, isTopLevel=true,
    run_once=true, dest=0x55645000e698, altdest=0x55645000e698,
qc=0x7ffeb3287970)
    at ../../Desktop/pg_sources/main/postgres/src/backend/tcop/pquery.c:791
#13 0x000055644e91f206 in exec_simple_query (
    query_string=0x55645000d330 "ALTER TABLE
temporal_fk_rng2rng\n\tADD CONSTRAINT
temporal_fk_rng2rng_fk\n\tFOREIGN KEY (parent_id, PERIOD
valid_at)\n\tREFERENCES temporal_rng\n        on update set DEFAULT \n
       on delete set DEFAULT;")
    at ../../Desktop/pg_sources/main/postgres/src/backend/tcop/postgres.c:1274
--Type <RET> for more, q to quit, c to continue without paging--
#14 0x000055644e926c49 in PostgresMain (dbname=0x556450045610 "regression",
    username=0x5564500455f8 "jian")
    at ../../Desktop/pg_sources/main/postgres/src/backend/tcop/postgres.c:4637
#15 0x000055644e7ff0e9 in BackendRun (port=0x5564500394f0)
    at ../../Desktop/pg_sources/main/postgres/src/backend/postmaster/postmaster.c:4438
#16 0x000055644e7fe6a1 in BackendStartup (port=0x5564500394f0)
    at ../../Desktop/pg_sources/main/postgres/src/backend/postmaster/postmaster.c:4166
#17 0x000055644e7f8aa0 in ServerLoop ()
    at ../../Desktop/pg_sources/main/postgres/src/backend/postmaster/postmaster.c:1780
#18 0x000055644e7f8042 in PostmasterMain (argc=3, argv=0x55644ff77e60)
    at ../../Desktop/pg_sources/main/postgres/src/backend/postmaster/postmaster.c:1464
#19 0x000055644e67f884 in main (argc=3, argv=0x55644ff77e60)
    at ../../Desktop/pg_sources/main/postgres/src/backend/main/main.c:198