Re: Considering jOOQ

[Peter Cooner]

Not sure how I forgot the exception for #1

Exception in thread "main" org.jooq.exception.DataAccessException: SQL [select "public"."st_intersects"(cast(? as any), cast(? as any)) as "magic"]; ERROR: syntax error at or near "any"

  Position: 44

at org.jooq.impl.Util.translate(Util.java:649)

at org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:360)

at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:262)

at org.jooq.impl.AbstractResultQuery.fetch(AbstractResultQuery.java:274)

at org.jooq.impl.AbstractResultQuery.fetch(AbstractResultQuery.java:350)

at org.jooq.impl.AbstractDelegatingSelect.fetch(AbstractDelegatingSelect.java:184)

at dao.Main.main(Main.java:80)

Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "any"

  Position: 44

at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2101)

at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1834)

at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)

at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:510)

at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:386)

at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:379)

at org.jooq.impl.DataSourcePreparedStatement.execute(DataSourcePreparedStatement.java:86)

at org.jooq.impl.AbstractResultQuery.execute(AbstractResultQuery.java:196)

at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:258)

... 4 more

Java Result: 1

On Fri, Feb 1, 2013 at 12:04 PM, Peter Cooner <petr...@gmail.com> wrote:

Hi all,

I am doing a refactor of our database application and am considering using jOOQ.

Previous setup:

- Postgres + PostGIS

- JDBC + PostGIS-JDBC

- Apache's DBUtil and DBCP

- 90% PreparedStatements

- Use of Point / PGgeometryLW

- Use of PGhstore

I've done a review of jOOQ already, and am fairly pleased with what I see,

- Support of Postgres + PostGIS

- Support new dynamic query options

- Cleaner, more obvious DB API

- Support for functions (PostGIS has a lot)

- Support for PGobject (and thus PGhstore, PGgeometry, and PGgeometryLW)

- Support to limit what is imported

A few questions I have:

1. I was trying to use a PostGIS routine st_intersects(geom1, geom2) -> boolean, but jOOQ tries to cast the inputs:

270 [main] DEBUG org.jooq.tools.LoggerListener  - -> with bind values      : select "public"."st_intersects"(cast('POINT(0 0)' as any), cast('LINESTRING(2 0,0 2)' as any)) as "magic"

From the Java side, I'm passing in PGobjects and no cast should be needed.

Code:

Connection c = DriverManager.getConnection(URL, "postgres", "");

Point point = new Point(0, 0);

//point.setSrid(4326);

PGgeometry pgpt = new PGgeometry(point);

LineString line = new LineString("LINESTRING ( 2 0, 0 2 )");

//line.setSrid(4326);

PGgeometry pgln = new PGgeometry(line);

Factory f = new Factory(c, SQLDialect.POSTGRES);

SelectSelectStep s = f.select(Routines.stIntersects2(pgpt, pgln).as("magic"));

List<?> fetch = s.fetch("magic");

2. WIth this refactoring of the Java, I'm trying to figure out how I could implement table partitioning - suggestions?

Table partitioning in Postgres is implemented via table inheritance. Partitioning tables in Postgres can make working with the data more complicated and slower though. To avoid some of the slowdown I plan to use jOOQ's ability to dynamically build queries, assigning inserts, updates, and deletes to the correct table(s) avoiding using triggers and other Postgres magic functions - I'm wondering if you have any suggestions on this...

--
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.
 
 

--
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"'

[Peter Cooner]

Example project for #1 - This assumes you're talking to Postgres 9.1 and PostGIS 2, There is an SQL file to create the DB and such.....

https://github.com/petriborg/jooq-gis-test

Maybe the PostGIS function st_intersects() isn't being generated correctly?

http://postgis.refractions.net/docs/ST_Intersects.html

It expects to geom objects, but of course jOOQ doesn't know about those - so it tries to use just plain Java Object... But then tries to cast them on the postgres side which just confuses me. :-)

