The “O” in ORDBMS: PostgreSQL Inheritance

In this blog entry we’ll go through PostgreSQL inheritance, traditionally one of PostgreSQL’s top features since the early releases. Some typical uses of inheritance in PostgreSQL are:

• table partitioning
• multi-tenancy

PostgreSQL until version 10 implemented table partitioning using inheritance. PostgreSQL 10 provides a new way of declarative partitioning. PostgreSQL partitioning using inheritance is a pretty mature technology, well documented and tested, however inheritance in PostgreSQL from a data model perspective is (in my opinion) not so widespread, therefore we’ll concentrate on more classic use cases in this blog. We saw from the previous blog (multi-tenancy options for PostgreSQL) that one of the methods to achieve multi-tenancy is to use separate tables and then consolidate them via a view. We also saw the drawbacks of this design. In this blog we will enhance this design using inheritance.

Introduction to Inheritance

Looking back at the multi-tenancy method implemented with separates tables and views we recall that its major disadvantage is the inability to do inserts/updates/deletes. The moment we try an update on the rental view we’ll get this ERROR:

ERROR:  cannot insert into view "rental"
DETAIL:  Views containing UNION, INTERSECT, or EXCEPT are not automatically updatable.
HINT:  To enable inserting into the view, provide an INSTEAD OF INSERT trigger or an unconditional ON INSERT DO INSTEAD rule.

So, we would need to create a trigger or a rule on the rental view specifying a function to handle the insert/update/delete. The alternative is to use inheritance. Let us change the schema of the previous blog:

template1=# create database rentaldb_hier;
template1=# c rentaldb_hier
rentaldb_hier=# create schema boats;
rentaldb_hier=# create schema cars;

Now let’s create the main parent table:

rentaldb_hier=# CREATE TABLE rental (
    id integer NOT NULL,
    customerid integer NOT NULL,
    vehicleno text,
    datestart date NOT NULL,
    dateend date
); 

In OO terms this table corresponds to the superclass (in java terminology). Now let’s define the children tables by inheriting from public.rental and also adding a column for each table that is specific to the domain: e.g. the mandatory driver’s (customer) license number in case of cars and the optional boat sailing certificate.

rentaldb_hier=# create table cars.rental(driv_lic_no text NOT NULL) INHERITS (public.rental);
rentaldb_hier=# create table boats.rental(sail_cert_no text) INHERITS (public.rental);

The two tables cars.rental and boats.rental inherit all the columns from their parent public.rental:
 

rentaldb_hier=# d cars.rental
                           Table "cars.rental"
     Column     |         Type          | Collation | Nullable | Default
----------------+-----------------------+-----------+----------+---------
 id             | integer               |           | not null |
 customerid     | integer               |           | not null |
 vehicleno      | text                  |           |          |
 datestart      | date                  |           | not null |
 dateend        | date                  |           |          |
 driv_lic_no | text                  |           | not null |
Inherits: rental

rentaldb_hier=# d boats.rental
                         Table "boats.rental"
    Column    |         Type          | Collation | Nullable | Default
--------------+-----------------------+-----------+----------+---------
 id           | integer               |           | not null |
 customerid   | integer               |           | not null |
 vehicleno    | text                  |           |          |
 datestart    | date                  |           | not null |
 dateend      | date                  |           |          |
 sail_cert_no | text                  |           |          |
Inherits: rental

We notice that we omitted the company column in the definition of the parent table (and as a consequence in the children tables as well). This is no longer needed since the identification of the tenant is in the table’s full name! We will see later an easy way to find this out in queries. Now let’s insert some rows in the three tables (we borrow customers schema and data from the previous blog):

rentaldb_hier=# insert into rental (id, customerid, vehicleno, datestart) VALUES(1,1,'SOME ABSTRACT PLATE NO',current_date);
rentaldb_hier=# insert into cars.rental (id, customerid, vehicleno, datestart,driv_lic_no) VALUES(2,1,'INI 8888',current_date,'gr690131');
rentaldb_hier=# insert into boats.rental (id, customerid, vehicleno, datestart) VALUES(3,2,'INI 9999',current_date);

Now let’s see what’s in the tables:

rentaldb_hier=# select * from rental ;
 id | customerid |       vehicleno        | datestart  | dateend
----+------------+------------------------+------------+---------
  1 |          1 | SOME ABSTRACT PLATE NO | 2018-08-31 |
  2 |          1 | INI 8888               | 2018-08-31 |
  3 |          2 | INI 9999               | 2018-08-31 |
(3 rows)

rentaldb_hier=# select * from boats.rental ;
 id | customerid | vehicleno | datestart  | dateend | sail_cert_no
