From 35a5fb68638e932db95b01e947885f9becb376fe Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Sun, 8 Aug 2004 21:33:11 +0000 Subject: [PATCH] Move expanded discussion of inheritance's limitations out of tutorial and into ddl.sgml. Rewrite for more completeness and (hopefully) clarity. --- doc/src/sgml/advanced.sgml | 119 +++++++----------------------------- doc/src/sgml/ddl.sgml | 121 +++++++++++++++++++++++-------------- doc/src/sgml/query.sgml | 16 +++-- 3 files changed, 110 insertions(+), 146 deletions(-) diff --git a/doc/src/sgml/advanced.sgml b/doc/src/sgml/advanced.sgml index d761b996ab..79464fb892 100644 --- a/doc/src/sgml/advanced.sgml +++ b/doc/src/sgml/advanced.sgml @@ -1,5 +1,5 @@ @@ -103,16 +103,16 @@ SELECT * FROM myview; CREATE TABLE cities ( - city varchar(80) primary key, - location point + city varchar(80) primary key, + location point ); CREATE TABLE weather ( - city varchar(80) references cities(city), - temp_lo int, - temp_hi int, - prcp real, - date date + city varchar(80) references cities(city), + temp_lo int, + temp_hi int, + prcp real, + date date ); @@ -327,97 +327,16 @@ COMMIT; - Inheritance is a concept from object-oriented databases. Although - it opens up interesting new possibilities of database design, - this feature is currently unmaintained and known to have serious - gotchas in its foreign key implementation, which you should take - care to avoid. The fixes below are probably version-specific and may - require updates in the future. + Inheritance is a concept from object-oriented databases. It opens + up interesting new possibilities of database design. - - The example below illustrates the gotcha. - - - -BEGIN; -CREATE TABLE foo ( - foo_id SERIAL PRIMARY KEY -); - -CREATE TABLE parent ( - parent_id SERIAL PRIMARY KEY -, foo_id INTEGER NOT NULL REFERENCES foo(foo_id) ON DELETE CASCADE -, parent_1_text TEXT NOT NULL -); - -CREATE TABLE child_1 ( - child_1_text TEXT NOT NULL -) INHERITS(parent); - -CREATE TABLE child_2 ( - child_2_text TEXT NOT NULL -) INHERITS(parent); - -INSERT INTO foo VALUES(DEFAULT); -INSERT INTO child_1 (foo_id, parent_1_text, child_1_text) -VALUES (currval('public.foo_foo_id_seq'), 'parent text 1', 'child_1 text 1'); - -INSERT INTO foo VALUES(DEFAULT); -INSERT INTO child_1 (foo_id, parent_1_text, child_1_text) -VALUES (currval('public.foo_foo_id_seq'), 'parent text 2', 'child_1 text 2'); - -INSERT INTO foo VALUES(DEFAULT); -INSERT INTO child_2 (foo_id, parent_1_text, child_2_text) -VALUES (currval('foo_foo_id_seq'), 'parent text 3', 'child_2 text 1'); - -DELETE FROM foo WHERE foo_id = 1; - -SELECT * FROM parent; - parent_id | foo_id | parent_1_text ------------+--------+--------------- - 1 | 1 | parent text 1 - 2 | 2 | parent text 2 - 3 | 3 | parent text 3 -(3 rows) - -SELECT * FROM child_1; - parent_id | foo_id | parent_1_text | child_1_text ------------+--------+---------------+---------------- - 1 | 1 | parent text 1 | child_1 text 1 - 2 | 2 | parent text 2 | child_1 text 2 -(2 rows) -ROLLBACK; - - - Oops!! None of parent, child or foo should have any rows with -foo_id = 1 in them. Here is a way to fix the above tables. - - - - To fix the gotcha, you must put foreign key constraints on each of - the child tables, as they will not be automatically inherited as - you might expect. - - - - -ALTER TABLE child_1 ADD CONSTRAINT cascade_foo -FOREIGN KEY (foo_id) REFERENCES foo(foo_id) ON DELETE CASCADE; - -ALTER TABLE child_2 ADD CONSTRAINT cascade_foo -FOREIGN KEY (foo_id) REFERENCES foo(foo_id) ON DELETE CASCADE; - - - - - That caveat out of the way, let's create two tables: A table - cities and a table - capitals. Naturally, capitals are also cities, - so you want some way to show the capitals implicitly when you list all - cities. If you're really clever you might invent some scheme like - this: + Let's create two tables: A table cities + and a table capitals. Naturally, capitals + are also cities, so you want some way to show the capitals + implicitly when you list all cities. If you're really clever you + might invent some scheme like this: CREATE TABLE capitals ( @@ -525,6 +444,14 @@ SELECT name, altitude DELETE -- support this ONLY notation. + + + + Although inheritance is frequently useful, it has not been integrated + with unique constraints or foreign keys, which limits its usefulness. + See for more detail. + + diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index af87cabc0c..547c6e57de 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -1,4 +1,4 @@ - + Data Definition @@ -996,19 +996,12 @@ CREATE TABLE capitals ( ) INHERITS (cities); - In this case, a row of capitals inherits all - attributes (name, population, and altitude) from its - parent, cities. The type of the attribute name is - text, a native PostgreSQL type - for variable length character strings. The type of the attribute - population is - float, a native PostgreSQL type for double precision - floating-point numbers. State capitals have an extra - attribute, state, that shows their state. In PostgreSQL, - a table can inherit from zero or more other tables, - and a query can reference either all rows of a - table or all rows of a table plus all of its - descendants. + In this case, a row of capitals inherits all + attributes (name, population, and altitude) from its parent, cities. State + capitals have an extra attribute, state, that shows their state. In + PostgreSQL, a table can inherit from zero or + more other tables, and a query can reference either all rows of a table or + all rows of a table plus all of its descendants. @@ -1065,6 +1058,32 @@ SELECT name, altitude support this ONLY notation. + + Deprecated + + In previous versions of PostgreSQL, the + default behavior was not to include child tables in queries. This was + found to be error prone and is also in violation of the SQL99 + standard. Under the old syntax, to get the sub-tables you append + * to the table name. + For example + +SELECT * from cities*; + + You can still explicitly specify scanning child tables by appending + *, as well as explicitly specify not scanning child tables by + writing ONLY. But beginning in version 7.1, the default + behavior for an undecorated table name is to scan its child tables + too, whereas before the default was not to do so. To get the old + default behavior, set the configuration option + SQL_Inheritance to off, e.g., + +SET SQL_Inheritance TO OFF; + + or add a line in your postgresql.conf file. + + + In some cases you may wish to know which table a particular row originated from. There is a system column called @@ -1109,39 +1128,51 @@ WHERE c.altitude > 500 and c.tableoid = p.oid; - - Deprecated - - In previous versions of PostgreSQL, the - default behavior was not to include child tables in queries. This was - found to be error prone and is also in violation of the SQL99 - standard. Under the old syntax, to get the sub-tables you append - * to the table name. - For example - -SELECT * from cities*; - - You can still explicitly specify scanning child tables by appending - *, as well as explicitly specify not scanning child tables by - writing ONLY. But beginning in version 7.1, the default - behavior for an undecorated table name is to scan its child tables - too, whereas before the default was not to do so. To get the old - default behavior, set the configuration option - SQL_Inheritance to off, e.g., - -SET SQL_Inheritance TO OFF; - - or add a line in your postgresql.conf file. - - - - A limitation of the inheritance feature is that indexes (including + A serious limitation of the inheritance feature is that indexes (including unique constraints) and foreign key constraints only apply to single - tables, not to their inheritance children. Thus, in the above example, - specifying that another table's column REFERENCES cities(name) - would allow the other table to contain city names but not capital names. - This deficiency will probably be fixed in some future release. + tables, not to their inheritance children. This is true on both the + referencing and referenced sides of a foreign key constraint. Thus, + in the terms of the above example: + + + + + If we declared cities.name to be + UNIQUE or a PRIMARY KEY, this would not stop the + capitals table from having rows with names duplicating + rows in cities. And those duplicate rows would by + default show up in SELECTs from cities. In fact, by + default capitals would have no unique constraint at all, + and so could contain multiple rows with the same name. + You could add a unique constraint to capitals, but this + would not prevent duplication compared to cities. + + + + + + Similarly, if we were to specify that + cities.name REFERENCES some + other table, this constraint would not automatically propagate to + capitals. In this case you could work around it by + manually adding the same REFERENCES constraint to + capitals. + + + + + + Specifying that another table's column REFERENCES + cities(name) would allow the other table to contain city names, but + not capital names. There is no good workaround for this case. + + + + + These deficiencies will probably be fixed in some future release, + but in the meantime considerable care is needed in deciding whether + inheritance is useful for your problem. diff --git a/doc/src/sgml/query.sgml b/doc/src/sgml/query.sgml index 85a00bcd0b..bbf5c7a780 100644 --- a/doc/src/sgml/query.sgml +++ b/doc/src/sgml/query.sgml @@ -1,5 +1,5 @@ @@ -284,10 +284,16 @@ COPY weather FROM '/home/user/weather.txt'; SELECT * FROM weather; - (here * means all columns. - Note: While SELECT * is useful for off-the-cuff - queries, it is considered bad style in production code for - maintenance reasons) and the output should be: + (here * means all columns). + + + While SELECT * is useful for off-the-cuff + queries, it is considered bad style in production code for + maintenance reasons: adding a column to the table changes the results. + + + The output should be: + city | temp_lo | temp_hi | prcp | date ---------------+---------+---------+------+------------ -- 2.40.0