From 984afe98ad62a39003e2373b3feb223755f42e64 Mon Sep 17 00:00:00 2001 From: "Paul A. Jungwirth" Date: Sat, 30 Dec 2023 23:10:59 -0800 Subject: [PATCH v27 5/8] Add support funcs for FOR PORTION OF - Adds intersect support procs. These just call the existing intersect functions, but they let us compute the portion of a row that is updated/deleted in a FOR PORTION command. - Adds {multi,}range_without_portion support procs These return an array of their input type and work like minus but don't fail on splits. They never contain empty elements. We will use this to compute FOR PORTION OF leftovers. --- contrib/bloom/blvalidate.c | 2 +- doc/src/sgml/catalogs.sgml | 2 +- doc/src/sgml/gist.sgml | 155 ++++++++++++++++- doc/src/sgml/xindex.sgml | 14 +- src/backend/access/brin/brin_validate.c | 8 +- src/backend/access/gin/ginvalidate.c | 12 +- src/backend/access/gist/gistvalidate.c | 31 ++-- src/backend/access/index/amvalidate.c | 6 +- src/backend/access/nbtree/nbtvalidate.c | 8 +- src/backend/access/spgist/spgvalidate.c | 8 +- src/backend/utils/adt/multirangetypes.c | 71 ++++++++ src/backend/utils/adt/rangetypes.c | 163 ++++++++++++++++++ src/include/access/amvalidate.h | 4 +- src/include/access/gist.h | 4 +- src/include/catalog/pg_amproc.dat | 15 ++ src/include/catalog/pg_proc.dat | 8 + src/include/utils/rangetypes.h | 2 + src/test/regress/expected/multirangetypes.out | 116 +++++++++++++ src/test/regress/expected/rangetypes.out | 54 ++++++ src/test/regress/sql/multirangetypes.sql | 22 +++ src/test/regress/sql/rangetypes.sql | 10 ++ 21 files changed, 676 insertions(+), 39 deletions(-) diff --git a/contrib/bloom/blvalidate.c b/contrib/bloom/blvalidate.c index 88c5a791975..9e39e39e174 100644 --- a/contrib/bloom/blvalidate.c +++ b/contrib/bloom/blvalidate.c @@ -105,7 +105,7 @@ blvalidate(Oid opclassoid) switch (procform->amprocnum) { case BLOOM_HASH_PROC: - ok = check_amproc_signature(procform->amproc, INT4OID, false, + ok = check_amproc_signature(procform->amproc, INT4OID, false, false, 1, 1, opckeytype); break; case BLOOM_OPTIONS_PROC: diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index 880f717b103..6f6c8a52049 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -865,7 +865,7 @@ The B-tree operator family this entry sorts according to, if an - ordering operator; zero if a search operator + ordering operator; zero if a search operator or portion operator diff --git a/doc/src/sgml/gist.sgml b/doc/src/sgml/gist.sgml index e67dd4b859f..bbf7841807d 100644 --- a/doc/src/sgml/gist.sgml +++ b/doc/src/sgml/gist.sgml @@ -272,7 +272,7 @@ CREATE INDEX ON my_table USING GIST (my_inet_column inet_ops); There are five methods that an index operator class for - GiST must provide, and eight that are optional. + GiST must provide, and ten that are optional. Correctness of the index is ensured by proper implementation of the same, consistent and union methods, while efficiency (size and speed) of the @@ -304,6 +304,12 @@ CREATE INDEX ON my_table USING GIST (my_inet_column inet_ops); temporal foreign keys to combined referenced rows with the same non-WITHOUT OVERLAPS value(s) into one WITHOUT OVERLAPS span to compare against referencing rows. + The optional fourteenth method intersect is used by + FOR PORTION OF to compute the new bounds of the updated/ + deleted record. + The optional fifteenth method without_portion is used by + FOR PORTION OF to compute the leftover records outside the + targeted bounds. @@ -1352,6 +1358,153 @@ my_range_agg_finalfn(PG_FUNCTION_ARGS) PG_RETURN_MULTIRANGE_P(make_multirange(mltrngtypoid, typcache->rngtype, range_count, ranges)); } + + + + + + intersect + + + Given two values of this opclass, it returns their intersection. + + + This is used for temporal update commands to compute the + new bounds of the changed row. + + + + The SQL declaration of the function must look like + this (using my_intersect as an example): + + +CREATE OR REPLACE FUNCTION my_range_intersect(anyrange, anyrange) +RETURNS anyrange +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT; + + + + + The matching code in the C module could then follow this example: + + +Datum +my_range_intersect(PG_FUNCTION_ARGS) +{ + RangeType *r1 = PG_GETARG_RANGE_P(0); + RangeType *r2 = PG_GETARG_RANGE_P(1); + TypeCacheEntry *typcache; + + /* Different types should be prevented by ANYRANGE matching rules */ + if (RangeTypeGetOid(r1) != RangeTypeGetOid(r2)) elog(ERROR, "range types do not match"); + + typcache = range_get_typcache(fcinfo, RangeTypeGetOid(r1)); + + PG_RETURN_RANGE_P(range_intersect_internal(typcache, r1, r2)); +} + + + + + + without_portion + + + Given two values of this opclass, it subtracts the second for the first + and returns an array of the results. + + + This is used for temporal update/delete commands to compute the + bounds of the untouched duration. + + + + The SQL declaration of the function must look like + this (using my_range_without_portion as an example): + + +CREATE OR REPLACE FUNCTION my_range_without_portion(anyrange, anyrange) +RETURNS anyarray +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT; + + + + + The matching code in the C module could then follow this example: + + +Datum +range_without_portion(PG_FUNCTION_ARGS) +{ + typedef struct { + RangeType *rs[2]; + int n; + } range_without_portion_fctx; + + FuncCallContext *funcctx; + range_without_portion_fctx *fctx; + MemoryContext oldcontext; + + /* stuff done only on the first call of the function */ + if (SRF_IS_FIRSTCALL()) + { + RangeType *r1; + RangeType *r2; + Oid rngtypid; + TypeCacheEntry *typcache; + + /* create a function context for cross-call persistence */ + funcctx = SRF_FIRSTCALL_INIT(); + + /* + * switch to memory context appropriate for multiple function calls + */ + oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx); + + r1 = PG_GETARG_RANGE_P(0); + r2 = PG_GETARG_RANGE_P(1); + + /* Different types should be prevented by ANYRANGE matching rules */ + if (RangeTypeGetOid(r1) != RangeTypeGetOid(r2)) + elog(ERROR, "range types do not match"); + + /* allocate memory for user context */ + fctx = (range_without_portion_fctx *) palloc(sizeof(range_without_portion_fctx)); + + /* + * Initialize state. + * We can't store the range typcache in fn_extra because the caller + * uses that for the SRF state. + */ + rngtypid = RangeTypeGetOid(r1); + typcache = lookup_type_cache(rngtypid, TYPECACHE_RANGE_INFO); + if (typcache->rngelemtype == NULL) + elog(ERROR, "type %u is not a range type", rngtypid); + range_without_portion_internal(typcache, r1, r2, fctx->rs, &fctx->n); + + funcctx->user_fctx = fctx; + MemoryContextSwitchTo(oldcontext); + } + + /* stuff done on every call of the function */ + funcctx = SRF_PERCALL_SETUP(); + fctx = funcctx->user_fctx; + + if (funcctx->call_cntr < fctx->n) + { + /* + * We must keep these on separate lines + * because SRF_RETURN_NEXT does call_cntr++: + */ + RangeType *ret = fctx->rs[funcctx->call_cntr]; + SRF_RETURN_NEXT(funcctx, RangeTypePGetDatum(ret)); + } + else + /* do when there is no more left */ + SRF_RETURN_DONE(funcctx); +} diff --git a/doc/src/sgml/xindex.sgml b/doc/src/sgml/xindex.sgml index 93df136eba3..d4d0f6dc685 100644 --- a/doc/src/sgml/xindex.sgml +++ b/doc/src/sgml/xindex.sgml @@ -508,7 +508,7 @@ - GiST indexes have thirteen support functions, eight of which are optional, + GiST indexes have fifteen support functions, ten of which are optional, as shown in . (For more information see .) @@ -602,6 +602,18 @@ part 13 + + intersect + computes intersection with FOR PORTION OF + bounds + 14 + + + without_portion + computes remaining duration(s) outside + FOR PORTION OF bounds + 15 + diff --git a/src/backend/access/brin/brin_validate.c b/src/backend/access/brin/brin_validate.c index 539ac9cb064..082f96b847d 100644 --- a/src/backend/access/brin/brin_validate.c +++ b/src/backend/access/brin/brin_validate.c @@ -87,21 +87,21 @@ brinvalidate(Oid opclassoid) switch (procform->amprocnum) { case BRIN_PROCNUM_OPCINFO: - ok = check_amproc_signature(procform->amproc, INTERNALOID, true, + ok = check_amproc_signature(procform->amproc, INTERNALOID, false, true, 1, 1, INTERNALOID); break; case BRIN_PROCNUM_ADDVALUE: - ok = check_amproc_signature(procform->amproc, BOOLOID, true, + ok = check_amproc_signature(procform->amproc, BOOLOID, false, true, 4, 4, INTERNALOID, INTERNALOID, INTERNALOID, INTERNALOID); break; case BRIN_PROCNUM_CONSISTENT: - ok = check_amproc_signature(procform->amproc, BOOLOID, true, + ok = check_amproc_signature(procform->amproc, BOOLOID, false, true, 3, 4, INTERNALOID, INTERNALOID, INTERNALOID, INT4OID); break; case BRIN_PROCNUM_UNION: - ok = check_amproc_signature(procform->amproc, BOOLOID, true, + ok = check_amproc_signature(procform->amproc, BOOLOID, false, true, 3, 3, INTERNALOID, INTERNALOID, INTERNALOID); break; diff --git a/src/backend/access/gin/ginvalidate.c b/src/backend/access/gin/ginvalidate.c index 13cf390bb3d..5a11e0cea25 100644 --- a/src/backend/access/gin/ginvalidate.c +++ b/src/backend/access/gin/ginvalidate.c @@ -106,37 +106,37 @@ ginvalidate(Oid opclassoid) switch (procform->amprocnum) { case GIN_COMPARE_PROC: - ok = check_amproc_signature(procform->amproc, INT4OID, false, + ok = check_amproc_signature(procform->amproc, INT4OID, false, false, 2, 2, opckeytype, opckeytype); break; case GIN_EXTRACTVALUE_PROC: /* Some opclasses omit nullFlags */ - ok = check_amproc_signature(procform->amproc, INTERNALOID, false, + ok = check_amproc_signature(procform->amproc, INTERNALOID, false, false, 2, 3, opcintype, INTERNALOID, INTERNALOID); break; case GIN_EXTRACTQUERY_PROC: /* Some opclasses omit nullFlags and searchMode */ - ok = check_amproc_signature(procform->amproc, INTERNALOID, false, + ok = check_amproc_signature(procform->amproc, INTERNALOID, false, false, 5, 7, opcintype, INTERNALOID, INT2OID, INTERNALOID, INTERNALOID, INTERNALOID, INTERNALOID); break; case GIN_CONSISTENT_PROC: /* Some opclasses omit queryKeys and nullFlags */ - ok = check_amproc_signature(procform->amproc, BOOLOID, false, + ok = check_amproc_signature(procform->amproc, BOOLOID, false, false, 6, 8, INTERNALOID, INT2OID, opcintype, INT4OID, INTERNALOID, INTERNALOID, INTERNALOID, INTERNALOID); break; case GIN_COMPARE_PARTIAL_PROC: - ok = check_amproc_signature(procform->amproc, INT4OID, false, + ok = check_amproc_signature(procform->amproc, INT4OID, false, false, 4, 4, opckeytype, opckeytype, INT2OID, INTERNALOID); break; case GIN_TRICONSISTENT_PROC: - ok = check_amproc_signature(procform->amproc, CHAROID, false, + ok = check_amproc_signature(procform->amproc, CHAROID, false, false, 7, 7, INTERNALOID, INT2OID, opcintype, INT4OID, INTERNALOID, INTERNALOID, diff --git a/src/backend/access/gist/gistvalidate.c b/src/backend/access/gist/gistvalidate.c index ac3d9e50f50..0a4338e8b84 100644 --- a/src/backend/access/gist/gistvalidate.c +++ b/src/backend/access/gist/gistvalidate.c @@ -107,36 +107,36 @@ gistvalidate(Oid opclassoid) switch (procform->amprocnum) { case GIST_CONSISTENT_PROC: - ok = check_amproc_signature(procform->amproc, BOOLOID, false, + ok = check_amproc_signature(procform->amproc, BOOLOID, false, false, 5, 5, INTERNALOID, opcintype, INT2OID, OIDOID, INTERNALOID); break; case GIST_UNION_PROC: - ok = check_amproc_signature(procform->amproc, opckeytype, false, + ok = check_amproc_signature(procform->amproc, opckeytype, false, false, 2, 2, INTERNALOID, INTERNALOID); break; case GIST_COMPRESS_PROC: case GIST_DECOMPRESS_PROC: case GIST_FETCH_PROC: - ok = check_amproc_signature(procform->amproc, INTERNALOID, true, + ok = check_amproc_signature(procform->amproc, INTERNALOID, false, true, 1, 1, INTERNALOID); break; case GIST_PENALTY_PROC: - ok = check_amproc_signature(procform->amproc, INTERNALOID, true, + ok = check_amproc_signature(procform->amproc, INTERNALOID, false, true, 3, 3, INTERNALOID, INTERNALOID, INTERNALOID); break; case GIST_PICKSPLIT_PROC: - ok = check_amproc_signature(procform->amproc, INTERNALOID, true, + ok = check_amproc_signature(procform->amproc, INTERNALOID, false, true, 2, 2, INTERNALOID, INTERNALOID); break; case GIST_EQUAL_PROC: - ok = check_amproc_signature(procform->amproc, INTERNALOID, false, + ok = check_amproc_signature(procform->amproc, INTERNALOID, false, false, 3, 3, opckeytype, opckeytype, INTERNALOID); break; case GIST_DISTANCE_PROC: - ok = check_amproc_signature(procform->amproc, FLOAT8OID, false, + ok = check_amproc_signature(procform->amproc, FLOAT8OID, false, false, 5, 5, INTERNALOID, opcintype, INT2OID, OIDOID, INTERNALOID); break; @@ -144,18 +144,26 @@ gistvalidate(Oid opclassoid) ok = check_amoptsproc_signature(procform->amproc); break; case GIST_SORTSUPPORT_PROC: - ok = check_amproc_signature(procform->amproc, VOIDOID, true, + ok = check_amproc_signature(procform->amproc, VOIDOID, false, true, 1, 1, INTERNALOID); break; case GIST_STRATNUM_PROC: - ok = check_amproc_signature(procform->amproc, INT2OID, true, + ok = check_amproc_signature(procform->amproc, INT2OID, false, true, 1, 1, INT2OID); break; case GIST_REFERENCED_AGG_PROC: - ok = check_amproc_signature(procform->amproc, InvalidOid, false, + ok = check_amproc_signature(procform->amproc, InvalidOid, false, false, 1, 1, opcintype) && check_amproc_is_aggregate(procform->amproc); break; + case GIST_INTERSECT_PROC: + ok = check_amproc_signature(procform->amproc, InvalidOid, false, true, + 2, 2, opcintype, opcintype); + break; + case GIST_WITHOUT_PORTION_PROC: + ok = check_amproc_signature(procform->amproc, opcintype, true, true, + 2, 2, opcintype, opcintype); + break; default: ereport(INFO, (errcode(ERRCODE_INVALID_OBJECT_DEFINITION), @@ -277,7 +285,8 @@ gistvalidate(Oid opclassoid) if (i == GIST_DISTANCE_PROC || i == GIST_FETCH_PROC || i == GIST_COMPRESS_PROC || i == GIST_DECOMPRESS_PROC || i == GIST_OPTIONS_PROC || i == GIST_SORTSUPPORT_PROC || - i == GIST_STRATNUM_PROC || i == GIST_REFERENCED_AGG_PROC) + i == GIST_STRATNUM_PROC || i == GIST_REFERENCED_AGG_PROC || + i == GIST_INTERSECT_PROC || i == GIST_WITHOUT_PORTION_PROC) continue; /* optional methods */ ereport(INFO, (errcode(ERRCODE_INVALID_OBJECT_DEFINITION), diff --git a/src/backend/access/index/amvalidate.c b/src/backend/access/index/amvalidate.c index 9eb1b172ae1..cc27cc28ab5 100644 --- a/src/backend/access/index/amvalidate.c +++ b/src/backend/access/index/amvalidate.c @@ -166,7 +166,7 @@ check_amproc_is_aggregate(Oid funcid) * unless it is InvalidOid. */ bool -check_amproc_signature(Oid funcid, Oid restype, bool exact, +check_amproc_signature(Oid funcid, Oid restype, bool retset, bool exact, int minargs, int maxargs,...) { bool result = true; @@ -181,7 +181,7 @@ check_amproc_signature(Oid funcid, Oid restype, bool exact, procform = (Form_pg_proc) GETSTRUCT(tp); if ((procform->prorettype != restype && OidIsValid(restype)) - || procform->proretset || procform->pronargs < minargs + || procform->proretset != retset || procform->pronargs < minargs || procform->pronargs > maxargs) result = false; @@ -209,7 +209,7 @@ check_amproc_signature(Oid funcid, Oid restype, bool exact, bool check_amoptsproc_signature(Oid funcid) { - return check_amproc_signature(funcid, VOIDOID, true, 1, 1, INTERNALOID); + return check_amproc_signature(funcid, VOIDOID, false, true, 1, 1, INTERNALOID); } /* diff --git a/src/backend/access/nbtree/nbtvalidate.c b/src/backend/access/nbtree/nbtvalidate.c index e9d4cd60de3..04e43ef1dce 100644 --- a/src/backend/access/nbtree/nbtvalidate.c +++ b/src/backend/access/nbtree/nbtvalidate.c @@ -91,16 +91,16 @@ btvalidate(Oid opclassoid) switch (procform->amprocnum) { case BTORDER_PROC: - ok = check_amproc_signature(procform->amproc, INT4OID, true, + ok = check_amproc_signature(procform->amproc, INT4OID, false, true, 2, 2, procform->amproclefttype, procform->amprocrighttype); break; case BTSORTSUPPORT_PROC: - ok = check_amproc_signature(procform->amproc, VOIDOID, true, + ok = check_amproc_signature(procform->amproc, VOIDOID, false, true, 1, 1, INTERNALOID); break; case BTINRANGE_PROC: - ok = check_amproc_signature(procform->amproc, BOOLOID, true, + ok = check_amproc_signature(procform->amproc, BOOLOID, false, true, 5, 5, procform->amproclefttype, procform->amproclefttype, @@ -108,7 +108,7 @@ btvalidate(Oid opclassoid) BOOLOID, BOOLOID); break; case BTEQUALIMAGE_PROC: - ok = check_amproc_signature(procform->amproc, BOOLOID, true, + ok = check_amproc_signature(procform->amproc, BOOLOID, false, true, 1, 1, OIDOID); break; case BTOPTIONS_PROC: diff --git a/src/backend/access/spgist/spgvalidate.c b/src/backend/access/spgist/spgvalidate.c index 8834f22ba85..870cb395761 100644 --- a/src/backend/access/spgist/spgvalidate.c +++ b/src/backend/access/spgist/spgvalidate.c @@ -109,7 +109,7 @@ spgvalidate(Oid opclassoid) switch (procform->amprocnum) { case SPGIST_CONFIG_PROC: - ok = check_amproc_signature(procform->amproc, VOIDOID, true, + ok = check_amproc_signature(procform->amproc, VOIDOID, false, true, 2, 2, INTERNALOID, INTERNALOID); configIn.attType = procform->amproclefttype; memset(&configOut, 0, sizeof(configOut)); @@ -164,11 +164,11 @@ spgvalidate(Oid opclassoid) case SPGIST_CHOOSE_PROC: case SPGIST_PICKSPLIT_PROC: case SPGIST_INNER_CONSISTENT_PROC: - ok = check_amproc_signature(procform->amproc, VOIDOID, true, + ok = check_amproc_signature(procform->amproc, VOIDOID, false, true, 2, 2, INTERNALOID, INTERNALOID); break; case SPGIST_LEAF_CONSISTENT_PROC: - ok = check_amproc_signature(procform->amproc, BOOLOID, true, + ok = check_amproc_signature(procform->amproc, BOOLOID, false, true, 2, 2, INTERNALOID, INTERNALOID); break; case SPGIST_COMPRESS_PROC: @@ -177,7 +177,7 @@ spgvalidate(Oid opclassoid) ok = false; else ok = check_amproc_signature(procform->amproc, - configOutLeafType, true, + configOutLeafType, false, true, 1, 1, procform->amproclefttype); break; case SPGIST_OPTIONS_PROC: diff --git a/src/backend/utils/adt/multirangetypes.c b/src/backend/utils/adt/multirangetypes.c index f82e6f42d98..5a6a5041c42 100644 --- a/src/backend/utils/adt/multirangetypes.c +++ b/src/backend/utils/adt/multirangetypes.c @@ -1226,6 +1226,77 @@ multirange_minus_internal(Oid mltrngtypoid, TypeCacheEntry *rangetyp, return make_multirange(mltrngtypoid, rangetyp, range_count3, ranges3); } +/* + * multirange minus but returning the result as a SRF, + * with no rows if the result would be empty. + */ +Datum +multirange_without_portion(PG_FUNCTION_ARGS) +{ + FuncCallContext *funcctx; + MemoryContext oldcontext; + + if (!SRF_IS_FIRSTCALL()) + { + /* We never have more than one result */ + funcctx = SRF_PERCALL_SETUP(); + SRF_RETURN_DONE(funcctx); + } + else + { + MultirangeType *mr1; + MultirangeType *mr2; + Oid mltrngtypoid; + TypeCacheEntry *typcache; + TypeCacheEntry *rangetyp; + int32 range_count1; + int32 range_count2; + RangeType **ranges1; + RangeType **ranges2; + MultirangeType *mr; + + funcctx = SRF_FIRSTCALL_INIT(); + + /* + * switch to memory context appropriate for multiple function calls + */ + oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx); + + /* get args, detoasting into multi-call memory context */ + mr1 = PG_GETARG_MULTIRANGE_P(0); + mr2 = PG_GETARG_MULTIRANGE_P(1); + + mltrngtypoid = MultirangeTypeGetOid(mr1); + typcache = lookup_type_cache(mltrngtypoid, TYPECACHE_MULTIRANGE_INFO); + if (typcache->rngtype == NULL) + elog(ERROR, "type %u is not a multirange type", mltrngtypoid); + rangetyp = typcache->rngtype; + + if (MultirangeIsEmpty(mr1) || MultirangeIsEmpty(mr2)) + mr = mr1; + else + { + multirange_deserialize(rangetyp, mr1, &range_count1, &ranges1); + multirange_deserialize(rangetyp, mr2, &range_count2, &ranges2); + + mr = multirange_minus_internal(mltrngtypoid, + rangetyp, + range_count1, + ranges1, + range_count2, + ranges2); + } + + MemoryContextSwitchTo(oldcontext); + + funcctx = SRF_PERCALL_SETUP(); + if (MultirangeIsEmpty(mr)) + SRF_RETURN_DONE(funcctx); + else + SRF_RETURN_NEXT(funcctx, MultirangeTypePGetDatum(mr)); + } +} + /* multirange intersection */ Datum multirange_intersect(PG_FUNCTION_ARGS) diff --git a/src/backend/utils/adt/rangetypes.c b/src/backend/utils/adt/rangetypes.c index 2d94a6b8774..2ab27bb7573 100644 --- a/src/backend/utils/adt/rangetypes.c +++ b/src/backend/utils/adt/rangetypes.c @@ -31,6 +31,7 @@ #include "postgres.h" #include "common/hashfn.h" +#include "funcapi.h" #include "libpq/pqformat.h" #include "miscadmin.h" #include "nodes/makefuncs.h" @@ -39,6 +40,7 @@ #include "optimizer/clauses.h" #include "optimizer/cost.h" #include "optimizer/optimizer.h" +#include "utils/array.h" #include "utils/builtins.h" #include "utils/date.h" #include "utils/lsyscache.h" @@ -1213,6 +1215,167 @@ range_split_internal(TypeCacheEntry *typcache, const RangeType *r1, const RangeT return false; } +/* subtraction but returning an array to accommodate splits */ +Datum +range_without_portion(PG_FUNCTION_ARGS) +{ + typedef struct { + RangeType *rs[2]; + int n; + } range_without_portion_fctx; + + FuncCallContext *funcctx; + range_without_portion_fctx *fctx; + MemoryContext oldcontext; + + /* stuff done only on the first call of the function */ + if (SRF_IS_FIRSTCALL()) + { + RangeType *r1; + RangeType *r2; + Oid rngtypid; + TypeCacheEntry *typcache; + + /* create a function context for cross-call persistence */ + funcctx = SRF_FIRSTCALL_INIT(); + + /* + * switch to memory context appropriate for multiple function calls + */ + oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx); + + r1 = PG_GETARG_RANGE_P(0); + r2 = PG_GETARG_RANGE_P(1); + + /* Different types should be prevented by ANYRANGE matching rules */ + if (RangeTypeGetOid(r1) != RangeTypeGetOid(r2)) + elog(ERROR, "range types do not match"); + + /* allocate memory for user context */ + fctx = (range_without_portion_fctx *) palloc(sizeof(range_without_portion_fctx)); + + /* + * Initialize state. + * We can't store the range typcache in fn_extra because the caller + * uses that for the SRF state. + */ + rngtypid = RangeTypeGetOid(r1); + typcache = lookup_type_cache(rngtypid, TYPECACHE_RANGE_INFO); + if (typcache->rngelemtype == NULL) + elog(ERROR, "type %u is not a range type", rngtypid); + range_without_portion_internal(typcache, r1, r2, fctx->rs, &fctx->n); + + funcctx->user_fctx = fctx; + MemoryContextSwitchTo(oldcontext); + } + + /* stuff done on every call of the function */ + funcctx = SRF_PERCALL_SETUP(); + fctx = funcctx->user_fctx; + + if (funcctx->call_cntr < fctx->n) + { + /* + * We must keep these on separate lines + * because SRF_RETURN_NEXT does call_cntr++: + */ + RangeType *ret = fctx->rs[funcctx->call_cntr]; + SRF_RETURN_NEXT(funcctx, RangeTypePGetDatum(ret)); + } + else + /* do when there is no more left */ + SRF_RETURN_DONE(funcctx); +} + +/* + * range_without_portion_internal - Sets outputs and outputn to the ranges + * remaining and their count (respectively) after subtracting r2 from r1. + * The array should never contain empty ranges. + * The outputs will be ordered. We expect that outputs is an array of + * RangeType pointers, already allocated with two slots. + */ +void +range_without_portion_internal(TypeCacheEntry *typcache, RangeType *r1, + RangeType *r2, RangeType **outputs, int *outputn) +{ + int cmp_l1l2, + cmp_l1u2, + cmp_u1l2, + cmp_u1u2; + RangeBound lower1, + lower2; + RangeBound upper1, + upper2; + bool empty1, + empty2; + + range_deserialize(typcache, r1, &lower1, &upper1, &empty1); + range_deserialize(typcache, r2, &lower2, &upper2, &empty2); + + if (empty1) + { + /* if r1 is empty then r1 - r2 is empty, so return zero results */ + *outputn = 0; + return; + } + else if (empty2) + { + /* r2 is empty so the result is just r1 (which we know is not empty) */ + outputs[0] = r1; + *outputn = 1; + return; + } + + /* + * Use the same logic as range_minus_internal, + * but support the split case + */ + cmp_l1l2 = range_cmp_bounds(typcache, &lower1, &lower2); + cmp_l1u2 = range_cmp_bounds(typcache, &lower1, &upper2); + cmp_u1l2 = range_cmp_bounds(typcache, &upper1, &lower2); + cmp_u1u2 = range_cmp_bounds(typcache, &upper1, &upper2); + + if (cmp_l1l2 < 0 && cmp_u1u2 > 0) + { + lower2.inclusive = !lower2.inclusive; + lower2.lower = false; /* it will become the upper bound */ + outputs[0] = make_range(typcache, &lower1, &lower2, false, NULL); + + upper2.inclusive = !upper2.inclusive; + upper2.lower = true; /* it will become the lower bound */ + outputs[1] = make_range(typcache, &upper2, &upper1, false, NULL); + + *outputn = 2; + } + else if (cmp_l1u2 > 0 || cmp_u1l2 < 0) + { + outputs[0] = r1; + *outputn = 1; + } + else if (cmp_l1l2 >= 0 && cmp_u1u2 <= 0) + { + *outputn = 0; + } + else if (cmp_l1l2 <= 0 && cmp_u1l2 >= 0 && cmp_u1u2 <= 0) + { + lower2.inclusive = !lower2.inclusive; + lower2.lower = false; /* it will become the upper bound */ + outputs[0] = make_range(typcache, &lower1, &lower2, false, NULL); + *outputn = 1; + } + else if (cmp_l1l2 >= 0 && cmp_u1u2 >= 0 && cmp_l1u2 <= 0) + { + upper2.inclusive = !upper2.inclusive; + upper2.lower = true; /* it will become the lower bound */ + outputs[0] = make_range(typcache, &upper2, &upper1, false, NULL); + *outputn = 1; + } + else + { + elog(ERROR, "unexpected case in range_without_portion"); + } +} + /* range -> range aggregate functions */ Datum diff --git a/src/include/access/amvalidate.h b/src/include/access/amvalidate.h index c795a4bc1bf..4844ba82dc3 100644 --- a/src/include/access/amvalidate.h +++ b/src/include/access/amvalidate.h @@ -29,8 +29,8 @@ typedef struct OpFamilyOpFuncGroup /* Functions in access/index/amvalidate.c */ extern List *identify_opfamily_groups(CatCList *oprlist, CatCList *proclist); extern bool check_amproc_is_aggregate(Oid funcid); -extern bool check_amproc_signature(Oid funcid, Oid restype, bool exact, - int minargs, int maxargs,...); +extern bool check_amproc_signature(Oid funcid, Oid restype, bool retset, + bool exact, int minargs, int maxargs,...); extern bool check_amoptsproc_signature(Oid funcid); extern bool check_amop_signature(Oid opno, Oid restype, Oid lefttype, Oid righttype); diff --git a/src/include/access/gist.h b/src/include/access/gist.h index 641677e191c..e8b393f9dfd 100644 --- a/src/include/access/gist.h +++ b/src/include/access/gist.h @@ -41,7 +41,9 @@ #define GIST_SORTSUPPORT_PROC 11 #define GIST_STRATNUM_PROC 12 #define GIST_REFERENCED_AGG_PROC 13 -#define GISTNProcs 13 +#define GIST_INTERSECT_PROC 14 +#define GIST_WITHOUT_PORTION_PROC 15 +#define GISTNProcs 15 /* * Page opaque data in a GiST index page. diff --git a/src/include/catalog/pg_amproc.dat b/src/include/catalog/pg_amproc.dat index 1d3d5fcf4d8..8c922974bb1 100644 --- a/src/include/catalog/pg_amproc.dat +++ b/src/include/catalog/pg_amproc.dat @@ -510,6 +510,9 @@ { amprocfamily => 'gist/box_ops', amproclefttype => 'box', amprocrighttype => 'box', amprocnum => '12', amproc => 'gist_stratnum_identity' }, +{ amprocfamily => 'gist/box_ops', amproclefttype => 'box', + amprocrighttype => 'box', amprocnum => '14', + amproc => 'box_intersect(box,box)' }, { amprocfamily => 'gist/poly_ops', amproclefttype => 'polygon', amprocrighttype => 'polygon', amprocnum => '1', amproc => 'gist_poly_consistent' }, @@ -613,6 +616,12 @@ { amprocfamily => 'gist/range_ops', amproclefttype => 'anyrange', amprocrighttype => 'anyrange', amprocnum => '13', amproc => 'range_agg(anyrange)' }, +{ amprocfamily => 'gist/range_ops', amproclefttype => 'anyrange', + amprocrighttype => 'anyrange', amprocnum => '14', + amproc => 'range_intersect(anyrange,anyrange)' }, +{ amprocfamily => 'gist/range_ops', amproclefttype => 'anyrange', + amprocrighttype => 'anyrange', amprocnum => '15', + amproc => 'range_without_portion(anyrange,anyrange)' }, { amprocfamily => 'gist/network_ops', amproclefttype => 'inet', amprocrighttype => 'inet', amprocnum => '1', amproc => 'inet_gist_consistent' }, @@ -656,6 +665,12 @@ { amprocfamily => 'gist/multirange_ops', amproclefttype => 'anymultirange', amprocrighttype => 'anymultirange', amprocnum => '13', amproc => 'range_agg(anymultirange)' }, +{ amprocfamily => 'gist/multirange_ops', amproclefttype => 'anymultirange', + amprocrighttype => 'anymultirange', amprocnum => '14', + amproc => 'multirange_intersect(anymultirange,anymultirange)' }, +{ amprocfamily => 'gist/multirange_ops', amproclefttype => 'anymultirange', + amprocrighttype => 'anymultirange', amprocnum => '15', + amproc => 'multirange_without_portion(anymultirange,anymultirange)' }, # gin { amprocfamily => 'gin/array_ops', amproclefttype => 'anyarray', diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index a8367ff5843..7fe2542f058 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -10582,6 +10582,10 @@ { oid => '3869', proname => 'range_minus', prorettype => 'anyrange', proargtypes => 'anyrange anyrange', prosrc => 'range_minus' }, +{ oid => '8408', descr => 'remove portion from range', + proname => 'range_without_portion', prorows => '2', + proretset => 't', prorettype => 'anyrange', + proargtypes => 'anyrange anyrange', prosrc => 'range_without_portion' }, { oid => '3870', descr => 'less-equal-greater', proname => 'range_cmp', prorettype => 'int4', proargtypes => 'anyrange anyrange', prosrc => 'range_cmp' }, @@ -10869,6 +10873,10 @@ { oid => '4271', proname => 'multirange_minus', prorettype => 'anymultirange', proargtypes => 'anymultirange anymultirange', prosrc => 'multirange_minus' }, +{ oid => '8406', descr => 'remove portion from multirange', + proname => 'multirange_without_portion', prorows => '1', + proretset => 't', prorettype => 'anymultirange', + proargtypes => 'anymultirange anymultirange', prosrc => 'multirange_without_portion' }, { oid => '4272', proname => 'multirange_intersect', prorettype => 'anymultirange', proargtypes => 'anymultirange anymultirange', diff --git a/src/include/utils/rangetypes.h b/src/include/utils/rangetypes.h index 2b574873cef..e1b256b65f4 100644 --- a/src/include/utils/rangetypes.h +++ b/src/include/utils/rangetypes.h @@ -164,5 +164,7 @@ extern RangeType *make_empty_range(TypeCacheEntry *typcache); extern bool range_split_internal(TypeCacheEntry *typcache, const RangeType *r1, const RangeType *r2, RangeType **output1, RangeType **output2); +extern void range_without_portion_internal(TypeCacheEntry *typcache, RangeType *r1, + RangeType *r2, RangeType **outputs, int *outputn); #endif /* RANGETYPES_H */ diff --git a/src/test/regress/expected/multirangetypes.out b/src/test/regress/expected/multirangetypes.out index c6363ebeb24..11aa282ff35 100644 --- a/src/test/regress/expected/multirangetypes.out +++ b/src/test/regress/expected/multirangetypes.out @@ -2200,6 +2200,122 @@ SELECT nummultirange(numrange(1,2), numrange(4,5)) - nummultirange(numrange(-2,0 {[1,2),[4,5)} (1 row) +-- without_portion +SELECT multirange_without_portion(nummultirange(), nummultirange()); + multirange_without_portion +---------------------------- +(0 rows) + +SELECT multirange_without_portion(nummultirange(), nummultirange(numrange(1,2))); + multirange_without_portion +---------------------------- +(0 rows) + +SELECT multirange_without_portion(nummultirange(numrange(1,2)), nummultirange()); + multirange_without_portion +---------------------------- + {[1,2)} +(1 row) + +SELECT multirange_without_portion(nummultirange(numrange(1,2), numrange(3,4)), nummultirange()); + multirange_without_portion +---------------------------- + {[1,2),[3,4)} +(1 row) + +SELECT multirange_without_portion(nummultirange(numrange(1,2)), nummultirange(numrange(1,2))); + multirange_without_portion +---------------------------- +(0 rows) + +SELECT multirange_without_portion(nummultirange(numrange(1,2)), nummultirange(numrange(2,4))); + multirange_without_portion +---------------------------- + {[1,2)} +(1 row) + +SELECT multirange_without_portion(nummultirange(numrange(1,2)), nummultirange(numrange(3,4))); + multirange_without_portion +---------------------------- + {[1,2)} +(1 row) + +SELECT multirange_without_portion(nummultirange(numrange(1,4)), nummultirange(numrange(1,2))); + multirange_without_portion +---------------------------- + {[2,4)} +(1 row) + +SELECT multirange_without_portion(nummultirange(numrange(1,4)), nummultirange(numrange(2,3))); + multirange_without_portion +---------------------------- + {[1,2),[3,4)} +(1 row) + +SELECT multirange_without_portion(nummultirange(numrange(1,4)), nummultirange(numrange(0,8))); + multirange_without_portion +---------------------------- +(0 rows) + +SELECT multirange_without_portion(nummultirange(numrange(1,4)), nummultirange(numrange(0,2))); + multirange_without_portion +---------------------------- + {[2,4)} +(1 row) + +SELECT multirange_without_portion(nummultirange(numrange(1,8)), nummultirange(numrange(0,2), numrange(3,4))); + multirange_without_portion +---------------------------- + {[2,3),[4,8)} +(1 row) + +SELECT multirange_without_portion(nummultirange(numrange(1,8)), nummultirange(numrange(2,3), numrange(5,null))); + multirange_without_portion +---------------------------- + {[1,2),[3,5)} +(1 row) + +SELECT multirange_without_portion(nummultirange(numrange(1,2), numrange(4,5)), nummultirange(numrange(-2,0))); + multirange_without_portion +---------------------------- + {[1,2),[4,5)} +(1 row) + +SELECT multirange_without_portion(nummultirange(numrange(1,2), numrange(4,5)), nummultirange(numrange(2,4))); + multirange_without_portion +---------------------------- + {[1,2),[4,5)} +(1 row) + +SELECT multirange_without_portion(nummultirange(numrange(1,2), numrange(4,5)), nummultirange(numrange(3,5))); + multirange_without_portion +---------------------------- + {[1,2)} +(1 row) + +SELECT multirange_without_portion(nummultirange(numrange(1,2), numrange(4,5)), nummultirange(numrange(0,9))); + multirange_without_portion +---------------------------- +(0 rows) + +SELECT multirange_without_portion(nummultirange(numrange(1,3), numrange(4,5)), nummultirange(numrange(2,9))); + multirange_without_portion +---------------------------- + {[1,2)} +(1 row) + +SELECT multirange_without_portion(nummultirange(numrange(1,2), numrange(4,5)), nummultirange(numrange(8,9))); + multirange_without_portion +---------------------------- + {[1,2),[4,5)} +(1 row) + +SELECT multirange_without_portion(nummultirange(numrange(1,2), numrange(4,5)), nummultirange(numrange(-2,0), numrange(8,9))); + multirange_without_portion +---------------------------- + {[1,2),[4,5)} +(1 row) + -- intersection SELECT nummultirange() * nummultirange(); ?column? diff --git a/src/test/regress/expected/rangetypes.out b/src/test/regress/expected/rangetypes.out index a7cc220bf0d..ab2309e8c1d 100644 --- a/src/test/regress/expected/rangetypes.out +++ b/src/test/regress/expected/rangetypes.out @@ -481,6 +481,60 @@ select range_minus(numrange(10.1,12.2,'[]'), numrange(0.0,120.2,'(]')); empty (1 row) +select range_without_portion('empty'::numrange, numrange(2.0, 3.0)); + range_without_portion +----------------------- +(0 rows) + +select range_without_portion(numrange(1.1, 2.2), 'empty'::numrange); + range_without_portion +----------------------- + [1.1,2.2) +(1 row) + +select range_without_portion(numrange(1.1, 2.2), numrange(2.0, 3.0)); + range_without_portion +----------------------- + [1.1,2.0) +(1 row) + +select range_without_portion(numrange(1.1, 2.2), numrange(2.2, 3.0)); + range_without_portion +----------------------- + [1.1,2.2) +(1 row) + +select range_without_portion(numrange(1.1, 2.2,'[]'), numrange(2.0, 3.0)); + range_without_portion +----------------------- + [1.1,2.0) +(1 row) + +select range_without_portion(numrange(1.0, 3.0), numrange(1.5, 2.0)); + range_without_portion +----------------------- + [1.0,1.5) + [2.0,3.0) +(2 rows) + +select range_without_portion(numrange(10.1,12.2,'[]'), numrange(110.0,120.2,'(]')); + range_without_portion +----------------------- + [10.1,12.2] +(1 row) + +select range_without_portion(numrange(10.1,12.2,'[]'), numrange(0.0,120.2,'(]')); + range_without_portion +----------------------- +(0 rows) + +select range_without_portion(numrange(1.0,3.0,'[]'), numrange(1.5,2.0,'(]')); + range_without_portion +----------------------- + [1.0,1.5] + (2.0,3.0] +(2 rows) + select numrange(4.5, 5.5, '[]') && numrange(5.5, 6.5); ?column? ---------- diff --git a/src/test/regress/sql/multirangetypes.sql b/src/test/regress/sql/multirangetypes.sql index 41d5524285a..0bfa71caca0 100644 --- a/src/test/regress/sql/multirangetypes.sql +++ b/src/test/regress/sql/multirangetypes.sql @@ -414,6 +414,28 @@ SELECT nummultirange(numrange(1,3), numrange(4,5)) - nummultirange(numrange(2,9) SELECT nummultirange(numrange(1,2), numrange(4,5)) - nummultirange(numrange(8,9)); SELECT nummultirange(numrange(1,2), numrange(4,5)) - nummultirange(numrange(-2,0), numrange(8,9)); +-- without_portion +SELECT multirange_without_portion(nummultirange(), nummultirange()); +SELECT multirange_without_portion(nummultirange(), nummultirange(numrange(1,2))); +SELECT multirange_without_portion(nummultirange(numrange(1,2)), nummultirange()); +SELECT multirange_without_portion(nummultirange(numrange(1,2), numrange(3,4)), nummultirange()); +SELECT multirange_without_portion(nummultirange(numrange(1,2)), nummultirange(numrange(1,2))); +SELECT multirange_without_portion(nummultirange(numrange(1,2)), nummultirange(numrange(2,4))); +SELECT multirange_without_portion(nummultirange(numrange(1,2)), nummultirange(numrange(3,4))); +SELECT multirange_without_portion(nummultirange(numrange(1,4)), nummultirange(numrange(1,2))); +SELECT multirange_without_portion(nummultirange(numrange(1,4)), nummultirange(numrange(2,3))); +SELECT multirange_without_portion(nummultirange(numrange(1,4)), nummultirange(numrange(0,8))); +SELECT multirange_without_portion(nummultirange(numrange(1,4)), nummultirange(numrange(0,2))); +SELECT multirange_without_portion(nummultirange(numrange(1,8)), nummultirange(numrange(0,2), numrange(3,4))); +SELECT multirange_without_portion(nummultirange(numrange(1,8)), nummultirange(numrange(2,3), numrange(5,null))); +SELECT multirange_without_portion(nummultirange(numrange(1,2), numrange(4,5)), nummultirange(numrange(-2,0))); +SELECT multirange_without_portion(nummultirange(numrange(1,2), numrange(4,5)), nummultirange(numrange(2,4))); +SELECT multirange_without_portion(nummultirange(numrange(1,2), numrange(4,5)), nummultirange(numrange(3,5))); +SELECT multirange_without_portion(nummultirange(numrange(1,2), numrange(4,5)), nummultirange(numrange(0,9))); +SELECT multirange_without_portion(nummultirange(numrange(1,3), numrange(4,5)), nummultirange(numrange(2,9))); +SELECT multirange_without_portion(nummultirange(numrange(1,2), numrange(4,5)), nummultirange(numrange(8,9))); +SELECT multirange_without_portion(nummultirange(numrange(1,2), numrange(4,5)), nummultirange(numrange(-2,0), numrange(8,9))); + -- intersection SELECT nummultirange() * nummultirange(); SELECT nummultirange() * nummultirange(numrange(1,2)); diff --git a/src/test/regress/sql/rangetypes.sql b/src/test/regress/sql/rangetypes.sql index a5ecdf5372f..7fc805d9ffa 100644 --- a/src/test/regress/sql/rangetypes.sql +++ b/src/test/regress/sql/rangetypes.sql @@ -107,6 +107,16 @@ select numrange(1.1, 2.2,'[]') - numrange(2.0, 3.0); select range_minus(numrange(10.1,12.2,'[]'), numrange(110.0,120.2,'(]')); select range_minus(numrange(10.1,12.2,'[]'), numrange(0.0,120.2,'(]')); +select range_without_portion('empty'::numrange, numrange(2.0, 3.0)); +select range_without_portion(numrange(1.1, 2.2), 'empty'::numrange); +select range_without_portion(numrange(1.1, 2.2), numrange(2.0, 3.0)); +select range_without_portion(numrange(1.1, 2.2), numrange(2.2, 3.0)); +select range_without_portion(numrange(1.1, 2.2,'[]'), numrange(2.0, 3.0)); +select range_without_portion(numrange(1.0, 3.0), numrange(1.5, 2.0)); +select range_without_portion(numrange(10.1,12.2,'[]'), numrange(110.0,120.2,'(]')); +select range_without_portion(numrange(10.1,12.2,'[]'), numrange(0.0,120.2,'(]')); +select range_without_portion(numrange(1.0,3.0,'[]'), numrange(1.5,2.0,'(]')); + select numrange(4.5, 5.5, '[]') && numrange(5.5, 6.5); select numrange(1.0, 2.0) << numrange(3.0, 4.0); select numrange(1.0, 3.0,'[]') << numrange(3.0, 4.0,'[]'); -- 2.42.0