----+------------+-----------+------------+---------+--------------
  3 |          2 | INI 9999  | 2018-08-31 |         |
(1 row)

rentaldb_hier=# select * from cars.rental ;
 id | customerid | vehicleno | datestart  | dateend | driv_lic_no
----+------------+-----------+------------+---------+-------------
  2 |          1 | INI 8888  | 2018-08-31 |         | gr690131
(1 row)

So the same notions of inheritance that exist in Object Oriented languages (like Java) exist also in PostgreSQL! We can think of this as follows:
public.rental: superclass
cars.rental: subclass
boats.rental: subclass
row public.rental.id = 1: instance of public.rental
row cars.rental.id = 2: instance of cars.rental and public.rental
row boats.rental.id = 3: instance of boats.rental and public.rental

Since the rows of boats.rental and cars.rental are also instances of public.rental it is natural that they appear as rows of public.rental. If we want only rows exclusive of public.rental (in other words the rows inserted directly to public.rental) we do it using the ONLY keyword as follows:

rentaldb_hier=# select * from ONLY rental ;
 id | customerid |       vehicleno        | datestart  | dateend
----+------------+------------------------+------------+---------
  1 |          1 | SOME ABSTRACT PLATE NO | 2018-08-31 |
(1 row)

One difference between Java and PostgreSQL as far as inheritance goes is this: Java does not support multiple inheritance while PostgreSQL does, it is possible to inherit from more than one tables, so in this regard we may think of tables more like interfaces in Java.

If we want to find out the exact table in the hierarchy where a specific row belongs (the equivalent of obj.getClass().getName() in java) we can do by specifying the tableoid special column (oid of the respective table in pgclass), casted to regclass which gives the full table name:

rentaldb_hier=# select tableoid::regclass,* from rental ;
   tableoid   | id | customerid |       vehicleno        | datestart  | dateend
--------------+----+------------+------------------------+------------+---------
 rental       |  1 |          1 | SOME ABSTRACT PLATE NO | 2018-08-31 |
 cars.rental  |  2 |          1 | INI 8888               | 2018-08-31 |
 boats.rental |  3 |          2 | INI 9999               | 2018-08-31 |
(3 rows)

From the above (different tableoid) we can infer that the tables in the hierarchy are just plain old PostgreSQL tables, connected with an inheritance relationship. But besides this, they act pretty much like normal tables. And this will be further emphasized in the following section.

Important Facts & Caveats About PostgreSQL Inheritance

The child table inherits:

• NOT NULL constraints
• CHECK constraints

The child table does NOT inherit:

• PRIMARY KEY constraints
• UNIQUE constraints
• FOREIGN KEY constraints

When columns with the same name appear on the definition of more than one tables on the hierarchy, then those columns must have the same type and are merged into one single column. If a NOT NULL constraint exists for a column name anywhere in the hierarchy then this is inherited to the child table. CHECK constraints with the same name are also merged and must have the same condition.

Schema changes to the parent table (via ALTER TABLE) are propagated throughout the hierarchy that exists below this parent table. And this is one of the nice features of inheritance in PostgreSQL.

Security and Security Policies (RLS) are decided based on the actual table we use. If we use a parent table then those table’s Security and RLS will be used. It is implied that granting a privilege on the parent table gives permission to the child table(s) as well, but only when accessed via the parent table. In order to access the child table directly, then we must give explicit GRANT directly to the child table, the privilege on the parent table won’t suffice. The same holds true for RLS.

Regarding the firing of triggers, statement-level triggers depend on the named table of the statement, while row-level triggers will be fired depending on the table the actual row belongs to (so it could be a child table).

Things to watch out for:

• Most commands work on the whole hierarchy and support the ONLY notation. However, some low level commands (REINDEX, VACUUM, etc) work only on the physical tables named by the command. Be sure to read the documentation each time in case of doubt.
• FOREIGN KEY constraints (the parent table being on the referencing side) are not inherited. This is easily solved by specifying the same FK constraint in all children tables of the hierarchy.
• As of this point (PostgreSQL 10), there is no way to have global UNIQUE INDEX (PRIMARY KEYs or UNIQUE constraints) on a group of tables. As a result of this:
  • PRIMARY KEY and UNIQUE constraints are not inherited, and there is no easy way to enforce uniqueness on a column across all members of the hierarchy
  • When the parent table is on the referenced side of a FOREIGN KEY constraint, then the check is made only for the values of the column on rows genuinely (physically) belonging to the parent table, not any children tables.

The last limitation is a serious one. According to the official docs there is no good workaround for this. However, FK and uniqueness are fundamental for any serious database design. We will look into a way to deal with this.