[Peter Cooner]

If I change the line to

SelectSelectStep s = f.select(Routines.stIntersects3(cast(pgpt, String.class), cast(pgln, String.class)).as("magic"));

Then the cast as any goes away and the SQL looks like:

280 [main] DEBUG org.jooq.tools.LoggerListener  - -> with bind values      : select "public"."st_intersects"('POINT(0 0)', 'LINESTRING(0 0,0 2)') as "magic"

[Lukas Eder]

Hello and thank you for your interest in jOOQ

> I've done a review of jOOQ already, and am fairly pleased with what I see,
> - Support of Postgres + PostGIS

Unfortunately, that's not true. The PostGIS extension isn't really
well supported by jOOQ. This has been on the roadmap for a while now:
https://github.com/jOOQ/jOOQ/issues/982

> - Support new dynamic query options

What are these, according to you?

> - Cleaner, more obvious DB API
> - Support for functions (PostGIS has a lot)

Yes.

> - Support for PGobject (and thus PGhstore, PGgeometry, and PGgeometryLW)

They *might* work, but jOOQ doesn't guarantee anything

> 1. I was trying to use a PostGIS routine st_intersects(geom1, geom2) ->
> boolean, but jOOQ tries to cast the inputs:

Unfortunately, as I said, with jOOQ 2.6 you will probably not be able
to properly use PostGIS. It will probably work as long as you avoid
variable binding of PG* objects, though.

> 2. WIth this refactoring of the Java, I'm trying to figure out how I could
> implement table partitioning - suggestions?
>
> Table partitioning in Postgres is implemented via table inheritance.
> Partitioning tables in Postgres can make working with the data more
> complicated and slower though. To avoid some of the slowdown I plan to use
> jOOQ's ability to dynamically build queries, assigning inserts, updates, and
> deletes to the correct table(s) avoiding using triggers and other Postgres
> magic functions - I'm wondering if you have any suggestions on this...

Postgres' table inheritance feature currently isn't supported
explicitly, either. This was discussed a long time ago on the user
group:
https://groups.google.com/d/topic/jooq-user/YuTp9-5K9fs/discussion

I haven't followed up on that discussion yet. In order to see how
suggestions could be made, could you explain a bit more about your
partitioning use case?

Cheers
Lukas

[Peter Cooner]

On Sun, Feb 3, 2013 at 3:07 AM, Lukas Eder <lukas...@gmail.com> wrote:

Hello and thank you for your interest in jOOQ

> I've done a review of jOOQ already, and am fairly pleased with what I see,
> - Support of Postgres + PostGIS

Unfortunately, that's not true. The PostGIS extension isn't really
well supported by jOOQ. This has been on the roadmap for a while now:
https://github.com/jOOQ/jOOQ/issues/982

> - Support new dynamic query options

What are these, according to you?

Nothing crazy, just the ability to append conditions, the SelectSelectStep object looks like it works for all the needs I have.

 

> - Cleaner, more obvious DB API
> - Support for functions (PostGIS has a lot)

Yes.

> - Support for PGobject (and thus PGhstore, PGgeometry, and PGgeometryLW)

They *might* work, but jOOQ doesn't guarantee anything

Well, selects and inserts (which is the bulk of what I do) worked for all these object types without any weird binding issues.

 

> 1. I was trying to use a PostGIS routine st_intersects(geom1, geom2) ->
> boolean, but jOOQ tries to cast the inputs:

Unfortunately, as I said, with jOOQ 2.6 you will probably not be able
to properly use PostGIS. It will probably work as long as you avoid
variable binding of PG* objects, though.

Well I'm used to having to implement all of this in raw JDBC, are there any facilities in jOOQ to extend, or what not? I found these:

http://www.jooq.org/doc/2.6/manual/sql-execution/fetching/data-type-conversion/

http://www.jooq.org/doc/2.6/manual/code-generation/custom-data-types/

