Postgres, inheritance and SELECT * FROM ONLY ...

4,581 views
Skip to first unread message

Jason Dusek

unread,
Oct 10, 2013, 1:42:49 AM10/10/13
to jooq...@googlegroups.com
When a Postgres table has other tables that inherit from, a SELECT against the parent will show rows in all the children, as well. The antidote to this is the ONLY keyword:

  SELECT * FROM ONLY <name-of-parent-table>

Is inheritance recognized by jOOQ? Are there options for making ONLY the default for different models/tables?

Lukas Eder

unread,
Oct 11, 2013, 1:55:09 PM10/11/13
to jooq...@googlegroups.com
Hello Jason,

2013/10/10 Jason Dusek <jason...@gmail.com>

When a Postgres table has other tables that inherit from, a SELECT against the parent will show rows in all the children, as well. The antidote to this is the ONLY keyword:

  SELECT * FROM ONLY <name-of-parent-table>

Is inheritance recognized by jOOQ?

Inheritance is an interesting PostgreSQL (and CUBRID) feature. But it is currently not recognised by jOOQ, although it was discussed a couple of times:

 
Are there options for making ONLY the default for different models/tables?

Right now, you will have to resort to tricks similar to what was discussed recently on this user group, with respect to Oracle's "partition extension clause":

In other words, you'll have to wrap your tables in order for them to generate the ONLY keyword. Another option, is to use plain SQL for that particular table, such as:

DSL.using(configuration)
   .select(...)
   .from("ONLY {0}", PARENT_TABLE);
 

For jOOQ 3.3, I have registered #2776:

I'll also review how PostgreSQL table inheritance can play into the code generator. I would imagine that it could map quite well to Java inheritance for generated tables, records, pojos, and daos:

Cheers
Lukas

Lukas Eder

unread,
Oct 15, 2013, 9:36:56 AM10/15/13
to jooq...@googlegroups.com
I'm currently looking into supporting PostgreSQL's table inheritance in jOOQ's code generator. PostgreSQL also supports multiple inheritance, e.g:

CREATE TABLE t_inheritance_all (
  text_1_all text
) INHERITS (t_inheritance_1_1, t_inheritance_1_2)

This cannot be mapped to Java very easily. I would say that such relationships would not be reflected in the generated code, and a warning would be issued by the code generator.

Does anyone on this list have any experience with multiple inheritance in PostgreSQL?

Cheers
Lukas


2013/10/10 Jason Dusek <jason...@gmail.com>
When a Postgres table has other tables that inherit from, a SELECT against the parent will show rows in all the children, as well. The antidote to this is the ONLY keyword:

  SELECT * FROM ONLY <name-of-parent-table>

Is inheritance recognized by jOOQ? Are there options for making ONLY the default for different models/tables?

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

Peter Cooner

unread,
Oct 15, 2013, 10:42:23 AM10/15/13
to jooq...@googlegroups.com
Honestly PG inheritance was pretty bad in 8.X, and only started to rock in 9.X, I'm thinking there isn't a lot of real multiple inheritance in use out there (that doesn't mean there won't be of course).

Anyway, I'm wondering if maybe some of those mapping functions in JOOQ couldn't be the best solution? My brain is fuzzy on the topic, but aren't the values are kept in a store, might there be a "fast" way to map between one table type and another? Maybe some sort of internal JOOQ table hinting would allow for quicker mapping, or re-typing as the case might be...

Just spit balling.


--
Pete
"Yates went on to say that using MS formats left a fresh, minty feeing in user's mouths while every time an open [format] is used a kitten dies." -- maggard (on slashdot)

perl -lne '(1x$_) !~ /^1?$|^(11+?)\1+$/ && print "$_ is prime"'

Lukas Eder

unread,
Oct 16, 2013, 3:17:40 AM10/16/13
to jooq...@googlegroups.com


2013/10/15 Peter Cooner <petr...@gmail.com>

Honestly PG inheritance was pretty bad in 8.X, and only started to rock in 9.X, I'm thinking there isn't a lot of real multiple inheritance in use out there (that doesn't mean there won't be of course).

Well, a good use case that I could see is to define something mixin types for very common attribute sets, such as DATE_CREATED, DATE_MODIFIED, USER_CREATED, USER_MODIFIED. Such attributes could be declared in a base table / type and then inherited by all relevant tables, which can also inherit from other structures. I'm not particularly experienced with such an ORDBMS way of thinking, but it would certainly be interesting to experiment with that.

Anyway, I'm wondering if maybe some of those mapping functions in JOOQ couldn't be the best solution? My brain is fuzzy on the topic, but aren't the values are kept in a store, might there be a "fast" way to map between one table type and another? Maybe some sort of internal JOOQ table hinting would allow for quicker mapping, or re-typing as the case might be...

What do you mean by "those mapping functions" ?

Lukas Eder

unread,
Oct 16, 2013, 8:33:00 AM10/16/13
to jooq...@googlegroups.com
... as a follow up on these experiments:

The way jOOQ currently generates tables, it's quite hard to implement table inheritance within the generated artefacts. The reason for this is that org.jooq.Table is parameterised with <R extends Record>. Correlating the table hierarchy with the generic parameter's record hierarchy is hard. The reasons for this are explained here:

