Re: DAO for table with inheritance (postgres), table is no UpdateableRecord
Am Do., 12. Juli 2018 um 09:56 Uhr schrieb : > The actual isse was that the derived table records would not implement > UpdateableRecord and thus could not be used as the type in a class derived > from DaoImpl. > Oh, I see now. Well, the problem is, the primary key is a lie :-) Check this out: CREATE TABLE a ( id int CONSTRAINT pk_a PRIMARY KEY (id) ); CREATE TABLE b ( x text ) INHERITS (a); INSERT INTO a (id) VALUES (1); INSERT INTO a (id) VALUES (1); -- Constraint violation INSERT INTO x.b (id) VALUES (1); -- No constraint violation! SELECT * FROM x.a; -- You get two times the value 1, despite the constraint SELECT * FROM ONLY x.a; -- Aha, that's how the constraint is validated This is documented here: https://www.postgresql.org/docs/current/static/ddl-inherit.html *Other types of constraints (unique, primary key, and foreign key > constraints) are not inherited.* I haven't checked how a constraint can be enforced on the entire type hierarchy, but given how obscure this feature is, I generally recommend not using it (both in PostgreSQL and Oracle). The only historic use-case I've seen for table inheritance was partitioning, which has recently been supported natively in PostgreSQL as well (just like in Oracle). After all, just like in OO, composition beats inheritance in most cases :-) -- 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+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
Re: DAO for table with inheritance (postgres), table is no UpdateableRecord
Am Do., 12. Juli 2018 um 09:56 Uhr schrieb : > The actual isse was that the derived table records would not implement > UpdateableRecord and thus could not be used as the type in a class derived > from DaoImpl. > Oh, I see now. Well, the problem is, the primary key is a lie :-) Check this out: CREATE TABLE a ( id int CONSTRAINT pk_a PRIMARY KEY (id) ); CREATE TABLE b ( x text ) INHERITS (a); INSERT INTO a (id) VALUES (1); INSERT INTO a (id) VALUES (1); -- Constraint violation INSERT INTO x.b (id) VALUES (1); -- No constraint violation! SELECT * FROM x.a; -- You get two times the value 1, despite the constraint SELECT * FROM ONLY x.a; -- Aha, that's how the constraint is validated This is documented here: https://www.postgresql.org/docs/current/static/ddl-inherit.html *Other types of constraints (unique, primary key, and foreign key > constraints) are not inherited.* I haven't checked how a constraint can be enforced on the entire type hierarchy, but given how obscure this feature is, I generally recommend not using it (both in PostgreSQL and Oracle). The only historic use-case I've seen for table inheritance was partitioning, which has recently been supported natively in PostgreSQL as well (just like in Oracle). After all, just like in OO, composition beats inheritance in most cases :-) -- 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+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
Re: DAO for table with inheritance (postgres), table is no UpdateableRecord
Hi Lukas, thank you for taking the time to check the issue. To continue the development, I switched to individual tables with mostly the same columns. My hope was to not have to implement so much boilerplate code multiple times, but I probably overrated how much that would be. The actual isse was that the derived table records would not implement UpdateableRecord and thus could not be used as the type in a class derived from DaoImpl. - David -- 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+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
Re: DAO for table with inheritance (postgres), table is no UpdateableRecord
Hi David, I see, thanks for the explanation. Well, to my understanding, behind the scenes, PostgreSQL stores copies of the base table for each inherited table and replaces the original base table by a view of some sort. Querying your base table yields: QUERY PLAN| --| Append (cost=0.00..47.80 rows=781 width=318) | -> Seq Scan on metadata (cost=0.00..0.00 rows=1 width=318)| -> Seq Scan on metadata_int32 (cost=0.00..12.30 rows=230 width=318) | -> Seq Scan on metadata_float64 (cost=0.00..12.30 rows=230 width=318) | -> Seq Scan on metadata_string (cost=0.00..10.90 rows=90 width=318) | -> Seq Scan on metadata_boolean (cost=0.00..12.30 rows=230 width=318) | So, personally, I'm a bit reluctant to think that this will be decidedly beneficial to performance. In what aspects or usage situations have you measured performance to be superior? In any case, to jOOQ right now, the base table and the 5 inherited tables are all independent tables with no relationship (just as they are to most of the SQL language). So, you can already use those tables with jOOQ, but perhaps not in the way you would like. Maybe, you have any specific questions on how to make best use of the underlying inheritance in jOOQ, or what's keeping you from going forward with jOOQ's DAO's in this area? Thanks, Lukas -- 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+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
Re: DAO for table with inheritance (postgres), table is no UpdateableRecord
Hello Lucas,
the use case is I want to store typed metadata, e.g. numbers, strings,
boolean and geolocation, like this:
CREATE TYPE meta.metadata_type AS ENUM ('int32', 'float64', 'string',
'lnglat', 'boolean');
CREATE TABLE meta.metadata (
uuid uuid NOT NULL,
reference_uuid uuid NOT NULL,
datetime timestamptz NOT NULL,
type meta.metadata_type NOT NULL,
key varchar(128) NOT NULL,
CONSTRAINT pk_metadata_id PRIMARY KEY ( uuid )
);
CREATE TABLE meta.metadata_int32 (
valueinteger NOT NULL
) INHERITS (meta.metadata);
CREATE TABLE meta.metadata_float64 (
valuedouble precision NOT NULL
) INHERITS (meta.metadata);
CREATE TABLE meta.metadata_string (
valuevarchar(255) NOT NULL
) INHERITS (meta.metadata);
CREATE TABLE meta.metadata_lnglat (
valueGEOGRAPHY(POINT) NOT NULL
) INHERITS (meta.metadata);
CREATE TABLE meta.metadata_boolean (
valueboolean NOT NULL
) INHERITS (meta.metadata);
This is in order to be able to build more intelligent and performant
queries for certain types of metadata. I hope the idea becomes clear.
- David
On Wednesday, July 11, 2018 at 8:33:51 PM UTC+2, Lukas Eder wrote:
>
> Hello David,
>
> I'm assuming you plan to be using the code generator, which unfortunately
> does not support PostgreSQL table inheritance yet. There are a few pending
> feature requests:
> - https://github.com/jOOQ/jOOQ/issues/2777
> - https://github.com/jOOQ/jOOQ/issues/2782
>
> Also related: Oracle OBJECT type inheritance:
> - https://github.com/jOOQ/jOOQ/issues/644
>
> This topic simply hasn't seen much traction in the past, compared to the
> complexity it would introduce to jOOQ's type system.
>
> What's the use-case you're covering with PostgreSQL table inheritance?
>
> Am Mi., 11. Juli 2018 um 16:21 Uhr schrieb
> >:
>
>> Hello,
>>
>> I have a postgres table with inheritance, I want to write a DAO for,
>> which I want to derive from DAOImpl. Now DAOImpl expects the table to be
>> of type UpdatetableRecord: DAOImpl, P, T>.
>> What would be the best way to implement a class working on an inherited
>> table with jOOQ?
>>
>
--
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+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Re: DAO for table with inheritance (postgres), table is no UpdateableRecord
Hello David, I'm assuming you plan to be using the code generator, which unfortunately does not support PostgreSQL table inheritance yet. There are a few pending feature requests: - https://github.com/jOOQ/jOOQ/issues/2777 - https://github.com/jOOQ/jOOQ/issues/2782 Also related: Oracle OBJECT type inheritance: - https://github.com/jOOQ/jOOQ/issues/644 This topic simply hasn't seen much traction in the past, compared to the complexity it would introduce to jOOQ's type system. What's the use-case you're covering with PostgreSQL table inheritance? Thanks, Lukas Am Mi., 11. Juli 2018 um 16:21 Uhr schrieb : > Hello, > > I have a postgres table with inheritance, I want to write a DAO for, which > I want to derive from DAOImpl. Now DAOImpl expects the table to be of > type UpdatetableRecord: DAOImpl, P, T>. What > would be the best way to implement a class working on an inherited table > with jOOQ? > > Best regards, > David > > -- > 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+unsubscr...@googlegroups.com. > For more options, visit https://groups.google.com/d/optout. > -- 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+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