So I see how the converters can work for tables easily enough - is there something similar for routines?

> 2. WIth this refactoring of the Java, I'm trying to figure out how I could
> implement table partitioning - suggestions?
>
> Table partitioning in Postgres is implemented via table inheritance.
> Partitioning tables in Postgres can make working with the data more
> complicated and slower though. To avoid some of the slowdown I plan to use
> jOOQ's ability to dynamically build queries, assigning inserts, updates, and
> deletes to the correct table(s) avoiding using triggers and other Postgres
> magic functions - I'm wondering if you have any suggestions on this...

Postgres' table inheritance feature currently isn't supported
explicitly, either. This was discussed a long time ago on the user
group:
https://groups.google.com/d/topic/jooq-user/YuTp9-5K9fs/discussion

I haven't followed up on that discussion yet. In order to see how
suggestions could be made, could you explain a bit more about your
partitioning use case?

This sums it up pretty well -

http://www.postgresql.org/docs/9.1/static/ddl-partitioning.html

The difference between the above example and what I'm trying to implement is the insert logic will be done in the Java code and not by a trigger. I was looking for suggestions on how best to use jOOQ for this - I was considering a DAO implementation, but I'm not sure how the DAO interfaces tie to jOOQ (are they just a convence layer?) and the interface is so very tightly bound to the record and table interfaces of jOOQ it looks like I would have to drop all the type safety.

Pete

 

[Lukas Eder]

Hello Pete,

>> > - Support new dynamic query options
>>
>> What are these, according to you?
>
> Nothing crazy, just the ability to append conditions, the SelectSelectStep
> object looks like it works for all the needs I have.

I see. Yes, you'll find many examples about this on Stack Overflow, on
this user group, or in the jOOQ manual

>> > - Support for PGobject (and thus PGhstore, PGgeometry, and PGgeometryLW)
>>
>> They *might* work, but jOOQ doesn't guarantee anything
>
> Well, selects and inserts (which is the bulk of what I do) worked for all
> these object types without any weird binding issues.

OK. Still, fixing those cases that don't work is best done by formally
supporting PostGIS. Otherwise, new surprises might arise later on.

>> Unfortunately, as I said, with jOOQ 2.6 you will probably not be able
>> to properly use PostGIS. It will probably work as long as you avoid
>> variable binding of PG* objects, though.
>
> Well I'm used to having to implement all of this in raw JDBC, are there any
> facilities in jOOQ to extend, or what not? I found these:
>
> http://www.jooq.org/doc/2.6/manual/sql-execution/fetching/data-type-conversion/
> http://www.jooq.org/doc/2.6/manual/code-generation/custom-data-types/

Yes, this is your best choice right now, although I haven't made any
concrete experiences with PostGIS and jOOQ's converters yet. You might
be breaking new grounds, at least from what I know from this user
group. Any feedback you may have about your experience is very welcome
on this group!

> So I see how the converters can work for tables easily enough - is there
> something similar for routines?

Converters for routines aren't formally supported (yet). As a reminder
to implement this, I have created #2155
https://github.com/jOOQ/jOOQ/issues/2155

You *could* play around with your own routine implementations, by
extending org.jooq.impl.CustomField:
http://www.jooq.org/javadoc/latest/org/jooq/impl/CustomField.html

Essentially, you'll have to implement jOOQ's internal toSQL() and
bind() methods. The bind() method receives an org.jooq.BindContext,
which exposes the underlying PreparedStatement, so you're free to
properly bind your PG objects.

>> I haven't followed up on that discussion yet. In order to see how
>> suggestions could be made, could you explain a bit more about your
>> partitioning use case?
>
> This sums it up pretty well -
>
> http://www.postgresql.org/docs/9.1/static/ddl-partitioning.html

I see. That is an interesting approach. I've read about Instagram
having made good experience with such kinds of partitioning for
sharding. I've referenced that article here:
http://blog.jooq.org/2011/12/10/a-success-story-of-sql-scaling-horizontally/

