From 7c6df91dda27accab3097390ef0d21d93028c7a1 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Fri, 12 Jul 2002 18:43:19 +0000 Subject: Second phase of committing Rod Taylor's pg_depend/pg_constraint patch. pg_relcheck is gone; CHECK, UNIQUE, PRIMARY KEY, and FOREIGN KEY constraints all have real live entries in pg_constraint. pg_depend exists, and RESTRICT/CASCADE options work on most kinds of DROP; however, pg_depend is not yet very well populated with dependencies. (Most of the ones that are present at this point just replace formerly hardwired associations, such as the implicit drop of a relation's pg_type entry when the relation is dropped.) Need to add more logic to create dependency entries, improve pg_dump to dump constraints in place of indexes and triggers, and add some regression tests. --- doc/src/sgml/catalogs.sgml | 391 ++++++++++++++++++++++++++++------- doc/src/sgml/ref/alter_table.sgml | 26 +-- doc/src/sgml/ref/comment.sgml | 5 +- doc/src/sgml/ref/drop_aggregate.sgml | 21 +- doc/src/sgml/ref/drop_domain.sgml | 28 ++- doc/src/sgml/ref/drop_function.sgml | 33 ++- doc/src/sgml/ref/drop_index.sgml | 21 +- doc/src/sgml/ref/drop_language.sgml | 31 ++- doc/src/sgml/ref/drop_operator.sgml | 21 +- doc/src/sgml/ref/drop_rule.sgml | 22 +- doc/src/sgml/ref/drop_sequence.sgml | 23 ++- doc/src/sgml/ref/drop_table.sgml | 56 ++--- doc/src/sgml/ref/drop_trigger.sgml | 21 +- doc/src/sgml/ref/drop_type.sgml | 37 ++-- doc/src/sgml/ref/drop_view.sgml | 74 ++----- doc/src/sgml/release.sgml | 3 +- 16 files changed, 562 insertions(+), 251 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index c3ed19e4b2a..602680aaacb 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -1,6 +1,6 @@ @@ -71,11 +71,21 @@ tables, indexes, sequences (relations) + + pg_constraint + check constraints, unique / primary key constraints, foreign key constraints + + pg_database databases within this database cluster + + pg_depend + dependencies between database objects + + pg_description descriptions or comments on database objects @@ -131,11 +141,6 @@ functions and procedures - - pg_relcheck - check constraints - - pg_rewrite query rewriter rules @@ -680,7 +685,7 @@ Number of check constraints on the table; see - pg_relcheck catalog + pg_constraint catalog @@ -764,6 +769,167 @@ + + pg_constraint + + + This system catalog stores CHECK, PRIMARY KEY, UNIQUE, and FOREIGN KEY + constraints on tables. (Column + constraints are not treated specially. Every column constraint is + equivalent to some table constraint.) See under CREATE + TABLE for more information. + + + + + NOT NULL constraints are represented in the pg_attribute + catalog. + + + + + CHECK constraints on domains are stored here, too. Global ASSERTIONS + (a currently-unsupported SQL feature) may someday appear here as well. + + + + pg_constraint Columns + + + + + Name + Type + References + Description + + + + + + conname + name + + Constraint name (not necessarily unique!) + + + + connamespace + oid + pg_namespace.oid + + The OID of the namespace that contains this constraint + + + + + contype + char + + + 'c' = check constraint, + 'f' = foreign key constraint, + 'p' = primary key constraint, + 'u' = unique constraint + + + + + condeferrable + boolean + + Is the constraint deferrable? + + + + condeferred + boolean + + Is the constraint deferred by default? + + + + conrelid + oid + pg_class.oid + The table this constraint is on; 0 if not a table constraint + + + + contypid + oid + pg_type.oid + The domain this constraint is on; 0 if not a domain constraint + + + + confrelid + oid + pg_class.oid + If a foreign key, the referenced table; else 0 + + + + confupdtype + char + + Foreign key update action code + + + + confdeltype + char + + Foreign key deletion action code + + + + confmatchtype + char + + Foreign key match type + + + + conkey + smallint[] + pg_attribute.attnum + If a table constraint, list of columns which the constraint constrains + + + + confkey + smallint[] + pg_attribute.attnum + If a foreign key, list of the referenced columns + + + + conbin + text + + If a check constraint, an internal representation of the expression + + + + consrc + text + + If a check constraint, a human-readable representation of the expression + + + +
+ + + + pg_class.relchecks + needs to agree with the number of check-constraint entries found in this + table for the given relation. + + + + pg_database @@ -903,6 +1069,151 @@ + + pg_depend + + + The pg_depend table records the dependency + relationships between database objects. This information allows + DROP commands to find which other objects must be dropped + by DROP CASCADE, or prevent dropping in the DROP + RESTRICT case. + + + + pg_depend Columns + + + + + Name + Type + References + Description + + + + + + classid + oid + pg_class.oid + The oid of the system catalog the dependent object is in + + + + objid + oid + any oid attribute + The oid of the specific dependent object + + + + objsubid + integer + + For a table attribute, this is the attribute's + column number (the objid and classid refer to the table itself). + For all other object types, this field is presently zero. + + + + + refclassid + oid + pg_class.oid + The oid of the system catalog the referenced object is in + + + + refobjid + oid + any oid attribute + The oid of the specific referenced object + + + + refobjsubid + integer + + For a table attribute, this is the attribute's + column number (the objid and classid refer to the table itself). + For all other object types, this field is presently zero. + + + + + deptype + char + + + A code defining the specific semantics of this dependency relationship. + + + + + +
+ + + In all cases, a pg_depend entry indicates that the + referenced object may not be dropped without also dropping the dependent + object. However, there are several subflavors identified by + deptype: + + + + + DEPENDENCY_NORMAL ('n'): normal relationship between separately-created + objects. The dependent object may be dropped without affecting the + referenced object. The referenced object may only be dropped by + specifying CASCADE, in which case the dependent object is dropped too. + Example: a table column has a normal dependency on its datatype. + + + + + + DEPENDENCY_AUTO ('a'): the dependent object can be dropped separately + from the referenced object, and should be automatically dropped + (regardless of RESTRICT or CASCADE mode) if the referenced object + is dropped. + Example: a named constraint on a table is made auto-dependent on + the table, so that it will go away if the table is dropped. + + + + + + DEPENDENCY_INTERNAL ('i'): the dependent object was created as part + of creation of the referenced object, and is really just a part of + its internal implementation. A DROP of the dependent object will be + disallowed outright (we'll tell the user to issue a DROP against the + referenced object, instead). A DROP of the referenced object will be + propagated through to drop the dependent object whether CASCADE is + specified or not. + Example: a trigger that's created to enforce a foreign-key constraint + is made internally dependent on the constraint's pg_constraint entry. + + + + + + DEPENDENCY_PIN ('p'): there is no dependent object; this type of entry + is a signal that the system itself depends on the referenced object, + and so that object must never be deleted. Entries of this type are + created only during initdb. The fields for the dependent object + contain zeroes. + + + + + Other dependency flavors may be needed in future. + + + + + pg_description @@ -1866,72 +2177,6 @@ - - - pg_relcheck - - - This system catalog stores CHECK constraints on tables. (Column - constraints are not treated specially. Every column constraint is - equivalent to some table constraint.) See under CREATE - TABLE for more information. - - - - pg_relcheck Columns - - - - - Name - Type - References - Description - - - - - - rcrelid - oid - pg_class.oid - The table this check constraint is on - - - - rcname - name - - Constraint name - - - - rcbin - text - - An internal representation of the constraint expression - - - - rcsrc - text - - A human-readable representation of the constraint expression - - - -
- - - - pg_class.relchecks - needs to match up with the entries in this table. - - - - - - pg_rewrite diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml index 4966eb757b8..9acd474203c 100644 --- a/doc/src/sgml/ref/alter_table.sgml +++ b/doc/src/sgml/ref/alter_table.sgml @@ -1,5 +1,5 @@ @@ -39,7 +39,7 @@ ALTER TABLE table ALTER TABLE table ADD table_constraint_definition ALTER TABLE [ ONLY ] table - DROP CONSTRAINT constraint_name { RESTRICT | CASCADE } + DROP CONSTRAINT constraint_name [ RESTRICT | CASCADE ] ALTER TABLE table OWNER TO new_owner @@ -315,26 +315,6 @@ ALTER TABLE table form after you've entered non-null values for the column in all rows. - - In DROP CONSTRAINT, the RESTRICT keyword is required, although - dependencies are not yet checked. The CASCADE option is unsupported. - Currently DROP CONSTRAINT only handles CHECK constraints. - To remove a PRIMARY or UNIQUE constraint, drop the - relevant index using the command. - To remove FOREIGN KEY constraints you need to recreate - and reload the table, using other parameters to the - command. - - - For example, to drop all constraints on a table distributors: - -CREATE TABLE temp AS SELECT * FROM distributors; -DROP TABLE distributors; -CREATE TABLE distributors AS SELECT * FROM temp; -DROP TABLE temp; - - - Changing any part of the schema of a system catalog is not permitted. @@ -395,7 +375,7 @@ ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5); To remove a check constraint from a table and all its children: -ALTER TABLE distributors DROP CONSTRAINT zipchk RESTRICT; +ALTER TABLE distributors DROP CONSTRAINT zipchk; diff --git a/doc/src/sgml/ref/comment.sgml b/doc/src/sgml/ref/comment.sgml index 3d4041ba8ee..ea173e1741a 100644 --- a/doc/src/sgml/ref/comment.sgml +++ b/doc/src/sgml/ref/comment.sgml @@ -1,5 +1,5 @@ @@ -26,6 +26,7 @@ COMMENT ON TABLE object_name | COLUMN table_name.column_name | AGGREGATE agg_name (agg_type) | + CONSTRAINT constraint_name ON table_name | DATABASE object_name | DOMAIN object_name | FUNCTION func_name (arg1_type, arg2_type, ...) | @@ -52,7 +53,7 @@ COMMENT ON object_name, - table_name.column_name, agg_name, func_name, op, rule_name, trigger_name + table_name.column_name, agg_name, constraint_name, func_name, op, rule_name, trigger_name The name of the object to be be commented. Names of tables, diff --git a/doc/src/sgml/ref/drop_aggregate.sgml b/doc/src/sgml/ref/drop_aggregate.sgml index 857a7f10f48..9913e5d8ed3 100644 --- a/doc/src/sgml/ref/drop_aggregate.sgml +++ b/doc/src/sgml/ref/drop_aggregate.sgml @@ -1,5 +1,5 @@ @@ -21,7 +21,7 @@ PostgreSQL documentation 1999-07-20 -DROP AGGREGATE name ( type ) +DROP AGGREGATE name ( type ) [ CASCADE | RESTRICT ] @@ -54,6 +54,23 @@ DROP AGGREGATE name ( + + CASCADE + + + Automatically drop objects that depend on the aggregate. + + + + + RESTRICT + + + Refuse to drop the aggregate if there are any dependent objects. + This is the default. + + + diff --git a/doc/src/sgml/ref/drop_domain.sgml b/doc/src/sgml/ref/drop_domain.sgml index 5bc73a065fa..b39e2493021 100644 --- a/doc/src/sgml/ref/drop_domain.sgml +++ b/doc/src/sgml/ref/drop_domain.sgml @@ -1,5 +1,5 @@ @@ -48,8 +48,8 @@ DROP DOMAIN domainname [, ...] [ C CASCADE - Automatically drop objects that depend on the domain. This - behavior is not currently supported. + Automatically drop objects that depend on the domain + (such as table columns). @@ -58,7 +58,8 @@ DROP DOMAIN domainname [, ...] [ C RESTRICT - Do not drop dependent objects. This is the default. + Refuse to drop the domain if there are any dependent objects. + This is the default. @@ -143,19 +144,14 @@ DROP DOMAIN box; Compatibility + + + + SQL92 + - - A DROP DOMAIN statement exists in SQL99. As with - most other drop commands, DROP - DOMAIN in SQL99 requires a drop behavior - clause to select between dropping all dependent objects or refusing - to drop if dependent objects exist: - -DROP DOMAIN name { CASCADE | RESTRICT } - - PostgreSQL accepts only the RESTRICT - option, and currently does not check for existence of dependent objects. - + + diff --git a/doc/src/sgml/ref/drop_function.sgml b/doc/src/sgml/ref/drop_function.sgml index 268d8c66e8c..5aa1b141bad 100644 --- a/doc/src/sgml/ref/drop_function.sgml +++ b/doc/src/sgml/ref/drop_function.sgml @@ -1,5 +1,5 @@ @@ -21,7 +21,7 @@ PostgreSQL documentation 1999-07-20 -DROP FUNCTION name ( [ type [, ...] ] ) +DROP FUNCTION name ( [ type [, ...] ] ) [ CASCADE | RESTRICT ] @@ -49,6 +49,24 @@ DROP FUNCTION name ( [ + + CASCADE + + + Automatically drop objects that depend on the function + (such as operators or triggers). + + + + + RESTRICT + + + Refuse to drop the function if there are any dependent objects. + This is the default. + + + @@ -136,15 +154,8 @@ DROP FUNCTION sqrt(integer); Compatibility - A DROP FUNCTION statement is defined in SQL99. One of its syntax forms is: - - -DROP FUNCTION name (arg, ...) { RESTRICT | CASCADE } - - - where CASCADE specifies dropping all objects that - depend on the function and RESTRICT refuses to - drop the function if dependent objects exist. + A DROP FUNCTION statement is defined in SQL99. One of + its syntax forms is similar to PostgreSQL's. diff --git a/doc/src/sgml/ref/drop_index.sgml b/doc/src/sgml/ref/drop_index.sgml index 0db2c609461..149afff0779 100644 --- a/doc/src/sgml/ref/drop_index.sgml +++ b/doc/src/sgml/ref/drop_index.sgml @@ -1,5 +1,5 @@ @@ -21,7 +21,7 @@ PostgreSQL documentation 1999-07-20 -DROP INDEX index_name [, ...] +DROP INDEX index_name [, ...] [ CASCADE | RESTRICT ] @@ -41,6 +41,23 @@ DROP INDEX index_name [, ...] + + CASCADE + + + Automatically drop objects that depend on the index. + + + + + RESTRICT + + + Refuse to drop the index if there are any dependent objects. + This is the default. + + + diff --git a/doc/src/sgml/ref/drop_language.sgml b/doc/src/sgml/ref/drop_language.sgml index 08badab8083..e0690a88ac6 100644 --- a/doc/src/sgml/ref/drop_language.sgml +++ b/doc/src/sgml/ref/drop_language.sgml @@ -1,5 +1,5 @@ @@ -21,7 +21,7 @@ PostgreSQL documentation 1999-07-20 -DROP [ PROCEDURAL ] LANGUAGE name +DROP [ PROCEDURAL ] LANGUAGE name [ CASCADE | RESTRICT ] @@ -43,7 +43,26 @@ DROP [ PROCEDURAL ] LANGUAGE name + + CASCADE + + + Automatically drop objects that depend on the language + (such as functions in the language). + + + + + RESTRICT + + + Refuse to drop the language if there are any dependent objects. + This is the default. + + + + @@ -112,14 +131,6 @@ ERROR: Language "name" doesn't exis for information on how to create procedural languages. - - - No checks are made if functions or trigger procedures registered - in this language still exist. To re-enable them without having - to drop and recreate all the functions, the pg_proc's prolang - attribute of the functions must be adjusted to the new object - ID of the recreated pg_language entry for the PL. - diff --git a/doc/src/sgml/ref/drop_operator.sgml b/doc/src/sgml/ref/drop_operator.sgml index bc4657038fd..1cfb824090c 100644 --- a/doc/src/sgml/ref/drop_operator.sgml +++ b/doc/src/sgml/ref/drop_operator.sgml @@ -1,5 +1,5 @@ @@ -22,7 +22,7 @@ PostgreSQL documentation 1999-07-20 -DROP OPERATOR id ( lefttype | NONE , righttype | NONE ) +DROP OPERATOR id ( lefttype | NONE , righttype | NONE ) [ CASCADE | RESTRICT ] @@ -60,6 +60,23 @@ DROP OPERATOR id ( + + CASCADE + + + Automatically drop objects that depend on the operator. + + + + + RESTRICT + + + Refuse to drop the operator if there are any dependent objects. + This is the default. + + + diff --git a/doc/src/sgml/ref/drop_rule.sgml b/doc/src/sgml/ref/drop_rule.sgml index 4f8ea97f88d..afb1b6a874b 100644 --- a/doc/src/sgml/ref/drop_rule.sgml +++ b/doc/src/sgml/ref/drop_rule.sgml @@ -1,5 +1,5 @@ @@ -21,7 +21,7 @@ PostgreSQL documentation 1998-09-22 -DROP RULE name ON relation +DROP RULE name ON relation [ CASCADE | RESTRICT ] @@ -50,7 +50,25 @@ DROP RULE name ON + + CASCADE + + + Automatically drop objects that depend on the rule. + + + + + RESTRICT + + + Refuse to drop the rule if there are any dependent objects. + This is the default. + + + + diff --git a/doc/src/sgml/ref/drop_sequence.sgml b/doc/src/sgml/ref/drop_sequence.sgml index 4c371164e1e..50623849342 100644 --- a/doc/src/sgml/ref/drop_sequence.sgml +++ b/doc/src/sgml/ref/drop_sequence.sgml @@ -1,5 +1,5 @@ @@ -21,7 +21,8 @@ PostgreSQL documentation 1999-07-20 -DROP SEQUENCE name [, ...] +DROP SEQUENCE name [, ...] [ CASCADE | RESTRICT ] + @@ -75,7 +76,25 @@ ERROR: sequence "name" does not exi + + CASCADE + + + Automatically drop objects that depend on the sequence. + + + + + RESTRICT + + + Refuse to drop the sequence if there are any dependent objects. + This is the default. + + + + diff --git a/doc/src/sgml/ref/drop_table.sgml b/doc/src/sgml/ref/drop_table.sgml index cae3275067d..23148ff827d 100644 --- a/doc/src/sgml/ref/drop_table.sgml +++ b/doc/src/sgml/ref/drop_table.sgml @@ -1,5 +1,5 @@ @@ -21,7 +21,8 @@ PostgreSQL documentation 1999-07-20 -DROP TABLE name [, ...] +DROP TABLE name [, ...] [ CASCADE | RESTRICT ] + @@ -41,6 +42,24 @@ DROP TABLE name [, ...] + + CASCADE + + + Automatically drop objects that depend on the table + (such as views). + + + + + RESTRICT + + + Refuse to drop the table if there are any dependent objects. + This is the default. + + + @@ -136,44 +155,11 @@ DROP TABLE films, distributors; - - 1998-09-22 - SQL92 - SQL92 specifies some additional capabilities for DROP TABLE: - -DROP TABLE table { RESTRICT | CASCADE } - - - - RESTRICT - - - Ensures that only a table with no dependent views or - integrity constraints can be destroyed. - - - - - CASCADE - - - Any referencing views or integrity constraints - will also be dropped. - - - - - - - At present, to remove a referencing view you must drop - it explicitly. - - diff --git a/doc/src/sgml/ref/drop_trigger.sgml b/doc/src/sgml/ref/drop_trigger.sgml index e4f157b1d7c..c154d8117db 100644 --- a/doc/src/sgml/ref/drop_trigger.sgml +++ b/doc/src/sgml/ref/drop_trigger.sgml @@ -1,5 +1,5 @@ @@ -21,7 +21,7 @@ PostgreSQL documentation 1998-09-22 -DROP TRIGGER name ON table +DROP TRIGGER name ON table [ CASCADE | RESTRICT ] @@ -50,6 +50,23 @@ DROP TRIGGER name ON + + CASCADE + + + Automatically drop objects that depend on the trigger. + + + + + RESTRICT + + + Refuse to drop the trigger if there are any dependent objects. + This is the default. + + + diff --git a/doc/src/sgml/ref/drop_type.sgml b/doc/src/sgml/ref/drop_type.sgml index 3ac0f17ae9f..81cf010bb00 100644 --- a/doc/src/sgml/ref/drop_type.sgml +++ b/doc/src/sgml/ref/drop_type.sgml @@ -1,5 +1,5 @@ @@ -21,7 +21,8 @@ PostgreSQL documentation 1999-07-20 -DROP TYPE typename [, ...] +DROP TYPE typename [, ...] [ CASCADE | RESTRICT ] + @@ -41,6 +42,24 @@ DROP TYPE typename [, ...] + + CASCADE + + + Automatically drop objects that depend on the type + (such as table columns, functions, operators, etc). + + + + + RESTRICT + + + Refuse to drop the type if there are any dependent objects. + This is the default. + + + @@ -75,6 +94,7 @@ ERROR: RemoveType: type 'typename' + @@ -132,19 +152,6 @@ DROP TYPE box; Compatibility - - A DROP TYPE statement exists in SQL99. As with - most other drop commands, DROP - TYPE in SQL99 requires a drop behavior - clause to select between dropping all dependent objects or refusing - to drop if dependent objects exist: - -DROP TYPE name { CASCADE | RESTRICT } - - PostgreSQL currently ignores - dependencies altogether. - - Note that the CREATE TYPE command and the data type extension mechanisms in PostgreSQL diff --git a/doc/src/sgml/ref/drop_view.sgml b/doc/src/sgml/ref/drop_view.sgml index a789e38c4ce..52711d984ef 100644 --- a/doc/src/sgml/ref/drop_view.sgml +++ b/doc/src/sgml/ref/drop_view.sgml @@ -1,5 +1,5 @@ @@ -21,7 +21,7 @@ PostgreSQL documentation 1999-07-20 -DROP VIEW name [, ...] +DROP VIEW name [, ...] [ CASCADE | RESTRICT ] @@ -42,7 +42,26 @@ DROP VIEW name [, ...] + + CASCADE + + + Automatically drop objects that depend on the view + (such as other views). + + + + + RESTRICT + + + Refuse to drop the view if there are any dependent objects. + This is the default. + + + + @@ -134,58 +153,7 @@ DROP VIEW kinds; SQL92 - SQL92 specifies some additional capabilities for - DROP VIEW: - - -DROP VIEW view { RESTRICT | CASCADE } - - - - - 1998-09-22 - - - Inputs - - - - - RESTRICT - - - Ensures that only a view with no dependent views or - integrity constraints can be destroyed. - - - - - CASCADE - - - Any referencing views and integrity constraints - will be dropped as well. - - - - - - - - - - 1998-09-22 - - - Notes - - - At present, to remove a referencing view from a - PostgreSQL database, - you must drop it explicitly. - - diff --git a/doc/src/sgml/release.sgml b/doc/src/sgml/release.sgml index 9cd74fae205..7fd8c92e371 100644 --- a/doc/src/sgml/release.sgml +++ b/doc/src/sgml/release.sgml @@ -1,5 +1,5 @@ @@ -24,6 +24,7 @@ CDATA means the content is "SGML-free", so you can write without worries about funny characters. -->