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.