> The difference between the above example and what I'm trying to implement is
> the insert logic will be done in the Java code and not by a trigger. I was
> looking for suggestions on how best to use jOOQ for this

Hmm, these are some interesting points that might even lead to a new
feature within jOOQ. Essentially, you want to decide within the Java
application (within a DAO layer), whether your inserted record should
be inserted into a table [A]_1 or [A]_2, while the application layer
only knows about [A]. When selecting (depending on a relevant
predicate), you'd issue a UNION over [A]_1 and [A]_2, again with the
application layer only knowing about [A]. Is that it?

> - I was considering
> a DAO implementation, but I'm not sure how the DAO interfaces tie to jOOQ
> (are they just a convence layer?)

jOOQ's DAOs today are a mere convenience and a response to a
competitor product's feature set (OneWebSQL). They aren't very
sophisticated, but they take care of much of the basic CRUD
operations. Feel free to provide feature requests, should you see
anything missing.

> and the interface is so very tightly bound
> to the record and table interfaces of jOOQ it looks like I would have to
> drop all the type safety.

I'm not sure if I'm correctly understanding your ideas here. Why would
jOOQ's DAOs make you drop type safety?

Cheers
Lukas

[Peter Cooner]

Rodger that, I'll make sure to write these all before making final designs, best to avoid surprises. :-)

 

> So I see how the converters can work for tables easily enough - is there
> something similar for routines?

Converters for routines aren't formally supported (yet). As a reminder
to implement this, I have created #2155
https://github.com/jOOQ/jOOQ/issues/2155

You *could* play around with your own routine implementations, by
extending org.jooq.impl.CustomField:
http://www.jooq.org/javadoc/latest/org/jooq/impl/CustomField.html

Essentially, you'll have to implement jOOQ's internal toSQL() and
bind() methods. The bind() method receives an org.jooq.BindContext,
which exposes the underlying PreparedStatement, so you're free to
properly bind your PG objects.

OK, I'm guessing there are examples of this in the source code I could base an implementation off?

 

>> I haven't followed up on that discussion yet. In order to see how
>> suggestions could be made, could you explain a bit more about your
>> partitioning use case?
>
> This sums it up pretty well -
>
> http://www.postgresql.org/docs/9.1/static/ddl-partitioning.html

I see. That is an interesting approach. I've read about Instagram
having made good experience with such kinds of partitioning for
sharding. I've referenced that article here:
http://blog.jooq.org/2011/12/10/a-success-story-of-sql-scaling-horizontally/

Ah, interesting, thanks for the link.

 

> The difference between the above example and what I'm trying to implement is
> the insert logic will be done in the Java code and not by a trigger. I was
> looking for suggestions on how best to use jOOQ for this

Hmm, these are some interesting points that might even lead to a new
feature within jOOQ. Essentially, you want to decide within the Java
application (within a DAO layer), whether your inserted record should
be inserted into a table [A]_1 or [A]_2, while the application layer
only knows about [A]. When selecting (depending on a relevant
predicate), you'd issue a UNION over [A]_1 and [A]_2, again with the
application layer only knowing about [A]. Is that it?

Yes thats exactly it. In the case of Postgres 9.2+ you get the select for free - Postgres's query planner now uses table CHECK() conditions when planning and so a SELECT on [A] would work as expected. The insert does not, at this time, come for free. Testing shows that using a trigger in the DB to do the inserts is very slow, my own testing shows that doing the same logic on the Java side is considerably faster and can be batched.

 

> - I was considering
> a DAO implementation, but I'm not sure how the DAO interfaces tie to jOOQ
> (are they just a convence layer?)

jOOQ's DAOs today are a mere convenience and a response to a
competitor product's feature set (OneWebSQL). They aren't very
sophisticated, but they take care of much of the basic CRUD
operations. Feel free to provide feature requests, should you see
anything missing.

