postgresql - designing a tree hierarchy with mixed node types (inheritance does not help!)

Question

I have a question about implementing inheritance in postgresql(9.1).

The purpose is to build a geo-hierarchy model, where countries, states and continents can be mixed up to create "regions". And then these regions too can be mixed up with the countries, etc. to create a truly awesome region-hierarchy

So in my logical model, everything is a type of "place". A region-tree can be constructed by specifying edgewise using the two "places". The design is as below, and easy to manage in the Java layer.

    create table place_t (
            place_id serial primary key,
            place_type varchar(10)
    );
    create table country_t (
            short_name varchar(30) unique, 
            name varchar(255) null
    ) inherits(place_t);
    create table region_t( 
            short_name varchar(30),
            hierarchy_id integer, -- references hierarchy_t(hierarchy_id)
            unique(short_name) -- (short_name,hierarchy_id)
    ) inherits(place_t);
    create table region_hier_t( 
            parent integer references place_t(place_id), -- would prefer FK region_t(place_id)
            child integer references place_t(place_id),
            primary key(parent,child)
    );

    insert into region_t values(DEFAULT, 'region', 'NA', 'north american ops');
    insert into region_t values(DEFAULT, 'region', 'EMEA', 'europe and middle east');
    insert into country_t values(DEFAULT, 'country', 'US', 'USD', 'united states');
    insert into country_t values(DEFAULT, 'country', 'CN', 'CND', 'canada');

So far so good. But the following fails:

    insert  into region_hier_t
        select p.place_id, c.place_id
        from region_t as p, country_t as c
        where p.short_name = 'NA' and c.short_name = 'US';

The reason is that the first 4 inserts did not create any row in "place_t". RTFM! Postgres docs actually mention this.

The question is - is there a workaround? Via insert triggers on region_t and country_t to implement my own "inheritance" is the only thing I could think of.

A second question is - is there a better design for such a mixed-node tree structure?

For certain reasons I do not want to rely too much on postgres-contrib features. Perhaps that's very silly and please feel free to chime in, but gently (and only after answering the other question)!

Thanks

Answer 1

References on parent and child column in region_hier_t table are wrong, because you cannot insert a key from country_t if your reference calls another table (child integer references place_t(place_id)); You can either drop them or add new ones. So let's take the second option and add an unique constraint matching given keys for referenced tables region_t and country_t:

ALTER TABLE region_t
  ADD CONSTRAINT pk_region_t PRIMARY KEY(place_id );

ALTER TABLE country_t
  ADD CONSTRAINT pk_country_t PRIMARY KEY(place_id );

The correct CREATE statement for region_hier_t is:

        create table region_hier_t( 
            parent integer references region_t(place_id),
            child integer references country_t(place_id),
            primary key(parent,child)
    );

And finally you can run your INSERT.

So, as you see there is many improvements for you to do. Maybe you should reconsider your design. Take a look at this answer: How to store postal addresses and political divisions in a normalized way? It's much simpler than your solution and easier to maintain.

But if you wanna stay by your solution don't forget to set primary keys on child tables(as shown above). Only check constraints and not-null constraints are inherited by its children and you haven't done it already.

I see that other of your insert don't work correctly:

insert into region_t values(DEFAULT, 'region', 'NA', 'north american ops');


ERROR:  invalid input syntax for integer: "north american ops"
LINE 1: ...ert into region_t values(DEFAULT, 'region', 'NA', 'north ame...

So there is problem with column assignment as well.

Answer 2

So it turns out that inheritance in PostgreSQL is somewhat different from that used in typical OOP languages. In particular, the "superclass" table is not populated automatically. If I had to use my own triggers to do that, I didn't have a use case left for the inheritance structure.

So I abandoned Postgresql inheritance and created my own "place_t" table. And "country_t", "state_t", "county_t" and "region_t" children tables, linked to parent "place_t" through "place_id".

On these children tables, I created an before insert/update row level trigger to ensure that "place_id" refers to a valid row in "place_t" and the reference is not changed later. IOW, "place_id" in children tables should behave like write-once-read-many.

Now, I can insert the world geo. Also, define a new "region". I created a "region_composition_t" to record the edges of a regional hierarchy, the parent being a reference to "region_t" and child being a reference to "place_t".

So far so good. The challenge now is how to suppress any update/delete cascading effects.

Answer 3

The workaround is to get rid of your foreign keys to place_t and do instead:

CREATE FUNCTION place_t_exists(id int)
RETURNS bool LANGUAGE SQL AS 
$$
   SELECT count(*) = 1 FROM place_t;
$$;

CREATE FUNCTION fkey_place_t() RETURNS TRIGGER
LANGUAGE PLPGSQL AS $$
BEGIN;
  IF place_t_exists(TG_ARGV[1]) THEN RETURN NEW
  ELSE RAISE EXCEPTION 'place_t does not exist';
  END IF;
END;
$$;

You also need something on the child tables to restrain when the hierarchy node exists:

CREATE FUNCTION hierarchy_exists(id int) RETURNS BOOL LANGUAGE SQL AS
$$
SELECT COUNT(*) > 0 FROM region_heir_t WHERE parent = $1 or child = $1;
$$;

CREATE OR REPLACE FUNCTION fkey_hierarchy_trigger() RETURNS trigger LANGUAGE PLPGSQL AS
$$
BEGIN
   IF hierarchy_exists(old.place_id) THEN RAISE EXCEPTION 'Hierarchy node still exists';
   ELSE RETURN OLD;
END;
$$;

Then you can create your triggers:

CREATE CONSTRAINT TRIGGER fkey_place_parent AFTER INSERT OR UPDATE TO region_hier_t
FOR EACH ROW EXECUTE PROCEDURE fkey_place_t(new.parent);
CREATE CONSTRAINT TRIGGER fkey_place_child AFTER INSERT OR UPDATE TO region_hier_t
FOR EACH ROW EXECUTE PROCEDURE fkey_place_t(new.child);

And then for each of the place_t child tables:

CREATE CONSTRAINT TRIGGER fkey_hier_t TO [child_table]
FOR EACH ROW EXECUTE PROCEDURE fkey_hierarchy_trigger();

This solution may not be worth it, but it is worth knowing how to do it if you need to.