onDuplicateKeyUpdate with records and optimistic locking

[Dominik Hirt]

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();

}

}

[Lukas Eder]

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

--
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.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/EBF08891-9BEF-4B47-A9E6-BF048CD4D3B1%40googlemail.com.