> and the interface is so very tightly bound
> to the record and table interfaces of jOOQ it looks like I would have to
> drop all the type safety.

I'm not sure if I'm correctly understanding your ideas here. Why would
jOOQ's DAOs make you drop type safety?

Not to say that any of this isn't correct! I know I'm trying to do something different then most people here - but when you have ~1 billion rows per year being inserted into the DB, you get interesting problems to solve. ;-)

Anyway, if we look at the interfaces -

interface DAO<R extends TableRecord<R>, P, T>

interface TableRecord<R extends TableRecord<R>> extends Record

interface Record extends FieldProvider, Store<Object>

interface FieldProvider

I can see two obvious choices -

Solution #1

Construct [A]_1 or [A]_2 at data ingest time, storing them in buckets which then get dispatched to the correct DAO

Problems:

- The data ingest is all really the same, but the POJO interfaces don't inherit from each other, and the objects can't be assigned to anything but Object, which makes working with the data classes difficult (as far as I can see)

- I didn't see any sort of putValue(Field,Value) function for TableRecord

- I suppose I could modify the [A] POJO or TableRecord that is generated, making it implement all of the interfaces of [A]_*

- Don't know how the generated TableRecord and POJO are converted, or how they interact w/ the generated interface

Solution #2

Build all [A] and then pass them to a custom DAO which then converts the [A] TableRecord or POJO into [A]_1 or [A]_2 and pass them to their respective DAO.

- How to convert [A] into [A]_* ? I've no idea, I hadn't seen anything obvious in the interfaces.

Hoping you can prevent me from traveling down the wrong path(s).

Thanks again,

Pete

[Lukas Eder]

>> > So I see how the converters can work for tables easily enough - is there
>> > something similar for routines?
>>
>> Converters for routines aren't formally supported (yet). As a reminder
>> to implement this, I have created #2155
>> https://github.com/jOOQ/jOOQ/issues/2155
>>
>> You *could* play around with your own routine implementations, by
>> extending org.jooq.impl.CustomField:
>> http://www.jooq.org/javadoc/latest/org/jooq/impl/CustomField.html
>>
>> Essentially, you'll have to implement jOOQ's internal toSQL() and
>> bind() methods. The bind() method receives an org.jooq.BindContext,
>> which exposes the underlying PreparedStatement, so you're free to
>> properly bind your PG objects.
>
>
> OK, I'm guessing there are examples of this in the source code I could base
> an implementation off?

Yes. This section of the manual will give you a first impression about
what you're going to be doing:
http://www.jooq.org/doc/2.6/manual/sql-building/queryparts/custom-queryparts/

The jOOQ code base is full of examples about how to properly implement
query parts.

>> > The difference between the above example and what I'm trying to
>> > implement is
>> > the insert logic will be done in the Java code and not by a trigger. I
>> > was
>> > looking for suggestions on how best to use jOOQ for this
>>
>> Hmm, these are some interesting points that might even lead to a new
>> feature within jOOQ. Essentially, you want to decide within the Java
>> application (within a DAO layer), whether your inserted record should
>> be inserted into a table [A]_1 or [A]_2, while the application layer
>> only knows about [A]. When selecting (depending on a relevant
>> predicate), you'd issue a UNION over [A]_1 and [A]_2, again with the
>> application layer only knowing about [A]. Is that it?
>>
> Yes thats exactly it. In the case of Postgres 9.2+ you get the select for
> free - Postgres's query planner now uses table CHECK() conditions when
> planning and so a SELECT on [A] would work as expected.

Very interesting. I thought that Oracle was the only database that
really implemented partitioning "for free". Good to know

> The insert does not,
> at this time, come for free. Testing shows that using a trigger in the DB to
> do the inserts is very slow, my own testing shows that doing the same logic
> on the Java side is considerably faster and can be batched.

OK

