Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit1772d55

Browse files
committed
Fix NO ACTION temporal foreign keys when the referenced endpoints change
If a referenced UPDATE changes the temporal start/end times, shrinkingthe span the row is valid, we get a false return fromri_Check_Pk_Match(), but overlapping references may still be valid, iftheir reference didn't overlap with the removed span.We need to consider what span(s) are still provided in the referencedtable. Instead of returning that from ri_Check_Pk_Match(), we canjust look it up in the main SQL query.Reported-by: Sam Gabrielsson <sam@movsom.se>Author: Paul Jungwirth <pj@illuminatedcomputing.com>Discussion:https://www.postgresql.org/message-id/flat/CA+renyUApHgSZF9-nd-a0+OPGharLQLO=mDHcY4_qQ0+noCUVg@mail.gmail.com
1 parent888d452 commit1772d55

File tree

7 files changed

+233
-7
lines changed

7 files changed

+233
-7
lines changed

‎src/backend/catalog/pg_constraint.c

Lines changed: 16 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1618,11 +1618,14 @@ DeconstructFkConstraintRow(HeapTuple tuple, int *numfks,
16181618
* aggedcontainedbyoperoid is also a ContainedBy operator,
16191619
* but one whose rhs is a multirange.
16201620
* That way foreign keys can compare fkattr <@ range_agg(pkattr).
1621+
* intersectoperoid is used by NO ACTION constraints to trim the range being considered
1622+
* to just what was updated/deleted.
16211623
*/
16221624
void
16231625
FindFKPeriodOpers(Oidopclass,
16241626
Oid*containedbyoperoid,
1625-
Oid*aggedcontainedbyoperoid)
1627+
Oid*aggedcontainedbyoperoid,
1628+
Oid*intersectoperoid)
16261629
{
16271630
Oidopfamily=InvalidOid;
16281631
Oidopcintype=InvalidOid;
@@ -1663,6 +1666,18 @@ FindFKPeriodOpers(Oid opclass,
16631666
COMPARE_CONTAINED_BY,
16641667
aggedcontainedbyoperoid,
16651668
&strat);
1669+
1670+
switch (opcintype)
1671+
{
1672+
caseANYRANGEOID:
1673+
*intersectoperoid=OID_RANGE_INTERSECT_RANGE_OP;
1674+
break;
1675+
caseANYMULTIRANGEOID:
1676+
*intersectoperoid=OID_MULTIRANGE_INTERSECT_MULTIRANGE_OP;
1677+
break;
1678+
default:
1679+
elog(ERROR,"unexpected opcintype: %u",opcintype);
1680+
}
16661681
}
16671682

16681683
/*

‎src/backend/commands/tablecmds.c

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -10260,8 +10260,10 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
1026010260
{
1026110261
Oidperiodoperoid;
1026210262
Oidaggedperiodoperoid;
10263+
Oidintersectoperoid;
1026310264

10264-
FindFKPeriodOpers(opclasses[numpks - 1], &periodoperoid, &aggedperiodoperoid);
10265+
FindFKPeriodOpers(opclasses[numpks - 1], &periodoperoid, &aggedperiodoperoid,
10266+
&intersectoperoid);
1026510267
}
1026610268

1026710269
/* First, create the constraint catalog entry itself. */

‎src/backend/utils/adt/ri_triggers.c

Lines changed: 92 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -129,6 +129,7 @@ typedef struct RI_ConstraintInfo
129129
Oidff_eq_oprs[RI_MAX_NUMKEYS];/* equality operators (FK = FK) */
130130
Oidperiod_contained_by_oper;/* anyrange <@ anyrange */
131131
Oidagged_period_contained_by_oper;/* fkattr <@ range_agg(pkattr) */
132+
Oidperiod_intersect_oper;/* anyrange * anyrange */
132133
dlist_nodevalid_link;/* Link in list of valid entries */
133134
}RI_ConstraintInfo;
134135

