Jakub Licznerski
Jakub Licznerski

Reputation: 1088

Jooq not generating index info for postgres expression index

I noticed that jooq does not generate Index entries (neither in Indexes.java nor TableName::getIndexes) for expression indices like this:

CREATE UNIQUE INDEX idx_table_client_id_type ON table(client_id, (payload->>'type'));

Obviously, the index exists in the database. It will be used used in queries generated by jooq, when filtering by client_id and same expression as in the index:

private static final Field<String> TYPE = field("{0}->>'type'", String.class, TABLE_NAME.PAYLOAD);

I wanted to ask however, what are the implications of this mismatch? Is there a case where lack of this information will cause jooq to generate an underperforming query?

Upvotes: 1

Views: 292

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 220987

The code generator uses the parser to detect column expressions in index definitions, which may be quoted, cast, or otherwise turned into expressions. But this means that the parser has to be able to recognise the syntax used in an index definition.

In your case, the index is probably skipped because of a parser error, which you should see in your code generation logs, see #11047 and #11977. The a->>'b' syntax will be supported by the jOOQ 3.18 parser only, see #10018.

There are no implications of this missing meta data in your generated code, unless you use this meta data for any purpose, including e.g. creating the schema on a new machine, etc.

Upvotes: 1

Related Questions