Thank you very much for your report. Up until jOOQ 3.16, the existing optimistic locking feature is an UpdatableRecord only feature. It is not available to ordinary SQL queries that you hand-roll, irrespective of whether you use the set(Record) method or not.
jOOQ 3.17 will ship with a new feature called "client side computed columns" (see https://github.com/jOOQ/jOOQ/issues/9879), which will allow for such computations to happen in arbitrary statements, not just the ones generated by jOOQ's internals. So, there's a chance of optimistic locking also starting to work with INSERT .. ON DUPLICATE KEY UPDATE, although - no promises. There are always caveats which cannot be resolved in certain dialects. I've created a feature request for this: https://github.com/jOOQ/jOOQ/issues/13339 If you think you've found a bug with the UpdatableRecord::merge behaviour, may I ask you to report it here, please? https://github.com/jOOQ/jOOQ/issues/new/choose Thanks, Lukas On Thu, Mar 24, 2022 at 11:21 PM 'Dominik Hirt' via jOOQ User Group < [email protected]> wrote: > > Hi Lukas, > > I have a table USERSETTINGS in which I want to insert data via > onDuplicateKeyUpdate in PostgreSQL. > If a row with the primary key combination (username,key) already exists, > UPDATE should be used instead of INSERT. Everything works fine until I > noticed that the modified_at field is not set during the optimistic locking > handling. My current implementation looks like this: > > UsersettingsRecord dbRecord = jooq.newRecord(USERSETTINGS, > usersettings); > > jooq.insertInto(USERSETTINGS).set(dbRecord).onDuplicateKeyUpdate().set(dbRecord).execute(); > > a) if I manually set the date before the insertInto/onDuplicateKeyUpdate > > dbRecord.setModifiedAt(LocalDateTime.now()); > > everything works. But I wanted to avoid that. > > b) if I use dbRecord.merge() instead of jooq.insertInto... it doesn't work > anymore. There are duplicate-key exceptions during INSERT, i.e. the > onDuplicateKeyUpdate mechanism does not work at all. This is probably due > to the UpdatableRecordImpl.lockValuePresent: > > // [#10050] A lock value is present if we either have locking columns or > if the record was fetched from the database > > return getRecordVersion() != null // NULL in my case > || getRecordTimestamp() != null // not NULL in my case > || getTable().getRecordVersion() == null // NULL in my case > && getTable().getRecordTimestamp() == null // not NULL in my > case > && fetched; // true in my case > > The whole expression evaluates to FALSE in my case which leads in method > > UpdatableRecordImpl.storeMerge0 > > always to the call of storeInsert0 instead of storeUpdate0. > > Is there a bug in UpdatableRecordImpl.lockValuePresent or how can I > profit from updateOnDuplicateKeyUpdate while using records instead of > insertInto(…) > ? > > Kind regards > Dominik > > This is the table: > > *CREATE* *TABLE* > usersettings > ( > username *CHARACTER* *VARYING* *NOT* *NULL*, > *KEY* *CHARACTER* *VARYING* *NOT* *NULL*, > *value* *CHARACTER* *VARYING*, > modified_at *TIMESTAMP*(6) *WITHOUT* *TIME* *ZONE*, > *PRIMARY* *KEY* (username, *KEY*) > ); > > And her a nearly complete JBANG example: > > > //DEPS org.jooq:jooq:${jooq.version:RELEASE} > //DEPS org.postgresql:postgresql:${postgresql.version:RELEASE} > //DEPS org.slf4j:slf4j-api:1.7.32 > //DEPS ch.qos.logback:logback-classic:1.2.4 > //SOURCES de/hub28/steel42/generated/** > > import static org.jooq.impl.DSL.*; > import static de.hub28.steel42.generated.Tables.*; > import org.jooq.*; > import org.jooq.conf.*; > import java.io.*; > import java.io.File; > import java.sql.*;; > import java.util.*; > import java.time.LocalDateTime; > import de.hub28.steel42.generated.tables.records.*; > import de.hub28.steel42.generated.tables.pojos.*; > > > public class InsertOnDuplicate { > private static DSLContext jooq; > > public static void main(String[] args) throws Exception { > DriverManager.registerDriver((Driver) > Class.forName("org.postgresql.Driver").newInstance()); > String url = "jdbc:postgresql://127.0.0.1:5432/steel42"; > Connection conn = DriverManager.getConnection(url); > > Settings settings = new Settings() > .withRenderNameCase(RenderNameCase.AS_IS) > .withRenderQuotedNames(RenderQuotedNames.EXPLICIT_DEFAULT_QUOTED) > .withRenderSchema(false) > .withUpdateRecordTimestamp(true) > .withExecuteWithOptimisticLocking(true) // nutzt die als > 'recordTimestampFields' definierten Spalten 'modified_at' > .withReturnAllOnUpdatableRecord(true) > .withExecuteLogging(true); > > jooq = using(conn, settings); > // System.out.println(jooq.toString()); > Usersettings usersettings = new Usersettings(); > usersettings.setUsername("dominik"); > usersettings.setKey("KEY"); > usersettings.setValue("VALUE"); > > dontWorkWithoutManuallySet(usersettings); > dontWorkWithRecordMerge(usersettings); > > fix(usersettings); > } > > private static void dontWorkWithoutManuallySet(Usersettings usersettings ) > { > UsersettingsRecord dbRecord = jooq.newRecord(USERSETTINGS, usersettings); > > > jooq.insertInto(USERSETTINGS).set(dbRecord).onDuplicateKeyUpdate().set(dbRecord).execute(); > > jooq.selectFrom(USERSETTINGS).where(USERSETTINGS.USERNAME.eq("dominik")).fetch(); > //dbRecord.delete(); > } > > private static void dontWorkWithRecordMerge(Usersettings usersettings ) { > UsersettingsRecord dbRecord = jooq.newRecord(USERSETTINGS, usersettings); > dbRecord.merge(); > } > > private static void fix(Usersettings usersettings ) { > UsersettingsRecord dbRecord = jooq.newRecord(USERSETTINGS, usersettings); > dbRecord.setModifiedAt(LocalDateTime.now()); // optimistic lock > geht hier nicht > > > jooq.insertInto(USERSETTINGS).set(dbRecord).onDuplicateKeyUpdate().set(dbRecord).execute(); > > jooq.selectFrom(USERSETTINGS).where(USERSETTINGS.USERNAME.eq("dominik")).fetch(); > dbRecord.delete(); > } > > } > > -- > 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 [email protected]. > To view this discussion on the web visit > https://groups.google.com/d/msgid/jooq-user/EBF08891-9BEF-4B47-A9E6-BF048CD4D3B1%40googlemail.com > <https://groups.google.com/d/msgid/jooq-user/EBF08891-9BEF-4B47-A9E6-BF048CD4D3B1%40googlemail.com?utm_medium=email&utm_source=footer> > . > -- 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 [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAB4ELO4tP%3DJZhbmFqyyMe7WLDX0BQYXrWuS-NoP2-aEeObXjXQ%40mail.gmail.com.
