Re: [sqlalchemy] Concrete Inheritance With Postgres Inheritance

[Michael Bayer]

On Jun 29, 2012, at 8:21 AM, Barry Steyn wrote:

Hi All

Firstly, I know that SA does not support Postgres Inheritance "out of the box". But the concepts for concrete inheritance should allow some support, at least for what I am trying to do. I am new to SA and Python (about a month of experience here) and so I don't know if I am barking up the wrong tree. Here is what I think...

Each table has a unique "tableoid" column in Postgres. So lets assume this table structure:

CREATE TABLE A (
  id SERIAL Primary Key,
  name text
)

CREATE TABLE B (
 id SERIAL Primary Key,
 name text,
 language text
) INHERITS(A)

Table B uses the postgres Inherits. Now lets assume that Postgres has given table A an oid of 1, and table B an oid of 2. If I do...

SELECT A.tableoid, * FROM A

Then the rows that are from table B will have an oid of 2, while those from A will have an oid of 1. So this mimics a polymorphism in a way and I can use this OID as a discriminator.

Here is my question (finally): How can I accomplish this in SA using concrete inheritance and polymorphism. What I want is that when querying table A, a polymorphic call will load up the class representing B in the appropriate cases. Here follows EXACTLY what I am trying to do (and the code will follow after):

• There are three postgres tables in play: (1) A Users table, (2) An Authentications table (3) A Password_Authentications table
• Users has a foreignkey into Authorisations
• Password_Authentications is inherited from Authentications

what we have for PG inheritance is here:

http://www.sqlalchemy.org/trac/wiki/UsageRecipes/PostgreSQLInheritance

as you'll see there, we're not using concrete inheritance, we're using "single table" inheritance.   This because PG's INHERIT feature is designed to produce a single table interface that reads/writes distinct inheriting tables transparently.    The recipe there is derived from PG's documentation on how the feature is intended to be used.

[Barry Steyn]

Hi Michael

I did read that recipe before I posted to this group. Unfortunately, single table inheritance does not provide much advantage if the child table has additional columns to the parent - with single table inheritance, one cannot get to those additional child columns.

After reading up on concrete table inheritance, I am under the impression that the only thing one needs to implement this is a extinguisher.  I could be totally incorrect here. So the question is: Can I accomplish polymorphic inheritance of postgres tables using the tableoid as a polymorphic_identity?

[Michael Bayer]

On Jun 29, 2012, at 12:53 PM, Barry Steyn wrote:

> Hi Michael
>
> I did read that recipe before I posted to this group. Unfortunately, single table inheritance does not provide much advantage if the child table has additional columns to the parent - with single table inheritance, one cannot get to those additional child columns.
>
> After reading up on concrete table inheritance, I am under the impression that the only thing one needs to implement this is a extinguisher. I could be totally incorrect here. So the question is: Can I accomplish polymorphic inheritance of postgres tables using the tableoid as a polymorphic_identity?

I'm not sure what you mean by "extinguisher" here. As far as OID, SQLAlchemy would require that "oid" be present in the list of columns being selected, and that's all it needs. So if adding Column("oid", Integer) produces "SELECT .. oid ..." and the value comes back, it can be used as a discriminator. The discriminator can also be a SQL expression or function if that's what it needs to be.

I haven't worked with PG oids in many years since they've been deprecated (see http://www.postgresql.org/docs/8.4/interactive/runtime-config-compatible.html#GUC-DEFAULT-WITH-OIDS: "The use of OIDs in user tables is considered deprecated") and are disabled by default.

[Barry Steyn | Twayd]

Hi Michael

Sorry, I meant distuingisher in my last response (not extuigisher). The thing is, these are nore oids, they are tableoids. The difference (I think) is that oids are row based, and tableoids are distinct for the table. So every table has a unique tableoid. This is what I have been using. When I query the parent, I keep on getting this reply for the child table:

sqlalchemy.exc.NoSuchColumnError: "Could not locate column in row for column 'banker.id'"

The setup is that Banker inhertits from Person. They both have an id as primary key, and a name as a string, and the tableoid. Querying the tables individually works perfectly along with the tableoid.

From what I have read, this should really work. But I don't know why I am getting that error above. I think this could solve the issue. Check out tableoid, in the inheritance documentation, it says this: In some cases you might wish to know which table a particular row originated from. There is a system column called tableoid in each table which can tell you the originating table:

As a fun aside: If you have postgres running, then do select A.tableoid from A, and it will pop up, even though not explicitly specified on the schema.

Michael, thank you for the great product. I don't know if I will ever get a chance to thank you personally...

Barry

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

[Michael Bayer]

On Jun 29, 2012, at 2:17 PM, Barry Steyn | Twayd wrote:

Hi Michael

Sorry, I meant distuingisher in my last response (not extuigisher). The thing is, these are nore oids, they are tableoids. The difference (I think) is that oids are row based, and tableoids are distinct for the table. So every table has a unique tableoid. This is what I have been using. When I query the parent, I keep on getting this reply for the child table:

sqlalchemy.exc.NoSuchColumnError: "Could not locate column in row for column 'banker.id'"

The setup is that Banker inhertits from Person. They both have an id as primary key, and a name as a string, and the tableoid. Querying the tables individually works perfectly along with the tableoid.

From what I have read, this should really work. But I don't know why I am getting that error above. I think this could solve the issue. Check out tableoid, in the inheritance documentation, it says this: In some cases you might wish to know which table a particular row originated from. There is a system column called tableoid in each table which can tell you the originating table:

SQLAlchemy has no additional opinions about this column that wouldn't exist at those levels.  If the column is truly present as "select a.tableoid from a", and acts just the same way, adding Column("tableoid") is all that's needed.

The specific error you have seems to indicate a different column entirely.     I know that you'd like additional columns to be present on your "child" table, but if the SQL here is in expressed lexically in terms of a single, base table name, SQLAlchemy is going to need to see things in that way, that is, if Banker is your subclass, if you've got it on the "banker.id" column and the result row only has "parent.id", it's not going to work.   The polymorphic discriminator transfers control to the subclass mapper which then expects to see the columns it knows about.   The mappers generate a naming scheme (such as <tablename>_<columnname> in the simple case) so that it knows how to target its specific columns when they come back in cursor.description.  A concrete subclass mapper is going to look for column names that don't exist in the SQL.

The best you can do here is stick with the single-table model and perhaps try to add those extra columns all on the base, and maybe use group-deferral to not load them in by default.  There could be other hacks to get it to work but this would require a significant amount of experimentation and possibly changes to the ORM.

Modeling around PG's inherits feature right now is simply not a supported feature of SQLAlchemy, it would likely take a considerable amount of effort to implement, and while I can only find some comments on message boards to this effect, the general consensus I've heard up until now (though only anecdotally, sorry) is that PG's table inheritance is not really used in the real world for data modeling, only for data partitioning (see: http://bytes.com/topic/postgresql/answers/423188-any-reason-not-use-inheritance for one such anecdote).

On the other hand, if you just use a traditional joined inheritance model, you get the benefits of the years of development that have gone into perfecting this database-agnostic model (with more enhancements coming), it works great and at least hundreds of people including myself use it regularly with great success.

Michael, thank you for the great product. I don't know if I will ever get a chance to thank you personally...

thanks !