>> > and the interface is so very tightly bound
>> > to the record and table interfaces of jOOQ it looks like I would have to
>> > drop all the type safety.
>>
>> I'm not sure if I'm correctly understanding your ideas here. Why would
>> jOOQ's DAOs make you drop type safety?
>>
>
> Not to say that any of this isn't correct! I know I'm trying to do something
> different then most people here - but when you have ~1 billion rows per year
> being inserted into the DB, you get interesting problems to solve. ;-)

I have done that with Oracle, although we used some lower-level
features (lower than SQL) to get the data loaded... It's interesting
nonetheless. :-)

> I can see two obvious choices -
>
> Solution #1

> [...]
> Solution #2
> [...]

> Hoping you can prevent me from traveling down the wrong path(s).

Yes, there are a lot of possible "wrong" paths here. First off, I
think it is important to understand that partitioning should be seen
as a storage-level feature, if implemented correctly. A good example
for this are Oracle's "PARTITION BY" DDL clause, which does not change
the logical structure of the partitioned table. In other words,
outside of the storage engine, there is only [A]. [A]_1 and [A]_2 are
not visible, except maybe to the CBO and some query hints.

From what you're telling me, Postgres isn't that far advanced yet, and
some of the partitioning into [A]_1 and [A]_2 has to be done above the
storage-level, i.e. at the SQL-level (using triggers) or at the DAO
level (using Java code, phrasing the correct SQL).

Nonetheless, I think you should avoid disclosing the existence of
[A]_1 and [A]_2 anywhere above some "last minute" SQL patching
performed by some hook that you provide jOOQ with. Your application
should only ever perform actions on [A], as [A] is the only relevant
entity from a logical perspective. Storage facts should be hidden
entirely.

In other words, this is more or less your Solution #2:

> Solution #2
> Build all [A] and then pass them to a custom DAO which then converts the [A]
> TableRecord or POJO into [A]_1 or [A]_2 and pass them to their respective
> DAO.

Yes. Based on your partitioning criteria (e.g. some date), you can
divide the set of [A] into various buckets, generate SQL for that
bucket and batch insert each bucket.

> - How to convert [A] into [A]_* ? I've no idea, I hadn't seen anything
> obvious in the interfaces.

Maybe, runtime table mapping could help, here?
http://www.jooq.org/doc/2.6/manual/sql-building/factory/runtime-schema-mapping/#N1061E

This feature was originally designed to allow for adding table
prefixes in environments where the database / schema is shared among
several applications - e.g. on a shared hosting provider. It could
work just the same for you.

If you want, we could also discuss Solution #1, but I feel that it
will be much harder to do correctly. Specifically because the _1 and
_2 suffixes used for partitioning should probably be dynamic...?

Cheers
Lukas

[Peter Cooner]

On Tue, Feb 5, 2013 at 10:19 AM, Lukas Eder <lukas...@gmail.com> wrote:

>> > So I see how the converters can work for tables easily enough - is there
>> > something similar for routines?
>>
>> Converters for routines aren't formally supported (yet). As a reminder
>> to implement this, I have created #2155
>> https://github.com/jOOQ/jOOQ/issues/2155
>>
>> You *could* play around with your own routine implementations, by
>> extending org.jooq.impl.CustomField:
>> http://www.jooq.org/javadoc/latest/org/jooq/impl/CustomField.html
>>
>> Essentially, you'll have to implement jOOQ's internal toSQL() and
>> bind() methods. The bind() method receives an org.jooq.BindContext,
>> which exposes the underlying PreparedStatement, so you're free to
>> properly bind your PG objects.
>
>
> OK, I'm guessing there are examples of this in the source code I could base
> an implementation off?

Yes. This section of the manual will give you a first impression about
what you're going to be doing:
http://www.jooq.org/doc/2.6/manual/sql-building/queryparts/custom-queryparts/

The jOOQ code base is full of examples about how to properly implement
query parts.

So let me try and sum this up then - what I'll need to do is

