From bd3e2de65063c811aa30d0891cd4b125f1bda79f Mon Sep 17 00:00:00 2001 From: "Paul A. Jungwirth" Date: Sat, 9 Jan 2021 21:59:43 -0800 Subject: [PATCH v9 1/4] Add PERIODs - Added parsing for SQL:2011 syntax to define an application-time PERIOD on a table (in both CREATE TABLE and ALTER TABLE). Make sure we create the PERIOD after columns are known (since PERIODs can refer to them) but before constraints are handled (since PERIODs can appear in them). - Added ALTER TABLE DROP support for PERIODs. - Created postgres.pg_period table. - Created information_schema.periods view. - Added pg_dump support. - Added tests and documentation. - Automatically define a constraint for each PERIOD requiring the start column to be less than the end column. - When creating a PERIOD, choose an appropriate range type we can use to implement PERIOD-related operations. You can choose one explicitly if there is ambiguity (due to multiple range types created over the same base type). --- doc/src/sgml/catalogs.sgml | 132 ++++++ doc/src/sgml/ddl.sgml | 58 +++ doc/src/sgml/information_schema.sgml | 63 +++ doc/src/sgml/ref/alter_table.sgml | 25 + doc/src/sgml/ref/comment.sgml | 2 + doc/src/sgml/ref/create_table.sgml | 39 ++ src/backend/catalog/Makefile | 5 +- src/backend/catalog/aclchk.c | 2 + src/backend/catalog/dependency.c | 9 + src/backend/catalog/heap.c | 67 +++ src/backend/catalog/information_schema.sql | 24 +- src/backend/catalog/objectaddress.c | 73 +++ src/backend/catalog/pg_period.c | 106 +++++ src/backend/catalog/sql_features.txt | 2 +- src/backend/commands/alter.c | 1 + src/backend/commands/comment.c | 10 + src/backend/commands/event_trigger.c | 4 + src/backend/commands/seclabel.c | 1 + src/backend/commands/tablecmds.c | 510 ++++++++++++++++++++- src/backend/commands/view.c | 4 +- src/backend/nodes/copyfuncs.c | 19 + src/backend/nodes/equalfuncs.c | 17 + src/backend/nodes/nodeFuncs.c | 3 + src/backend/nodes/outfuncs.c | 45 ++ src/backend/parser/gram.y | 47 +- src/backend/parser/parse_utilcmd.c | 155 +++++++ src/backend/utils/cache/lsyscache.c | 87 ++++ src/backend/utils/cache/syscache.c | 34 +- src/bin/pg_dump/pg_backup_archiver.c | 1 + src/bin/pg_dump/pg_dump.c | 154 ++++++- src/bin/pg_dump/pg_dump.h | 15 + src/bin/pg_dump/pg_dump_sort.c | 7 + src/bin/psql/describe.c | 34 ++ src/include/catalog/dependency.h | 1 + src/include/catalog/heap.h | 4 + src/include/catalog/pg_index.h | 2 +- src/include/catalog/pg_period.h | 55 +++ src/include/catalog/pg_range.h | 1 + src/include/commands/tablecmds.h | 3 +- src/include/nodes/nodes.h | 1 + src/include/nodes/parsenodes.h | 31 +- src/include/parser/kwlist.h | 1 + src/include/parser/parse_utilcmd.h | 1 + src/include/utils/lsyscache.h | 3 + src/include/utils/syscache.h | 3 + src/test/regress/expected/periods.out | 79 ++++ src/test/regress/expected/sanity_check.out | 3 + src/test/regress/parallel_schedule | 2 +- src/test/regress/sql/periods.sql | 62 +++ 49 files changed, 1970 insertions(+), 37 deletions(-) create mode 100644 src/backend/catalog/pg_period.c create mode 100644 src/include/catalog/pg_period.h create mode 100644 src/test/regress/expected/periods.out create mode 100644 src/test/regress/sql/periods.sql diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index c1d11be73f..e0d48a1463 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -225,6 +225,11 @@ information about partition key of tables + + pg_period + periods + + pg_policy row-security policies @@ -5525,6 +5530,133 @@ SCRAM-SHA-256$<iteration count>:&l are simple references. + + + + + + + + + <structname>pg_period</structname> + + + pg_period + + + + The catalog pg_period stores + information about system and application time periods. + + + + Periods are described in . + + + + <structname>pg_period</structname> Columns + + + + + + Column Type + + + Description + + + + + + + + oid oid + + + Row identifier + + + + + + pername text + + + Period name + + + + + + perrelid oid + (references pg_class.oid) + + + The table this period belongs to + + + + + + perstart int2 + (references pg_attribute.attnum) + + + The number of the start column + + + + + + perend int2 + (references pg_attribute.attnum) + + + The number of the end column + + + + + + perrngtype oid + (references pg_type.oid) + + + The OID of the range type associated with this period + + + + + + perconstraint oid + (references pg_constraint.oid) + + + The OID of the period's CHECK constraint + + + + + + perislocal bool + + + This period is defined locally for the relation. Note that a period can + be locally defined and inherited simultaneously. + + + + + + perinhcount int4 + + + The number of direct inheritance ancestors this period has. A period + with a nonzero number of ancestors cannot be dropped. + + +
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index 94f745aed0..1b8f524191 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -1140,6 +1140,64 @@ CREATE TABLE circles ( + + Periods + + + Periods are definitions on a table that associate a period name with a start + column and an end column. Both columns must be of exactly the same type + (including collation), have a btree operator class, and the start column + value must be strictly less than the end column value. + + + + There are two types of periods: application and system. System periods are + distinguished by their name which must be SYSTEM_TIME. Any + other name is an application period. + + + + Application Periods + + + period + application + + + + Application periods are defined on a table using the following syntax: + + + +CREATE TABLE billing_addresses ( + customer_id integer, + address_id integer, + valid_from date, + valid_to date, + PERIOD FOR validity (valid_from, valid_to) +); + + + + Application periods can be used to define temporal primary and foreign keys. + Any table with a temporal primary key is a temporal table and supports temporal update and delete commands. + + + + + System Periods + + + period + system + + + + Periods for SYSTEM_TIME are currently not implemented. + + + + System Columns diff --git a/doc/src/sgml/information_schema.sgml b/doc/src/sgml/information_schema.sgml index c5e68c175f..925f3e9d4b 100644 --- a/doc/src/sgml/information_schema.sgml +++ b/doc/src/sgml/information_schema.sgml @@ -4171,6 +4171,69 @@ ORDER BY c.ordinal_position; + + <literal>periods</literal> + + + The view parameters contains information about the + periods of all tables in the current database. The start and end column + names are only shown if the current user has access to them (by way of being + the owner or having some privilege). + + + + <literal>periods</literal> Columns + + + + + Name + Data Type + Description + + + + + + table_catalog + sql_identifier + Name of the database containing the period (always the current database) + + + + table_schema + sql_identifier + Name of the schema containing the period + + + + table_name + sql_identifier + Name of the table containing the period + + + + period_name + sql_identifier + Name of the period + + + + start_column_name + sql_identifier + Name of the start column for the period + + + + end_column_name + sql_identifier + Name of the end column for the period + + + +
+ + <literal>referential_constraints</literal> diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml index bc5dcba59c..3521b679b3 100644 --- a/doc/src/sgml/ref/alter_table.sgml +++ b/doc/src/sgml/ref/alter_table.sgml @@ -60,6 +60,8 @@ ALTER TABLE [ IF EXISTS ] name ALTER CONSTRAINT constraint_name [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] VALIDATE CONSTRAINT constraint_name DROP CONSTRAINT [ IF EXISTS ] constraint_name [ RESTRICT | CASCADE ] + ADD PERIOD FOR period_name ( start_column, end_column ) [ WITH ( period_option = value [, ... ] ) ] + DROP PERIOD FOR period_name [ RESTRICT | CASCADE ] DISABLE TRIGGER [ trigger_name | ALL | USER ] ENABLE TRIGGER [ trigger_name | ALL | USER ] ENABLE REPLICA TRIGGER trigger_name @@ -566,6 +568,29 @@ WITH ( MODULUS numeric_literal, REM + + ADD PERIOD FOR + + + This form adds a new period to a table using the same syntax as + . + + + + + + DROP PERIOD FOR + + + This form drops the specified period on a table. The start and end + columns will not be dropped by this command but the + CHECK constraint will be. You will need to say + CASCADE if anything outside the table depends on the + column. + + + + DISABLE/ENABLE [ REPLICA | ALWAYS ] TRIGGER diff --git a/doc/src/sgml/ref/comment.sgml b/doc/src/sgml/ref/comment.sgml index e07fc47fd3..2fffa99289 100644 --- a/doc/src/sgml/ref/comment.sgml +++ b/doc/src/sgml/ref/comment.sgml @@ -44,6 +44,7 @@ COMMENT ON OPERATOR operator_name (left_type, right_type) | OPERATOR CLASS object_name USING index_method | OPERATOR FAMILY object_name USING index_method | + PERIOD relation_name.period_name | POLICY policy_name ON table_name | [ PROCEDURAL ] LANGUAGE object_name | PROCEDURE procedure_name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] | @@ -327,6 +328,7 @@ COMMENT ON OPERATOR ^ (text, text) IS 'Performs intersection of two texts'; COMMENT ON OPERATOR - (NONE, integer) IS 'Unary minus'; COMMENT ON OPERATOR CLASS int4ops USING btree IS '4 byte integer operators for btrees'; COMMENT ON OPERATOR FAMILY integer_ops USING btree IS 'all integer operators for btrees'; +COMMENT ON PERIOD my_table.my_column IS 'Sales promotion validity'; COMMENT ON POLICY my_policy ON mytable IS 'Filter rows by users'; COMMENT ON PROCEDURE my_proc (integer, integer) IS 'Runs a report'; COMMENT ON PUBLICATION alltables IS 'Publishes all operations on all tables'; diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index 57d51a676a..7d3ef82f6a 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -23,6 +23,7 @@ PostgreSQL documentation CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name ( [ { column_name data_type [ COMPRESSION compression_method ] [ COLLATE collation ] [ column_constraint [ ... ] ] + | period_definition | table_constraint | LIKE source_table [ like_option ... ] } [, ... ] @@ -37,6 +38,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name OF type_name [ ( { column_name [ WITH OPTIONS ] [ column_constraint [ ... ] ] + | period_definition | table_constraint } [, ... ] ) ] @@ -49,6 +51,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name PARTITION OF parent_table [ ( { column_name [ WITH OPTIONS ] [ column_constraint [ ... ] ] + | period_definition | table_constraint } [, ... ] ) ] { FOR VALUES partition_bound_spec | DEFAULT } @@ -73,6 +76,11 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI [ ON DELETE referential_action ] [ ON UPDATE referential_action ] } [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] +and period_definition is: + +PERIOD FOR { period_name | SYSTEM_TIME } ( column_name, column_name ) +[ WITH ( period_option = value [, ... ] ) ] + and table_constraint is: [ CONSTRAINT constraint_name ] @@ -139,6 +147,14 @@ WITH ( MODULUS numeric_literal, REM name as any existing data type in the same schema. + + Periods my be defined on tables, specifying that two existing columns + represent start and end values for the period. Periods may have any name + that doesn't conflict with a column name, but the name + SYSTEM_TIME is special, used for versioning tables. + System periods are not yet implemented. See for more details. + + The optional constraint clauses specify constraints (tests) that new or updated rows must satisfy for an insert or update operation @@ -753,6 +769,29 @@ WITH ( MODULUS numeric_literal, REM + + PERIOD FOR period_name ( column_name, column_name ) [ WITH ( period_option = value [, ... ] ) ] + + + A period definition gives semantic meaning to two existing columns of + the table. It defines a "start column" and an "end column" where the + start value is strictly less than the end value. A + CHECK constraint is automatically created to enforce + this. + + + + Both columns must have exactly the same type and must have a range type + defined from their base type. If there are several range types for that + base type, you must specify which one you want by using the + rangetype period_option. + Any base type is allowed, as long as it has a range type, although it is + expected that most periods will use temporal types like timestamptz + or date. + + + + CONSTRAINT constraint_name diff --git a/src/backend/catalog/Makefile b/src/backend/catalog/Makefile index 4e6efda97f..cef4bf26e8 100644 --- a/src/backend/catalog/Makefile +++ b/src/backend/catalog/Makefile @@ -37,6 +37,7 @@ OBJS = \ pg_largeobject.o \ pg_namespace.o \ pg_operator.o \ + pg_period.o \ pg_proc.o \ pg_publication.o \ pg_range.o \ @@ -67,8 +68,8 @@ CATALOG_HEADERS := \ pg_foreign_data_wrapper.h pg_foreign_server.h pg_user_mapping.h \ pg_foreign_table.h pg_policy.h pg_replication_origin.h \ pg_default_acl.h pg_init_privs.h pg_seclabel.h pg_shseclabel.h \ - pg_collation.h pg_partitioned_table.h pg_range.h pg_transform.h \ - pg_sequence.h pg_publication.h pg_publication_namespace.h \ + pg_collation.h pg_partitioned_table.h pg_period.h pg_range.h \ + pg_transform.h pg_sequence.h pg_publication.h pg_publication_namespace.h \ pg_publication_rel.h pg_subscription.h pg_subscription_rel.h GENERATED_HEADERS := $(CATALOG_HEADERS:%.h=%_d.h) schemapg.h system_fk_info.h diff --git a/src/backend/catalog/aclchk.c b/src/backend/catalog/aclchk.c index ce0a4ff14e..c7e752ac8c 100644 --- a/src/backend/catalog/aclchk.c +++ b/src/backend/catalog/aclchk.c @@ -3427,6 +3427,7 @@ aclcheck_error(AclResult aclerr, ObjectType objtype, case OBJECT_DEFAULT: case OBJECT_DEFACL: case OBJECT_DOMCONSTRAINT: + case OBJECT_PERIOD: case OBJECT_PUBLICATION_NAMESPACE: case OBJECT_PUBLICATION_REL: case OBJECT_ROLE: @@ -3567,6 +3568,7 @@ aclcheck_error(AclResult aclerr, ObjectType objtype, case OBJECT_DEFAULT: case OBJECT_DEFACL: case OBJECT_DOMCONSTRAINT: + case OBJECT_PERIOD: case OBJECT_PUBLICATION_NAMESPACE: case OBJECT_PUBLICATION_REL: case OBJECT_ROLE: diff --git a/src/backend/catalog/dependency.c b/src/backend/catalog/dependency.c index fe9c714257..558b5afb99 100644 --- a/src/backend/catalog/dependency.c +++ b/src/backend/catalog/dependency.c @@ -46,6 +46,7 @@ #include "catalog/pg_opclass.h" #include "catalog/pg_operator.h" #include "catalog/pg_opfamily.h" +#include "catalog/pg_period.h" #include "catalog/pg_policy.h" #include "catalog/pg_proc.h" #include "catalog/pg_publication.h" @@ -151,6 +152,7 @@ static const Oid object_classes[] = { CastRelationId, /* OCLASS_CAST */ CollationRelationId, /* OCLASS_COLLATION */ ConstraintRelationId, /* OCLASS_CONSTRAINT */ + PeriodRelationId, /* OCLASS_PERIOD */ ConversionRelationId, /* OCLASS_CONVERSION */ AttrDefaultRelationId, /* OCLASS_DEFAULT */ LanguageRelationId, /* OCLASS_LANGUAGE */ @@ -1431,6 +1433,10 @@ doDeletion(const ObjectAddress *object, int flags) RemoveConstraintById(object->objectId); break; + case OCLASS_PERIOD: + RemovePeriodById(object->objectId); + break; + case OCLASS_DEFAULT: RemoveAttrDefaultById(object->objectId); break; @@ -2781,6 +2787,9 @@ getObjectClass(const ObjectAddress *object) case ConstraintRelationId: return OCLASS_CONSTRAINT; + case PeriodRelationId: + return OCLASS_PERIOD; + case ConversionRelationId: return OCLASS_CONVERSION; diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c index 81cc39fb70..49cb627f5e 100644 --- a/src/backend/catalog/heap.c +++ b/src/backend/catalog/heap.c @@ -56,6 +56,7 @@ #include "catalog/pg_namespace.h" #include "catalog/pg_opclass.h" #include "catalog/pg_partitioned_table.h" +#include "catalog/pg_period.h" #include "catalog/pg_statistic.h" #include "catalog/pg_subscription_rel.h" #include "catalog/pg_tablespace.h" @@ -2203,6 +2204,72 @@ SetAttrMissing(Oid relid, char *attname, char *value) table_close(tablerel, AccessExclusiveLock); } +/* + * Store a period of relation rel. + * + * Returns the OID of the new pg_period tuple. + */ +Oid +StorePeriod(Relation rel, const char *periodname, AttrNumber startnum, + AttrNumber endnum, Oid rngtypid, Oid conoid) +{ + Datum values[Natts_pg_period]; + bool nulls[Natts_pg_period]; + Relation pg_period; + HeapTuple tuple; + Oid oid; + NameData pername; + ObjectAddress myself, referenced; + + namestrcpy(&pername, periodname); + + MemSet(values, 0, sizeof(values)); + MemSet(nulls, false, sizeof(nulls)); + + pg_period = table_open(PeriodRelationId, RowExclusiveLock); + + oid = GetNewOidWithIndex(pg_period, AttrDefaultOidIndexId, Anum_pg_period_oid); + values[Anum_pg_period_oid - 1] = ObjectIdGetDatum(oid); + values[Anum_pg_period_pername - 1] = NameGetDatum(&pername); + values[Anum_pg_period_perrelid - 1] = RelationGetRelid(rel); + values[Anum_pg_period_perstart - 1] = startnum; + values[Anum_pg_period_perend - 1] = endnum; + values[Anum_pg_period_perrngtype - 1] = rngtypid; + values[Anum_pg_period_perconstraint - 1] = conoid; + values[Anum_pg_period_perislocal - 1] = true; + values[Anum_pg_period_perinhcount - 1] = 0; + + tuple = heap_form_tuple(RelationGetDescr(pg_period), values, nulls); + CatalogTupleInsert(pg_period, tuple); + + ObjectAddressSet(myself, PeriodRelationId, oid); + + /* Drop the period when the table is dropped. */ + ObjectAddressSet(referenced, RelationRelationId, RelationGetRelid(rel)); + recordDependencyOn(&myself, &referenced, DEPENDENCY_AUTO); + + /* Forbid dropping the columns of the period. */ + ObjectAddressSubSet(referenced, RelationRelationId, RelationGetRelid(rel), startnum); + recordDependencyOn(&myself, &referenced, DEPENDENCY_NORMAL); + ObjectAddressSubSet(referenced, RelationRelationId, RelationGetRelid(rel), endnum); + recordDependencyOn(&myself, &referenced, DEPENDENCY_NORMAL); + + /* Make sure we don't lose our rangetype. */ + ObjectAddressSet(referenced, TypeRelationId, rngtypid); + recordDependencyOn(&myself, &referenced, DEPENDENCY_NORMAL); + + /* + * The constraint is an implementation detail, so we mark it as such. + * (Note that myself and referenced are reversed for this one.) + */ + ObjectAddressSet(referenced, ConstraintRelationId, conoid); + recordDependencyOn(&referenced, &myself, DEPENDENCY_INTERNAL); + + table_close(pg_period, RowExclusiveLock); + + return oid; +} + /* * Store a default expression for column attnum of relation rel. * diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql index 11d9dd60c2..5758703f7a 100644 --- a/src/backend/catalog/information_schema.sql +++ b/src/backend/catalog/information_schema.sql @@ -1198,7 +1198,29 @@ GRANT SELECT ON parameters TO PUBLIC; * PERIODS view */ --- feature not supported +CREATE VIEW periods AS + SELECT current_database()::information_schema.sql_identifier AS table_catalog, + nc.nspname::information_schema.sql_identifier AS table_schema, + c.relname::information_schema.sql_identifier AS table_name, + p.pername::information_schema.sql_identifier AS period_name, + CASE WHEN pg_has_role(c.relowner, 'USAGE') + OR has_column_privilege(sa.attrelid, sa.attnum, 'SELECT, INSERT, UPDATE, REFERENCES') + THEN sa.attname::information_schema.sql_identifier + END AS start_column_name, + CASE WHEN pg_has_role(c.relowner, 'USAGE') + OR has_column_privilege(ea.attrelid, ea.attnum, 'SELECT, INSERT, UPDATE, REFERENCES') + THEN ea.attname::information_schema.sql_identifier + END AS end_column_name + FROM pg_period AS p + JOIN pg_class AS c ON c.oid = p.perrelid + JOIN pg_namespace AS nc ON nc.oid = c.relnamespace + JOIN pg_attribute AS sa ON (sa.attrelid, sa.attnum) = (p.perrelid, p.perstart) + JOIN pg_attribute AS ea ON (ea.attrelid, ea.attnum) = (p.perrelid, p.perend) + WHERE NOT pg_is_other_temp_schema(nc.oid) + --AND c.relkind = ANY (ARRAY['r']); + AND c.relkind IN ('r', 'v'); + +GRANT SELECT ON periods TO PUBLIC; /* diff --git a/src/backend/catalog/objectaddress.c b/src/backend/catalog/objectaddress.c index 2bae3fbb17..8b714b980c 100644 --- a/src/backend/catalog/objectaddress.c +++ b/src/backend/catalog/objectaddress.c @@ -45,6 +45,7 @@ #include "catalog/pg_opclass.h" #include "catalog/pg_operator.h" #include "catalog/pg_opfamily.h" +#include "catalog/pg_period.h" #include "catalog/pg_policy.h" #include "catalog/pg_proc.h" #include "catalog/pg_publication.h" @@ -714,6 +715,10 @@ static const struct object_type_map { "domain constraint", OBJECT_DOMCONSTRAINT }, + /* OCLASS_PERIOD */ + { + "period", OBJECT_PERIOD + }, /* OCLASS_CONVERSION */ { "conversion", OBJECT_CONVERSION @@ -983,6 +988,7 @@ get_object_address(ObjectType objtype, Node *object, case OBJECT_TRIGGER: case OBJECT_TABCONSTRAINT: case OBJECT_POLICY: + case OBJECT_PERIOD: address = get_object_address_relobject(objtype, castNode(List, object), &relation, missing_ok); break; @@ -1478,6 +1484,13 @@ get_object_address_relobject(ObjectType objtype, List *object, InvalidOid; address.objectSubId = 0; break; + case OBJECT_PERIOD: + address.classId = PeriodRelationId; + address.objectId = relation ? + get_relation_period_oid(reloid, depname, missing_ok) : + InvalidOid; + address.objectSubId = 0; + break; default: elog(ERROR, "unrecognized objtype: %d", (int) objtype); } @@ -2320,6 +2333,7 @@ pg_get_object_address(PG_FUNCTION_ARGS) case OBJECT_RULE: case OBJECT_TRIGGER: case OBJECT_TABCONSTRAINT: + case OBJECT_PERIOD: case OBJECT_OPCLASS: case OBJECT_OPFAMILY: objnode = (Node *) name; @@ -2435,6 +2449,7 @@ check_object_ownership(Oid roleid, ObjectType objtype, ObjectAddress address, case OBJECT_TRIGGER: case OBJECT_POLICY: case OBJECT_TABCONSTRAINT: + case OBJECT_PERIOD: if (!pg_class_ownercheck(RelationGetRelid(relation), roleid)) aclcheck_error(ACLCHECK_NOT_OWNER, objtype, RelationGetRelationName(relation)); @@ -3128,6 +3143,38 @@ getObjectDescription(const ObjectAddress *object, bool missing_ok) break; } + case OCLASS_PERIOD: + { + HeapTuple perTup; + Form_pg_period per; + + perTup = SearchSysCache1(PERIODOID, + ObjectIdGetDatum(object->objectId)); + if (!HeapTupleIsValid(perTup)) + elog(ERROR, "cache lookup failed for period %u", + object->objectId); + per = (Form_pg_period) GETSTRUCT(perTup); + + if (OidIsValid(per->perrelid)) + { + StringInfoData rel; + + initStringInfo(&rel); + getRelationDescription(&rel, per->perrelid, false); + appendStringInfo(&buffer, _("period %s on %s"), + NameStr(per->pername), rel.data); + pfree(rel.data); + } + else + { + appendStringInfo(&buffer, _("period %s"), + NameStr(per->pername)); + } + + ReleaseSysCache(perTup); + break; + } + case OCLASS_CONVERSION: { HeapTuple conTup; @@ -4478,6 +4525,10 @@ getObjectTypeDescription(const ObjectAddress *object, bool missing_ok) missing_ok); break; + case OCLASS_PERIOD: + appendStringInfoString(&buffer, "period"); + break; + case OCLASS_CONVERSION: appendStringInfoString(&buffer, "conversion"); break; @@ -4977,6 +5028,28 @@ getObjectIdentityParts(const ObjectAddress *object, break; } + case OCLASS_PERIOD: + { + HeapTuple perTup; + Form_pg_period per; + + perTup = SearchSysCache1(PERIODOID, + ObjectIdGetDatum(object->objectId)); + if (!HeapTupleIsValid(perTup)) + elog(ERROR, "cache lookup failed for period %u", + object->objectId); + per = (Form_pg_period) GETSTRUCT(perTup); + + appendStringInfo(&buffer, "%s on ", + quote_identifier(NameStr(per->pername))); + getRelationIdentity(&buffer, per->perrelid, objname, false); + if (objname) + *objname = lappend(*objname, pstrdup(NameStr(per->pername))); + + ReleaseSysCache(perTup); + break; + } + case OCLASS_CONVERSION: { HeapTuple conTup; diff --git a/src/backend/catalog/pg_period.c b/src/backend/catalog/pg_period.c new file mode 100644 index 0000000000..40ec3fbdbd --- /dev/null +++ b/src/backend/catalog/pg_period.c @@ -0,0 +1,106 @@ +/*------------------------------------------------------------------------- + * + * pg_period.c + * routines to support manipulation of the pg_period relation + * + * Portions Copyright (c) 1996-2021, PostgreSQL Global Development Group + * + * + * IDENTIFICATION + * src/backend/catalog/pg_period.c + * + *------------------------------------------------------------------------- + */ +#include "postgres.h" + +#include "access/genam.h" +#include "access/heapam.h" +#include "access/htup_details.h" +#include "catalog/indexing.h" +#include "catalog/pg_period.h" +#include "utils/fmgroids.h" +#include "utils/lsyscache.h" +#include "utils/rel.h" +#include "utils/syscache.h" + + +/* + * Delete a single period record. + */ +void +RemovePeriodById(Oid periodId) +{ + Relation pg_period; + HeapTuple tup; + + pg_period = table_open(PeriodRelationId, RowExclusiveLock); + + tup = SearchSysCache1(PERIODOID, ObjectIdGetDatum(periodId)); + if (!HeapTupleIsValid(tup)) /* should not happen */ + elog(ERROR, "cache lookup failed for period %u", periodId); + + /* Fry the period itself */ + CatalogTupleDelete(pg_period, &tup->t_self); + + /* Clean up */ + ReleaseSysCache(tup); + table_close(pg_period, RowExclusiveLock); +} + +/* + * get_relation_period_oid + * Find a period on the specified relation with the specified name. + * Returns period's OID. + */ +Oid +get_relation_period_oid(Oid relid, const char *pername, bool missing_ok) +{ + Relation pg_period; + HeapTuple tuple; + SysScanDesc scan; + ScanKeyData skey[1]; + Oid perOid = InvalidOid; + + /* + * Fetch the period tuple from pg_period. There may be more than + * one match, because periods are not required to have unique names; + * if so, error out. + */ + pg_period = table_open(PeriodRelationId, AccessShareLock); + + ScanKeyInit(&skey[0], + Anum_pg_period_perrelid, + BTEqualStrategyNumber, F_OIDEQ, + ObjectIdGetDatum(relid)); + + scan = systable_beginscan(pg_period, PeriodRelidNameIndexId, true, + NULL, 1, skey); + + while (HeapTupleIsValid(tuple = systable_getnext(scan))) + { + Form_pg_period period = (Form_pg_period) GETSTRUCT(tuple); + + if (strcmp(NameStr(period->pername), pername) == 0) + { + if (OidIsValid(perOid)) + ereport(ERROR, + (errcode(ERRCODE_DUPLICATE_OBJECT), + errmsg("table \"%s\" has multiple periods named \"%s\"", + get_rel_name(relid), pername))); + perOid = period->oid; + } + } + + systable_endscan(scan); + + /* If no such period exists, complain */ + if (!OidIsValid(perOid) && !missing_ok) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("period \"%s\" for table \"%s\" does not exist", + pername, get_rel_name(relid)))); + + table_close(pg_period, AccessShareLock); + + return perOid; +} diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt index 9f424216e2..9662d713c9 100644 --- a/src/backend/catalog/sql_features.txt +++ b/src/backend/catalog/sql_features.txt @@ -438,7 +438,7 @@ T176 Sequence generator support NO supported except for NEXT VALUE FOR T177 Sequence generator support: simple restart option YES T178 Identity columns: simple restart option YES T180 System-versioned tables NO -T181 Application-time period tables NO +T181 Application-time period tables YES T191 Referential action RESTRICT YES T201 Comparable data types for referential constraints YES T211 Basic trigger capability NO diff --git a/src/backend/commands/alter.c b/src/backend/commands/alter.c index 40044070cf..6b2efae03f 100644 --- a/src/backend/commands/alter.c +++ b/src/backend/commands/alter.c @@ -640,6 +640,7 @@ AlterObjectNamespace_oid(Oid classId, Oid objid, Oid nspOid, case OCLASS_CAST: case OCLASS_CONSTRAINT: + case OCLASS_PERIOD: case OCLASS_DEFAULT: case OCLASS_LANGUAGE: case OCLASS_LARGEOBJECT: diff --git a/src/backend/commands/comment.c b/src/backend/commands/comment.c index d4943e374a..ea59c2fa04 100644 --- a/src/backend/commands/comment.c +++ b/src/backend/commands/comment.c @@ -102,6 +102,16 @@ CommentObject(CommentStmt *stmt) RelationGetRelationName(relation)), errdetail_relkind_not_supported(relation->rd_rel->relkind))); break; + + case OBJECT_PERIOD: + /* Periods can only go on tables */ + if (relation->rd_rel->relkind != RELKIND_RELATION) + ereport(ERROR, + (errcode(ERRCODE_WRONG_OBJECT_TYPE), + errmsg("\"%s\" is not a table", + RelationGetRelationName(relation)))); + break; + default: break; } diff --git a/src/backend/commands/event_trigger.c b/src/backend/commands/event_trigger.c index df264329d8..bd0804ea1c 100644 --- a/src/backend/commands/event_trigger.c +++ b/src/backend/commands/event_trigger.c @@ -970,6 +970,7 @@ EventTriggerSupportsObjectType(ObjectType obtype) case OBJECT_OPCLASS: case OBJECT_OPERATOR: case OBJECT_OPFAMILY: + case OBJECT_PERIOD: case OBJECT_POLICY: case OBJECT_PROCEDURE: case OBJECT_PUBLICATION: @@ -1026,6 +1027,7 @@ EventTriggerSupportsObjectClass(ObjectClass objclass) case OCLASS_CAST: case OCLASS_COLLATION: case OCLASS_CONSTRAINT: + case OCLASS_PERIOD: case OCLASS_CONVERSION: case OCLASS_DEFAULT: case OCLASS_LANGUAGE: @@ -2126,6 +2128,7 @@ stringify_grant_objtype(ObjectType objtype) case OBJECT_OPCLASS: case OBJECT_OPERATOR: case OBJECT_OPFAMILY: + case OBJECT_PERIOD: case OBJECT_POLICY: case OBJECT_PUBLICATION: case OBJECT_PUBLICATION_NAMESPACE: @@ -2209,6 +2212,7 @@ stringify_adefprivs_objtype(ObjectType objtype) case OBJECT_OPCLASS: case OBJECT_OPERATOR: case OBJECT_OPFAMILY: + case OBJECT_PERIOD: case OBJECT_POLICY: case OBJECT_PUBLICATION: case OBJECT_PUBLICATION_NAMESPACE: diff --git a/src/backend/commands/seclabel.c b/src/backend/commands/seclabel.c index 53c18628a7..eea5de2750 100644 --- a/src/backend/commands/seclabel.c +++ b/src/backend/commands/seclabel.c @@ -78,6 +78,7 @@ SecLabelSupportsObjectType(ObjectType objtype) case OBJECT_OPCLASS: case OBJECT_OPERATOR: case OBJECT_OPFAMILY: + case OBJECT_PERIOD: case OBJECT_POLICY: case OBJECT_PUBLICATION_NAMESPACE: case OBJECT_PUBLICATION_REL: diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index d675d261f7..745e78cdcd 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -40,6 +40,7 @@ #include "catalog/pg_inherits.h" #include "catalog/pg_namespace.h" #include "catalog/pg_opclass.h" +#include "catalog/pg_period.h" #include "catalog/pg_tablespace.h" #include "catalog/pg_statistic_ext.h" #include "catalog/pg_trigger.h" @@ -144,13 +145,14 @@ static List *on_commits = NIL; #define AT_PASS_OLD_CONSTR 3 /* re-add existing constraints */ /* We could support a RENAME COLUMN pass here, but not currently used */ #define AT_PASS_ADD_COL 4 /* ADD COLUMN */ -#define AT_PASS_ADD_CONSTR 5 /* ADD constraints (initial examination) */ -#define AT_PASS_COL_ATTRS 6 /* set column attributes, eg NOT NULL */ -#define AT_PASS_ADD_INDEXCONSTR 7 /* ADD index-based constraints */ -#define AT_PASS_ADD_INDEX 8 /* ADD indexes */ -#define AT_PASS_ADD_OTHERCONSTR 9 /* ADD other constraints, defaults */ -#define AT_PASS_MISC 10 /* other stuff */ -#define AT_NUM_PASSES 11 +#define AT_PASS_ADD_PERIOD 5 /* ADD PERIOD */ +#define AT_PASS_ADD_CONSTR 6 /* ADD constraints (initial examination) */ +#define AT_PASS_COL_ATTRS 7 /* set column attributes, eg NOT NULL */ +#define AT_PASS_ADD_INDEXCONSTR 8 /* ADD index-based constraints */ +#define AT_PASS_ADD_INDEX 9 /* ADD indexes */ +#define AT_PASS_ADD_OTHERCONSTR 10 /* ADD other constraints, defaults */ +#define AT_PASS_MISC 11 /* other stuff */ +#define AT_NUM_PASSES 12 typedef struct AlteredTableInfo { @@ -418,6 +420,8 @@ static ObjectAddress ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, AlterTableUtilityContext *context); static bool check_for_column_name_collision(Relation rel, const char *colname, bool if_not_exists); +static bool check_for_period_name_collision(Relation rel, const char *pername, + bool if_not_exists); static void add_column_datatype_dependency(Oid relid, int32 attnum, Oid typid); static void add_column_collation_dependency(Oid relid, int32 attnum, Oid collid); static void ATPrepDropNotNull(Relation rel, bool recurse, bool recursing); @@ -437,6 +441,12 @@ static ObjectAddress ATExecColumnDefault(Relation rel, const char *colName, Node *newDefault, LOCKMODE lockmode); static ObjectAddress ATExecCookedColumnDefault(Relation rel, AttrNumber attnum, Node *newDefault); +static ObjectAddress ATExecAddPeriod(Relation rel, Period *period, LOCKMODE lockmode, + AlterTableUtilityContext *context); +static void ATExecDropPeriod(Relation rel, const char *periodName, + DropBehavior behavior, + bool recurse, bool recursing, + bool missing_ok, LOCKMODE lockmode); static ObjectAddress ATExecAddIdentity(Relation rel, const char *colName, Node *def, LOCKMODE lockmode); static ObjectAddress ATExecSetIdentity(Relation rel, const char *colName, @@ -602,6 +612,7 @@ static void refuseDupeIndexAttach(Relation parentIdx, Relation partIdx, static List *GetParentedForeignKeyRefs(Relation partition); static void ATDetachCheckNoForeignKeyRefs(Relation partition); static char GetAttributeCompression(Oid atttypid, char *compression); +static void AddRelationNewPeriod(Relation rel, Period *period); /* ---------------------------------------------------------------- @@ -1213,6 +1224,17 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId, AddRelationNewConstraints(rel, NIL, stmt->constraints, true, true, false, queryString); + /* + * Create periods for the table. This must come after we create columns + * and before we create index constraints. It will automatically create + * NOT NULL and CHECK constraints for the period. + */ + foreach(listptr, stmt->periods) + { + Period *period = (Period *) lfirst(listptr); + AddRelationNewPeriod(rel, period); + } + ObjectAddressSet(address, RelationRelationId, relationId); /* @@ -1224,6 +1246,161 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId, return address; } +static Constraint * +make_period_not_backward(Relation rel, Period *period, char *constraintname) +{ + ColumnRef *scol, *ecol; + Constraint *constr; + + if (constraintname == NULL) + constraintname = ChooseConstraintName(RelationGetRelationName(rel), + period->periodname, + "check", + RelationGetNamespace(rel), + NIL); + period->constraintname = constraintname; + + scol = makeNode(ColumnRef); + scol->fields = list_make1(makeString(pstrdup(period->startcolname))); + scol->location = 0; + + ecol = makeNode(ColumnRef); + ecol->fields = list_make1(makeString(pstrdup(period->endcolname))); + ecol->location = 0; + + constr = makeNode(Constraint); + constr->contype = CONSTR_CHECK; + constr->conname = constraintname; + constr->deferrable = false; + constr->initdeferred = false; + constr->location = -1; + constr->is_no_inherit = false; + constr->raw_expr = (Node *) makeSimpleA_Expr(AEXPR_OP, "<", (Node *) scol, (Node *) ecol, 0); + constr->cooked_expr = NULL; + constr->skip_validation = false; + constr->initially_valid = true; + + return constr; +} + +static void +validate_period(Relation rel, Period *period, HeapTuple *starttuple, HeapTuple *endtuple, Relation *attrelation, Form_pg_attribute *startatttuple, AttrNumber *startattnum, AttrNumber *endattnum) +{ + Form_pg_attribute endatttuple; + *attrelation = table_open(AttributeRelationId, RowExclusiveLock); + + /* Find the start column */ + *starttuple = SearchSysCacheCopyAttName(RelationGetRelid(rel), period->startcolname); + if (!HeapTupleIsValid(*starttuple)) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_COLUMN), + errmsg("column \"%s\" of relation \"%s\" does not exist", + period->startcolname, RelationGetRelationName(rel)))); + *startatttuple = (Form_pg_attribute) GETSTRUCT(*starttuple); + *startattnum = (*startatttuple)->attnum; + + /* Make sure it's not a system column */ + if (*startattnum <= 0) + ereport(ERROR, + (errcode(ERRCODE_INVALID_OBJECT_DEFINITION), + errmsg("cannot use system column \"%s\" in period", + period->startcolname))); + + /* Find the end column */ + *endtuple = SearchSysCacheCopyAttName(RelationGetRelid(rel), period->endcolname); + if (!HeapTupleIsValid(*endtuple)) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_COLUMN), + errmsg("column \"%s\" of relation \"%s\" does not exist", + period->endcolname, RelationGetRelationName(rel)))); + endatttuple = (Form_pg_attribute) GETSTRUCT(*endtuple); + *endattnum = endatttuple->attnum; + + /* Make sure it's not a system column */ + if (*endattnum <= 0) + ereport(ERROR, + (errcode(ERRCODE_INVALID_OBJECT_DEFINITION), + errmsg("cannot use system column \"%s\" in period", + period->endcolname))); + + /* Both columns must be of same type */ + if ((*startatttuple)->atttypid != endatttuple->atttypid) + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("start and end columns of period must be of same type"))); + + /* Both columns must have the same collation */ + if ((*startatttuple)->attcollation != endatttuple->attcollation) + ereport(ERROR, + (errcode(ERRCODE_COLLATION_MISMATCH), + errmsg("start and end columns of period must have same collation"))); +} + +/* + * make_constraint_for_period + * + * Add constraints to make both columns NOT NULL and CHECK (start < end). + * + * Returns the CHECK constraint Oid. + */ +static Oid +make_constraint_for_period(Relation rel, Period *period, char *constraintname, + LOCKMODE lockmode, AlterTableUtilityContext *context) +{ + List *cmds = NIL; + AlterTableCmd *cmd; + Constraint *constr; + + constr = make_period_not_backward(rel, period, constraintname); + cmd = makeNode(AlterTableCmd); + cmd->subtype = AT_AddConstraint; + cmd->def = (Node *) constr; + cmds = lappend(cmds, cmd); + + /* Do the deed. */ + AlterTableInternal(RelationGetRelid(rel), cmds, true, context); + + return get_relation_constraint_oid(RelationGetRelid(rel), period->constraintname, false); +} + +static void +AddRelationNewPeriod(Relation rel, Period *period) +{ + Relation attrelation; + HeapTuple starttuple, endtuple; + Form_pg_attribute startatttuple; + AttrNumber startattnum, endattnum; + Oid conoid; + Constraint *constr; + List *newconstrs; + + /* The period name must not already exist */ + (void) check_for_period_name_collision(rel, period->periodname, false); + + validate_period(rel, period, &starttuple, &endtuple, &attrelation, &startatttuple, &startattnum, &endattnum); + + heap_freetuple(starttuple); + heap_freetuple(endtuple); + + if (period->constraintname == NULL) + { + period->constraintname = ChooseConstraintName(RelationGetRelationName(rel), + period->periodname, + "check", + RelationGetNamespace(rel), + NIL); + } + + constr = make_period_not_backward(rel, period, period->constraintname); + newconstrs = AddRelationNewConstraints(rel, NIL, list_make1(constr), false, true, true, NULL); + conoid = ((CookedConstraint *) linitial(newconstrs))->conoid; + + /* Save it */ + StorePeriod(rel, period->periodname, startattnum, endattnum, period->rngtypid, conoid); + + table_close(attrelation, RowExclusiveLock); +} + /* * Emit the right error or warning message for a "DROP" command issued on a * non-existent relation @@ -2284,6 +2461,8 @@ MergeAttributes(List *schema, List *supers, char relpersistence, * * Note that we also need to check that we do not exceed this figure after * including columns from inherited relations. + * + * TODO: What about periods? */ if (list_length(schema) > MaxHeapAttributeNumber) ereport(ERROR, @@ -2368,6 +2547,8 @@ MergeAttributes(List *schema, List *supers, char relpersistence, * Scan the parents left-to-right, and merge their attributes to form a * list of inherited attributes (inhSchema). Also check to see if we need * to inherit an OID column. + * + * TODO: probably need periods here, too. */ child_attno = 0; foreach(entry, supers) @@ -4048,12 +4229,12 @@ AlterTable(AlterTableStmt *stmt, LOCKMODE lockmode, * existing query plans. On the assumption it's not used for such, we * don't have to reject pending AFTER triggers, either. * - * Also, since we don't have an AlterTableUtilityContext, this cannot be + * Also, if you don't pass an AlterTableUtilityContext, this cannot be * used for any subcommand types that require parse transformation or * could generate subcommands that have to be passed to ProcessUtility. */ void -AlterTableInternal(Oid relid, List *cmds, bool recurse) +AlterTableInternal(Oid relid, List *cmds, bool recurse, AlterTableUtilityContext *context) { Relation rel; LOCKMODE lockmode = AlterTableGetLockLevel(cmds); @@ -4062,7 +4243,7 @@ AlterTableInternal(Oid relid, List *cmds, bool recurse) EventTriggerAlterTableRelid(relid); - ATController(NULL, rel, cmds, recurse, lockmode, NULL); + ATController(NULL, rel, cmds, recurse, lockmode, context); } /* @@ -4124,6 +4305,20 @@ AlterTableGetLockLevel(List *cmds) cmd_lockmode = AccessExclusiveLock; break; + /* + * Adding a period may conflict with a column name so we need + * an exclusive lock to make sure columns aren't added + * concurrently. + * + * It also adds a CHECK constraint so we need to match that + * level, and dropping a period drops the constraint so that + * level needs to be matched, too. + */ + case AT_AddPeriod: + case AT_DropPeriod: + cmd_lockmode = AccessExclusiveLock; + break; + /* * These subcommands may require addition of toast tables. If * we add a toast table to a table currently being scanned, we @@ -4479,6 +4674,18 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd, /* This command never recurses */ pass = AT_PASS_ADD_OTHERCONSTR; break; + case AT_AddPeriod: /* ALTER TABLE ... ADD PERIOD FOR name (start, end) */ + ATSimplePermissions(cmd->subtype, rel, ATT_TABLE); + /* + * We must add PERIODs after columns, in case they reference a newly-added column, + * and before constraints, in case a newly-added PK/FK references them. + */ + pass = AT_PASS_ADD_PERIOD; + break; + case AT_DropPeriod: /* ALTER TABLE ... DROP PERIOD FOR name */ + ATSimplePermissions(cmd->subtype, rel, ATT_TABLE); + pass = AT_PASS_DROP; + break; case AT_AddIdentity: ATSimplePermissions(cmd->subtype, rel, ATT_TABLE | ATT_VIEW | ATT_FOREIGN_TABLE); /* This command never recurses */ @@ -4878,6 +5085,14 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab, case AT_CookedColumnDefault: /* add a pre-cooked default */ address = ATExecCookedColumnDefault(rel, cmd->num, cmd->def); break; + case AT_AddPeriod: + address = ATExecAddPeriod(rel, (Period *) cmd->def, lockmode, context); + break; + case AT_DropPeriod: + ATExecDropPeriod(rel, cmd->name, cmd->behavior, + false, false, + cmd->missing_ok, lockmode); + break; case AT_AddIdentity: cmd = ATParseTransformCmd(wqueue, tab, rel, cmd, false, lockmode, cur_pass, context); @@ -6008,6 +6223,8 @@ alter_table_type_to_string(AlterTableType cmdtype) case AT_AddColumnRecurse: case AT_AddColumnToView: return "ADD COLUMN"; + case AT_AddPeriod: + return "ADD PERIOD"; case AT_ColumnDefault: case AT_CookedColumnDefault: return "ALTER COLUMN ... SET DEFAULT"; @@ -6032,6 +6249,8 @@ alter_table_type_to_string(AlterTableType cmdtype) case AT_DropColumn: case AT_DropColumnRecurse: return "DROP COLUMN"; + case AT_DropPeriod: + return "DROP PERIOD"; case AT_AddIndex: case AT_ReAddIndex: return NULL; /* not real grammar */ @@ -6986,14 +7205,29 @@ ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel, /* * If a new or renamed column will collide with the name of an existing * column and if_not_exists is false then error out, else do nothing. + * + * See also check_for_period_name_collision. */ static bool check_for_column_name_collision(Relation rel, const char *colname, bool if_not_exists) { - HeapTuple attTuple; + HeapTuple attTuple, perTuple; int attnum; + /* If the name exists as a period, we're done. */ + perTuple = SearchSysCache2(PERIODNAME, + ObjectIdGetDatum(RelationGetRelid(rel)), + PointerGetDatum(colname)); + if (HeapTupleIsValid(perTuple)) + { + ReleaseSysCache(perTuple); + ereport(ERROR, + (errcode(ERRCODE_DUPLICATE_COLUMN), + errmsg("column name \"%s\" conflicts with a period name", + colname))); + } + /* * this test is deliberately not attisdropped-aware, since if one tries to * add a column matching a dropped column name, it's gonna fail anyway. @@ -7037,6 +7271,78 @@ check_for_column_name_collision(Relation rel, const char *colname, return true; } +/* + * If a new period name will collide with the name of an existing column or + * period [and if_not_exists is false] then error out, else do nothing. + * + * See also check_for_column_name_collision. + */ +static bool +check_for_period_name_collision(Relation rel, const char *pername, + bool if_not_exists) +{ + HeapTuple attTuple, perTuple; + int attnum; + + /* TODO: implement IF [NOT] EXISTS for periods */ + Assert(!if_not_exists); + + /* If there is already a period with this name, then we're done. */ + perTuple = SearchSysCache2(PERIODNAME, + ObjectIdGetDatum(RelationGetRelid(rel)), + PointerGetDatum(pername)); + if (HeapTupleIsValid(perTuple)) + { + if (if_not_exists) + { + ReleaseSysCache(perTuple); + + ereport(NOTICE, + (errcode(ERRCODE_DUPLICATE_COLUMN), + errmsg("period \"%s\" of relation \"%s\" already exists, skipping", + pername, RelationGetRelationName(rel)))); + return false; + } + + ereport(ERROR, + (errcode(ERRCODE_DUPLICATE_COLUMN), + errmsg("period \"%s\" of relation \"%s\" already exists", + pername, RelationGetRelationName(rel)))); + } + + /* + * this test is deliberately not attisdropped-aware, since if one tries to + * add a column matching a dropped column name, it's gonna fail anyway. + * + * XXX: Does this hold for periods? + */ + attTuple = SearchSysCache2(ATTNAME, + ObjectIdGetDatum(RelationGetRelid(rel)), + PointerGetDatum(pername)); + if (HeapTupleIsValid(attTuple)) + { + attnum = ((Form_pg_attribute) GETSTRUCT(attTuple))->attnum; + ReleaseSysCache(attTuple); + + /* + * We throw a different error message for conflicts with system column + * names, since they are normally not shown and the user might otherwise + * be confused about the reason for the conflict. + */ + if (attnum <= 0) + ereport(ERROR, + (errcode(ERRCODE_DUPLICATE_COLUMN), + errmsg("period name \"%s\" conflicts with a system column name", + pername))); + ereport(ERROR, + (errcode(ERRCODE_DUPLICATE_COLUMN), + errmsg("period name \"%s\" conflicts with a column name", + pername))); + } + + return true; +} + /* * Install a column's dependency on its datatype. */ @@ -7552,6 +7858,177 @@ ATExecCookedColumnDefault(Relation rel, AttrNumber attnum, return address; } +/* + * ALTER TABLE ADD PERIOD + * + * Return the address of the period. + */ +static ObjectAddress +ATExecAddPeriod(Relation rel, Period *period, LOCKMODE lockmode, + AlterTableUtilityContext *context) +{ + Relation attrelation; + HeapTuple starttuple, endtuple; + Form_pg_attribute startatttuple; + AttrNumber startattnum, endattnum; + Oid coltypid, rngtypid, conoid, periodoid; + ObjectAddress address = InvalidObjectAddress; + + /* + * PERIOD FOR SYSTEM_TIME is not yet implemented, but make sure no one uses + * the name. + */ + if (strcmp(period->periodname, "system_time") == 0) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("PERIOD FOR SYSTEM_TIME is not supported"))); + + /* The period name must not already exist */ + (void) check_for_period_name_collision(rel, period->periodname, false); + + /* Parse options */ + transformPeriodOptions(period); + + validate_period(rel, period, &starttuple, &endtuple, &attrelation, &startatttuple, &startattnum, &endattnum); + + /* + * Find a suitable range type for operations involving this period. + * Use the rangetype option if provided, otherwise try to find a + * non-ambiguous existing type. + */ + coltypid = startatttuple->atttypid; + if (period->rangetypename != NULL) + { + /* Make sure it exists */ + rngtypid = TypenameGetTypidExtended(period->rangetypename, false); + if (rngtypid == InvalidOid) + ereport(ERROR, (errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("Range type %s not found", period->rangetypename))); + + /* Make sure it is a range type */ + if (!type_is_range(rngtypid)) + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("Type %s is not a range type", period->rangetypename))); + + /* Make sure it matches the column type */ + if (get_range_subtype(rngtypid) != coltypid) + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("Range type %s does not match column type %s", + period->rangetypename, + format_type_be(coltypid)))); + } + else + { + rngtypid = get_subtype_range(coltypid); + if (rngtypid == InvalidOid) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("no range type for %s found for period %s", + format_type_be(coltypid), + period->periodname), + errhint("You can define a custom range type with CREATE TYPE"))); + + } + + heap_freetuple(starttuple); + heap_freetuple(endtuple); + + conoid = make_constraint_for_period(rel, period, period->constraintname, lockmode, context); + + /* Save it */ + periodoid = StorePeriod(rel, period->periodname, startattnum, endattnum, rngtypid, conoid); + + ObjectAddressSet(address, PeriodRelationId, periodoid); + + table_close(attrelation, RowExclusiveLock); + + return address; +} + +/* + * ALTER TABLE DROP PERIOD + * + * Like DROP COLUMN, we can't use the normal ALTER TABLE recursion mechanism. + */ +static void +ATExecDropPeriod(Relation rel, const char *periodName, + DropBehavior behavior, + bool recurse, bool recursing, + bool missing_ok, LOCKMODE lockmode) +{ + Relation pg_period; + Form_pg_period period; + SysScanDesc scan; + ScanKeyData key; + HeapTuple tuple; + bool found = false; + + /* At top level, permission check was done in ATPrepCmd, else do it */ + if (recursing) + ATSimplePermissions(AT_DropPeriod, rel, ATT_TABLE); + + pg_period = table_open(PeriodRelationId, RowExclusiveLock); + + /* + * Find and drop the target period + */ + ScanKeyInit(&key, + Anum_pg_period_perrelid, + BTEqualStrategyNumber, F_OIDEQ, + ObjectIdGetDatum(RelationGetRelid(rel))); + scan = systable_beginscan(pg_period, PeriodRelidNameIndexId, + true, NULL, 1, &key); + + while (HeapTupleIsValid(tuple = systable_getnext(scan))) + { + ObjectAddress perobj; + + period = (Form_pg_period) GETSTRUCT(tuple); + + if (strcmp(NameStr(period->pername), periodName) != 0) + continue; + + /* + * Perform the actual period deletion + */ + perobj.classId = PeriodRelationId; + perobj.objectId = period->oid; + perobj.objectSubId = 0; + + performDeletion(&perobj, behavior, 0); + + found = true; + + /* period found and dropped -- no need to keep looping */ + break; + } + + systable_endscan(scan); + + if (!found) + { + if (!missing_ok) + { + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("period \"%s\" on relation \"%s\" does not exist", + periodName, RelationGetRelationName(rel)))); + } + else + { + ereport(NOTICE, + (errmsg("period \"%s\" on relation \"%s\" does not exist, skipping", + periodName, RelationGetRelationName(rel)))); + table_close(pg_period, RowExclusiveLock); + return; + } + } + + table_close(pg_period, RowExclusiveLock); +} + /* * ALTER TABLE ALTER COLUMN ADD IDENTITY * @@ -12193,6 +12670,15 @@ ATExecAlterColumnType(AlteredTableInfo *tab, Relation rel, RememberConstraintForRebuilding(foundObject.objectId, tab); break; + case OCLASS_PERIOD: + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot alter type of a column used by a period"), + errdetail("%s depends on column \"%s\"", + getObjectDescription(&foundObject, false), + colName))); + break; + case OCLASS_REWRITE: /* XXX someday see if we can cope with revising views */ ereport(ERROR, @@ -14217,7 +14703,7 @@ AlterTableMoveAll(AlterTableMoveAllStmt *stmt) EventTriggerAlterTableStart((Node *) stmt); /* OID is set by AlterTableInternal */ - AlterTableInternal(lfirst_oid(l), cmds, false); + AlterTableInternal(lfirst_oid(l), cmds, false, NULL); EventTriggerAlterTableEnd(); } diff --git a/src/backend/commands/view.c b/src/backend/commands/view.c index 4df05a0b33..22bc499f47 100644 --- a/src/backend/commands/view.c +++ b/src/backend/commands/view.c @@ -169,7 +169,7 @@ DefineVirtualRelation(RangeVar *relation, List *tlist, bool replace, } /* EventTriggerAlterTableStart called by ProcessUtilitySlow */ - AlterTableInternal(viewOid, atcmds, true); + AlterTableInternal(viewOid, atcmds, true, NULL); /* Make the new view columns visible */ CommandCounterIncrement(); @@ -201,7 +201,7 @@ DefineVirtualRelation(RangeVar *relation, List *tlist, bool replace, atcmds = list_make1(atcmd); /* EventTriggerAlterTableStart called by ProcessUtilitySlow */ - AlterTableInternal(viewOid, atcmds, true); + AlterTableInternal(viewOid, atcmds, true, NULL); ObjectAddressSet(address, RelationRelationId, viewOid); diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c index ad1ea2ff2f..bbaee19905 100644 --- a/src/backend/nodes/copyfuncs.c +++ b/src/backend/nodes/copyfuncs.c @@ -3089,6 +3089,21 @@ _copyConstraint(const Constraint *from) return newnode; } +static Period * +_copyPeriod(const Period *from) +{ + Period *newnode = makeNode(Period); + + COPY_STRING_FIELD(periodname); + COPY_STRING_FIELD(startcolname); + COPY_STRING_FIELD(endcolname); + COPY_NODE_FIELD(options); + COPY_SCALAR_FIELD(rngtypid); + COPY_LOCATION_FIELD(location); + + return newnode; +} + static DefElem * _copyDefElem(const DefElem *from) { @@ -3648,6 +3663,7 @@ _copyIndexStmt(const IndexStmt *from) COPY_STRING_FIELD(tableSpace); COPY_NODE_FIELD(indexParams); COPY_NODE_FIELD(indexIncludingParams); + COPY_NODE_FIELD(period); COPY_NODE_FIELD(options); COPY_NODE_FIELD(whereClause); COPY_NODE_FIELD(excludeOpNames); @@ -5823,6 +5839,9 @@ copyObjectImpl(const void *from) case T_Constraint: retval = _copyConstraint(from); break; + case T_Period: + retval = _copyPeriod(from); + break; case T_DefElem: retval = _copyDefElem(from); break; diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c index f537d3eb96..8c8e4c5ffc 100644 --- a/src/backend/nodes/equalfuncs.c +++ b/src/backend/nodes/equalfuncs.c @@ -1391,6 +1391,7 @@ _equalIndexStmt(const IndexStmt *a, const IndexStmt *b) COMPARE_STRING_FIELD(tableSpace); COMPARE_NODE_FIELD(indexParams); COMPARE_NODE_FIELD(indexIncludingParams); + COMPARE_NODE_FIELD(period); COMPARE_NODE_FIELD(options); COMPARE_NODE_FIELD(whereClause); COMPARE_NODE_FIELD(excludeOpNames); @@ -2733,6 +2734,19 @@ _equalConstraint(const Constraint *a, const Constraint *b) return true; } +static bool +_equalPeriod(const Period *a, const Period *b) +{ + COMPARE_STRING_FIELD(periodname); + COMPARE_STRING_FIELD(startcolname); + COMPARE_STRING_FIELD(endcolname); + COMPARE_NODE_FIELD(options); + COMPARE_SCALAR_FIELD(rngtypid); + COMPARE_LOCATION_FIELD(location); + + return true; +} + static bool _equalDefElem(const DefElem *a, const DefElem *b) { @@ -3828,6 +3842,9 @@ equal(const void *a, const void *b) case T_Constraint: retval = _equalConstraint(a, b); break; + case T_Period: + retval = _equalPeriod(a, b); + break; case T_DefElem: retval = _equalDefElem(a, b); break; diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c index e276264882..d4c4e90c29 100644 --- a/src/backend/nodes/nodeFuncs.c +++ b/src/backend/nodes/nodeFuncs.c @@ -1565,6 +1565,9 @@ exprLocation(const Node *expr) case T_Constraint: loc = ((const Constraint *) expr)->location; break; + case T_Period: + loc = ((const Period *) expr)->location; + break; case T_FunctionParameter: /* just use typename's location */ loc = exprLocation((Node *) ((const FunctionParameter *) expr)->argType); diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c index 23f23f11dc..ee03b54ddc 100644 --- a/src/backend/nodes/outfuncs.c +++ b/src/backend/nodes/outfuncs.c @@ -2707,6 +2707,7 @@ _outCreateStmtInfo(StringInfo str, const CreateStmt *node) { WRITE_NODE_FIELD(relation); WRITE_NODE_FIELD(tableElts); + WRITE_NODE_FIELD(periods); WRITE_NODE_FIELD(inhRelations); WRITE_NODE_FIELD(partspec); WRITE_NODE_FIELD(partbound); @@ -2738,6 +2739,27 @@ _outCreateForeignTableStmt(StringInfo str, const CreateForeignTableStmt *node) WRITE_NODE_FIELD(options); } +static void +_outAlterTableStmt(StringInfo str, const AlterTableStmt *node) +{ + WRITE_NODE_TYPE("ALTERTABLESTMT"); + + WRITE_NODE_FIELD(relation); + WRITE_NODE_FIELD(cmds); +} + +static void +_outAlterTableCmd(StringInfo str, const AlterTableCmd *node) +{ + WRITE_NODE_TYPE("ALTERTABLECMD"); + + WRITE_ENUM_FIELD(subtype, AlterTableType); + WRITE_STRING_FIELD(name); + WRITE_INT_FIELD(num); + WRITE_NODE_FIELD(def); + WRITE_BOOL_FIELD(missing_ok); +} + static void _outImportForeignSchemaStmt(StringInfo str, const ImportForeignSchemaStmt *node) { @@ -2762,6 +2784,7 @@ _outIndexStmt(StringInfo str, const IndexStmt *node) WRITE_STRING_FIELD(tableSpace); WRITE_NODE_FIELD(indexParams); WRITE_NODE_FIELD(indexIncludingParams); + WRITE_NODE_FIELD(period); WRITE_NODE_FIELD(options); WRITE_NODE_FIELD(whereClause); WRITE_NODE_FIELD(excludeOpNames); @@ -3761,6 +3784,19 @@ _outConstraint(StringInfo str, const Constraint *node) } } +static void +_outPeriod(StringInfo str, const Period *node) +{ + WRITE_NODE_TYPE("PERIOD"); + + WRITE_STRING_FIELD(periodname); + WRITE_STRING_FIELD(startcolname); + WRITE_STRING_FIELD(endcolname); + WRITE_NODE_FIELD(options); + WRITE_OID_FIELD(rngtypid); + WRITE_LOCATION_FIELD(location); +} + static void _outForeignKeyCacheInfo(StringInfo str, const ForeignKeyCacheInfo *node) { @@ -4345,6 +4381,12 @@ outNode(StringInfo str, const void *obj) case T_CreateForeignTableStmt: _outCreateForeignTableStmt(str, obj); break; + case T_AlterTableStmt: + _outAlterTableStmt(str, obj); + break; + case T_AlterTableCmd: + _outAlterTableCmd(str, obj); + break; case T_ImportForeignSchemaStmt: _outImportForeignSchemaStmt(str, obj); break; @@ -4489,6 +4531,9 @@ outNode(StringInfo str, const void *obj) case T_Constraint: _outConstraint(str, obj); break; + case T_Period: + _outPeriod(str, obj); + break; case T_FuncCall: _outFuncCall(str, obj); break; diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index a6d0cefa6b..8514d5f5ce 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -565,7 +565,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); %type col_name_keyword reserved_keyword %type bare_label_keyword -%type TableConstraint TableLikeClause +%type TableConstraint TableLikeClause TablePeriod %type TableLikeOptionList TableLikeOption %type column_compression opt_column_compression %type ColQualList @@ -698,7 +698,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); ORDER ORDINALITY OTHERS OUT_P OUTER_P OVER OVERLAPS OVERLAY OVERRIDING OWNED OWNER - PARALLEL PARSER PARTIAL PARTITION PASSING PASSWORD PLACING PLANS POLICY + PARALLEL PARSER PARTIAL PARTITION PASSING PASSWORD PERIOD PLACING PLANS POLICY POSITION PRECEDING PRECISION PRESERVE PREPARE PREPARED PRIMARY PRIOR PRIVILEGES PROCEDURAL PROCEDURE PROCEDURES PROGRAM PUBLICATION @@ -2408,6 +2408,24 @@ alter_table_cmd: n->def = (Node *) $4; $$ = (Node *)n; } + /* ALTER TABLE ADD PERIOD FOR (, ) */ + | ADD_P TablePeriod + { + AlterTableCmd *n = makeNode(AlterTableCmd); + n->subtype = AT_AddPeriod; + n->def = $2; + $$ = (Node *)n; + } + /* ALTER TABLE DROP PERIOD FOR [RESTRICT|CASCADE] */ + | DROP PERIOD FOR name opt_drop_behavior + { + AlterTableCmd *n = makeNode(AlterTableCmd); + n->subtype = AT_DropPeriod; + n->name = $4; + n->behavior = $5; + n->missing_ok = false; + $$ = (Node *)n; + } /* ALTER TABLE ADD CONSTRAINT ... */ | ADD_P TableConstraint { @@ -3469,8 +3487,10 @@ TableElement: columnDef { $$ = $1; } | TableLikeClause { $$ = $1; } | TableConstraint { $$ = $1; } + | TablePeriod { $$ = $1; } ; + TypedTableElement: columnOptions { $$ = $1; } | TableConstraint { $$ = $1; } @@ -3781,6 +3801,19 @@ TableLikeOption: ; +TablePeriod: + PERIOD FOR name '(' name ',' name ')' opt_definition + { + Period *n = makeNode(Period); + n->periodname = $3; + n->startcolname = $5; + n->endcolname = $7; + n->options = $9; + n->location = @1; + $$ = (Node *) n; + } + ; + /* ConstraintElem specifies constraint syntax which is not embedded into * a column definition. ColConstraintElem specifies the embedded form. * - thomas 1997-12-03 @@ -6560,6 +6593,14 @@ CommentStmt: n->comment = $9; $$ = (Node *) n; } + | COMMENT ON PERIOD any_name IS comment_text + { + CommentStmt *n = makeNode(CommentStmt); + n->objtype = OBJECT_PERIOD; + n->object = (Node *) $4; + n->comment = $6; + $$ = (Node *) n; + } | COMMENT ON LARGE_P OBJECT_P NumericOnly IS comment_text { CommentStmt *n = makeNode(CommentStmt); @@ -16017,6 +16058,7 @@ reserved_keyword: | ONLY | OR | ORDER + | PERIOD | PLACING | PRIMARY | REFERENCES @@ -16311,6 +16353,7 @@ bare_label_keyword: | PARTITION | PASSING | PASSWORD + | PERIOD | PLACING | PLANS | POLICY diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c index 313d7b6ff0..ad95ba1f90 100644 --- a/src/backend/parser/parse_utilcmd.c +++ b/src/backend/parser/parse_utilcmd.c @@ -80,6 +80,7 @@ typedef struct bool isforeign; /* true if CREATE/ALTER FOREIGN TABLE */ bool isalter; /* true if altering existing table */ List *columns; /* ColumnDef items */ + List *periods; /* Period items */ List *ckconstraints; /* CHECK constraints */ List *fkconstraints; /* FOREIGN KEY constraints */ List *ixconstraints; /* index-creating constraints */ @@ -112,6 +113,8 @@ typedef struct static void transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column); +static void transformTablePeriod(CreateStmtContext *cxt, + Period *period); static void transformTableConstraint(CreateStmtContext *cxt, Constraint *constraint); static void transformTableLikeClause(CreateStmtContext *cxt, @@ -231,6 +234,7 @@ transformCreateStmt(CreateStmt *stmt, const char *queryString) cxt.inhRelations = stmt->inhRelations; cxt.isalter = false; cxt.columns = NIL; + cxt.periods = NIL; cxt.ckconstraints = NIL; cxt.fkconstraints = NIL; cxt.ixconstraints = NIL; @@ -270,6 +274,10 @@ transformCreateStmt(CreateStmt *stmt, const char *queryString) transformColumnDefinition(&cxt, (ColumnDef *) element); break; + case T_Period: + transformTablePeriod(&cxt, (Period *) element); + break; + case T_Constraint: transformTableConstraint(&cxt, (Constraint *) element); break; @@ -337,6 +345,7 @@ transformCreateStmt(CreateStmt *stmt, const char *queryString) * Output results. */ stmt->tableElts = cxt.columns; + stmt->periods = cxt.periods; stmt->constraints = cxt.ckconstraints; result = lappend(cxt.blist, stmt); @@ -857,6 +866,136 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column) } } +void +transformPeriodOptions(Period *period) +{ + ListCell *option; + DefElem *dconstraintname = NULL; + DefElem *drangetypename = NULL; + + foreach(option, period->options) + { + DefElem *defel = (DefElem *) lfirst(option); + + if (strcmp(defel->defname, "check_constraint_name") == 0) + { + if (dconstraintname) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("conflicting or redundant options"))); + dconstraintname = defel; + } + else if (strcmp(defel->defname, "rangetype") == 0) + { + if (drangetypename) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("conflicting or redundant options"))); + drangetypename = defel; + } + else + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("option \"%s\" not recognized", defel->defname))); + } + + if (dconstraintname != NULL) + period->constraintname = defGetString(dconstraintname); + else + period->constraintname = NULL; + + if (drangetypename != NULL) + period->rangetypename = defGetString(drangetypename); + else + period->rangetypename = NULL; +} + +/* + * transformTablePeriod + * transform a Period node within CREATE TABLE or ALTER TABLE + */ +static void +transformTablePeriod(CreateStmtContext *cxt, Period *period) +{ + Oid coltypid; + ColumnDef *col; + ListCell *columns; + + if (strcmp(period->periodname, "system_time") == 0) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("PERIOD FOR SYSTEM_TIME is not supported"), + parser_errposition(cxt->pstate, + period->location))); + + /* + * Determine the column info and range type so that transformIndexConstraints + * knows how to create PRIMARY KEY/UNIQUE constraints using this PERIOD. + */ + transformPeriodOptions(period); + + /* + * Find a suitable range type for operations involving this period. + * Use the rangetype option if provided, otherwise try to find a + * non-ambiguous existing type. + */ + coltypid = InvalidOid; + + /* First find out the type of the period's columns */ + period->rngtypid = InvalidOid; + foreach (columns, cxt->columns) + { + col = (ColumnDef *) lfirst(columns); + if (strcmp(col->colname, period->startcolname) == 0) + { + coltypid = typenameTypeId(cxt->pstate, col->typeName); + break; + } + } + if (coltypid == InvalidOid) + ereport(ERROR, (errmsg("column \"%s\" of relation \"%s\" does not exist", + period->startcolname, cxt->relation->relname))); + + /* Now make sure it matches rangetypename or we can find a matching range */ + if (period->rangetypename != NULL) + { + /* Make sure it exists */ + period->rngtypid = TypenameGetTypidExtended(period->rangetypename, false); + if (period->rngtypid == InvalidOid) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("Range type %s not found", period->rangetypename))); + + /* Make sure it is a range type */ + if (!type_is_range(period->rngtypid)) + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("Type %s is not a range type", period->rangetypename))); + + /* Make sure it matches the column type */ + if (get_range_subtype(period->rngtypid) != coltypid) + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("Range type %s does not match column type %s", + period->rangetypename, + format_type_be(coltypid)))); + } + else + { + period->rngtypid = get_subtype_range(coltypid); + if (period->rngtypid == InvalidOid) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("no range type for %s found for period %s", + format_type_be(coltypid), + period->periodname), + errhint("You can define a custom range type with CREATE TYPE"))); + + } + + cxt->periods = lappend(cxt->periods, period); +} + /* * transformTableConstraint * transform a Constraint node within CREATE TABLE or ALTER TABLE @@ -1587,6 +1726,11 @@ generateClonedIndexStmt(RangeVar *heapRel, Relation source_idx, index->if_not_exists = false; index->reset_default_tblspc = false; + /* Copy the period */ + Period *p = makeNode(Period); + p->oid = idxrec->indperiod; + index->period = p; + /* * We don't try to preserve the name of the source index; instead, just * let DefineIndex() choose a reasonable name. (If we tried to preserve @@ -2869,6 +3013,10 @@ transformIndexStmt(Oid relid, IndexStmt *stmt, const char *queryString) } } + /* take care of the period */ + if (stmt->period) + stmt->period->oid = get_period_oid(relid, stmt->period->periodname, false); + /* * Check that only the base rel is mentioned. (This should be dead code * now that add_missing_from is history.) @@ -3330,6 +3478,7 @@ transformAlterTableStmt(Oid relid, AlterTableStmt *stmt, cxt.inhRelations = NIL; cxt.isalter = true; cxt.columns = NIL; + cxt.periods = NIL; cxt.ckconstraints = NIL; cxt.fkconstraints = NIL; cxt.ixconstraints = NIL; @@ -3393,6 +3542,12 @@ transformAlterTableStmt(Oid relid, AlterTableStmt *stmt, (int) nodeTag(cmd->def)); break; + case AT_AddPeriod: + { + newcmds = lappend(newcmds, cmd); + break; + } + case AT_AlterColumnType: { ColumnDef *def = castNode(ColumnDef, cmd->def); diff --git a/src/backend/utils/cache/lsyscache.c b/src/backend/utils/cache/lsyscache.c index 4ebaa552a2..73ccea4a69 100644 --- a/src/backend/utils/cache/lsyscache.c +++ b/src/backend/utils/cache/lsyscache.c @@ -30,6 +30,7 @@ #include "catalog/pg_namespace.h" #include "catalog/pg_opclass.h" #include "catalog/pg_operator.h" +#include "catalog/pg_period.h" #include "catalog/pg_proc.h" #include "catalog/pg_range.h" #include "catalog/pg_statistic.h" @@ -1020,6 +1021,68 @@ get_attoptions(Oid relid, int16 attnum) return result; } +/* ---------- PG_PERIOD CACHE ---------- */ + +/* + * get_periodname - given its OID, look up a period + * + * If missing_ok is false, throw an error if the period is not found. + * If true, just return InvalidOid. + */ +char * +get_periodname(Oid periodid, bool missing_ok) +{ + HeapTuple tp; + + tp = SearchSysCache1(PERIODOID, + ObjectIdGetDatum(periodid)); + if (HeapTupleIsValid(tp)) + { + Form_pg_period period_tup = (Form_pg_period) GETSTRUCT(tp); + char *result; + + result = pstrdup(NameStr(period_tup->pername)); + ReleaseSysCache(tp); + return result; + } + + if (!missing_ok) + elog(ERROR, "cache lookup failed for period %d", + periodid); + return NULL; +} + +/* + * get_period_oid - gets its relation and name, look up a period + * + * If missing_ok is false, throw an error if the cast is not found. If + * true, just return InvalidOid. + */ +Oid +get_period_oid(Oid relid, const char *periodname, bool missing_ok) +{ + HeapTuple tp; + + tp = SearchSysCache2(PERIODNAME, + ObjectIdGetDatum(relid), + PointerGetDatum(periodname)); + + if (HeapTupleIsValid(tp)) + { + Form_pg_period period_tup = (Form_pg_period) GETSTRUCT(tp); + Oid result; + + result = period_tup->oid; + ReleaseSysCache(tp); + return result; + } + + if (!missing_ok) + elog(ERROR, "cache lookup failed for period %s", + periodname); + return InvalidOid; +} + /* ---------- PG_CAST CACHE ---------- */ /* @@ -3448,6 +3511,30 @@ get_multirange_range(Oid multirangeOid) return InvalidOid; } +Oid +get_subtype_range(Oid subtypeOid) +{ + CatCList *catlist; + Oid result = InvalidOid; + + catlist = SearchSysCacheList1(RANGESUBTYPE, ObjectIdGetDatum(subtypeOid)); + + if (catlist->n_members == 1) + { + HeapTuple tuple = &catlist->members[0]->tuple; + Form_pg_range rngtup = (Form_pg_range) GETSTRUCT(tuple); + result = rngtup->rngtypid; + ReleaseCatCacheList(catlist); + } + else if (catlist->n_members > 1) + ereport(ERROR, + (errcode(ERRCODE_INDETERMINATE_DATATYPE), + errmsg("ambiguous range for type %s", + format_type_be(subtypeOid)))); + + return result; +} + /* ---------- PG_INDEX CACHE ---------- */ /* diff --git a/src/backend/utils/cache/syscache.c b/src/backend/utils/cache/syscache.c index 56870b46e4..2a7817669c 100644 --- a/src/backend/utils/cache/syscache.c +++ b/src/backend/utils/cache/syscache.c @@ -48,6 +48,7 @@ #include "catalog/pg_operator.h" #include "catalog/pg_opfamily.h" #include "catalog/pg_partitioned_table.h" +#include "catalog/pg_period.h" #include "catalog/pg_proc.h" #include "catalog/pg_publication.h" #include "catalog/pg_publication_namespace.h" @@ -585,6 +586,27 @@ static const struct cachedesc cacheinfo[] = { }, 32 }, + {PeriodRelationId, /* PERIODNAME */ + PeriodRelidNameIndexId, + 2, + { + Anum_pg_period_perrelid, + Anum_pg_period_pername, + 0, + 0 + }, + 32 + }, + {PeriodRelationId, /* PERIODOID */ + PeriodObjectIndexId, + 1, + { + Anum_pg_period_oid, + 0, + 0 + }, + 32 + }, {ProcedureRelationId, /* PROCNAMEARGSNSP */ ProcedureNameArgsNspIndexId, 3, @@ -684,7 +706,17 @@ static const struct cachedesc cacheinfo[] = { }, 4 }, - + {RangeRelationId, /* RANGESUBTYPE */ + RangeSubTypidTypidIndexId, + 2, + { + Anum_pg_range_rngsubtype, + Anum_pg_range_rngtypid, + 0, + 0 + }, + 4 + }, {RangeRelationId, /* RANGETYPE */ RangeTypidIndexId, 1, diff --git a/src/bin/pg_dump/pg_backup_archiver.c b/src/bin/pg_dump/pg_backup_archiver.c index 2c4cfb9457..0f3a55f1fe 100644 --- a/src/bin/pg_dump/pg_backup_archiver.c +++ b/src/bin/pg_dump/pg_backup_archiver.c @@ -3613,6 +3613,7 @@ _printTocEntry(ArchiveHandle *AH, TocEntry *te, bool isData) strcmp(te->desc, "DATABASE PROPERTIES") == 0 || strcmp(te->desc, "DEFAULT") == 0 || strcmp(te->desc, "FK CONSTRAINT") == 0 || + strcmp(te->desc, "PERIOD") == 0 || strcmp(te->desc, "INDEX") == 0 || strcmp(te->desc, "RULE") == 0 || strcmp(te->desc, "TRIGGER") == 0 || diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index 7e98371d25..1392ca09c8 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -6360,6 +6360,7 @@ getTables(Archive *fout, int *numTables) int i_relkind; int i_rolname; int i_relchecks; + int i_nperiod; int i_relhasindex; int i_relhasrules; int i_relpages; @@ -6424,6 +6425,14 @@ getTables(Archive *fout, int *numTables) appendPQExpBufferStr(query, "c.relhasoids, "); + /* In PG15 upwards we have PERIODs. */ + if (fout->remoteVersion >= 150000) + appendPQExpBufferStr(query, + "(SELECT count(*) FROM pg_period WHERE perrelid = c.oid) AS nperiods, "); + else + appendPQExpBufferStr(query, + "NULL AS nperiods, "); + if (fout->remoteVersion >= 80400) appendPQExpBufferStr(query, "c.relhastriggers, "); @@ -6686,6 +6695,7 @@ getTables(Archive *fout, int *numTables) i_relkind = PQfnumber(res, "relkind"); i_rolname = PQfnumber(res, "rolname"); i_relchecks = PQfnumber(res, "relchecks"); + i_nperiod = PQfnumber(res, "nperiod"); i_relhasindex = PQfnumber(res, "relhasindex"); i_relhasrules = PQfnumber(res, "relhasrules"); i_relpages = PQfnumber(res, "relpages"); @@ -6762,6 +6772,7 @@ getTables(Archive *fout, int *numTables) } tblinfo[i].reltablespace = pg_strdup(PQgetvalue(res, i, i_reltablespace)); tblinfo[i].hasoids = (strcmp(PQgetvalue(res, i, i_relhasoids), "t") == 0); + tblinfo[i].nperiod = atoi(PQgetvalue(res, i, i_nperiod)); tblinfo[i].hastriggers = (strcmp(PQgetvalue(res, i, i_relhastriggers), "t") == 0); tblinfo[i].relpersistence = *(PQgetvalue(res, i, i_relpersistence)); tblinfo[i].relispopulated = (strcmp(PQgetvalue(res, i, i_relispopulated), "t") == 0); @@ -8551,6 +8562,8 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables) PGresult *res; int ntups; bool hasdefaults; + int ndumpablechecks; /* number of CHECK constraints that do + not belong to a period */ /* Don't bother to collect info for sequences */ if (tbinfo->relkind == RELKIND_SEQUENCE) @@ -8857,10 +8870,12 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables) } /* - * Get info about table CHECK constraints. This is skipped for a - * data-only dump, as it is only needed for table schemas. + * Get info about table CHECK constraints that don't belong to a PERIOD. + * This is skipped for a data-only dump, as it is only needed for table + * schemas. */ - if (tbinfo->ncheck > 0 && !dopt->dataOnly) + ndumpablechecks = tbinfo->ncheck - tbinfo->nperiod; + if (ndumpablechecks > 0 && !dopt->dataOnly) { ConstraintInfo *constrs; int numConstrs; @@ -8870,7 +8885,25 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables) tbinfo->dobj.name); resetPQExpBuffer(q); - if (fout->remoteVersion >= 90200) + if (fout->remoteVersion >= 150000) + { + /* + * PERIODs were added in v15 and we don't dump CHECK + * constraints for them. + */ + appendPQExpBuffer(q, + "SELECT tableoid, oid, conname, " + "pg_catalog.pg_get_constraintdef(oid) AS consrc, " + "conislocal, convalidated " + "FROM pg_catalog.pg_constraint " + "WHERE conrelid = '%u'::pg_catalog.oid " + " AND contype = 'c' " + " AND NOT EXISTS (SELECT FROM pg_period " + " WHERE (perrelid, perconstraint) = (conrelid, pg_constraint.oid)) " + "ORDER BY conname", + tbinfo->dobj.catId.oid); + } + else if (fout->remoteVersion >= 90200) { /* * convalidated is new in 9.2 (actually, it is there in 9.1, @@ -8912,12 +8945,12 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables) res = ExecuteSqlQuery(fout, q->data, PGRES_TUPLES_OK); numConstrs = PQntuples(res); - if (numConstrs != tbinfo->ncheck) + if (numConstrs != ndumpablechecks) { pg_log_error(ngettext("expected %d check constraint on table \"%s\" but found %d", "expected %d check constraints on table \"%s\" but found %d", - tbinfo->ncheck), - tbinfo->ncheck, tbinfo->dobj.name, numConstrs); + ndumpablechecks), + ndumpablechecks, tbinfo->dobj.name, numConstrs); pg_log_error("(The system catalogs might be corrupted.)"); exit_nicely(1); } @@ -8975,6 +9008,76 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables) } PQclear(res); } + + /* + * Get info about PERIOD definitions + */ + if (tbinfo->nperiod > 0) + { + PeriodInfo *periods; + int numPeriods; + int j; + + /* We shouldn't have any periods before v15 */ + Assert(fout->remoteVersion >= 150000); + + pg_log_info("finding periods for table \"%s.%s\"\n", + tbinfo->dobj.namespace->dobj.name, + tbinfo->dobj.name); + + resetPQExpBuffer(q); + appendPQExpBuffer(q, + "SELECT p.tableoid, p.oid, p.pername, " + " sa.attname AS perstart, ea.attname AS perend, " + " no.nspname AS opcnamespace, o.opcname, " + " c.conname AS conname " + "FROM pg_catalog.pg_period AS p " + "JOIN pg_catalog.pg_attribute AS sa ON (sa.attrelid, sa.attnum) = (p.perrelid, p.perstart) " + "JOIN pg_catalog.pg_attribute AS ea ON (ea.attrelid, ea.attnum) = (p.perrelid, p.perend) " + "JOIN pg_catalog.pg_opclass AS o ON o.oid = p.peropclass " + "JOIN pg_catalog.pg_namespace AS no ON no.oid = o.opcnamespace " + "JOIN pg_catalog.pg_constraint AS c ON c.oid = p.perconstraint " + "WHERE p.perrelid = '%u'::pg_catalog.oid " + "ORDER BY p.pername", + tbinfo->dobj.catId.oid); + + res = ExecuteSqlQuery(fout, q->data, PGRES_TUPLES_OK); + + /* + * If we didn't get the number of rows we thought we were going to, + * then those JOINs didn't work. + */ + numPeriods = PQntuples(res); + if (numPeriods != tbinfo->nperiod) + { + pg_log_info(ngettext("expected %d period on table \"%s\" but found %d\n", + "expected %d periods on table \"%s\" but found %d\n", + tbinfo->nperiod), + tbinfo->nperiod, tbinfo->dobj.name, numPeriods); + pg_log_info("(The system catalogs might be corrupted.)\n"); + exit_nicely(1); + } + + periods = (PeriodInfo *) pg_malloc(numPeriods * sizeof(PeriodInfo)); + tbinfo->periods = periods; + + for (j = 0; j < numPeriods; j++) + { + periods[j].dobj.objType = DO_PERIOD; + periods[j].dobj.catId.tableoid = atooid(PQgetvalue(res, j, 0)); + periods[j].dobj.catId.oid = atooid(PQgetvalue(res, j, 1)); + AssignDumpId(&periods[j].dobj); + periods[j].dobj.name = pg_strdup(PQgetvalue(res, j, 2)); + periods[j].dobj.namespace = tbinfo->dobj.namespace; + periods[j].pertable = tbinfo; + periods[j].perstart = pg_strdup(PQgetvalue(res, j, 3)); + periods[j].perend = pg_strdup(PQgetvalue(res, j, 4)); + periods[j].opcnamespace = pg_strdup(PQgetvalue(res, j, 5)); + periods[j].opcname = pg_strdup(PQgetvalue(res, j, 6)); + periods[j].conname = pg_strdup(PQgetvalue(res, j, 7)); + } + PQclear(res); + } } destroyPQExpBuffer(q); @@ -10253,6 +10356,8 @@ dumpDumpableObject(Archive *fout, const DumpableObject *dobj) case DO_FK_CONSTRAINT: dumpConstraint(fout, (const ConstraintInfo *) dobj); break; + case DO_PERIOD: + break; case DO_PROCLANG: dumpProcLang(fout, (const ProcLangInfo *) dobj); break; @@ -15983,6 +16088,34 @@ dumpTableSchema(Archive *fout, const TableInfo *tbinfo) } } + /* + * Add non-inherited PERIOD definitions, if any. + */ + for (j = 0; j < tbinfo->nperiod; j++) + { + PeriodInfo *period = &(tbinfo->periods[j]); + + char *name = pg_strdup(fmtId(period->dobj.name)); + char *start = pg_strdup(fmtId(period->perstart)); + char *end = pg_strdup(fmtId(period->perend)); + char *opcnamespace = pg_strdup(fmtId(period->opcnamespace)); + char *opcname = pg_strdup(fmtId(period->opcname)); + char *conname = pg_strdup(fmtId(period->conname)); + + if (actual_atts == 0) + appendPQExpBufferStr(q, " (\n "); + else + appendPQExpBufferStr(q, ",\n "); + + appendPQExpBuffer(q, "PERIOD FOR %s (%s, %s) " + "WITH (operator_class = %s.%s, constraint_name = %s)", + name, start, end, + opcnamespace, opcname, + conname); + + actual_atts++; + } + /* * Add non-inherited CHECK constraints, if any. * @@ -15991,7 +16124,7 @@ dumpTableSchema(Archive *fout, const TableInfo *tbinfo) * PARTITION that we'll emit later expects the constraint to be * there. (No need to fix conislocal: ATTACH PARTITION does that) */ - for (j = 0; j < tbinfo->ncheck; j++) + for (j = 0; j < tbinfo->ncheck - tbinfo->nperiod; j++) { ConstraintInfo *constr = &(tbinfo->checkexprs[j]); @@ -16191,7 +16324,7 @@ dumpTableSchema(Archive *fout, const TableInfo *tbinfo) * For partitions, they were already dumped, and conislocal * doesn't need fixing. */ - for (k = 0; k < tbinfo->ncheck; k++) + for (k = 0; k < tbinfo->ncheck - tbinfo->nperiod; k++) { ConstraintInfo *constr = &(tbinfo->checkexprs[k]); @@ -16473,7 +16606,7 @@ dumpTableSchema(Archive *fout, const TableInfo *tbinfo) dumpTableSecLabel(fout, tbinfo, reltypename); /* Dump comments on inlined table constraints */ - for (j = 0; j < tbinfo->ncheck; j++) + for (j = 0; j < tbinfo->ncheck - tbinfo->nperiod; j++) { ConstraintInfo *constr = &(tbinfo->checkexprs[j]); @@ -18556,6 +18689,7 @@ addBoundaryDependencies(DumpableObject **dobjs, int numObjs, case DO_TRIGGER: case DO_EVENT_TRIGGER: case DO_DEFAULT_ACL: + case DO_PERIOD: case DO_POLICY: case DO_PUBLICATION: case DO_PUBLICATION_REL: diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h index d1d8608e9c..db989ba021 100644 --- a/src/bin/pg_dump/pg_dump.h +++ b/src/bin/pg_dump/pg_dump.h @@ -59,6 +59,7 @@ typedef enum DO_TRIGGER, DO_CONSTRAINT, DO_FK_CONSTRAINT, /* see note for ConstraintInfo */ + DO_PERIOD, DO_PROCLANG, DO_CAST, DO_TABLE_DATA, @@ -283,12 +284,14 @@ typedef struct _tableInfo bool rowsec; /* is row security enabled? */ bool forcerowsec; /* is row security forced? */ bool hasoids; /* does it have OIDs? */ + bool hasperiods; /* does it have any periods? */ uint32 frozenxid; /* table's relfrozenxid */ uint32 minmxid; /* table's relminmxid */ Oid toast_oid; /* toast table's OID, or 0 if none */ uint32 toast_frozenxid; /* toast table's relfrozenxid, if any */ uint32 toast_minmxid; /* toast table's relminmxid */ int ncheck; /* # of CHECK expressions */ + int nperiod; /* # of PERIOD definitions */ char *reloftype; /* underlying type for typed table */ Oid foreign_server; /* foreign server oid, if applicable */ /* these two are set only if table is a sequence owned by a column: */ @@ -328,6 +331,7 @@ typedef struct _tableInfo bool *inhNotNull; /* true if NOT NULL is inherited */ struct _attrDefInfo **attrdefs; /* DEFAULT expressions */ struct _constraintInfo *checkexprs; /* CHECK constraints */ + struct _periodInfo *periods; /* PERIOD definitions */ char *partkeydef; /* partition key definition */ char *partbound; /* partition bound definition */ bool needs_override; /* has GENERATED ALWAYS AS IDENTITY */ @@ -468,6 +472,17 @@ typedef struct _constraintInfo bool separate; /* true if must dump as separate item */ } ConstraintInfo; +typedef struct _periodInfo +{ + DumpableObject dobj; + TableInfo *pertable; + char *perstart; /* the name of the start column */ + char *perend; /* the name of the end column */ + char *opcnamespace; /* the name of the operator class schema */ + char *opcname; /* the name of the operator class */ + char *conname; /* the name of the CHECK constraint */ +} PeriodInfo; + typedef struct _procLangInfo { DumpableObject dobj; diff --git a/src/bin/pg_dump/pg_dump_sort.c b/src/bin/pg_dump/pg_dump_sort.c index 410d1790ee..3fee695379 100644 --- a/src/bin/pg_dump/pg_dump_sort.c +++ b/src/bin/pg_dump/pg_dump_sort.c @@ -75,6 +75,7 @@ enum dbObjectTypePriorities PRIO_CONSTRAINT, PRIO_INDEX, PRIO_INDEX_ATTACH, + PRIO_PERIOD, PRIO_STATSEXT, PRIO_RULE, PRIO_TRIGGER, @@ -109,6 +110,7 @@ static const int dbObjectTypePriority[] = PRIO_ATTRDEF, /* DO_ATTRDEF */ PRIO_INDEX, /* DO_INDEX */ PRIO_INDEX_ATTACH, /* DO_INDEX_ATTACH */ + PRIO_PERIOD, /* DO_PERIOD */ PRIO_STATSEXT, /* DO_STATSEXT */ PRIO_RULE, /* DO_RULE */ PRIO_TRIGGER, /* DO_TRIGGER */ @@ -1385,6 +1387,11 @@ describeDumpableObject(DumpableObject *obj, char *buf, int bufsize) "FK CONSTRAINT %s (ID %d OID %u)", obj->name, obj->dumpId, obj->catId.oid); return; + case DO_PERIOD: + snprintf(buf, bufsize, + "PERIOD %s (ID %d OID %u)", + obj->name, obj->dumpId, obj->catId.oid); + return; case DO_PROCLANG: snprintf(buf, bufsize, "PROCEDURAL LANGUAGE %s (ID %d OID %u)", diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index ea721d963a..c6358f91b1 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -2519,6 +2519,40 @@ describeOneTableDetails(const char *schemaname, PGresult *result = NULL; int tuples = 0; + /* print periods */ + if (pset.sversion >= 150000) + { + printfPQExpBuffer(&buf, + "SELECT quote_ident(p.pername), quote_ident(s.attname) AS startatt, quote_ident(e.attname) AS endatt\n" + "FROM pg_period AS p\n" + "JOIN pg_attribute AS s ON (s.attrelid, s.attnum) = (p.perrelid, p.perstart)\n" + "JOIN pg_attribute AS e ON (e.attrelid, e.attnum) = (p.perrelid, p.perend)\n" + "WHERE p.perrelid = '%s'\n" + "ORDER BY 1;", + oid); + result = PSQLexec(buf.data); + if (!result) + goto error_return; + else + tuples = PQntuples(result); + + if (tuples > 0) + { + printTableAddFooter(&cont, _("Periods:")); + for (i = 0; i < tuples; i++) + { + /* untranslated constraint name and def */ + printfPQExpBuffer(&buf, " %s (%s, %s)", + PQgetvalue(result, i, 0), + PQgetvalue(result, i, 1), + PQgetvalue(result, i, 2)); + + printTableAddFooter(&cont, buf.data); + } + } + PQclear(result); + } + /* print indexes */ if (tableinfo.hasindex) { diff --git a/src/include/catalog/dependency.h b/src/include/catalog/dependency.h index 3eca295ff4..fac04bd0e5 100644 --- a/src/include/catalog/dependency.h +++ b/src/include/catalog/dependency.h @@ -93,6 +93,7 @@ typedef enum ObjectClass OCLASS_CAST, /* pg_cast */ OCLASS_COLLATION, /* pg_collation */ OCLASS_CONSTRAINT, /* pg_constraint */ + OCLASS_PERIOD, /* pg_period */ OCLASS_CONVERSION, /* pg_conversion */ OCLASS_DEFAULT, /* pg_attrdef */ OCLASS_LANGUAGE, /* pg_language */ diff --git a/src/include/catalog/heap.h b/src/include/catalog/heap.h index 6ce480b49c..76d9597328 100644 --- a/src/include/catalog/heap.h +++ b/src/include/catalog/heap.h @@ -120,6 +120,10 @@ extern Oid StoreAttrDefault(Relation rel, AttrNumber attnum, Node *expr, bool is_internal, bool add_column_mode); +extern Oid StorePeriod(Relation rel, const char *period, + AttrNumber startnum, AttrNumber endnum, + Oid rngtypid, Oid conoid); + extern Node *cookDefault(ParseState *pstate, Node *raw_default, Oid atttypid, diff --git a/src/include/catalog/pg_index.h b/src/include/catalog/pg_index.h index a5a6075d4d..22cc1ba527 100644 --- a/src/include/catalog/pg_index.h +++ b/src/include/catalog/pg_index.h @@ -43,11 +43,11 @@ CATALOG(pg_index,2610,IndexRelationId) BKI_SCHEMA_MACRO bool indisready; /* is this index ready for inserts? */ bool indislive; /* is this index alive at all? */ bool indisreplident; /* is this index the identity for replication? */ + Oid indperiod; /* the period it contains, if any */ /* variable-length fields start here, but we allow direct access to indkey */ int2vector indkey BKI_FORCE_NOT_NULL; /* column numbers of indexed cols, * or 0 */ - #ifdef CATALOG_VARLEN oidvector indcollation BKI_LOOKUP_OPT(pg_collation) BKI_FORCE_NOT_NULL; /* collation identifiers */ oidvector indclass BKI_LOOKUP(pg_opclass) BKI_FORCE_NOT_NULL; /* opclass identifiers */ diff --git a/src/include/catalog/pg_period.h b/src/include/catalog/pg_period.h new file mode 100644 index 0000000000..a7d8a62258 --- /dev/null +++ b/src/include/catalog/pg_period.h @@ -0,0 +1,55 @@ +/*------------------------------------------------------------------------- + * + * pg_period.h + * definition of the "period" system catalog (pg_period) + * + * + * Portions Copyright (c) 1996-2021, PostgreSQL Global Development Group + * + * src/include/catalog/pg_period.h + * + * NOTES + * The Catalog.pm module reads this file and derives schema + * information. + * + *------------------------------------------------------------------------- + */ +#ifndef PG_PERIOD_H +#define PG_PERIOD_H + +#include "catalog/genbki.h" +#include "catalog/pg_period_d.h" + +/* ---------------- + * pg_period definition. cpp turns this into + * typedef struct FormData_pg_period + * ---------------- + */ +CATALOG(pg_period,8000,PeriodRelationId) +{ + Oid oid; /* OID of the period */ + NameData pername; /* name of period */ + Oid perrelid; /* OID of relation containing this period */ + int16 perstart; /* column for start value */ + int16 perend; /* column for end value */ + Oid perrngtype; /* OID of the range type for this period */ + Oid perconstraint; /* OID of (start < end) constraint */ + bool perislocal; /* is the period local or inherited? */ + int32 perinhcount; /* number of parents having this period */ +} FormData_pg_period; + +/* ---------------- + * Form_pg_period corresponds to a pointer to a tuple with + * the format of pg_period relation. + * ---------------- + */ +typedef FormData_pg_period *Form_pg_period; + +DECLARE_UNIQUE_INDEX_PKEY(pg_period_oid_index, 8001, PeriodObjectIndexId, on pg_period using btree(oid oid_ops)); +DECLARE_UNIQUE_INDEX(pg_period_perrelid_pername_index, 8002, PeriodRelidNameIndexId, on pg_period using btree(perrelid oid_ops, pername name_ops)); + +extern void RemovePeriodById(Oid periodId); + +extern Oid get_relation_period_oid(Oid relid, const char *pername, bool missing_ok); + +#endif /* PG_PERIOD_H */ diff --git a/src/include/catalog/pg_range.h b/src/include/catalog/pg_range.h index cde29114ba..ec4cf36fdd 100644 --- a/src/include/catalog/pg_range.h +++ b/src/include/catalog/pg_range.h @@ -59,6 +59,7 @@ typedef FormData_pg_range *Form_pg_range; DECLARE_UNIQUE_INDEX_PKEY(pg_range_rngtypid_index, 3542, RangeTypidIndexId, on pg_range using btree(rngtypid oid_ops)); DECLARE_UNIQUE_INDEX(pg_range_rngmultitypid_index, 2228, RangeMultirangeTypidIndexId, on pg_range using btree(rngmultitypid oid_ops)); +DECLARE_UNIQUE_INDEX(pg_range_rngsubtype_rngtypid_index, 8003, RangeSubTypidTypidIndexId, on pg_range using btree(rngsubtype oid_ops, rngtypid oid_ops)); /* * prototypes for functions in pg_range.c diff --git a/src/include/commands/tablecmds.h b/src/include/commands/tablecmds.h index 336549cc5f..89239205d2 100644 --- a/src/include/commands/tablecmds.h +++ b/src/include/commands/tablecmds.h @@ -38,7 +38,8 @@ extern LOCKMODE AlterTableGetLockLevel(List *cmds); extern void ATExecChangeOwner(Oid relationOid, Oid newOwnerId, bool recursing, LOCKMODE lockmode); -extern void AlterTableInternal(Oid relid, List *cmds, bool recurse); +extern void AlterTableInternal(Oid relid, List *cmds, bool recurse, + struct AlterTableUtilityContext *context); extern Oid AlterTableMoveAll(AlterTableMoveAllStmt *stmt); diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h index 7c657c1241..8cb6def639 100644 --- a/src/include/nodes/nodes.h +++ b/src/include/nodes/nodes.h @@ -457,6 +457,7 @@ typedef enum NodeTag T_IndexElem, T_StatsElem, T_Constraint, + T_Period, T_DefElem, T_RangeTblEntry, T_RangeTblFunction, diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 067138e6b5..f4462c1114 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -1813,6 +1813,7 @@ typedef enum ObjectType OBJECT_OPCLASS, OBJECT_OPERATOR, OBJECT_OPFAMILY, + OBJECT_PERIOD, OBJECT_POLICY, OBJECT_PROCEDURE, OBJECT_PUBLICATION, @@ -1905,6 +1906,8 @@ typedef enum AlterTableType AT_AddIndexConstraint, /* add constraint using existing index */ AT_DropConstraint, /* drop constraint */ AT_DropConstraintRecurse, /* internal to commands/tablecmds.c */ + AT_AddPeriod, /* ADD PERIOD */ + AT_DropPeriod, /* DROP PERIOD */ AT_ReAddComment, /* internal to commands/tablecmds.c */ AT_AlterColumnType, /* alter column type */ AT_AlterColumnGenericOptions, /* alter column OPTIONS (...) */ @@ -2170,9 +2173,9 @@ typedef struct VariableShowStmt /* ---------------------- * Create Table Statement * - * NOTE: in the raw gram.y output, ColumnDef and Constraint nodes are - * intermixed in tableElts, and constraints is NIL. After parse analysis, - * tableElts contains just ColumnDefs, and constraints contains just + * NOTE: in the raw gram.y output, ColumnDef, Period, and Constraint nodes are + * intermixed in tableElts; periods and constraints are NIL. After parse analysis, + * tableElts contains just ColumnDefs, periods contains just Period nodes, and constraints contains just * Constraint nodes (in fact, only CONSTR_CHECK nodes, in the present * implementation). * ---------------------- @@ -2183,6 +2186,7 @@ typedef struct CreateStmt NodeTag type; RangeVar *relation; /* relation to create */ List *tableElts; /* column definitions (list of ColumnDef) */ + List *periods; /* periods (list of Period nodes) */ List *inhRelations; /* relations to inherit from (list of * RangeVar) */ PartitionBoundSpec *partbound; /* FOR VALUES clause */ @@ -2196,6 +2200,26 @@ typedef struct CreateStmt bool if_not_exists; /* just do nothing if it already exists? */ } CreateStmt; + +/* ---------- + * Definitions for periods in CreateStmt + * ---------- + */ + +typedef struct Period +{ + NodeTag type; + Oid oid; /* period oid, once it's transformed */ + char *periodname; /* period name */ + char *startcolname; /* name of start column */ + char *endcolname; /* name of end column */ + List *options; /* options from WITH clause */ + char *constraintname; /* name of the CHECK constraint */ + char *rangetypename; /* name of the range type */ + Oid rngtypid; /* the range type to use */ + int location; /* token location, or -1 if unknown */ +} Period; + /* ---------- * Definitions for constraints in CreateStmt * @@ -2893,6 +2917,7 @@ typedef struct IndexStmt List *indexParams; /* columns to index: a list of IndexElem */ List *indexIncludingParams; /* additional columns to index: a list * of IndexElem */ + Period *period; /* The period included in the index */ List *options; /* WITH clause options: a list of DefElem */ Node *whereClause; /* qualification (partial-index predicate) */ List *excludeOpNames; /* exclusion operator names, or NIL if none */ diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h index f836acf876..43fc561075 100644 --- a/src/include/parser/kwlist.h +++ b/src/include/parser/kwlist.h @@ -312,6 +312,7 @@ PG_KEYWORD("partial", PARTIAL, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("partition", PARTITION, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("passing", PASSING, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("password", PASSWORD, UNRESERVED_KEYWORD, BARE_LABEL) +PG_KEYWORD("period", PERIOD, RESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("placing", PLACING, RESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("plans", PLANS, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("policy", POLICY, UNRESERVED_KEYWORD, BARE_LABEL) diff --git a/src/include/parser/parse_utilcmd.h b/src/include/parser/parse_utilcmd.h index 1056bf081b..8e81d170a9 100644 --- a/src/include/parser/parse_utilcmd.h +++ b/src/include/parser/parse_utilcmd.h @@ -39,5 +39,6 @@ extern IndexStmt *generateClonedIndexStmt(RangeVar *heapRel, Relation source_idx, const struct AttrMap *attmap, Oid *constraintOid); +extern void transformPeriodOptions(Period *period); #endif /* PARSE_UTILCMD_H */ diff --git a/src/include/utils/lsyscache.h b/src/include/utils/lsyscache.h index 77871aaefc..bb8c1b6742 100644 --- a/src/include/utils/lsyscache.h +++ b/src/include/utils/lsyscache.h @@ -96,6 +96,8 @@ extern Oid get_atttype(Oid relid, AttrNumber attnum); extern void get_atttypetypmodcoll(Oid relid, AttrNumber attnum, Oid *typid, int32 *typmod, Oid *collid); extern Datum get_attoptions(Oid relid, int16 attnum); +extern char *get_periodname(Oid periodid, bool missing_ok); +extern Oid get_period_oid(Oid relid, const char *periodname, bool missing_ok); extern Oid get_cast_oid(Oid sourcetypeid, Oid targettypeid, bool missing_ok); extern char *get_collation_name(Oid colloid); extern bool get_collation_isdeterministic(Oid colloid); @@ -194,6 +196,7 @@ extern Oid get_range_subtype(Oid rangeOid); extern Oid get_range_collation(Oid rangeOid); extern Oid get_range_multirange(Oid rangeOid); extern Oid get_multirange_range(Oid multirangeOid); +extern Oid get_subtype_range(Oid subtypeOid); extern Oid get_index_column_opclass(Oid index_oid, int attno); extern bool get_index_isreplident(Oid index_oid); extern bool get_index_isvalid(Oid index_oid); diff --git a/src/include/utils/syscache.h b/src/include/utils/syscache.h index c8cfbc30f6..ba52d7a38a 100644 --- a/src/include/utils/syscache.h +++ b/src/include/utils/syscache.h @@ -73,6 +73,8 @@ enum SysCacheIdentifier OPFAMILYAMNAMENSP, OPFAMILYOID, PARTRELID, + PERIODNAME, + PERIODOID, PROCNAMEARGSNSP, PROCOID, PUBLICATIONNAME, @@ -82,6 +84,7 @@ enum SysCacheIdentifier PUBLICATIONREL, PUBLICATIONRELMAP, RANGEMULTIRANGE, + RANGESUBTYPE, RANGETYPE, RELNAMENSP, RELOID, diff --git a/src/test/regress/expected/periods.out b/src/test/regress/expected/periods.out new file mode 100644 index 0000000000..3b9eca0f04 --- /dev/null +++ b/src/test/regress/expected/periods.out @@ -0,0 +1,79 @@ +/* System periods are not implemented */ +create table pt (id integer, ds date, de date, period for system_time (ds, de)); +ERROR: PERIOD FOR SYSTEM_TIME is not supported +LINE 2: create table pt (id integer, ds date, de date, period for sy... + ^ +/* Periods must specify actual columns */ +create table pt (id integer, ds date, de date, period for p (bogus, de)); +ERROR: column "bogus" of relation "pt" does not exist +create table pt (id integer, ds date, de date, period for p (ds, bogus)); +ERROR: column "bogus" of relation "pt" does not exist +/* Data types must match exactly */ +create table pt (id integer, ds date, de timestamp, period for p (ds, de)); +ERROR: start and end columns of period must be of same type +create table pt (id integer, ds text collate "C", de text collate "POSIX", period for p (ds, de)); +ERROR: start and end columns of period must have same collation +/* Periods must have a default BTree operator class */ +create table pt (id integer, ds xml, de xml, period for p (ds, de)); +ERROR: no range type for xml found for period p +HINT: You can define a custom range type with CREATE TYPE +/* Period and column names are in the same namespace */ +create table pt (id integer, ds date, de date, period for ctid (ds, de)); +ERROR: period name "ctid" conflicts with a system column name +create table pt (id integer, ds date, de date, period for id (ds, de)); +ERROR: period name "id" conflicts with a column name +/* Now make one that works */ +create table pt (id integer, ds date, de date, period for p (ds, de)); +/* + * CREATE TABLE currently adds an ALTER TABLE to add the periods, but let's do + * some explicit testing anyway + */ +alter table pt drop period for p; +alter table pt add period for system_time (ds, de); +ERROR: PERIOD FOR SYSTEM_TIME is not supported +alter table pt add period for p (ds, de); +/* Can't drop its columns */ +alter table pt drop column ds; +ERROR: cannot drop column ds of table pt because other objects depend on it +DETAIL: period p on table pt depends on column ds of table pt +HINT: Use DROP ... CASCADE to drop the dependent objects too. +alter table pt drop column de; +ERROR: cannot drop column de of table pt because other objects depend on it +DETAIL: period p on table pt depends on column de of table pt +HINT: Use DROP ... CASCADE to drop the dependent objects too. +/* Can't change the data types */ +alter table pt alter column ds type timestamp; +ERROR: cannot alter type of a column used by a period +DETAIL: period p on table pt depends on column "ds" +alter table pt alter column ds type timestamp; +ERROR: cannot alter type of a column used by a period +DETAIL: period p on table pt depends on column "ds" +/* column/period namespace conflicts */ +alter table pt add column p integer; +ERROR: column name "p" conflicts with a period name +alter table pt rename column id to p; +ERROR: column name "p" conflicts with a period name +/* adding columns and the period at the same time */ +create table pt2 (id integer); +alter table pt2 add column ds date, add column de date, add period for p (ds, de); +drop table pt2; +/* Ambiguous range types raise an error */ +create type mydaterange as range(subtype=date); +create table pt2 (id int, ds date, de date, period for p (ds, de)); +ERROR: ambiguous range for type date +/* You can give an explicit range type */ +create table pt2 (id int, ds date, de date, period for p (ds, de) with (rangetype = 'mydaterange')); +drop type mydaterange; +ERROR: cannot drop type mydaterange because other objects depend on it +DETAIL: period p on table pt2 depends on type mydaterange +HINT: Use DROP ... CASCADE to drop the dependent objects too. +drop type mydaterange cascade; +NOTICE: drop cascades to period p on table pt2 +drop table pt2; +create table pt2 (id int, ds date, de date, period for p (ds, de) with (rangetype = 'daterange')); +/* Range type is not found */ +create table pt3 (id int, ds date, de date, period for p (ds, de) with (rangetype = 'notarange')); +ERROR: Range type notarange not found +/* Range type is the wrong type */ +create table pt3 (id int, ds date, de date, period for p (ds, de) with (rangetype = 'tstzrange')); +ERROR: Range type tstzrange does not match column type date diff --git a/src/test/regress/expected/sanity_check.out b/src/test/regress/expected/sanity_check.out index d04dc66db9..eed75441c7 100644 --- a/src/test/regress/expected/sanity_check.out +++ b/src/test/regress/expected/sanity_check.out @@ -137,6 +137,7 @@ pg_opclass|t pg_operator|t pg_opfamily|t pg_partitioned_table|t +pg_period|t pg_policy|t pg_proc|t pg_publication|t @@ -167,6 +168,8 @@ pg_type|t pg_user_mapping|t point_tbl|t polygon_tbl|t +pt|f +pt2|f quad_box_tbl|t quad_box_tbl_ord_seq1|f quad_box_tbl_ord_seq2|f diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index 017e962fed..7a3b6fb145 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -58,7 +58,7 @@ test: create_index create_index_spgist create_view index_including index_includi # ---------- # Another group of parallel tests # ---------- -test: create_aggregate create_function_3 create_cast constraints triggers select inherit typed_table vacuum drop_if_exists updatable_views roleattributes create_am hash_func errors infinite_recurse +test: create_aggregate create_function_3 create_cast constraints triggers select inherit typed_table periods vacuum drop_if_exists updatable_views roleattributes create_am hash_func errors infinite_recurse # ---------- # sanity_check does a vacuum, affecting the sort order of SELECT * diff --git a/src/test/regress/sql/periods.sql b/src/test/regress/sql/periods.sql new file mode 100644 index 0000000000..61eaf356bc --- /dev/null +++ b/src/test/regress/sql/periods.sql @@ -0,0 +1,62 @@ +/* System periods are not implemented */ +create table pt (id integer, ds date, de date, period for system_time (ds, de)); + +/* Periods must specify actual columns */ +create table pt (id integer, ds date, de date, period for p (bogus, de)); +create table pt (id integer, ds date, de date, period for p (ds, bogus)); + +/* Data types must match exactly */ +create table pt (id integer, ds date, de timestamp, period for p (ds, de)); +create table pt (id integer, ds text collate "C", de text collate "POSIX", period for p (ds, de)); + +/* Periods must have a default BTree operator class */ +create table pt (id integer, ds xml, de xml, period for p (ds, de)); + +/* Period and column names are in the same namespace */ +create table pt (id integer, ds date, de date, period for ctid (ds, de)); +create table pt (id integer, ds date, de date, period for id (ds, de)); + +/* Now make one that works */ +create table pt (id integer, ds date, de date, period for p (ds, de)); + +/* + * CREATE TABLE currently adds an ALTER TABLE to add the periods, but let's do + * some explicit testing anyway + */ +alter table pt drop period for p; +alter table pt add period for system_time (ds, de); +alter table pt add period for p (ds, de); + +/* Can't drop its columns */ +alter table pt drop column ds; +alter table pt drop column de; + +/* Can't change the data types */ +alter table pt alter column ds type timestamp; +alter table pt alter column ds type timestamp; + +/* column/period namespace conflicts */ +alter table pt add column p integer; +alter table pt rename column id to p; + +/* adding columns and the period at the same time */ +create table pt2 (id integer); +alter table pt2 add column ds date, add column de date, add period for p (ds, de); +drop table pt2; + +/* Ambiguous range types raise an error */ +create type mydaterange as range(subtype=date); +create table pt2 (id int, ds date, de date, period for p (ds, de)); + +/* You can give an explicit range type */ +create table pt2 (id int, ds date, de date, period for p (ds, de) with (rangetype = 'mydaterange')); +drop type mydaterange; +drop type mydaterange cascade; +drop table pt2; +create table pt2 (id int, ds date, de date, period for p (ds, de) with (rangetype = 'daterange')); + +/* Range type is not found */ +create table pt3 (id int, ds date, de date, period for p (ds, de) with (rangetype = 'notarange')); + +/* Range type is the wrong type */ +create table pt3 (id int, ds date, de date, period for p (ds, de) with (rangetype = 'tstzrange')); -- 2.25.1