Using JOOQ 3.17.4, pgjdbc 42.5.0, postgres 14.3
I am aware of this answer here: https://stackoverflow.com/a/67292162/924597 - I've tried using the unqualified field name, but it makes no difference.
I'm trying to issue SQL that does "on conflict .. do update ... where" using a partial index, but I'm getting the error:
ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
The strange thing is, Postgres gives the failure, only when issuing the SQL through JOOQ. If I copy the SQL out of my console (as printed by P6Spy) and paste into IntelliJ IDEA's SQL editory - the exact same SQL executes properly.
Schema definition:
create table user_authz_request (
id bigint generated always as identity
(start with 30000000)
primary key not null,
status auth_request_status not null,
service_point_id bigint not null references service_point,
email varchar(256) not null,
client_id varchar(256) not null,
id_provider id_provider not null,
subject varchar(256) not null,
responding_user bigint references app_user null,
description varchar(1024) not null,
date_requested timestamp without time zone default transaction_timestamp(),
date_responded timestamp without time zone null
);
create unique index user_authz_request_once_active_key
on user_authz_request(service_point_id, client_id, subject)
where status = 'REQUESTED';
JOOQ Code:
db.insertInto(USER_AUTHZ_REQUEST).
set(USER_AUTHZ_REQUEST.SERVICE_POINT_ID, req.getServicePointId()).
set(USER_AUTHZ_REQUEST.STATUS, REQUESTED).
set(USER_AUTHZ_REQUEST.EMAIL, email).
set(USER_AUTHZ_REQUEST.CLIENT_ID, user.getClientId()).
set(USER_AUTHZ_REQUEST.ID_PROVIDER, idProvider).
set(USER_AUTHZ_REQUEST.SUBJECT, user.getSubject()).
set(USER_AUTHZ_REQUEST.DESCRIPTION, req.getComments()).
onConflict(
USER_AUTHZ_REQUEST.SERVICE_POINT_ID,
USER_AUTHZ_REQUEST.CLIENT_ID,
USER_AUTHZ_REQUEST.SUBJECT).
where(USER_AUTHZ_REQUEST.STATUS.eq(REQUESTED)).
doUpdate().
set(USER_AUTHZ_REQUEST.DESCRIPTION, req.getComments()).
set(USER_AUTHZ_REQUEST.DATE_REQUESTED, LocalDateTime.now()).
execute();
Generated SQL:
insert into api_svc.user_authz_request (service_point_id, status, email,
client_id, id_provider, subject,
description)
values (20000001, cast('REQUESTED' as api_svc.auth_request_status),
'email', 'client id',
cast('AAF' as api_svc.id_provider),
'subject', 'first')
on conflict (service_point_id, client_id, subject)
where status = cast('REQUESTED' as api_svc.auth_request_status) do
update
set description = 'first',
date_requested = cast('2022-09-20T05:35:35.927 0000' as timestamp(6))
The above is the SQL that fails when it runs in my server, but works fine when I execute it through IntelliJ.
What am I doing wrong?
CodePudding user response:
Starting from jOOQ 3.18 and #12531, jOOQ will auto-inline all the bind variables in that WHERE
clause, because it hardly ever makes sense to use bind values. It's a big exception in jOOQ's usual behaviour, because there are only very few cases where:
- Bind values are syntactically correct
- But at the same time, completely useless
In most other cases where bind values are auto-inlined, they are also not syntactically correct, so auto-inlining may be less controversial.
Until 3.18 and #12531 ships, you can simply inline your bind value manually, instead:
where(USER_AUTHZ_REQUEST.STATUS.eq(inline(REQUESTED, USER_AUTHZ_REQUEST.STATUS))).
See also:
It's actually the same problem as the one you've linked: Upsert with "on conflict do update" with partial index in jOOQ, just a different manifestation