1. Create a CustomeField

  - That requires a DataType, which is? PostgresDataType.ANY?

2. Create a CustomTable

  - Because the generated Table is bound to a TableField which is an incompatible type?

3. Create a CustomRecord

  - Because otherwise it wouldn't play right w/ the POJO or anything else?

Maybe I am looking more for a way to modify the generator?

This seems like it would be easier if I could somehow help the generator with its typing problems (if there were some magic support for it, which I know there is not) ie

1. use <customType> to tell generator about a new type, not a convert, but a new type parser

      <customTypes>

        <customType>

          <name>org.postgis.PGgeometryLW</name>

          <parser>gis.PGgeometryLWParser</parser>

        </customType>

      </customTypes>

2. use <forcedType> to tell the generator on what columns (or functions) to use the type on

      <forcedTypes>

        <forcedType>

          <name>org.postgis.PGgeometryLW</name>

          <expressions>public.t1.geom_pos</expressions>

        </forcedType>

      </forcedTypes>

I don't know about Oracle, but Postgre's objects all inherit from PGobject, which you're already parsing, I know I'm missing something obvious here.

Thanks, Pete

[Peter Cooner]

On Tue, Feb 5, 2013 at 10:19 AM, Lukas Eder <lukas...@gmail.com> wrote:

>> > The difference between the above example and what I'm trying to
>> > implement is
>> > the insert logic will be done in the Java code and not by a trigger. I
>> > was
>> > looking for suggestions on how best to use jOOQ for this
>>
>> Hmm, these are some interesting points that might even lead to a new
>> feature within jOOQ. Essentially, you want to decide within the Java
>> application (within a DAO layer), whether your inserted record should
>> be inserted into a table [A]_1 or [A]_2, while the application layer
>> only knows about [A]. When selecting (depending on a relevant
>> predicate), you'd issue a UNION over [A]_1 and [A]_2, again with the
>> application layer only knowing about [A]. Is that it?
>>
> Yes thats exactly it. In the case of Postgres 9.2+ you get the select for
> free - Postgres's query planner now uses table CHECK() conditions when
> planning and so a SELECT on [A] would work as expected.

Very interesting. I thought that Oracle was the only database that
really implemented partitioning "for free". Good to know

I should also mention that since Postgres 8 sometime, selecting on [A] was equivalent of of a select / union on [A]_1 and [A]_2, but its since 9.2 that Postgres's query planner has gotten smarter about using table conditionals to make that select smart.

 

> The insert does not,
> at this time, come for free. Testing shows that using a trigger in the DB to
> do the inserts is very slow, my own testing shows that doing the same logic
> on the Java side is considerably faster and can be batched.

OK

>> > and the interface is so very tightly bound
>> > to the record and table interfaces of jOOQ it looks like I would have to
>> > drop all the type safety.
>>
>> I'm not sure if I'm correctly understanding your ideas here. Why would
>> jOOQ's DAOs make you drop type safety?
>>
>
> Not to say that any of this isn't correct! I know I'm trying to do something
> different then most people here - but when you have ~1 billion rows per year
> being inserted into the DB, you get interesting problems to solve. ;-)

I have done that with Oracle, although we used some lower-level
features (lower than SQL) to get the data loaded... It's interesting
nonetheless. :-)

Well Postgres has the COPY operation for bulk loading of data, but that is not my use case - this is a live feed of data which is being stored, so its important to keep insert time small.

 

> I can see two obvious choices -
>
> Solution #1

> [...]
> Solution #2
> [...]

> Hoping you can prevent me from traveling down the wrong path(s).

Yes, there are a lot of possible "wrong" paths here. First off, I
think it is important to understand that partitioning should be seen
as a storage-level feature, if implemented correctly. A good example
for this are Oracle's "PARTITION BY" DDL clause, which does not change
the logical structure of the partitioned table. In other words,
outside of the storage engine, there is only [A]. [A]_1 and [A]_2 are
not visible, except maybe to the CBO and some query hints.

