Folks,
This adds a caveat to the inheritance part of the tutorial.
Cheers,
D
--
David Fetter [EMAIL PROTECTED] http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778
Remember to vote!
Index: advanced.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/advanced.sgml,v
retrieving revision 1.42
diff -u -r1.42 advanced.sgml
--- advanced.sgml 1 Aug 2004 17:32:11 -0000 1.42
+++ advanced.sgml 6 Aug 2004 18:05:35 -0000
@@ -108,7 +108,7 @@
);
CREATE TABLE weather (
- city varchar(80) references cities,
+ city varchar(80) references cities(city),
temp_lo int,
temp_hi int,
prcp real,
@@ -326,16 +326,97 @@
</indexterm>
<para>
- Inheritance is a concept from object-oriented databases. It opens
- up interesting new possibilities of database design.
+ 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.
+ </para>
+ <para>
+ The example below illustrates the gotcha.
+ </para>
+ <para>
+<programlisting>
+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;
+</programlisting>
+
+ </para>
+ <para>
+ 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.
+ </para>
+
+ <para>
+ 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.
+ </para>
+
+ <para>
+<programlisting>
+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;
+</programlisting>
</para>
<para>
- Let's create two tables: A table <classname>cities</classname>
- and a table <classname>capitals</classname>. 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:
+ That caveat out of the way, let's create two tables: A table
+ <classname>cities</classname> and a table
+ <classname>capitals</classname>. 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:
<programlisting>
CREATE TABLE capitals (
@@ -358,7 +439,7 @@
</programlisting>
This works OK as far as querying goes, but it gets ugly when you
- need to update several rows, to name one thing.
+ need to update several rows, for one thing.
</para>
<para>
Index: query.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/query.sgml,v
retrieving revision 1.35
diff -u -r1.35 query.sgml
--- query.sgml 29 Nov 2003 19:51:37 -0000 1.35
+++ query.sgml 6 Aug 2004 18:05:36 -0000
@@ -284,8 +284,10 @@
<programlisting>
SELECT * FROM weather;
</programlisting>
- (here <literal>*</literal> means <quote>all columns</quote>) and
- the output should be:
+ (here <literal>*</literal> means <quote>all columns</quote>.
+ Note: While <literal>SELECT *</literal> is useful for off-the-cuff
+ queries, it is considered bad style in production code for
+ maintenance reasons) and the output should be:
<screen>
city | temp_lo | temp_hi | prcp | date
---------------+---------+---------+------+------------
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
