Yugabyte-db: Optimistic locking fails if "change detection" column is of type timestamp or timestamptz

[jk-1]

Optimistic locking fails if "change detection" column is of type timestamp or timestamptz but works fine when type is int

I use JOOQ https://www.jooq.org to generate db layer code from sql tables.

Used JOOQ version is 3.12.4
Yugabyte version is 2.1.4.0
ysqlsh version is psql (PostgreSQL) 11.2-YB-2.1.4.0-b0

Details of the defect and workaround are already documented as stackoverflow question and answer here:
https://stackoverflow.com/questions/62346175/jooq-optimistic-locking-and-datachangedexception-database-record-has-been-chang

JOOQ debugging output shows the generated sql code so that you can probably repeat the error without installing JOOQ.
JOOQ setting .withReturnAllOnUpdatableRecord(true) means that if db generates or modifies any values during insert/update, these values are returned in result record. Example: if db generates id value from sequence during row insert, JOOQ assigns this id value into the returned record into field record.id.

[ddorian]

Hi @jk-1

Is it possible to set verbose logging for JOOQ so we can see all the queries that are executed on all sessions ?

Or add gflag --ysql_log_statement=all to yb-tserver and attach the logs here.

[jk-1]

How can I add this --ysql_log_... flag if I start the cluster with yb-ctl start? yb-ctl does not accept that flag.

[ddorian]

You can do that using --tserver_flags

[jk-1]

yugabyte-defect-report-01.txt

JOOQ trace and yugabyte postgresql log dumps are in the attached file.

[rkarthik007]

yugabyte-defect-report-01.txt

JOOQ trace and yugabyte postgresql log dumps are in the attached file.

cc @m-iancu @ndeodhar