Multirange datatypes

Alexander Korotkov <akorotkov@postgresql.org>

Commit: 6df7a9698bb036610c1e8c6d375e1be38cb26d5f
Author: Alexander Korotkov <akorotkov@postgresql.org>
Date: 2020-12-20T04:20:33Z
Releases: 14.0
Multirange datatypes

Multiranges are basically sorted arrays of non-overlapping ranges with
set-theoretic operations defined over them.

Since v14, each range type automatically gets a corresponding multirange
datatype.  There are both manual and automatic mechanisms for naming multirange
types.  Once can specify multirange type name using multirange_type_name
attribute in CREATE TYPE.  Otherwise, a multirange type name is generated
automatically.  If the range type name contains "range" then we change that to
"multirange".  Otherwise, we add "_multirange" to the end.

Implementation of multiranges comes with a space-efficient internal
representation format, which evades extra paddings and duplicated storage of
oids.  Altogether this format allows fetching a particular range by its index
in O(n).

Statistic gathering and selectivity estimation are implemented for multiranges.
For this purpose, stored multirange is approximated as union range without gaps.
This field will likely need improvements in the future.

Catversion is bumped.

Discussion: https://postgr.es/m/CALNJ-vSUpQ_Y%3DjXvTxt1VYFztaBSsWVXeF1y6gTYQ4bOiWDLgQ%40mail.gmail.com
Discussion: https://postgr.es/m/a0b8026459d1e6167933be2104a6174e7d40d0ab.camel%40j-davis.com#fe7218c83b08068bfffb0c5293eceda0
Author: Paul Jungwirth, revised by me
Reviewed-by: David Fetter, Corey Huinker, Jeff Davis, Pavel Stehule
Reviewed-by: Alvaro Herrera, Tom Lane, Isaac Morland, David G. Johnston
Reviewed-by: Zhihong Yu, Alexander Korotkov

Files

PathChange+/−
doc/src/sgml/catalogs.sgml modified +13 −2
doc/src/sgml/datatype.sgml modified +23 −0
doc/src/sgml/extend.sgml modified +45 −14
doc/src/sgml/func.sgml modified +649 −7
doc/src/sgml/rangetypes.sgml modified +59 −8
doc/src/sgml/ref/create_type.sgml modified +21 −0
src/backend/catalog/pg_range.c modified +9 −1
src/backend/catalog/pg_type.c modified +94 −24
src/backend/commands/typecmds.c modified +354 −3
src/backend/executor/functions.c modified +2 −1
src/backend/parser/parse_coerce.c modified +316 −34
src/backend/utils/adt/Makefile modified +2 −0
src/backend/utils/adt/multirangetypes.c added +2679 −0
src/backend/utils/adt/multirangetypes_selfuncs.c added +1320 −0
src/backend/utils/adt/pg_upgrade_support.c modified +22 −0
src/backend/utils/adt/pseudotypes.c modified +38 −0
src/backend/utils/adt/rangetypes.c modified +153 −24
src/backend/utils/adt/rangetypes_typanalyze.c modified +74 −4
src/backend/utils/cache/lsyscache.c modified +61 −1
src/backend/utils/cache/syscache.c modified +12 −0
src/backend/utils/cache/typcache.c modified +91 −7
src/backend/utils/fmgr/funcapi.c modified +177 −3
src/bin/pg_dump/pg_dump.c modified +125 −41
src/bin/pg_dump/pg_dump.h modified +1 −0
src/bin/pg_dump/t/002_pg_dump.pl modified +2 −0
src/include/access/tupmacs.h modified +2 −2
src/include/catalog/binary_upgrade.h modified +2 −0
src/include/catalog/catversion.h modified +1 −1
src/include/catalog/pg_aggregate.dat modified +11 −0
src/include/catalog/pg_amop.dat modified +22 −0
src/include/catalog/pg_amproc.dat modified +10 −2
src/include/catalog/pg_cast.dat modified +13 −0
src/include/catalog/pg_opclass.dat modified +4 −0
src/include/catalog/pg_operator.dat modified +169 −0
src/include/catalog/pg_opfamily.dat modified +4 −0
src/include/catalog/pg_proc.dat modified +282 −0
src/include/catalog/pg_range.dat modified +9 −6
src/include/catalog/pg_range.h modified +8 −1
src/include/catalog/pg_type.dat modified +45 −0
src/include/catalog/pg_type.h modified +8 −2
src/include/commands/typecmds.h modified +2 −0
src/include/utils/lsyscache.h modified +3 −0
src/include/utils/multirangetypes.h added +116 −0
src/include/utils/rangetypes.h modified +12 −0
src/include/utils/selfuncs.h modified +3 −0
src/include/utils/syscache.h modified +1 −0
src/include/utils/typcache.h modified +22 −16
src/pl/plpgsql/src/pl_comp.c modified +6 −0
src/test/regress/expected/dependency.out modified +1 −0
src/test/regress/expected/hash_func.out modified +13 −0
src/test/regress/expected/multirangetypes.out added +2466 −0
src/test/regress/expected/opr_sanity.out modified +25 −7
src/test/regress/expected/plpgsql.out modified +2 −2
src/test/regress/expected/polymorphism.out modified +104 −21
src/test/regress/expected/rangefuncs.out modified +1 −1
src/test/regress/expected/rangetypes.out modified +36 −6
src/test/regress/expected/sanity_check.out modified +2 −0
src/test/regress/expected/type_sanity.out modified +29 −17
src/test/regress/parallel_schedule modified +4 −2
src/test/regress/serial_schedule modified +1 −0
src/test/regress/sql/hash_func.sql modified +10 −0
src/test/regress/sql/multirangetypes.sql added +677 −0
src/test/regress/sql/opr_sanity.sql modified +19 −5
src/test/regress/sql/polymorphism.sql modified +52 −0
src/test/regress/sql/rangetypes.sql modified +14 −0
src/test/regress/sql/type_sanity.sql modified +11 −5
src/tools/pgindent/typedefs.list modified +4 −0

Documentation touched

Discussion

Cited messages not in the archive: a0b8026459d1e6167933be2104a6174e7d40d0ab.camel@j-davis.com#fe7218c83b08068bfffb0c5293eceda0