PostgreSQL is a DBMS that supports many advanced features such as table inheritance. However, the native table inheritance implementation poses several problems when it comes to using it in some real-world projects.
We will describe the problems encountered when using PostgreSQL's inheritance implementation, then propose a solution to these problems using a set of functions and triggers.
Three issues are really critical as they prevent PostgreSQL's table inheritance to be used without having to create intermediary tables as workarounds: foreign key constraints and triggers not being propagated, which can lead to incoherences; primary keys and unique constraints not being propagated; and finally, the impossibility to create foreign keys that reference both a table and its children.
Foreign key constraints are not propagated by table inheritance: if some field has been defined as a foreign key in a parent table, the inherited tables should consider this field as a foreign key as well. That is not the case, as shown in the example below.
-- Create a table to which references will be made CREATE TABLE dest( id INT PRIMARY KEY ); INSERT INTO dest (id) VALUES (1); -- Create a parent and a child table CREATE TABLE parent( fk INT NOT NULL REFERENCES dest (id) ON DELETE CASCADE ON UPDATE CASCADE ); CREATE TABLE child () INHERITS (parent); -- This will fail, which is the expected behaviour INSERT INTO parent (fk) VALUES (2); -- This will succeed INSERT INTO child (fk) VALUES (2);
In the same trend, it should be noted that triggers are not propagated either.
Indexes are not propagated at all, which could cause performance problems. But what is much worse is the fact that, when a table with a primary key is inherited by another table, the primary key only applies to the parent table. This means that the child table doesn't have a primary key and can contain duplicates of the parent table's primary key.
-- Create a parent and a child table CREATE TABLE parent( pk INT NOT NULL PRIMARY KEY ); CREATE TABLE child () INHERITS (parent); -- Insert a first key into parent INSERT INTO parent (pk) VALUES (1); -- The following will fail, which is the expected behaviour INSERT INTO parent (pk) VALUES (1); -- The following will succeed INSERT INTO child (pk) VALUES (1);
Because of the previously described phenomenon, creating a foreign key referencing a table will only allow the foreign key to reference elements which are stored within that table; it will not be possible to reference elements contained in the table's children.
-- Create a parent and a child table CREATE TABLE parent( pk INT NOT NULL PRIMARY KEY ); CREATE TABLE child () INHERITS (parent); -- Fill parent and child INSERT INTO parent (pk) VALUES (1); INSERT INTO child (pk) VALUES (2); -- Create a referencing table CREATE TABLE src( fk INT NOT NULL REFERENCES parent (pk) ); -- This will work as expected INSERT INTO src (fk) VALUES (1); -- This will fail INSERT INTO src (fk) VALUES (2);
In order to work around these limitations, we wrote a set of PL/PgSQL functions that will automatically propagate foreign keys and generate functions and triggers that will enforce primary key and unique keys amongst all inherited tables as well as handle foreign keys to table hierarchies.
This new script, while very similar to the scripts that came along the Dia2Postgres project, presents two major differences. It has been written specifically with PostgreSQL 8 in mind, and therefore uses and handles namespaces. The second difference is that it is much more automated than the D2P scripts were.
When loading the script into a database, it will create a schema called inheritance in which the tables used to cache structure-related data as well as all of the required functions will reside.
The order in which the script is loaded is irrelevant: loading the script itself will not cause any code to be generated. First, one has to specify which schema(s) the script should handle usign the inheritance.add_schema() function. Once all schemas to handle have been listed, the inheritance.init() function must be called.
The example below creates a schema called "test" in which a table hierarchy is created; it then demonstrates how the inheritance code must be initialised.
-- Load the inheritance code
\i psql-inheritance.sql
-- Create a schema named test in which our tables will reside
CREATE SCHEMA test;
-- Create a table hierarchy into "test" here
-- Adds the test schema to the list of schemas to handle
SELECT inheritance.add_schema('test');
-- Generate the inheritance code
SELECT inheritance.init();
Once the structure has been defined and the inheritance code has been run, the table hierarchy will indeed behave differently. We will explore the major differences in the sections below.
The inheritance code ensures that primary keys and unique indexes are indeed unique in complete sections of a table hierarchy.
-- Create a parent and a child table
CREATE SCHEMA test
CREATE TABLE parent(
pk INT NOT NULL PRIMARY KEY
)
CREATE TABLE child () INHERITS (parent);
-- Run the inheritance code
SELECT inheritance.add_schema('test');
SELECT inheritance.init();
-- Insert an element into parent and one into child
INSERT INTO test.parent (pk) VALUES (1);
INSERT INTO test.child (pk) VALUES (2);
-- Now try inserting duplicate values. That will fail
INSERT INTO test.parent (pk) VALUES (2);
INSERT INTO test.child (pk) VALUES (1);
Triggers are now propagated from a table to its children.
-- Create the test schema with two tables, parent and child
-- ...
-- Create a trigger on test.parent
CREATE FUNCTION test.func_test() RETURNS TRIGGER AS $$
BEGIN
RAISE NOTICE 'func_test() called on insert on table % (#%) with argument "%"', TG_RELNAME, TG_RELID, TG_ARGV[0];
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trigger_test BEFORE INSERT ON test.parent
FOR EACH ROW EXECUTE PROCEDURE test.func_test('some value');
-- Run the inheritance code
-- ...
-- Insert into the child table
INSERT INTO child (id) VALUES (1);
-- That insertion will display the following:
-- NOTICE: func_test() called on insert on table child (#...) with argument "some value"
Foreign keys are now propagated from a table to its children and it is now possible to reference a table hierarchy.
-- Create a table hierarchy in the "test" schema
CREATE SCHEMA test
CREATE TABLE parent (
id INT PRIMARY KEY
)
CREATE TABLE child_1 () INHERITS (parent)
CREATE TABLE child_2 (
fk INT NOT NULL REFERENCES parent (id)
) INHERITS (parent)
CREATE TABLE grandchild () INHERITS (child_2);
-- Create a table in the public schema referencing test.parent
CREATE TABLE other_table (
fk INT NOT NULL REFERENCES test.parent (id)
);
-- Run the inheritance code
SELECT inheritance.add_schema('test');
SELECT inheritance.init();
-- Insert some elements into both test.parent and test.child_1
INSERT INTO test.parent (id) VALUES (1);
INSERT INTO test.child_1 (id) VALUES (2);
-- Now insert values into test.child_2 referencing both elements
INSERT INTO test.child_2 (id, fk) VALUES (3, 1); -- Always worked
INSERT INTO test.child_2 (id, fk) VALUES (4, 2); -- Works now!
-- Insert values into test.grandchild
INSERT INTO test.grandchild (id, fk) VALUES (5, 2); -- Always worked
INSERT INTO test.grandchild (id, fk) VALUES (6, 9); -- No longer works!
-- public.other_table has been handled by the inheritance code too
-- despite the fact that it's in the public schema because it references
-- tables in the "test" schema
INSERT INTO other_table (fk) VALUES (5);
INSERT INTO other_table (fk) VALUES (7);
One problem with foreign keys remains tho. It is not possible to create a foreign key referencing a child table directly using SQL: since primary keys are propagated by the inheritance code, the key doesn't have an index defined in the child tables when the structure is created.
For example, the code below would not work:
CREATE TABLE parent (id INT PRIMARY KEY); CREATE TABLE child() INHERITS (parent); CREATE TABLE childref(fk INT REFERENCES child (id)); -- ERROR: there is no unique constraint matching given keys for referenced table "child"
To work around this problem, the inheritance code contains a function named inheritance.foreign_key() which is used to define foreign keys to tables that don't have unique indexes yet. Using this function, the code above would become:
CREATE TABLE parent (id INT PRIMARY KEY);
CREATE TABLE child() INHERITS (parent);
CREATE TABLE childref(fk INT);
SELECT inheritance.foreign_key('public.childref', 'fk', 'public.child', 'id', 'NO ACTION', 'NO ACTION');
Before modifying the database's structure, it is very important that the inheritance code is neutralised, as it changes the initial table definitions. In order to do that, simply call the inheritance.kill() function before modifying the structure, then call inheritance.init() function again after the changes have been made.
IMPORTANT: no other database operations should be allowed while the inheritance code is disabled. This would put the database's coherence at risk.
WARNING! This code is mostly untested. It may not work as expected. I have not been using it
myself lately. However, if you encounter any problems with it, please contact me! Thank you.
NOTE: NO ACTION, while supported, is not deferrable. There is in fact no difference between
RESTRICT and NO ACTION, as far as this library is concerned.
I'd like to thank David M. St. Pierre from ShawneeLink for his help and patience in testing this library.