From what you're telling me, Postgres isn't that far advanced yet, and
some of the partitioning into [A]_1 and [A]_2 has to be done above the
storage-level, i.e. at the SQL-level (using triggers) or at the DAO
level (using Java code, phrasing the correct SQL).

Yes Postgres's partitioning requires considerable efforts on the developers part.

I think I understand what you're suggesting here - I'm going to try some of this out over the next couple of days.

Best I can come up with is to use the date as a generated prefix -

2013_01_TABLE_A

2013_02_TABLE_A

2013_03_TABLE_A

...

Of course, jOOQ itself won't even need to know about the child tables then, only the parent table TABLE_A.

Thank you again for the suggestions and help.

Pete

[Lukas Eder]

Hi Pete,

Sorry for the late reply. Here's to your first e-mail:

2013/2/5 Peter Cooner <petr...@gmail.com>:

Yes, that's about it.
Another option, instead of using the PostgresDataType.ANY might be to
hook in your converter for the relevant columns.

> Maybe I am looking more for a way to modify the generator?

You could try that, but it will certainly mean more work. Do note that
there is no strict API guarantee given for the generator API. While I
try to keep incompatible changes at a low level, they may happen
between minor releases.

> This seems like it would be easier if I could somehow help the generator
> with its typing problems (if there were some magic support for it, which I
> know there is not) ie
>
> 1. use <customType> to tell generator about a new type, not a convert, but a
> new type parser
> <customTypes>
> <customType>
> <name>org.postgis.PGgeometryLW</name>
> <parser>gis.PGgeometryLWParser</parser>
> </customType>
> </customTypes>
> 2. use <forcedType> to tell the generator on what columns (or functions) to
> use the type on
> <forcedTypes>
> <forcedType>
> <name>org.postgis.PGgeometryLW</name>
> <expressions>public.t1.geom_pos</expressions>
> </forcedType>
> </forcedTypes>

Most of jOOQ's JDBC access is pretty standard. There are only a few
exceptions to these rules, most of which are done by the official
Postgres JDBC driver

> I don't know about Oracle, but Postgre's objects all inherit from PGobject,
> which you're already parsing, I know I'm missing something obvious here.

I don't think you are. The current support for Postgres' PGobject
originates from some initial attempts to implement what the Postgres
JDBC driver should have implemented already. For instance, the correct
way to support user-defined types is through java.sql.Struct or
java.sql.SQLData. Unfortunately, that part of the JDBC API is quite
clumsy and it is not at all supported by Postgres.

In other words, I still think you're breaking new grounds in the
combination of jOOQ+PostGIS

[Lukas Eder]

Hi Pete,

>> > - How to convert [A] into [A]_* ? I've no idea, I hadn't seen anything
>> > obvious in the interfaces.
>>
>> Maybe, runtime table mapping could help, here?
>>
>> http://www.jooq.org/doc/2.6/manual/sql-building/factory/runtime-schema-mapping/#N1061E
>>
>> This feature was originally designed to allow for adding table
>> prefixes in environments where the database / schema is shared among
>> several applications - e.g. on a shared hosting provider. It could
>> work just the same for you.
>>
>> If you want, we could also discuss Solution #1, but I feel that it
>> will be much harder to do correctly. Specifically because the _1 and
>> _2 suffixes used for partitioning should probably be dynamic...?
>>
>
> I think I understand what you're suggesting here - I'm going to try some of
> this out over the next couple of days.
>
> Best I can come up with is to use the date as a generated prefix -
>
> 2013_01_TABLE_A
> 2013_02_TABLE_A
> 2013_03_TABLE_A
> ...
>
> Of course, jOOQ itself won't even need to know about the child tables then,
> only the parent table TABLE_A.
>
> Thank you again for the suggestions and help.

You're welcome. Looking forward to feedback about whether this works out for you

Cheers
Lukas