Postgres INHERITS and concrete table inheritance

[Martin Aspeli]

Hi,

I'm a little unclear about the relationship between SQLAlchemy
inheritance (in particular with the declarative extension) and the
INHERITS keyword in Postgres.

In Postgres, we have this (simplified for the purposes of his mail):

CREATE TABLE owner (
id integer NOT NULL,
name character varying
);

CREATE TABLE vehicle (
id integer NOT NULL,
owner_id integer,
price integer
);

CREATE TABLE vehicle_car (
id integer DEFAULT NULL,
owner_id integer,
price integer,
fuel_type integer,
)
INHERITS (vehicle);

CREATE TABLE vehicle_bus (
id integer DEFAULT NULL,
owner_id integer,
price integer,
passengers integer,
)
INHERITS (vehicle);

CREATE SEQUENCE vehicle_id_seq
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;

ALTER TABLE vehicle ALTER COLUMN id SET DEFAULT
nextval('vehicle_id_seq'::regclass);

Here, owner_id is meant to be a foreign key to owner.id, as well

Now, in Postgres, the INHERITS keyword means that:

- I can insert into vehicle directly, in which case the row will
appear in that table only

- I can insert into one of the sub-tables, vehicle_car or vehicle_bus,
in which case the row will appear both vehicle and the sub-table I
inserted into (unless I use FROM ONLY in the query)

I also *think* that repeating id, owner_id and price in the sub-tables
is unnecessary.

I'd like to map this to SQLAlchemy using inheritance, and I think the
correct thing to use is "concrete" inheritance. Here's what we've tried:

class Owner(Base):
__tablename__ = 'owner'

id = schema.Column(types.Integer(),
primary_key=True,
autoincrement=True)
name = schema.Column(types.String(64), nullable=False)

class Vehicle(Base):
__tablename__ = 'vehicle'

id = schema.Column(types.Integer(),
primary_key=True,
autoincrement=True)
owner_id = schema.Column(types.Integer(),
schema.ForeignKey('owner.id'),
nullable=False)
owner = orm.relation(Owner,
primaryjoin=Owner.id==owner_id, backref="vehicles")
price = schema.Column(types.Integer(), nullable=False)

class VehicleCar(Vehicle):
__tablename__ = 'vehicle_car'
__mapper_args__ = {'concrete':True }

id = schema.Column(types.Integer(),
primary_key=True,
autoincrement=True)
owner_id = schema.Column(types.Integer(),
schema.ForeignKey('owner.id'),
nullable=False)
owner = orm.relation(Owner,
primaryjoin=Owner.id==owner_id, backref="vehicles")
price = schema.Column(types.Integer(), nullable=False)

fuel_type = schema.Column(types.Integer())

class VehicleBus(Vehicle):
__tablename__ = 'vehicle_bus'
__mapper_args__ = {'concrete':True }

id = schema.Column(types.Integer(),
primary_key=True,
autoincrement=True)
owner_id = schema.Column(types.Integer(),
schema.ForeignKey('owner.id'),
nullable=False)
owner = orm.relation(Owner,
primaryjoin=Owner.id==owner_id, backref="vehicles")
price = schema.Column(types.Integer(), nullable=False)

fuel_type = schema.Column(types.Integer())

This kind of works, but there are a few problems:

- The 'owners' variable on the Game type only contains Vehicle
objects. I'd like it to contain the correct sub-class if possible.

- I've had to repeat all the fields from the base class in the
sub-classes. Otherwise, I'd get errors using those attributes, even
though VehicleCar and VehicleBus both inherits form Vehicle.

- Setting a 'backref' on the relation() on VehicleCar and VehicleBus
results in an error (the Owner object already has an 'owners' field)

I feel like I may've missed something here, though. Any suggestions on
how to do this better?

Cheers,
Martin

--
Author of `Professional Plone Development`, a book for developers who
want to work with Plone. See http://martinaspeli.net/plone-book

[Michael Bayer]

Martin Aspeli wrote:
> Hi,