Essentially, it means that parent tables would have to become generic themselves, for their child tables to be able to extend the generic type parameter, which might bring a lot of inconsistency into the type hierarchy. I'll have to think through this again, but it might not be possible without breaking backwards-compatibility to map PostgreSQL table inheritance to jOOQ table inheritance.

Cheers
Lukas


2013/10/15 Lukas Eder <lukas...@gmail.com>

Peter Cooner

unread,
Oct 16, 2013, 8:50:54 AM10/16/13
to jooq...@googlegroups.com
On Wed, Oct 16, 2013 at 3:17 AM, Lukas Eder <lukas...@gmail.com> wrote:


2013/10/15 Peter Cooner <petr...@gmail.com>
Honestly PG inheritance was pretty bad in 8.X, and only started to rock in 9.X, I'm thinking there isn't a lot of real multiple inheritance in use out there (that doesn't mean there won't be of course).

Well, a good use case that I could see is to define something mixin types for very common attribute sets, such as DATE_CREATED, DATE_MODIFIED, USER_CREATED, USER_MODIFIED. Such attributes could be declared in a base table / type and then inherited by all relevant tables, which can also inherit from other structures. I'm not particularly experienced with such an ORDBMS way of thinking, but it would certainly be interesting to experiment with that.


Hah. Yesterday I accidentally a word. I meant to write "Honestly PG inheritance performance was pretty bad in 8.X". Sorry about that.

I think you've made an excellent example.

And when I was working in JOOQ on my own inherited tables, I really wanted to be able to to do things such as:

Result<BaseRecord> results;
results = create.selectFrom(Tables.CHILD_TABLE)....

or

BaseRecord br = .... ChildTable ...

But I got over all that pretty quickly because I realized that usually what I really needed to do was to push data (or type) from ChildRecord into a BaseRecord or from ChildA into ChildB which had the same columns.

Besides that the table classes generated already extend a class so I think a different solution is in order.

Which brings me back to mapping...



Anyway, I'm wondering if maybe some of those mapping functions in JOOQ couldn't be the best solution? My brain is fuzzy on the topic, but aren't the values are kept in a store, might there be a "fast" way to map between one table type and another? Maybe some sort of internal JOOQ table hinting would allow for quicker mapping, or re-typing as the case might be...

What do you mean by "those mapping functions" ?

Ah yes, I meant on a result set there are the map() and into(Table<R>) functions which map data from one to another.

I guess the point of all this was to say that I can't see a good way for JOOQ to do this within the Java type system, so maybe a good helper is more useful then a perfect type-safe solution?

Pete

Lukas Eder

unread,
Oct 17, 2013, 4:49:18 AM10/17/13
to jooq...@googlegroups.com


2013/10/16 Peter Cooner <petr...@gmail.com>


On Wed, Oct 16, 2013 at 3:17 AM, Lukas Eder <lukas...@gmail.com> wrote:


2013/10/15 Peter Cooner <petr...@gmail.com>
Honestly PG inheritance was pretty bad in 8.X, and only started to rock in 9.X, I'm thinking there isn't a lot of real multiple inheritance in use out there (that doesn't mean there won't be of course).

Well, a good use case that I could see is to define something mixin types for very common attribute sets, such as DATE_CREATED, DATE_MODIFIED, USER_CREATED, USER_MODIFIED. Such attributes could be declared in a base table / type and then inherited by all relevant tables, which can also inherit from other structures. I'm not particularly experienced with such an ORDBMS way of thinking, but it would certainly be interesting to experiment with that.


Hah. Yesterday I accidentally a word. I meant to write "Honestly PG inheritance performance was pretty bad in 8.X". Sorry about that.

Between the lines, I was actually reading precisely that :-)
 
I think you've made an excellent example.

And when I was working in JOOQ on my own inherited tables, I really wanted to be able to to do things such as:

Result<BaseRecord> results;
results = create.selectFrom(Tables.CHILD_TABLE)....

or

BaseRecord br = .... ChildTable ...

But I got over all that pretty quickly because I realized that usually what I really needed to do was to push data (or type) from ChildRecord into a BaseRecord or from ChildA into ChildB which had the same columns.

Besides that the table classes generated already extend a class so I think a different solution is in order.

Which brings me back to mapping...



Anyway, I'm wondering if maybe some of those mapping functions in JOOQ couldn't be the best solution? My brain is fuzzy on the topic, but aren't the values are kept in a store, might there be a "fast" way to map between one table type and another? Maybe some sort of internal JOOQ table hinting would allow for quicker mapping, or re-typing as the case might be...

What do you mean by "those mapping functions" ?

Ah yes, I meant on a result set there are the map() and into(Table<R>) functions which map data from one to another.

I guess the point of all this was to say that I can't see a good way for JOOQ to do this within the Java type system, so maybe a good helper is more useful then a perfect type-safe solution?

Hmm, you mean like generating "helper" methods such as:

public class ParentTableRecord {
    public ChildTableRecord intoChildTable() {
        return into(CHILD_TABLE);
    }
}

public class ChildTableRecord {
    public ParentTableRecord intoParentTable() {
        return into(PARENT_TABLE);
    }
}

That would probably be quite useful... I've registered #2782 for this.

Let me know if you see other useful additions.
Reply all
Reply to author
Forward
0 new messages