Inheritance in Practice

In this section, we will convert a classic design with plain tables, PRIMARY KEY/UNIQUE and FOREIGN KEY constraints, to a multi-tenant design based on inheritance and we will try to solve the (expected as per the previous section) problems that we face. Let us consider the same rental business that we used as example in the previous blog and let’s imagine that at the beginning the business does only car rentals (no boats or other types of vehicles). Let’s consider the following schema, with the vehicles of the company and the service history on those vehicles:

create table vehicle (id SERIAL PRIMARY KEY, plate_no text NOT NULL, maker TEXT NOT NULL, model TEXT NOT NULL,vin text not null);
create table vehicle_service(id SERIAL PRIMARY KEY, vehicleid INT NOT NULL REFERENCES vehicle(id), service TEXT NOT NULL, date_performed DATE NOT NULL DEFAULT now(), cost real not null);
rentaldb=# insert into vehicle (plate_no,maker,model,vin) VALUES ('INI888','Hyundai','i20','HH999');
rentaldb=# insert into vehicle_service (vehicleid,service,cost) VALUES(1,'engine oil change/filters',50);

Now let’s imagine the system is in production, and then the company acquires a second company which does boat rentals and has to integrate those into the system, by having the two companies operating independently as far as the operation goes, but in a unified manner for use by the top mgmt. Also, let’s imagine that the vehicle_service data must not be split since all rows must be visible to both companies. So what we are looking for is provide a multi-tenancy solution based on inheritance on the vehicle table. First, we should create a new schema for cars, (the old business), and one for boats and then migrate existing data to cars.vehicle:

rentaldb=# create schema cars;
rentaldb=# create table cars.vehicle (CONSTRAINT vehicle_pkey PRIMARY KEY(id) ) INHERITS (public.vehicle);
rentaldb=# d cars.vehicle
                              Table "cars.vehicle"
  Column  |  Type   | Collation | Nullable |               Default               
----------+---------+-----------+----------+-------------------------------------
 id       | integer |           | not null | nextval('vehicle_id_seq'::regclass)
 plate_no | text    |           | not null |
 maker    | text    |           | not null |
 model    | text    |           | not null |
 vin      | text    |           | not null |
Indexes:
    "vehicle_pkey" PRIMARY KEY, btree (id)
Inherits: vehicle

rentaldb=# create schema boats;
rentaldb=# create table boats.vehicle (CONSTRAINT vehicle_pkey PRIMARY KEY(id) ) INHERITS (public.vehicle);
rentaldb=# d boats.vehicle
                              Table "boats.vehicle"
  Column  |  Type   | Collation | Nullable |               Default               
----------+---------+-----------+----------+-------------------------------------
 id       | integer |           | not null | nextval('vehicle_id_seq'::regclass)
 plate_no | text    |           | not null |
 maker    | text    |           | not null |
 model    | text    |           | not null |
 vin      | text    |           | not null |
Indexes:
    "vehicle_pkey" PRIMARY KEY, btree (id)
Inherits: vehicle

We note that the new tables shares the same default value for column id (same sequence) as the parent table. While this is far from a solution to the global uniqueness problem explained in the previous section, it is a work around, provided no explicit value will be ever used for inserts or updates. If all children tables (cars.vehicle and boats.vehicle) are defined as above, and we never explicit manipulate id, then we’ll be safe.

Since we’ll keep only the public vehicle_service table and this will reference rows of children tables, we must drop the FK constraint:

rentaldb=# alter table vehicle_service drop CONSTRAINT vehicle_service_vehicleid_fkey ;

But because we need to maintain the equivalent consistency in our database we must find a work around for this. We will implement this constraint using triggers. We need to add a trigger to vehicle_service which checks that for every INSERT or UPDATE the vehicleid points to a valid row somewhere in the public.vehicle* hierarchy, and one trigger on each of the tables of this hierarchy which checks that for every DELETE or UPDATE on id, no row in vehicle_service exists that points to the old value. (note by the vehicle* notation PostgreSQL implies this and all children tables)

CREATE OR REPLACE FUNCTION public.vehicle_service_fk_to_vehicle() RETURNS TRIGGER
        LANGUAGE plpgsql
AS $$
DECLARE
tmp INTEGER;
BEGIN
        IF (TG_OP = 'DELETE') THEN
          RAISE EXCEPTION 'TRIGGER : % called on unsuported op : %',TG_NAME, TG_OP;
        END IF;
        SELECT vh.id INTO tmp FROM public.vehicle vh WHERE vh.id=NEW.vehicleid;
        IF NOT FOUND THEN
          RAISE EXCEPTION '%''d % (id=%) with NEW.vehicleid (%) does not match any vehicle ',TG_OP, TG_TABLE_NAME, NEW.id, NEW.vehicleid USING ERRCODE = 'foreign_key_violation';
        END IF;
        RETURN NEW;
