postgres partitioning via jooq 3

[Peter Cooner]

I'll write something more detailed later, when I'm not under deadline.

TL;DR Postgres table partitioning is usable in jOOQ 3 with some tricks, more testing is needed to determine how speedy it is. Also, I'm new to jOOQ, and so its likely I'm not using it optimally.

Implementation:

Set up Postgres tables as described in

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

But don't setup any insert / update / delete triggers - all those operations will be done in Java code you write using jOOQ.

First build the record R from the parent table using the constructor (not the Executor).

Second use the record to determine the partition table.

Third, for each partition table build a custom org.jooq.conf.Settings object which has a MappedTable that maps the parent table to the partitioned child table - this can be done lazily.

Something like:

R record = ...

Table<R> parent = ...

Settings settings = new Settings()

        .withRenderMapping(new RenderMapping()

            .withSchemata(new MappedSchema()

                .withInput(parent.getSchema().getName())

                .withOutput(parent.getSchema().getName())

                .withTables(new MappedTable()

                    .withInput(parent.getName())

                    .withOutput(partioner.determinePartition(record))

                )

            )

        );

I cached these in a Map, and pulled them out when creating the Executor, which I had to build each time I did processing because the Executor is tied to the Connection.

Finally, call record.attach(settings), and record.store().

The fetching of settings, and inserts / updates / deletes can all be built into a DAO implementer if desired.

For selects, if you are using Postgres 9.2 or later you can allow Postgres to use the where condition to do query planning for you.

But you can always determine the partition you need in jOOQ, and do the select there, just like the other queries built using the Settings.

Another trick is to use a lookup to find the right table, though you loose some of the typing.

For this trick, I used Public.SCHEMANAME.getTables() to build a table map (names to Table<TableRecord<?>> objects). Which allows me to do the selects on the partition instead of on the parent table. Then simply merge the generic record into a more specific record.

Table<TableRecord<?>> tablePartition = tableMaps.getTablePartition(record);

TableRecord<?> record2 = create.selectFrom(tablePartition)...fetchOne();

record.from(record2);

OK, thats it for now.

-- 

Pete

[Peter Cooner]

Oh, something else I forgot, normally the parent table does not have a primary key because you aren't intended to ever insert into this table directly. This causes jOOQ generator to extend TableImpl instead of UpdatableTableImpl.

I really wanted UpdatableTableImpl to be used which gives you store() and other useful functions, so I had to add a PK the parent table during generation even if it doesn't exist in a production system.

Pete

[Lukas Eder]

Thank you for sharing this, Peter. Looks like jOOQ can withstand your
"extreme" use-case. Looking forward to hearing more about this.

Cheers
Lukas

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

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

[siki]

Hi, 

Is there an update to this by any chance? I'm also using PostgreSQL with partitioned tables and would like to determine and access the partitions from JOOQ. 

Is there a solution that would "survive" automatic code generation for tables (the first solution above doesn't seem to be able to do that) and still keep type-safety?

Thanks

[Lukas Eder]

Doesn't look like it

@Peter: Did you further delve into this issue?

For more options, visit https://groups.google.com/d/optout.

[Peter Cooner]

Siki, do you mean how I had to add a PK to the parent table to get jOOQ's generator to use UpdatableTableImpl instead of TableImpl?

I just used a shell script to add PK, generate java code, then remove PK from the parent table. I'm unaware of any other way to force the jOOQ generator.

Pete