Default ID value on new record is inserted when optimistic locking is enabled

[jnfeinstein]

I believe the issue occurs here. The version is incremented prior to deciding if the ID should be included. This results in the record being considered not new for purposes of including the ID.

CREATE TABLE account (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    version INTEGER NOT NULL
);

@Table
data class Account(
    @Id var id: Long = 0L,
    var name: String = "",
    @Version val version: Long = 0L,
)

org.springframework.dao.DataIntegrityViolationException: executeMany; SQL [INSERT INTO account (id, name, version) VALUES ($1, $2, $3)]; duplicate key value violates unique constraint "account_pkey"; nested exception is io.r2dbc.postgresql.ExceptionFactory$PostgresqlDataIntegrityViolationException: [23505] duplicate key value violates unique constraint "account_pkey"

[mp911de]

This works as expected. We include all non-null fields in the INSERT statement when inserting an object. In your case, the @Id field is declared as non-null long hence its value cannot be null. If the identifier field is null then it won't be included in the INSERT statement. The reference documentation is pretty clear about this behavior.

[jnfeinstein]

Please see here:

Spring Data R2DBC does not attempt to insert values of identifier columns when the entity is new and the identifier value defaults to its initial value. That is 0 for primitive types and null if the identifier property uses a numeric wrapper type such as Long.

I've confirmed that the behavior is correct when @Version is not present.

[mp911de]

Thanks for the hint and revisiting this topic, it qualifies as an actual bug. I think it requires a bit of investigation on how we want to approach the issue. We will need to post-process OutboundRow in some sense. Probably removing the Id from OutboundRow if its value maps to a primitive default seems the better way to go. We also should link the ID generation section with the section regarding inserts as these topics are related to each other.