diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index 3ed9021c2f..025d6b5355 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_pltemplate template data for procedural languages @@ -4902,6 +4907,116 @@ SCRAM-SHA-256$<iteration count>:&l + + <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 + + + + + Name + Type + References + Description + + + + + + + pername + name + + Period name + + + + perrelid + oid + pg_class.oid + The OID of the pg_class entry for the table containing this period. + + + + perstart + int2 + pg_attribute.attnum + + The attribute number of the start column. + + + + + perend + int2 + pg_attribute.attnum + + The attribute number of the end column. + + + + + peropclass + oid + pg_opclass.oid + + This contains the OID of the operator class to use. + + + + + perconstraint + oid + pg_constraint.oid + + This contains the OID of the CHECK constraint owned by the period to + ensure that (startcolumn + < + endcolumn). + + + + + 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. + + + + + +
+ + + <structname>pg_pltemplate</structname> diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index 2cd0b8ab9d..cdbe06196c 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -919,6 +919,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. + + + + Currently, periods in PostgreSQL have no functionality; they can only be + defined for future use. + + + + 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) +); + + + + + 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 09ef2827f2..95a621370d 100644 --- a/doc/src/sgml/information_schema.sgml +++ b/doc/src/sgml/information_schema.sgml @@ -3348,6 +3348,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/keywords.sgml b/doc/src/sgml/keywords.sgml index a37d0b756b..6c78fe221a 100644 --- a/doc/src/sgml/keywords.sgml +++ b/doc/src/sgml/keywords.sgml @@ -3431,7 +3431,7 @@ PERIOD - + reserved reserved diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml index 1cce00eaf9..2a0f16e175 100644 --- a/doc/src/sgml/ref/alter_table.sgml +++ b/doc/src/sgml/ref/alter_table.sgml @@ -58,6 +58,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 @@ -481,6 +483,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 965c5a40ad..89a5406da9 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 [, ...] ] ) ] | @@ -328,6 +329,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 ROLE my_role IS 'Administration group for finance tables'; diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index 2a1eac9592..b030866c12 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 [ COLLATE collation ] [ column_constraint [ ... ] ] + | period_definition | table_constraint | LIKE source_table [ like_option ... ] } [, ... ] @@ -36,6 +37,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 } [, ... ] ) ] @@ -47,6 +49,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 } @@ -69,6 +72,11 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI [ ON DELETE action ] [ ON UPDATE 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 ] @@ -130,6 +138,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 @@ -639,6 +655,27 @@ 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. The columns are also marked as NOT NULL. + + + + Both columns must have exactly the same type and must have a btree + operator class. The operator class can be specified with the + operator_class period_option. If not specified, the + default operator class for the type is used. + + + + CONSTRAINT constraint_name diff --git a/src/backend/catalog/Makefile b/src/backend/catalog/Makefile index 0865240f11..cb5e9f1d2b 100644 --- a/src/backend/catalog/Makefile +++ b/src/backend/catalog/Makefile @@ -17,7 +17,7 @@ OBJS = catalog.o dependency.o heap.o index.o indexing.o namespace.o aclchk.o \ objectaccess.o objectaddress.o partition.o pg_aggregate.o pg_collation.o \ pg_constraint.o pg_conversion.o \ pg_depend.o pg_enum.o pg_inherits.o pg_largeobject.o pg_namespace.o \ - pg_operator.o pg_proc.o pg_publication.o pg_range.o \ + pg_operator.o pg_period.o pg_proc.o pg_publication.o pg_range.o \ pg_db_role_setting.o pg_shdepend.o pg_subscription.o pg_type.o \ storage.o toasting.o @@ -44,7 +44,7 @@ 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_collation.h pg_partitioned_table.h pg_period.h pg_range.h pg_transform.h \ pg_sequence.h pg_publication.h pg_publication_rel.h pg_subscription.h \ pg_subscription_rel.h diff --git a/src/backend/catalog/aclchk.c b/src/backend/catalog/aclchk.c index 578e4c6592..1f70090367 100644 --- a/src/backend/catalog/aclchk.c +++ b/src/backend/catalog/aclchk.c @@ -3468,6 +3468,7 @@ aclcheck_error(AclResult aclerr, ObjectType objtype, case OBJECT_DEFAULT: case OBJECT_DEFACL: case OBJECT_DOMCONSTRAINT: + case OBJECT_PERIOD: case OBJECT_PUBLICATION_REL: case OBJECT_ROLE: case OBJECT_RULE: @@ -3607,6 +3608,7 @@ aclcheck_error(AclResult aclerr, ObjectType objtype, case OBJECT_DEFAULT: case OBJECT_DEFACL: case OBJECT_DOMCONSTRAINT: + case OBJECT_PERIOD: case OBJECT_PUBLICATION_REL: case OBJECT_ROLE: case OBJECT_TRANSFORM: diff --git a/src/backend/catalog/dependency.c b/src/backend/catalog/dependency.c index 4f1d365357..26452e5b76 100644 --- a/src/backend/catalog/dependency.c +++ b/src/backend/catalog/dependency.c @@ -43,6 +43,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" @@ -139,6 +140,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 */ @@ -1163,6 +1165,10 @@ doDeletion(const ObjectAddress *object, int flags) RemoveConstraintById(object->objectId); break; + case OCLASS_PERIOD: + RemovePeriodById(object->objectId); + break; + case OCLASS_CONVERSION: RemoveConversionById(object->objectId); break; @@ -2442,6 +2448,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 39813de991..dd164efab4 100644 --- a/src/backend/catalog/heap.c +++ b/src/backend/catalog/heap.c @@ -50,6 +50,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" @@ -2001,6 +2002,68 @@ RelationClearMissing(Relation rel) heap_close(attr_rel, RowExclusiveLock); } +/* + * 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 opclass, 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)); + + 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_peropclass -1] = opclass; + values[Anum_pg_period_perconstraint - 1] = conoid; + + pg_period = heap_open(PeriodRelationId, RowExclusiveLock); + + tuple = heap_form_tuple(RelationGetDescr(pg_period), values, nulls); + oid = 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 operator class. */ + ObjectAddressSet(referenced, OperatorClassRelationId, opclass); + 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_AUTO); + + heap_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 f4e69f4a26..4eaeb302bf 100644 --- a/src/backend/catalog/information_schema.sql +++ b/src/backend/catalog/information_schema.sql @@ -1179,7 +1179,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 ad682673e6..b430236eb8 100644 --- a/src/backend/catalog/objectaddress.c +++ b/src/backend/catalog/objectaddress.c @@ -42,6 +42,7 @@ #include "catalog/pg_opclass.h" #include "catalog/pg_opfamily.h" #include "catalog/pg_operator.h" +#include "catalog/pg_period.h" #include "catalog/pg_proc.h" #include "catalog/pg_policy.h" #include "catalog/pg_publication.h" @@ -587,6 +588,10 @@ static const struct object_type_map { "domain constraint", OBJECT_DOMCONSTRAINT }, + /* OCLASS_PERIOD */ + { + "period", OBJECT_PERIOD + }, /* OCLASS_CONVERSION */ { "conversion", OBJECT_CONVERSION @@ -844,6 +849,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; @@ -1335,6 +1341,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); } @@ -2129,6 +2142,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; @@ -2243,6 +2257,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)); @@ -2804,6 +2819,38 @@ getObjectDescription(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); + + if (OidIsValid(per->perrelid)) + { + StringInfoData rel; + + initStringInfo(&rel); + getRelationDescription(&rel, per->perrelid); + 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; @@ -3942,6 +3989,10 @@ getObjectTypeDescription(const ObjectAddress *object) getConstraintTypeDescription(&buffer, object->objectId); break; + case OCLASS_PERIOD: + appendStringInfoString(&buffer, "period"); + break; + case OCLASS_CONVERSION: appendStringInfoString(&buffer, "conversion"); break; @@ -4355,6 +4406,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); + 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..e819ea0cff --- /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-2018, 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 = heap_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); + heap_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 = heap_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 = HeapTupleGetOid(tuple); + } + } + + 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)))); + + heap_close(pg_period, AccessShareLock); + + return perOid; +} diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt index aeb262a5b0..61088d97ea 100644 --- a/src/backend/catalog/sql_features.txt +++ b/src/backend/catalog/sql_features.txt @@ -426,7 +426,7 @@ T176 Sequence generator support NO 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 eff325cc7d..ee64694fb7 100644 --- a/src/backend/commands/alter.c +++ b/src/backend/commands/alter.c @@ -608,6 +608,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 2f2e69b4a8..4e9c0fbf92 100644 --- a/src/backend/commands/comment.c +++ b/src/backend/commands/comment.c @@ -101,6 +101,16 @@ CommentObject(CommentStmt *stmt) errmsg("\"%s\" is not a table, view, materialized view, composite type, or foreign table", RelationGetRelationName(relation)))); 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 eecc85d14e..9c98a18495 100644 --- a/src/backend/commands/event_trigger.c +++ b/src/backend/commands/event_trigger.c @@ -1126,6 +1126,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: @@ -1181,6 +1182,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: @@ -2264,6 +2266,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_REL: @@ -2346,6 +2349,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_REL: diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 0e95037dcf..81398adfb8 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_trigger.h" #include "catalog/pg_type.h" @@ -365,6 +366,8 @@ static ObjectAddress ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, bool if_not_exists, LOCKMODE lockmode); 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 ATPrepAddOids(List **wqueue, Relation rel, bool recurse, @@ -376,6 +379,11 @@ static ObjectAddress ATExecSetNotNull(AlteredTableInfo *tab, Relation rel, const char *colName, LOCKMODE lockmode); static ObjectAddress ATExecColumnDefault(Relation rel, const char *colName, Node *newDefault, LOCKMODE lockmode); +static ObjectAddress ATExecAddPeriod(Relation rel, Period *period, LOCKMODE lockmode); +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, @@ -1854,6 +1862,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, @@ -1932,6 +1942,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) @@ -3382,6 +3394,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 @@ -3690,6 +3716,14 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd, /* No command-specific prep needed */ pass = cmd->def ? AT_PASS_ADD_CONSTR : AT_PASS_DROP; break; + case AT_AddPeriod: /* ALTER TABLE ... ADD PERIOD FOR name (start, end) */ + ATSimplePermissions(rel, ATT_TABLE); + pass = AT_PASS_ADD_CONSTR; + break; + case AT_DropPeriod: /* ALTER TABLE ... DROP PERIOD FOR name */ + ATSimplePermissions(rel, ATT_TABLE); + pass = AT_PASS_DROP; + break; case AT_AddIdentity: ATSimplePermissions(rel, ATT_TABLE | ATT_VIEW | ATT_FOREIGN_TABLE); pass = AT_PASS_ADD_CONSTR; @@ -4032,6 +4066,14 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab, Relation rel, case AT_ColumnDefault: /* ALTER COLUMN DEFAULT */ address = ATExecColumnDefault(rel, cmd->name, cmd->def, lockmode); break; + case AT_AddPeriod: + address = ATExecAddPeriod(rel, (Period *) cmd->def, lockmode); + break; + case AT_DropPeriod: + ATExecDropPeriod(rel, cmd->name, cmd->behavior, + false, false, + cmd->missing_ok, lockmode); + break; case AT_AddIdentity: address = ATExecAddIdentity(rel, cmd->name, cmd->def, lockmode); break; @@ -5731,14 +5773,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. @@ -5782,6 +5839,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. */ @@ -6154,6 +6283,285 @@ ATExecColumnDefault(Relation rel, const char *colName, return address; } +/* + * make_constraints_for_period + * + * Add constraints to make both columns NOT NULL and CHECK (start < end). + * + * Returns the CHECK constraint Oid. + */ +static Oid +make_constraints_for_period(Relation rel, Period *period, char *constraintname) +{ + List *cmds = NIL; + AlterTableCmd *cmd; + ColumnRef *scol, *ecol; + Constraint *constr; + char *conname; + + /* Start column must be NOT NULL */ + cmd = makeNode(AlterTableCmd); + cmd->subtype = AT_SetNotNull; + cmd->name = period->startcolname; + cmds = lappend(cmds, cmd); + + /* End column must be NOT NULL */ + cmd = makeNode(AlterTableCmd); + cmd->subtype = AT_SetNotNull; + cmd->name = period->endcolname; + cmds = lappend(cmds, cmd); + + /* + * Create the CHECK constraint + */ + if (constraintname) + conname = constraintname; + else + conname = ChooseConstraintName(RelationGetRelationName(rel), + period->periodname, + "check", + RelationGetNamespace(rel), + NIL); + + 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 = conname; + constr->deferrable = false; + constr->initdeferred = false; + constr->location = 0; + 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; + + cmd = makeNode(AlterTableCmd); + cmd->subtype = AT_AddConstraint; + cmd->def = (Node *) constr; + cmds = lappend(cmds, cmd); + + /* Do the deed. */ + AlterTableInternal(RelationGetRelid(rel), cmds, true); + + return get_relation_constraint_oid(RelationGetRelid(rel), conname, false); +} + +/* + * ALTER TABLE ADD PERIOD + * + * Return the address of the what? + */ +static ObjectAddress +ATExecAddPeriod(Relation rel, Period *period, LOCKMODE lockmode) +{ + Relation attrelation; + HeapTuple starttuple, endtuple; + Form_pg_attribute startatttuple, endatttuple; + AttrNumber startattnum, endattnum; + ListCell *option; + DefElem *dconstraintname = NULL; + DefElem *dopclass = NULL; + Oid conoid, opclass; + + /* + * 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 */ + 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, "operator_class") == 0) + { + if (dopclass) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("conflicting or redundant options"))); + dopclass = defel; + } + else + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("option \"%s\" not recognized", defel->defname))); + } + + attrelation = heap_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))); + + /* + * Get the btree operator class for it (ResolveOpClass will error out if + * necessary) + */ + opclass = ResolveOpClass(dopclass ? defGetQualifiedName(dopclass) : NULL, + startatttuple->atttypid, + "btree", BTREE_AM_OID); + + /* 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"))); + + heap_freetuple(starttuple); + heap_freetuple(endtuple); + + conoid = make_constraints_for_period(rel, period, + dconstraintname ? defGetString(dconstraintname) : NULL); + + /* Save it */ + StorePeriod(rel, period->periodname, startattnum, endattnum, opclass, conoid); + + heap_close(attrelation, RowExclusiveLock); + + return InvalidObjectAddress; +} + +/* + * 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(rel, ATT_TABLE); + + pg_period = heap_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 = HeapTupleGetOid(tuple); + 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)))); + heap_close(pg_period, RowExclusiveLock); + return; + } + } + + heap_close(pg_period, RowExclusiveLock); +} + /* * ALTER TABLE ALTER COLUMN ADD IDENTITY * @@ -9512,6 +9920,15 @@ ATExecAlterColumnType(AlteredTableInfo *tab, Relation rel, } 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), + colName))); + break; + case OCLASS_REWRITE: /* XXX someday see if we can cope with revising views */ ereport(ERROR, diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c index 7c045a7afe..fa5bc9cdd3 100644 --- a/src/backend/nodes/copyfuncs.c +++ b/src/backend/nodes/copyfuncs.c @@ -2911,6 +2911,20 @@ _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_LOCATION_FIELD(location); + + return newnode; +} + static DefElem * _copyDefElem(const DefElem *from) { @@ -5553,6 +5567,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 6a971d0141..13fc726cbb 100644 --- a/src/backend/nodes/equalfuncs.c +++ b/src/backend/nodes/equalfuncs.c @@ -2602,6 +2602,18 @@ _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_LOCATION_FIELD(location); + + return true; +} + static bool _equalDefElem(const DefElem *a, const DefElem *b) { @@ -3630,6 +3642,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 a10014f755..a45d82cf40 100644 --- a/src/backend/nodes/nodeFuncs.c +++ b/src/backend/nodes/nodeFuncs.c @@ -1501,6 +1501,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 1da9d7ed15..9aa4808015 100644 --- a/src/backend/nodes/outfuncs.c +++ b/src/backend/nodes/outfuncs.c @@ -3592,6 +3592,18 @@ _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_LOCATION_FIELD(location); +} + static void _outForeignKeyCacheInfo(StringInfo str, const ForeignKeyCacheInfo *node) { @@ -4275,6 +4287,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 90dfac2cb1..430021a984 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -533,7 +533,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); %type unreserved_keyword type_func_name_keyword %type col_name_keyword reserved_keyword -%type TableConstraint TableLikeClause +%type TableConstraint TableLikeClause TablePeriod %type TableLikeOptionList TableLikeOption %type ColQualList %type ColConstraint ColConstraintElem ConstraintAttr @@ -662,7 +662,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 @@ -2261,6 +2261,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 { @@ -3373,8 +3391,10 @@ TableElement: columnDef { $$ = $1; } | TableLikeClause { $$ = $1; } | TableConstraint { $$ = $1; } + | TablePeriod { $$ = $1; } ; + TypedTableElement: columnOptions { $$ = $1; } | TableConstraint { $$ = $1; } @@ -3651,6 +3671,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 @@ -6422,6 +6455,7 @@ opt_restart_seqs: * OPERATOR (leftoperand_typ, rightoperand_typ) | * OPERATOR CLASS USING | * OPERATOR FAMILY USING | + * PERIOD . | * RULE ON | * TRIGGER ON ] * IS { 'text' | NULL } @@ -6604,6 +6638,7 @@ comment_type_any_name: | TEXT_P SEARCH DICTIONARY { $$ = OBJECT_TSDICTIONARY; } | TEXT_P SEARCH PARSER { $$ = OBJECT_TSPARSER; } | TEXT_P SEARCH TEMPLATE { $$ = OBJECT_TSTEMPLATE; } + | PERIOD { $$ = OBJECT_PERIOD; } ; /* object types taking name */ @@ -15447,6 +15482,7 @@ reserved_keyword: | ONLY | OR | ORDER + | PERIOD | PLACING | PRIMARY | REFERENCES diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c index 17b54b20cc..43bbb3bbf7 100644 --- a/src/backend/parser/parse_utilcmd.c +++ b/src/backend/parser/parse_utilcmd.c @@ -114,6 +114,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, @@ -286,6 +288,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; @@ -806,6 +812,42 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column) } } +/* + * transformTablePeriod + * transform a Period node within CREATE TABLE or ALTER TABLE + */ +static void +transformTablePeriod(CreateStmtContext *cxt, Period *period) +{ + AlterTableStmt *alterstmt; + AlterTableCmd *altercmd; + + 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))); + + /* + * Instead of duplicating code, just create an ALTER TABLE statement to run + * after the table is created. + */ + alterstmt = makeNode(AlterTableStmt); + alterstmt->relation = cxt->relation; + alterstmt->cmds = NIL; + alterstmt->relkind = OBJECT_TABLE; + + altercmd = makeNode(AlterTableCmd); + altercmd->subtype = AT_AddPeriod; + altercmd->name = NULL; + altercmd->def = (Node *) period; + + alterstmt->cmds = lappend(alterstmt->cmds, altercmd); + + cxt->alist = lappend(cxt->alist, alterstmt); +} + /* * transformTableConstraint * transform a Constraint node within CREATE TABLE or ALTER TABLE @@ -3023,6 +3065,23 @@ transformAlterTableStmt(Oid relid, AlterTableStmt *stmt, (int) nodeTag(cmd->def)); break; + case AT_AddPeriod: + { + /* + Period *period = castNode(Period, cmd->def); + + 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))); + + */ + newcmds = lappend(newcmds, cmd); + break; + } + case AT_ProcessedConstraint: /* diff --git a/src/backend/utils/cache/syscache.c b/src/backend/utils/cache/syscache.c index 2b381782a3..3067cce14a 100644 --- a/src/backend/utils/cache/syscache.c +++ b/src/backend/utils/cache/syscache.c @@ -49,6 +49,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_rel.h" @@ -584,6 +585,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, + { + ObjectIdAttributeNumber, + 0, + 0 + }, + 32 + }, {ProcedureRelationId, /* PROCNAMEARGSNSP */ ProcedureNameArgsNspIndexId, 3, diff --git a/src/bin/pg_dump/pg_backup_archiver.c b/src/bin/pg_dump/pg_backup_archiver.c index 83c976eaf7..edb1fa5085 100644 --- a/src/bin/pg_dump/pg_backup_archiver.c +++ b/src/bin/pg_dump/pg_backup_archiver.c @@ -3686,6 +3686,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 f8fbcdad5b..9824aa5342 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -5843,6 +5843,7 @@ getTables(Archive *fout, int *numTables) int i_initrrelacl; int i_rolname; int i_relchecks; + int i_nperiod; int i_relhastriggers; int i_relhasindex; int i_relhasrules; @@ -5891,6 +5892,114 @@ getTables(Archive *fout, int *numTables) * we cannot correctly identify inherited columns, owned sequences, etc. */ + if (fout->remoteVersion >= 0 /* TODO */) + { + PQExpBuffer acl_subquery = createPQExpBuffer(); + PQExpBuffer racl_subquery = createPQExpBuffer(); + PQExpBuffer initacl_subquery = createPQExpBuffer(); + PQExpBuffer initracl_subquery = createPQExpBuffer(); + + PQExpBuffer attacl_subquery = createPQExpBuffer(); + PQExpBuffer attracl_subquery = createPQExpBuffer(); + PQExpBuffer attinitacl_subquery = createPQExpBuffer(); + PQExpBuffer attinitracl_subquery = createPQExpBuffer(); + + /* + * Left join to pick up dependency info linking sequences to their + * owning column, if any (note this dependency is AUTO as of 8.2) + * + * Left join to detect if any privileges are still as-set-at-init, in + * which case we won't dump out ACL commands for those. + */ + + buildACLQueries(acl_subquery, racl_subquery, initacl_subquery, + initracl_subquery, "c.relacl", "c.relowner", + "CASE WHEN c.relkind = " CppAsString2(RELKIND_SEQUENCE) + " THEN 's' ELSE 'r' END::\"char\"", + dopt->binary_upgrade); + + buildACLQueries(attacl_subquery, attracl_subquery, attinitacl_subquery, + attinitracl_subquery, "at.attacl", "c.relowner", "'c'", + dopt->binary_upgrade); + + appendPQExpBuffer(query, + "SELECT c.tableoid, c.oid, c.relname, " + "%s AS relacl, %s as rrelacl, " + "%s AS initrelacl, %s as initrrelacl, " + "c.relkind, c.relnamespace, " + "(%s c.relowner) AS rolname, " + "c.relchecks, c.relhastriggers, " + "c.relhasindex, c.relhasrules, c.relhasoids, " + "c.relrowsecurity, c.relforcerowsecurity, " + "c.relfrozenxid, c.relminmxid, tc.oid AS toid, " + "tc.relfrozenxid AS tfrozenxid, " + "tc.relminmxid AS tminmxid, " + "c.relpersistence, c.relispopulated, " + "c.relreplident, c.relpages, " + "(SELECT count(*) FROM pg_period WHERE perrelid = c.oid) AS nperiod, " + "CASE WHEN c.reloftype <> 0 THEN c.reloftype::pg_catalog.regtype ELSE NULL END AS reloftype, " + "d.refobjid AS owning_tab, " + "d.refobjsubid AS owning_col, " + "(SELECT spcname FROM pg_tablespace t WHERE t.oid = c.reltablespace) AS reltablespace, " + "array_remove(array_remove(c.reloptions,'check_option=local'),'check_option=cascaded') AS reloptions, " + "CASE WHEN 'check_option=local' = ANY (c.reloptions) THEN 'LOCAL'::text " + "WHEN 'check_option=cascaded' = ANY (c.reloptions) THEN 'CASCADED'::text ELSE NULL END AS checkoption, " + "tc.reloptions AS toast_reloptions, " + "c.relkind = '%c' AND EXISTS (SELECT 1 FROM pg_depend WHERE classid = 'pg_class'::regclass AND objid = c.oid AND objsubid = 0 AND refclassid = 'pg_class'::regclass AND deptype = 'i') AS is_identity_sequence, " + "EXISTS (SELECT 1 FROM pg_attribute at LEFT JOIN pg_init_privs pip ON " + "(c.oid = pip.objoid " + "AND pip.classoid = 'pg_class'::regclass " + "AND pip.objsubid = at.attnum)" + "WHERE at.attrelid = c.oid AND (" + "%s IS NOT NULL " + "OR %s IS NOT NULL " + "OR %s IS NOT NULL " + "OR %s IS NOT NULL" + "))" + "AS changed_acl, " + "pg_get_partkeydef(c.oid) AS partkeydef, " + "c.relispartition AS ispartition, " + "pg_get_expr(c.relpartbound, c.oid) AS partbound " + "FROM pg_class c " + "LEFT JOIN pg_depend d ON " + "(c.relkind = '%c' AND " + "d.classid = c.tableoid AND d.objid = c.oid AND " + "d.objsubid = 0 AND " + "d.refclassid = c.tableoid AND d.deptype IN ('a', 'i')) " + "LEFT JOIN pg_class tc ON (c.reltoastrelid = tc.oid) " + "LEFT JOIN pg_init_privs pip ON " + "(c.oid = pip.objoid " + "AND pip.classoid = 'pg_class'::regclass " + "AND pip.objsubid = 0) " + "WHERE c.relkind in ('%c', '%c', '%c', '%c', '%c', '%c', '%c') " + "ORDER BY c.oid", + acl_subquery->data, + racl_subquery->data, + initacl_subquery->data, + initracl_subquery->data, + username_subquery, + RELKIND_SEQUENCE, + attacl_subquery->data, + attracl_subquery->data, + attinitacl_subquery->data, + attinitracl_subquery->data, + RELKIND_SEQUENCE, + RELKIND_RELATION, RELKIND_SEQUENCE, + RELKIND_VIEW, RELKIND_COMPOSITE_TYPE, + RELKIND_MATVIEW, RELKIND_FOREIGN_TABLE, + RELKIND_PARTITIONED_TABLE); + + destroyPQExpBuffer(acl_subquery); + destroyPQExpBuffer(racl_subquery); + destroyPQExpBuffer(initacl_subquery); + destroyPQExpBuffer(initracl_subquery); + + destroyPQExpBuffer(attacl_subquery); + destroyPQExpBuffer(attracl_subquery); + destroyPQExpBuffer(attinitacl_subquery); + destroyPQExpBuffer(attinitracl_subquery); + } + else if (fout->remoteVersion >= 90600) { char *partkeydef = "NULL"; @@ -6420,6 +6529,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_relhastriggers = PQfnumber(res, "relhastriggers"); i_relhasindex = PQfnumber(res, "relhasindex"); i_relhasrules = PQfnumber(res, "relhasrules"); @@ -6499,6 +6609,7 @@ getTables(Archive *fout, int *numTables) else tblinfo[i].reloftype = pg_strdup(PQgetvalue(res, i, i_reloftype)); tblinfo[i].ncheck = atoi(PQgetvalue(res, i, i_relchecks)); + tblinfo[i].nperiod = atoi(PQgetvalue(res, i, i_nperiod)); if (PQgetisnull(res, i, i_owning_tab)) { tblinfo[i].owning_tab = InvalidOid; @@ -8106,6 +8217,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 */ for (i = 0; i < numTables; i++) { @@ -8408,9 +8521,10 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables) } /* - * Get info about table CHECK constraints + * Get info about table CHECK constraints that don't belong to a PERIOD */ - if (tbinfo->ncheck > 0) + ndumpablechecks = tbinfo->ncheck - tbinfo->nperiod; + if (ndumpablechecks > 0) { ConstraintInfo *constrs; int numConstrs; @@ -8421,7 +8535,25 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables) tbinfo->dobj.name); resetPQExpBuffer(q); - if (fout->remoteVersion >= 90200) + if (fout->remoteVersion >= 0) + { + /* + * PERIODs were added in v12 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, @@ -8463,12 +8595,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) { write_msg(NULL, ngettext("expected %d check constraint on table \"%s\" but found %d\n", "expected %d check constraints on table \"%s\" but found %d\n", - tbinfo->ncheck), - tbinfo->ncheck, tbinfo->dobj.name, numConstrs); + ndumpablechecks), + ndumpablechecks, tbinfo->dobj.name, numConstrs); write_msg(NULL, "(The system catalogs might be corrupted.)\n"); exit_nicely(1); } @@ -8526,6 +8658,76 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables) } PQclear(res); } + + /* + * Get info about PERIOD definitions + */ + if (tbinfo->nperiod > 0) + { + PeriodInfo *periods; + int numPeriods; + + /* We shouldn't have any periods before v12 */ + Assert(fout->remoteVersion >= 0); /* TODO */ + + if (g_verbose) + write_msg(NULL, "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) + { + write_msg(NULL, 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); + write_msg(NULL, "(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); @@ -9736,6 +9938,8 @@ dumpDumpableObject(Archive *fout, DumpableObject *dobj) case DO_FK_CONSTRAINT: dumpConstraint(fout, (ConstraintInfo *) dobj); break; + case DO_PERIOD: + break; case DO_PROCLANG: dumpProcLang(fout, (ProcLangInfo *) dobj); break; @@ -15553,10 +15757,38 @@ dumpTableSchema(Archive *fout, 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. */ - for (j = 0; j < tbinfo->ncheck; j++) + for (j = 0; j < tbinfo->ncheck - tbinfo->nperiod; j++) { ConstraintInfo *constr = &(tbinfo->checkexprs[j]); @@ -15723,7 +15955,7 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) } } - for (k = 0; k < tbinfo->ncheck; k++) + for (k = 0; k < tbinfo->ncheck - tbinfo->nperiod; k++) { ConstraintInfo *constr = &(tbinfo->checkexprs[k]); @@ -16001,7 +16233,7 @@ dumpTableSchema(Archive *fout, 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]); @@ -17844,6 +18076,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 e96c662b1e..631e94a997 100644 --- a/src/bin/pg_dump/pg_dump.h +++ b/src/bin/pg_dump/pg_dump.h @@ -62,6 +62,7 @@ typedef enum DO_TRIGGER, DO_CONSTRAINT, DO_FK_CONSTRAINT, /* see note for ConstraintInfo */ + DO_PERIOD, DO_PROCLANG, DO_CAST, DO_TABLE_DATA, @@ -279,12 +280,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 */ /* these two are set only if table is a sequence owned by a column: */ Oid owning_tab; /* OID of table owning sequence */ @@ -320,6 +323,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 */ @@ -448,6 +452,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; @@ -698,6 +713,7 @@ extern InhInfo *getInherits(Archive *fout, int *numInherits); extern void getIndexes(Archive *fout, TableInfo tblinfo[], int numTables); extern void getExtendedStatistics(Archive *fout); extern void getConstraints(Archive *fout, TableInfo tblinfo[], int numTables); +/*extern void getPeriods(Archive *fout, TableInfo tblinfo[], int numTables);*/ extern RuleInfo *getRules(Archive *fout, int *numRules); extern void getTriggers(Archive *fout, TableInfo tblinfo[], int numTables); extern ProcLangInfo *getProcLangs(Archive *fout, int *numProcLangs); diff --git a/src/bin/pg_dump/pg_dump_sort.c b/src/bin/pg_dump/pg_dump_sort.c index d2b0949d6b..57aab962ef 100644 --- a/src/bin/pg_dump/pg_dump_sort.c +++ b/src/bin/pg_dump/pg_dump_sort.c @@ -63,6 +63,7 @@ static const int dbObjectTypePriority[] = 32, /* DO_TRIGGER */ 27, /* DO_CONSTRAINT */ 33, /* DO_FK_CONSTRAINT */ + 30, /* DO_PERIOD */ 2, /* DO_PROCLANG */ 10, /* DO_CAST */ 23, /* DO_TABLE_DATA */ @@ -1361,6 +1362,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 e5b3c1ebf9..d694448130 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -2172,6 +2172,40 @@ describeOneTableDetails(const char *schemaname, PGresult *result = NULL; int tuples = 0; + /* print periods */ + if (pset.sversion >= 0) /* TODO */ + { + 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 46c271a46c..8031e9015b 100644 --- a/src/include/catalog/dependency.h +++ b/src/include/catalog/dependency.h @@ -149,6 +149,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 59fc052494..9e872a9b17 100644 --- a/src/include/catalog/heap.h +++ b/src/include/catalog/heap.h @@ -106,6 +106,10 @@ extern List *AddRelationNewConstraints(Relation rel, extern void RelationClearMissing(Relation rel); +extern Oid StorePeriod(Relation rel, const char *period, + AttrNumber startnum, AttrNumber endnum, + Oid opclass, Oid conoid); + extern Oid StoreAttrDefault(Relation rel, AttrNumber attnum, Node *expr, bool is_internal, bool add_column_mode); diff --git a/src/include/catalog/indexing.h b/src/include/catalog/indexing.h index 24915824ca..7a2703901c 100644 --- a/src/include/catalog/indexing.h +++ b/src/include/catalog/indexing.h @@ -339,6 +339,11 @@ DECLARE_UNIQUE_INDEX(pg_replication_origin_roname_index, 6002, on pg_replication DECLARE_UNIQUE_INDEX(pg_partitioned_table_partrelid_index, 3351, on pg_partitioned_table using btree(partrelid oid_ops)); #define PartitionedRelidIndexId 3351 +DECLARE_UNIQUE_INDEX(pg_period_oid_index, 4101, on pg_period using btree(oid oid_ops)); +#define PeriodObjectIndexId 4101 +DECLARE_UNIQUE_INDEX(pg_period_perrelid_pername_index, 3998, on pg_period using btree(perrelid oid_ops, pername name_ops)); +#define PeriodRelidNameIndexId 3998 + DECLARE_UNIQUE_INDEX(pg_publication_oid_index, 6110, on pg_publication using btree(oid oid_ops)); #define PublicationObjectIndexId 6110 diff --git a/src/include/catalog/pg_period.h b/src/include/catalog/pg_period.h new file mode 100644 index 0000000000..c5c84252ce --- /dev/null +++ b/src/include/catalog/pg_period.h @@ -0,0 +1,51 @@ +/*------------------------------------------------------------------------- + * + * pg_period.h + * definition of the "period" system catalog (pg_period) + * + * + * Portions Copyright (c) 1996-2018, 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,3996,PeriodRelationId) +{ + 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 peropclass; /* OID of the operator class used */ + 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; + +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/nodes/nodes.h b/src/include/nodes/nodes.h index adb159a6da..27d1c2a379 100644 --- a/src/include/nodes/nodes.h +++ b/src/include/nodes/nodes.h @@ -447,6 +447,7 @@ typedef enum NodeTag T_ColumnDef, T_IndexElem, 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 6390f7e8c1..60d12268b9 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -1660,6 +1660,7 @@ typedef enum ObjectType OBJECT_OPCLASS, OBJECT_OPERATOR, OBJECT_OPFAMILY, + OBJECT_PERIOD, OBJECT_POLICY, OBJECT_PROCEDURE, OBJECT_PUBLICATION, @@ -1749,6 +1750,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 (...) */ @@ -2000,9 +2003,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). * ---------------------- @@ -2018,6 +2021,7 @@ typedef struct CreateStmt PartitionBoundSpec *partbound; /* FOR VALUES clause */ PartitionSpec *partspec; /* PARTITION BY clause */ TypeName *ofTypename; /* OF typename */ + List *periods; /* periods (list of Period nodes) */ List *constraints; /* constraints (list of Constraint nodes) */ List *options; /* options from WITH clause */ OnCommitAction oncommit; /* what do we do at COMMIT? */ @@ -2025,6 +2029,22 @@ 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; + char *periodname; /* period name */ + char *startcolname; /* name of start column */ + char *endcolname; /* name of end column */ + List *options; /* options from WITH clause */ + int location; /* token location, or -1 if unknown */ +} Period; + /* ---------- * Definitions for constraints in CreateStmt * diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h index 23db40147b..59ea903cbc 100644 --- a/src/include/parser/kwlist.h +++ b/src/include/parser/kwlist.h @@ -300,6 +300,7 @@ PG_KEYWORD("partial", PARTIAL, UNRESERVED_KEYWORD) PG_KEYWORD("partition", PARTITION, UNRESERVED_KEYWORD) PG_KEYWORD("passing", PASSING, UNRESERVED_KEYWORD) PG_KEYWORD("password", PASSWORD, UNRESERVED_KEYWORD) +PG_KEYWORD("period", PERIOD, RESERVED_KEYWORD) PG_KEYWORD("placing", PLACING, RESERVED_KEYWORD) PG_KEYWORD("plans", PLANS, UNRESERVED_KEYWORD) PG_KEYWORD("policy", POLICY, UNRESERVED_KEYWORD) diff --git a/src/include/utils/syscache.h b/src/include/utils/syscache.h index 4f333586ee..f28dfb0e4e 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, diff --git a/src/test/regress/expected/periods.out b/src/test/regress/expected/periods.out new file mode 100644 index 0000000000..787b68eff5 --- /dev/null +++ b/src/test/regress/expected/periods.out @@ -0,0 +1,55 @@ +/* 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: data type xml has no default operator class for access method "btree" +HINT: You must specify an operator class for the index or define a default operator class for the data 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 diff --git a/src/test/regress/expected/sanity_check.out b/src/test/regress/expected/sanity_check.out index 0aa5357917..329864e7f1 100644 --- a/src/test/regress/expected/sanity_check.out +++ b/src/test/regress/expected/sanity_check.out @@ -136,6 +136,7 @@ pg_opclass|t pg_operator|t pg_opfamily|t pg_partitioned_table|t +pg_period|t pg_pltemplate|t pg_policy|t pg_proc|t @@ -165,6 +166,7 @@ pg_type|t pg_user_mapping|t point_tbl|t polygon_tbl|t +pt|f quad_box_tbl|t quad_point_tbl|t quad_poly_tbl|t diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index 16f979c8d9..95165c5f41 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -60,7 +60,7 @@ test: create_index create_view index_including # ---------- # Another group of parallel tests # ---------- -test: create_aggregate create_function_3 create_cast constraints triggers inherit create_table_like typed_table vacuum drop_if_exists updatable_views rolenames roleattributes create_am hash_func +test: create_aggregate create_function_3 create_cast constraints triggers inherit create_table_like typed_table periods vacuum drop_if_exists updatable_views rolenames roleattributes create_am hash_func # ---------- # sanity_check does a vacuum, affecting the sort order of SELECT * diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule index 42632be675..80c9d857ba 100644 --- a/src/test/regress/serial_schedule +++ b/src/test/regress/serial_schedule @@ -75,6 +75,7 @@ test: triggers test: inherit test: create_table_like test: typed_table +test: periods test: vacuum test: drop_if_exists test: updatable_views diff --git a/src/test/regress/sql/periods.sql b/src/test/regress/sql/periods.sql new file mode 100644 index 0000000000..4b40890b34 --- /dev/null +++ b/src/test/regress/sql/periods.sql @@ -0,0 +1,40 @@ +/* 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;