>
> This kind of works, but there are a few problems:
>
> - The 'owners' variable on the Game type only contains Vehicle
> objects. I'd like it to contain the correct sub-class if possible.

When a row is received SQLAlchemy would need to know what type that row
is, in order to dispatch to the correct class. SQLA currently uses a
discriminator column for that purpose, so you'd have to find some way to
have a column in the result set (or an expression) which can be used in
this way.

>
> - I've had to repeat all the fields from the base class in the
> sub-classes. Otherwise, I'd get errors using those attributes, even
> though VehicleCar and VehicleBus both inherits form Vehicle.

Well SQLA doesn't have any direct support for PG INHERITS, and the fact is
that concrete inherits means that each Table repeats each common column
specifically - one reason why concrete inheritance is widely considered to
be the most cumbersome form of relational inheritance. There was a trac
ticket requesting that the columns "inherit" the way they do with a
simpler single- or joined- table setup, but at the end of the day that
request was asking for some very complex magic to occur. Your database
expresses distinct columns at the public DDL level, even though INHERITS
means theyre the "same", so SQLA keeps it simple and would like you to
express them in the same way as what it will see when talking to the DB.

>
> - Setting a 'backref' on the relation() on VehicleCar and VehicleBus
> results in an error (the Owner object already has an 'owners' field)

there is documentation on how to address concrete backrefs, using the
"back_populates" keyword:
http://www.sqlalchemy.org/docs/05/mappers.html#using-relations-with-inheritance

>
> I feel like I may've missed something here, though. Any suggestions on
> how to do this better?

unfortunately we haven't attempted to smoothly integrate with PG's
INHERITS. It may or may not require additional complexity and would
provide a feature that would not work on any of the other half dozen
databases we support. My understanding is that INHERITS is usually used
in practice to provide transparent "sharding" of table data and not
necessarily to express class hierarchies, but this is strictly anecdotal
knowledge. I'm actually encouraged that you've gotten it to work
somewhat reasonably.

[Martin Aspeli]

Hi Michael,

Thanks for the quick reply!

Michael Bayer wrote:
> Martin Aspeli wrote:
>> Hi,
>>
>> This kind of works, but there are a few problems:
>>
>> - The 'owners' variable on the Game type only contains Vehicle
>> objects. I'd like it to contain the correct sub-class if possible.
>
> When a row is received SQLAlchemy would need to know what type that row
> is, in order to dispatch to the correct class. SQLA currently uses a
> discriminator column for that purpose, so you'd have to find some way to
> have a column in the result set (or an expression) which can be used in
> this way.

How would the mapper configuration look if it were an expression? If it
were a column?

>> - I've had to repeat all the fields from the base class in the
>> sub-classes. Otherwise, I'd get errors using those attributes, even
>> though VehicleCar and VehicleBus both inherits form Vehicle.
>
> Well SQLA doesn't have any direct support for PG INHERITS, and the fact is
> that concrete inherits means that each Table repeats each common column
> specifically - one reason why concrete inheritance is widely considered to
> be the most cumbersome form of relational inheritance. There was a trac
> ticket requesting that the columns "inherit" the way they do with a
> simpler single- or joined- table setup, but at the end of the day that
> request was asking for some very complex magic to occur. Your database
> expresses distinct columns at the public DDL level, even though INHERITS
> means theyre the "same", so SQLA keeps it simple and would like you to
> express them in the same way as what it will see when talking to the DB.

True, except I think it's superflos. I could do this in Postgres too:

CREATE TABLE vehicle (
id integer NOT NULL,
owner_id integer,
price integer
);

CREATE TABLE vehicle_car (

fuel_type integer

)
INHERITS (vehicle);

CREATE TABLE vehicle_bus (

passengers integer
)
INHERITS (vehicle);

vehicle_car and vehicle_bus now implicitly get columns id, owner_id and
price from the base table, as their first columns.

>> - Setting a 'backref' on the relation() on VehicleCar and VehicleBus
>> results in an error (the Owner object already has an 'owners' field)
>
> there is documentation on how to address concrete backrefs, using the
> "back_populates" keyword:
> http://www.sqlalchemy.org/docs/05/mappers.html#using-relations-with-inheritance