END
$$
;

CREATE CONSTRAINT TRIGGER vehicle_service_fk_to_vehicle_tg AFTER INSERT OR UPDATE ON public.vehicle_service FROM public.vehicle DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE public.vehicle_service_fk_to_vehicle();

If we try to update or insert with a value for column vehicleid that does not exist in vehicle* then we’ll get an error:

rentaldb=# insert into vehicle_service (vehicleid,service,cost) VALUES(2,'engine oil change/filters',50);
ERROR:  INSERT'd vehicle_service (id=2) with NEW.vehicleid (2) does not match any vehicle
CONTEXT:  PL/pgSQL function vehicle_service_fk_to_vehicle() line 10 at RAISE

Now if we insert a row in any table in the hierarchy e.g. boats.vehicle (which normally will take id=2) and retry:

rentaldb=# insert into boats.vehicle (maker, model,plate_no,vin) VALUES('Zodiac','xx','INI000','ZZ20011');
rentaldb=# select * from vehicle;
 id | plate_no |  maker  | model |   vin   
----+----------+---------+-------+---------
  1 | INI888   | Hyundai | i20   | HH999
  2 | INI000   | Zodiac  | xx    | ZZ20011
(2 rows)

rentaldb=# insert into vehicle_service (vehicleid,service,cost) VALUES(2,'engine oil change/filters',50);

Then the previous INSERT now succeeds. Now we should also protect this FK relationship on the other side, we must make sure that no update/deletion is allowed on any table in the hierarchy if the row to be deleted (or updated) is referenced by vehicle_service:

CREATE OR REPLACE FUNCTION public.vehicle_fk_from_vehicle_service() RETURNS TRIGGER
        LANGUAGE plpgsql
AS $$
DECLARE
tmp INTEGER;
BEGIN
        IF (TG_OP = 'INSERT') THEN
          RAISE EXCEPTION 'TRIGGER : % called on unsuported op : %',TG_NAME, TG_OP;
        END IF;
        IF (TG_OP = 'DELETE' OR OLD.id <> NEW.id) THEN
          SELECT vhs.id INTO tmp FROM vehicle_service vhs WHERE vhs.vehicleid=OLD.id;
          IF FOUND THEN
            RAISE EXCEPTION '%''d % (OLD id=%) matches existing vehicle_service with id=%',TG_OP, TG_TABLE_NAME, OLD.id,tmp USING ERRCODE = 'foreign_key_violation';
          END IF;
        END IF;
        IF (TG_OP = 'UPDATE') THEN
                RETURN NEW;
        ELSE
                RETURN OLD;
        END IF;
END
$$
;

CREATE CONSTRAINT TRIGGER vehicle_fk_from_vehicle_service AFTER DELETE OR UPDATE
ON public.vehicle FROM vehicle_service DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE vehicle_fk_from_vehicle_service();

CREATE CONSTRAINT TRIGGER vehicle_fk_from_vehicle_service AFTER DELETE OR UPDATE
ON cars.vehicle FROM vehicle_service DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE vehicle_fk_from_vehicle_service();

CREATE CONSTRAINT TRIGGER vehicle_fk_from_vehicle_service AFTER DELETE OR UPDATE
ON boats.vehicle FROM vehicle_service DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE vehicle_fk_from_vehicle_service();

Let us try it:

rentaldb=# delete from vehicle where id=2;
ERROR:  DELETE'd vehicle (OLD id=2) matches existing vehicle_service with id=3
CONTEXT:  PL/pgSQL function vehicle_fk_from_vehicle_service() line 11 at RAISE

Now we need to move the existing data in public.vehicle to cars.vehicle.

rentaldb=# begin ;
rentaldb=# set constraints ALL deferred ;
rentaldb=# set session_replication_role TO replica;
rentaldb=# insert into cars.vehicle select * from only public.vehicle;
rentaldb=# delete from only public.vehicle;
rentaldb=# commit ;

Setting session_replication_role TO replica prevents the firing of normal triggers. Note that, after moving the data, we may want to completely disable the parent table (public.vehicle) of accepting inserts (most probably via a rule). In this case, in the OO analogy, we would treat public.vehicle as an abstract class, i.e. without rows (instances). Using this design for multi-tenancy feels natural because the problem to be solved is a classic use case for inheritance, however, the problems we faced are not trivial. This has been discussed by the hackers community, and we hope for future improvements.