@@ -734,8 +735,11 @@ ri_restrict(TriggerData *trigdata, bool is_no_action)
734735
* not do anything. However, this check should only be made in the NO
735736
* ACTION case; in RESTRICT cases we don't wish to allow another row to be
736737
* substituted.
738+
*
739+
* If the foreign key has PERIOD, we incorporate looking for replacement
740+
* rows in the main SQL query below, so we needn't do it here.
737741
*/
738-
if (is_no_action&&
742+
if (is_no_action&& !riinfo->hasperiod&&
739743
ri_Check_Pk_Match(pk_rel,fk_rel,oldslot,riinfo))
740744
{
741745
table_close(fk_rel,RowShareLock);
@@ -753,8 +757,10 @@ ri_restrict(TriggerData *trigdata, bool is_no_action)
753757
if ((qplan=ri_FetchPreparedPlan(&qkey))==NULL)
754758
{
755759
StringInfoDataquerybuf;
760+
charpkrelname[MAX_QUOTED_REL_NAME_LEN];
756761
charfkrelname[MAX_QUOTED_REL_NAME_LEN];
757762
charattname[MAX_QUOTED_NAME_LEN];
763+
charperiodattname[MAX_QUOTED_NAME_LEN];
758764
charparamname[16];
759765
constchar*querysep;
760766
Oidqueryoids[RI_MAX_NUMKEYS];
@@ -790,6 +796,89 @@ ri_restrict(TriggerData *trigdata, bool is_no_action)
790796
querysep="AND";
791797
queryoids[i]=pk_type;
792798
}
799+
800+
/*----------
801+
* For temporal foreign keys, a reference could still be valid if the
802+
* referenced range didn't change too much. Also if a referencing
803+
* range extends past the current PK row, we don't want to check that
804+
* part: some other PK row should fulfill it. We only want to check
805+
* the part matching the PK record we've changed. Therefore to find
806+
* invalid records we do this:
807+
*
808+
* SELECT 1 FROM [ONLY] <fktable> x WHERE $1 = x.fkatt1 [AND ...]
809+
* -- begin temporal
810+
* AND $n && x.fkperiod
811+
* AND NOT coalesce((x.fkperiod * $n) <@
812+
* (SELECT range_agg(r)
813+
* FROM (SELECT y.pkperiod r
814+
* FROM [ONLY] <pktable> y
815+
* WHERE $1 = y.pkatt1 [AND ...] AND $n && y.pkperiod
816+
* FOR KEY SHARE OF y) y2), false)
817+
* -- end temporal
818+
* FOR KEY SHARE OF x
819+
*
820+
* We need the coalesce in case the first subquery returns no rows.
821+
* We need the second subquery because FOR KEY SHARE doesn't support
822+
* aggregate queries.
823+
*/
824+
if (riinfo->hasperiod&&is_no_action)
825+
{
826+
Oidpk_period_type=RIAttType(pk_rel,riinfo->pk_attnums[riinfo->nkeys-1]);
827+
Oidfk_period_type=RIAttType(fk_rel,riinfo->fk_attnums[riinfo->nkeys-1]);
828+
StringInfoDataintersectbuf;
829+
StringInfoDatareplacementsbuf;
830+
char*pk_only=pk_rel->rd_rel->relkind==RELKIND_PARTITIONED_TABLE ?
831+
"" :"ONLY ";
832+
833+
quoteOneName(attname,RIAttName(fk_rel,riinfo->fk_attnums[riinfo->nkeys-1]));
834+
sprintf(paramname,"$%d",riinfo->nkeys);
835+
836+
appendStringInfoString(&querybuf," AND NOT coalesce(");
837+
838+
/* Intersect the fk with the old pk range */
839+
initStringInfo(&intersectbuf);
840+
appendStringInfoString(&intersectbuf,"(");
841+
ri_GenerateQual(&intersectbuf,"",
842+
attname,fk_period_type,
843+
riinfo->period_intersect_oper,
844+
paramname,pk_period_type);
845+
appendStringInfoString(&intersectbuf,")");
846+
847+
/* Find the remaining history */
848+
initStringInfo(&replacementsbuf);
849+
appendStringInfoString(&replacementsbuf,"(SELECT pg_catalog.range_agg(r) FROM ");
850+
851+
quoteOneName(periodattname,RIAttName(pk_rel,riinfo->pk_attnums[riinfo->nkeys-1]));
852+
quoteRelationName(pkrelname,pk_rel);
853+
appendStringInfo(&replacementsbuf,"(SELECT y.%s r FROM %s%s y",
854+
periodattname,pk_only,pkrelname);
855+
856+
/* Restrict pk rows to what matches */
857+
querysep="WHERE";
858+
for (inti=0;i<riinfo->nkeys;i++)
859+
{
860+
Oidpk_type=RIAttType(pk_rel,riinfo->pk_attnums[i]);
861+
862+
quoteOneName(attname,
863+
RIAttName(pk_rel,riinfo->pk_attnums[i]));
864+
sprintf(paramname,"$%d",i+1);
865+
ri_GenerateQual(&replacementsbuf,querysep,
866+
paramname,pk_type,
867+
riinfo->pp_eq_oprs[i],
868+
attname,pk_type);
869+
querysep="AND";
870+
queryoids[i]=pk_type;
871+
}
872+
appendStringInfoString(&replacementsbuf," FOR KEY SHARE OF y) y2)");
873+
874+
ri_GenerateQual(&querybuf,"",
875+
intersectbuf.data,fk_period_type,
876+
riinfo->agged_period_contained_by_oper,
877+
replacementsbuf.data,ANYMULTIRANGEOID);
878+
/* end of coalesce: */
879+
appendStringInfoString(&querybuf,", false)");
880+
}
881+
793882
appendStringInfoString(&querybuf," FOR KEY SHARE OF x");
794883

795884
/* Prepare and save the plan */
@@ -2251,7 +2340,8 @@ ri_LoadConstraintInfo(Oid constraintOid)
22512340

22522341
FindFKPeriodOpers(opclass,
22532342
&riinfo->period_contained_by_oper,
2254-
&riinfo->agged_period_contained_by_oper);
2343+
&riinfo->agged_period_contained_by_oper,
2344+
&riinfo->period_intersect_oper);
22552345
}
22562346