Thanks for the pointer! I think I need to read that a few more times. :)

>> I feel like I may've missed something here, though. Any suggestions on
>> how to do this better?
>
> unfortunately we haven't attempted to smoothly integrate with PG's
> INHERITS. It may or may not require additional complexity and would
> provide a feature that would not work on any of the other half dozen
> databases we support. My understanding is that INHERITS is usually used
> in practice to provide transparent "sharding" of table data and not
> necessarily to express class hierarchies, but this is strictly anecdotal
> knowledge. I'm actually encouraged that you've gotten it to work
> somewhat reasonably.

Maybe just by luck? :)

We've inherited this schema. I had to look up the keyword in the
Postgres documentation, as I'd never heard of it before. It's kind of
neat, and has some uses in our application, but it's also fairly
esoteric, I guess.

[Michael Bayer]

Martin Aspeli wrote:
>
> How would the mapper configuration look if it were an expression? If it
> were a column?

this is the `polymorphic_on` attribute. It generally expects to point to
a column. Its not yet very flexible for expressions which is a TODO.
an actual column in your base table (and each table which you'd like to do
polymorphic queries) would accomplish the desired result.

>
>>> - I've had to repeat all the fields from the base class in the
>>> sub-classes. Otherwise, I'd get errors using those attributes, even
>>> though VehicleCar and VehicleBus both inherits form Vehicle.
>>
>> Well SQLA doesn't have any direct support for PG INHERITS, and the fact
>> is
>> that concrete inherits means that each Table repeats each common column
>> specifically - one reason why concrete inheritance is widely considered
>> to
>> be the most cumbersome form of relational inheritance. There was a trac
>> ticket requesting that the columns "inherit" the way they do with a
>> simpler single- or joined- table setup, but at the end of the day that
>> request was asking for some very complex magic to occur. Your database
>> expresses distinct columns at the public DDL level, even though INHERITS
>> means theyre the "same", so SQLA keeps it simple and would like you to
>> express them in the same way as what it will see when talking to the DB.
>
> True, except I think it's superflos. I could do this in Postgres too:

its superfluous except that SQLA wants to keep things simple and not guess
things about your schema. theres a request which I closed here:
http://www.sqlalchemy.org/trac/ticket/1469

[Alex Brasetvik]

On Dec 15, 2009, at 17:59 , Michael Bayer wrote:

> My understanding is that INHERITS is usually used
> in practice to provide transparent "sharding" of table data and not
> necessarily to express class hierarchies, but this is strictly anecdotal
> knowledge.

Agreed.

The PostgreSQL documentation[1] mentions many caveats with INHERITS, most notably:

"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. This is true on both the referencing and referenced sides of a foreign key constraint."

For those reasons it is also actively *discouraged* for expressing class hierarchies.

To put that into context, consider the following:

CREATE TABLE vehicle (
id integer NOT NULL,
owner_id integer,

price integer,
primary key (id) -- I added this

);

CREATE TABLE vehicle_bus (
passengers integer

)
INHERITS (vehicle);

CREATE TABLE vehicle_whatever(
id integer primary key references vehicle(id),
whatever text
);

INSERT INTO vehicle_bus VALUES (1, 1, 42, 123);

SELECT * FROM vehicle;
id | owner_id | price
----+----------+-------
1 | 1 | 42
(1 row)

SELECT * FROM vehicle_bus;
id | owner_id | price | passengers
----+----------+-------+------------
1 | 1 | 42 | 123
(1 row)

INSERT INTO vehicle_whatever VALUES (1, 'but clearly this is in vehicle, no?');
ERROR: insert or update on table "vehicle_whatever" violates foreign key constraint "vehicle_whatever_id_fkey"
DETAIL: Key (id)=(1) is not present in table "vehicle".

~

[1] http://www.postgresql.org/docs/8.4/static/ddl-inherit.html

--
Alex Brasetvik