Enum Order is not in sync with the database

[patrik...@netrapid.de]

Hello,

I am using jOOQ for some time now, and I am very happy about it :)

I'm just stumbling from time to time over some problems, and this is one of them:

The order of the enum values generated in the enum classes is not the same as the order defined in the database.

For instance:

POSTGRES: 

create type weekday as enum ( 'SUNDAY', 'MONDAY', 'TUESDAY', 'WEDNESDAY', 'THURSDAY', 'FRIDAY', 'SATURDAY');

this results in: 

dwh=# select * from pg_enum where enumtypid='260085';

 enumtypid | enumsortorder | enumlabel 

-----------+---------------+-----------

    260085 |             1 | SUNDAY

    260085 |             2 | MONDAY

    260085 |             3 | TUESDAY

    260085 |             4 | WEDNESDAY

    260085 |             5 | THURSDAY

    260085 |             6 | FRIDAY

    260085 |             7 | SATURDAY

And this is the created enum:

public enum Weekday implements org.jooq.EnumType {

FRIDAY("FRIDAY"),

MONDAY("MONDAY"),

SATURDAY("SATURDAY"),

SUNDAY("SUNDAY"),

THURSDAY("THURSDAY"),

TUESDAY("TUESDAY"),

WEDNESDAY("WEDNESDAY"),

;

Apparently, the query used by jOOQ to create the enum is using "order by enumlabel asc", 

not "order by enumsortorder". Therefore, the order of the enums are not in sync.

This is a problem as soon you try something like :

final static int SUNDAY = 1;

Weekday.values()[SUNDAY];

Sure, adding another constant to represent SUNDAY would be stupid, but if you use the

Calendar class, you want to map your Calendar.SUNDAY somehow to the Weekday.SUNDAY.

If they would be in the right order, Weekday.SUNDAY-1 would do the trick.

As they are not in the right order, I have to pull some dirty tricks. And I am using jOOQ to

get rid of dirty tricks and keep my database and my javacode "in sync".

Is there any chance that jOOQ will produce the enums in the right order, is there a flag or

configuration option, or is that something I will have to accept for the future ?

Best,

Patrik 

[Lukas Eder]

Hello Patrik,

Thanks for your detailed report! Indeed, the jOOQ query to collect PostgreSQL enum types is not optimal for PostgreSQL 9.1+:

.select(

    PG_NAMESPACE.NSPNAME,

    PG_TYPE.TYPNAME,

    PG_ENUM.ENUMLABEL)

.from(PG_ENUM)

.join(PG_TYPE).on("pg_enum.enumtypid = pg_type.oid")

.join(PG_NAMESPACE).on("pg_type.typnamespace = pg_namespace.oid")

.where(PG_NAMESPACE.NSPNAME.in(getInputSchemata()))

.orderBy(

    PG_NAMESPACE.NSPNAME,

    PG_TYPE.TYPNAME,

    PG_ENUM.ENUMLABEL)

.fetch();

In PostgreSQL 9.1, the pg_enum.enumsortorder was added. Compare:

- http://www.postgresql.org/docs/9.0/static/catalog-pg-enum.html

- http://www.postgresql.org/docs/9.1/static/catalog-pg-enum.html

In order for jOOQ to be compatible between < 9.1 and 9.1+ versions, I guess jOOQ could check for the existence of enumsortorder.

- If it exists, order by that

- If it doesn't exist, order by pg_enum.oid

Any other opinions?

I'll be tracking this as #2707. I think this can be fixed for jOOQ 3.2

https://github.com/jOOQ/jOOQ/issues/2707

Cheers

Lukas

2013/8/22 <patrik...@netrapid.de>

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

[patrik...@netrapid.de]

Thank you for your reply.

I think the ordering by oid as a fallback is fine... I assume postgres generates the oids in the right

order when storing the type, so the effective result will be the same as using pg_enum.enumsortorder, provided I don't

try to reorder the enum type fiddling with the internals ( adding an additional enum value is possible

in 9.1., I checked that, but that won't interfere with the order since the oid is strictly monotonic.... )

When updating the pg_enum.enumorder by hand I'd break even this workaround.

Maybe one should mention in the documentation that this is a bad idea.

keep up the good work :)

Patrik

[patrik...@netrapid.de]

I have to correct myself :(

"When an enum type is created, its members are assigned sort-order positions 1..n. But members added later might be given negative or fractional values of enumsortorder. The only requirement on these values is that they be correctly ordered and unique within each enum type."

"ALTER TYPE name ADD VALUE new_enum_value [ { BEFORE | AFTER } existing_enum_value ]"

So, one can add values to the type that have a higher oid, but a lower enumorder.

The workaround will only work as long one does not alters the type by adding values

in the sort order "BEFORE" :(

[Lukas Eder]

Hi,

2013/8/23 <patrik...@netrapid.de>

Thank you for your reply.

I think the ordering by oid as a fallback is fine... I assume postgres generates the oids in the right

order when storing the type, so the effective result will be the same as using pg_enum.enumsortorder, provided I don't

try to reorder the enum type fiddling with the internals ( adding an additional enum value is possible

in 9.1., I checked that, but that won't interfere with the order since the oid is strictly monotonic.... )

As a matter of fact, I didn't come up with the idea about ordering by oid myself ;-) The strict ordering was guaranteed by PostgreSQL 9.0 documentation:

http://www.postgresql.org/docs/9.0/static/catalog-pg-enum.html

The OIDs for a particular enum type are guaranteed to be ordered in the way the type should sort, but there is no guarantee about the ordering of OIDs of unrelated enum types.

But I got confused about their rather weird "feature upgrade" in 9.1 when they distinguish between even and odd OIDs:

http://www.postgresql.org/docs/9.1/static/catalog-pg-enum.html

 

The OIDs for pg_enum rows follow a special rule: even-numbered OIDs are guaranteed to be ordered in the same way as the sort ordering of their enum type. That is, if two even OIDs belong to the same enum type, the smaller OID must have the smaller enumsortorder value. Odd-numbered OID values need bear no relationship to the sort order.

I somewhat don't trust that funny distinction. Semantics that has been put on even or odd IDs is about the least expected thing ever in any database :-)

When updating the pg_enum.enumorder by hand I'd break even this workaround.

Maybe one should mention in the documentation that this is a bad idea.

What's a bad idea? Reordering enum literals? I suspect that each DDL operation on PostgreSQL enum types will trigger the generation of an entirely new set of enum literals / OIDs.

Cheers

Lukas

[Lukas Eder]

2013/8/23 <patrik...@netrapid.de>

Jeez, thanks for that insight. I'll run that question by Stack Overflow. Someone will give us an authoritative answer, I'm sure. Maybe there's an internal stored function for the task:

http://stackoverflow.com/q/18398590/521799

From what I understand, the OID ordering is mostly but not always backwards-compatible...

[Lukas Eder]

2013/8/23 Lukas Eder <lukas...@gmail.com>

It seems as though we have an answer:

http://stackoverflow.com/a/18399022/521799 

jOOQ will have to order the enum literals by casting the label to the enum type. PostgreSQL surely has its ways:

SELECT enumlabel, row_number() OVER (ORDER BY enumlabel::test_enum) AS sort_key
FROM pg_catalog.pg_enum
WHERE enumtypid = 'test_enum'::regtype;

Cheers

Lukas

[Lukas Eder]

I have committed a fix for jOOQ 3.2, fixing this issue. In order to be able to cast the ordering column to the enum type name, a separate SELECT statement needs to be performed to collect all applicable, fully-qualified type names.

The change can be seen here:

https://github.com/jOOQ/jOOQ/commit/9252e6a5500b59e66e1a66c2e7f309b2e96e965c

I have released a SNAPSHOT to Sonatype:

https://oss.sonatype.org/content/repositories/snapshots/org/jooq/

I'd be glad if you could review / test this fix.

[patrik...@netrapid.de]

For my particular application, the ordering is now correct and my unit tests run as expected.

There is only one minor problem, but jooq is not to blame for that. It could help, but I need

to check the code generation features first. 

[Lukas Eder]

OK, thanks for the feedback!

2013/8/26 <patrik...@netrapid.de>

For my particular application, the ordering is now correct and my unit tests run as expected.

There is only one minor problem, but jooq is not to blame for that. It could help, but I need

to check the code generation features first. 

--