22572347
ReleaseSysCache(tup);

‎src/include/catalog/pg_constraint.h

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -290,7 +290,8 @@ extern void DeconstructFkConstraintRow(HeapTuple tuple, int *numfks,
290290
int*num_fk_del_set_cols,AttrNumber*fk_del_set_cols);
291291
externvoidFindFKPeriodOpers(Oidopclass,
292292
Oid*containedbyoperoid,
293-
Oid*aggedcontainedbyoperoid);
293+
Oid*aggedcontainedbyoperoid,
294+
Oid*intersectoperoid);
294295

295296
externboolcheck_functional_grouping(Oidrelid,
296297
Indexvarno,Indexvarlevelsup,

‎src/include/catalog/pg_operator.dat

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -3150,7 +3150,8 @@
31503150
{ oid => '3899', descr => 'range difference',
31513151
oprname => '-', oprleft => 'anyrange', oprright => 'anyrange',
31523152
oprresult => 'anyrange', oprcode => 'range_minus' },
3153-
{ oid => '3900', descr => 'range intersection',
3153+
{ oid => '3900', oid_symbol => 'OID_RANGE_INTERSECT_RANGE_OP',
3154+
descr => 'range intersection',
31543155
oprname => '*', oprleft => 'anyrange', oprright => 'anyrange',
31553156
oprresult => 'anyrange', oprcom => '*(anyrange,anyrange)',
31563157
oprcode => 'range_intersect' },
@@ -3417,7 +3418,8 @@
34173418
{ oid => '4393', descr => 'multirange minus',
34183419
oprname => '-', oprleft => 'anymultirange', oprright => 'anymultirange',
34193420
oprresult => 'anymultirange', oprcode => 'multirange_minus' },
3420-
{ oid => '4394', descr => 'multirange intersect',
3421+
{ oid => '4394', oid_symbol => 'OID_MULTIRANGE_INTERSECT_MULTIRANGE_OP',
3422+
descr => 'multirange intersect',
34213423
oprname => '*', oprleft => 'anymultirange', oprright => 'anymultirange',
34223424
oprresult => 'anymultirange', oprcom => '*(anymultirange,anymultirange)',
34233425
oprcode => 'multirange_intersect' },

‎src/test/regress/expected/without_overlaps.out

Lines changed: 62 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1704,6 +1704,37 @@ UPDATE temporal_rng
17041704
SET valid_at = CASE WHEN lower(valid_at) = '2018-01-01' THEN daterange('2018-01-01', '2018-01-05')
17051705
WHEN lower(valid_at) = '2018-02-01' THEN daterange('2018-01-05', '2018-03-01') END
17061706
WHERE id = '[6,7)';
1707+
-- a PK update shrinking the referenced range but still valid:
1708+
-- There are two references: one fulfilled by the first pk row,
1709+
-- the other fulfilled by both pk rows combined.
1710+
INSERT INTO temporal_rng (id, valid_at) VALUES
1711+
('[1,2)', daterange('2018-01-01', '2018-03-01')),
1712+
('[1,2)', daterange('2018-03-01', '2018-06-01'));
1713+
INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES
1714+
('[1,2)', daterange('2018-01-15', '2018-02-01'), '[1,2)'),
1715+
('[2,3)', daterange('2018-01-15', '2018-05-01'), '[1,2)');
1716+
UPDATE temporal_rng SET valid_at = daterange('2018-01-15', '2018-03-01')
1717+
WHERE id = '[1,2)' AND valid_at @> '2018-01-15'::date;
1718+
-- a PK update growing the referenced range is fine:
1719+
UPDATE temporal_rng SET valid_at = daterange('2018-01-01', '2018-03-01')
1720+
WHERE id = '[1,2)' AND valid_at @> '2018-01-25'::date;
1721+
-- a PK update shrinking the referenced range and changing the id invalidates the whole range (error):
1722+
UPDATE temporal_rng SET id = '[2,3)', valid_at = daterange('2018-01-15', '2018-03-01')
1723+
WHERE id = '[1,2)' AND valid_at @> '2018-01-15'::date;
1724+
ERROR: update or delete on table "temporal_rng" violates foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng"
1725+
DETAIL: Key (id, valid_at)=([1,2), [2018-01-01,2018-03-01)) is still referenced from table "temporal_fk_rng2rng".
1726+
-- a PK update changing only the id invalidates the whole range (error):
1727+
UPDATE temporal_rng SET id = '[2,3)'
1728+
WHERE id = '[1,2)' AND valid_at @> '2018-01-15'::date;
1729+
ERROR: update or delete on table "temporal_rng" violates foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng"
1730+
DETAIL: Key (id, valid_at)=([1,2), [2018-01-01,2018-03-01)) is still referenced from table "temporal_fk_rng2rng".
1731+
-- a PK update that loses time from both ends, but is still valid:
1732+
INSERT INTO temporal_rng (id, valid_at) VALUES
1733+
('[2,3)', daterange('2018-01-01', '2018-03-01'));
1734+
INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES
1735+
('[5,6)', daterange('2018-01-15', '2018-02-01'), '[2,3)');
1736+
UPDATE temporal_rng SET valid_at = daterange('2018-01-15', '2018-02-15')
1737+
WHERE id = '[2,3)';
17071738
-- a PK update that fails because both are referenced:
17081739
UPDATE temporal_rng SET valid_at = daterange('2016-01-01', '2016-02-01')
17091740
WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01');
@@ -2196,6 +2227,37 @@ UPDATE temporal_mltrng
21962227
SET valid_at = CASE WHEN lower(valid_at) = '2018-01-01' THEN datemultirange(daterange('2018-01-01', '2018-01-05'))
21972228
WHEN lower(valid_at) = '2018-02-01' THEN datemultirange(daterange('2018-01-05', '2018-03-01')) END
21982229
WHERE id = '[6,7)';
2230+
-- a PK update shrinking the referenced multirange but still valid:
2231+
-- There are two references: one fulfilled by the first pk row,
2232+
-- the other fulfilled by both pk rows combined.
2233+
INSERT INTO temporal_mltrng (id, valid_at) VALUES
2234+
('[1,2)', datemultirange(daterange('2018-01-01', '2018-03-01'))),
2235+
('[1,2)', datemultirange(daterange('2018-03-01', '2018-06-01')));
2236+
INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES
2237+
('[1,2)', datemultirange(daterange('2018-01-15', '2018-02-01')), '[1,2)'),
2238+
('[2,3)', datemultirange(daterange('2018-01-15', '2018-05-01')), '[1,2)');
2239+
UPDATE temporal_mltrng SET valid_at = datemultirange(daterange('2018-01-15', '2018-03-01'))
2240+
WHERE id = '[1,2)' AND valid_at @> '2018-01-15'::date;
2241+
-- a PK update growing the referenced multirange is fine:
2242+
UPDATE temporal_mltrng SET valid_at = datemultirange(daterange('2018-01-01', '2018-03-01'))
2243+
WHERE id = '[1,2)' AND valid_at @> '2018-01-25'::date;
2244+
-- a PK update shrinking the referenced multirange and changing the id invalidates the whole multirange (error):
2245+
UPDATE temporal_mltrng SET id = '[2,3)', valid_at = datemultirange(daterange('2018-01-15', '2018-03-01'))
2246+
WHERE id = '[1,2)' AND valid_at @> '2018-01-15'::date;
2247+
ERROR: update or delete on table "temporal_mltrng" violates foreign key constraint "temporal_fk_mltrng2mltrng_fk" on table "temporal_fk_mltrng2mltrng"
2248+
DETAIL: Key (id, valid_at)=([1,2), {[2018-01-01,2018-03-01)}) is still referenced from table "temporal_fk_mltrng2mltrng".
2249+
-- a PK update changing only the id invalidates the whole multirange (error):
2250+
UPDATE temporal_mltrng SET id = '[2,3)'
2251+
WHERE id = '[1,2)' AND valid_at @> '2018-01-15'::date;
2252+
ERROR: update or delete on table "temporal_mltrng" violates foreign key constraint "temporal_fk_mltrng2mltrng_fk" on table "temporal_fk_mltrng2mltrng"
2253+
DETAIL: Key (id, valid_at)=([1,2), {[2018-01-01,2018-03-01)}) is still referenced from table "temporal_fk_mltrng2mltrng".
2254+
-- a PK update that loses time from both ends, but is still valid:
2255+
INSERT INTO temporal_mltrng (id, valid_at) VALUES
2256+
('[2,3)', datemultirange(daterange('2018-01-01', '2018-03-01')));
2257+
INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES
2258+
('[5,6)', datemultirange(daterange('2018-01-15', '2018-02-01')), '[2,3)');
2259+
UPDATE temporal_mltrng SET valid_at = datemultirange(daterange('2018-01-15', '2018-02-15'))
2260+
WHERE id = '[2,3)';
21992261
-- a PK update that fails because both are referenced:
22002262
UPDATE temporal_mltrng SET valid_at = datemultirange(daterange('2016-01-01', '2016-02-01'))
22012263
WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01'));

‎src/test/regress/sql/without_overlaps.sql

Lines changed: 54 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1249,6 +1249,33 @@ UPDATE temporal_rng
12491249
SET valid_at= CASE WHENlower(valid_at)='2018-01-01' THEN daterange('2018-01-01','2018-01-05')
12501250
WHENlower(valid_at)='2018-02-01' THEN daterange('2018-01-05','2018-03-01') END
12511251
WHERE id='[6,7)';
1252+
-- a PK update shrinking the referenced range but still valid:
1253+
-- There are two references: one fulfilled by the first pk row,
1254+
-- the other fulfilled by both pk rows combined.
1255+
INSERT INTO temporal_rng (id, valid_at)VALUES
1256+
('[1,2)', daterange('2018-01-01','2018-03-01')),
1257+
('[1,2)', daterange('2018-03-01','2018-06-01'));
1258+
INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id)VALUES
1259+
('[1,2)', daterange('2018-01-15','2018-02-01'),'[1,2)'),
1260+
('[2,3)', daterange('2018-01-15','2018-05-01'),'[1,2)');
1261+
UPDATE temporal_rngSET valid_at= daterange('2018-01-15','2018-03-01')
1262+
WHERE id='[1,2)'AND valid_at @>'2018-01-15'::date;
1263+
-- a PK update growing the referenced range is fine:
1264+
UPDATE temporal_rngSET valid_at= daterange('2018-01-01','2018-03-01')
1265+
WHERE id='[1,2)'AND valid_at @>'2018-01-25'::date;
1266+
-- a PK update shrinking the referenced range and changing the id invalidates the whole range (error):
1267+
UPDATE temporal_rngSET id='[2,3)', valid_at= daterange('2018-01-15','2018-03-01')
1268+
WHERE id='[1,2)'AND valid_at @>'2018-01-15'::date;
1269+
-- a PK update changing only the id invalidates the whole range (error):
1270+
UPDATE temporal_rngSET id='[2,3)'
1271+
WHERE id='[1,2)'AND valid_at @>'2018-01-15'::date;
1272+
-- a PK update that loses time from both ends, but is still valid:
1273+
INSERT INTO temporal_rng (id, valid_at)VALUES
1274+
('[2,3)', daterange('2018-01-01','2018-03-01'));
1275+
INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id)VALUES
1276+
('[5,6)', daterange('2018-01-15','2018-02-01'),'[2,3)');
1277+
UPDATE temporal_rngSET valid_at= daterange('2018-01-15','2018-02-15')
1278+
WHERE id='[2,3)';
12521279
-- a PK update that fails because both are referenced:
12531280
UPDATE temporal_rngSET valid_at= daterange('2016-01-01','2016-02-01')
12541281
WHERE id='[5,6)'AND valid_at= daterange('2018-01-01','2018-02-01');
@@ -1710,6 +1737,33 @@ UPDATE temporal_mltrng
17101737
SET valid_at= CASE WHENlower(valid_at)='2018-01-01' THEN datemultirange(daterange('2018-01-01','2018-01-05'))
17111738
WHENlower(valid_at)='2018-02-01' THEN datemultirange(daterange('2018-01-05','2018-03-01')) END
17121739
WHERE id='[6,7)';
1740+
-- a PK update shrinking the referenced multirange but still valid:
1741+
-- There are two references: one fulfilled by the first pk row,
1742+
-- the other fulfilled by both pk rows combined.
1743+
INSERT INTO temporal_mltrng (id, valid_at)VALUES
1744+
('[1,2)', datemultirange(daterange('2018-01-01','2018-03-01'))),
1745+
('[1,2)', datemultirange(daterange('2018-03-01','2018-06-01')));
1746+
INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id)VALUES
1747+
('[1,2)', datemultirange(daterange('2018-01-15','2018-02-01')),'[1,2)'),
1748+
('[2,3)', datemultirange(daterange('2018-01-15','2018-05-01')),'[1,2)');
1749+
UPDATE temporal_mltrngSET valid_at= datemultirange(daterange('2018-01-15','2018-03-01'))
1750+
WHERE id='[1,2)'AND valid_at @>'2018-01-15'::date;
1751+
-- a PK update growing the referenced multirange is fine:
1752+
UPDATE temporal_mltrngSET valid_at= datemultirange(daterange('2018-01-01','2018-03-01'))
1753+
WHERE id='[1,2)'AND valid_at @>'2018-01-25'::date;
1754+
-- a PK update shrinking the referenced multirange and changing the id invalidates the whole multirange (error):
1755+
UPDATE temporal_mltrngSET id='[2,3)', valid_at= datemultirange(daterange('2018-01-15','2018-03-01'))
1756+
WHERE id='[1,2)'AND valid_at @>'2018-01-15'::date;
1757+
-- a PK update changing only the id invalidates the whole multirange (error):
1758+
UPDATE temporal_mltrngSET id='[2,3)'
1759+
WHERE id='[1,2)'AND valid_at @>'2018-01-15'::date;
1760+
-- a PK update that loses time from both ends, but is still valid:
1761+
INSERT INTO temporal_mltrng (id, valid_at)VALUES
1762+
('[2,3)', datemultirange(daterange('2018-01-01','2018-03-01')));
1763+
INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id)VALUES
1764+
('[5,6)', datemultirange(daterange('2018-01-15','2018-02-01')),'[2,3)');
1765+
UPDATE temporal_mltrngSET valid_at= datemultirange(daterange('2018-01-15','2018-02-15'))
1766+
WHERE id='[2,3)';
17131767
-- a PK update that fails because both are referenced:
17141768
UPDATE temporal_mltrngSET valid_at= datemultirange(daterange('2016-01-01','2016-02-01'))
17151769
WHERE id='[5,6)'AND valid_at= datemultirange(daterange('2018-01-01','2018-02-01'));

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp