Problem with table sequence autoincrement

Hi,

Recently I encountered a quite annoying problem.

I have a standard browser-editor screen for updating a postgresql table with long autoinc id.
From time to time, when a user wants to add a new row into the table I get a PK violation error.
It happened for example from one specific PC, but from another PC it worked fine.
I checked the current sequence value for that table, which was 29 at the time the error happened. The property: cuba.numberIdCacheSize has it’s default value 100. After restarting tomcat the user could insert the new row without error (the sequence value was incremented with 100).
Is it a problem with the sequence value stored in cache? Should I change the property: cuba.numberIdCacheSize to 1?
Did you encounter this kind of issue?

Thanks.

Edit: The current sequence value for that table was not 29, at the time the error happened. It was 100.
29 was the number of already existing rows.

Hi Tudor,

Do you have a standard deployment with one Tomcat working with the database, and multiple users working via web interface?

Exactly.

Could you provide the DDL of the table (the part defining primary key field is enough) and entity source (omitting domain attributes)?

the DDL:


create table TORTURI_MAGAZINE (
    ID bigint,
    UUID uuid,
    VERSION integer not null,
    UPDATE_TS timestamp,
    UPDATED_BY varchar(50),
    CREATE_TS timestamp,
    CREATED_BY varchar(50),
    --
    COD varchar(3) not null,
    DENUMIRE varchar(30) not null,
    ADRESA varchar(50) not null,
    EMAIL varchar(60),
    --
    primary key (ID)
)^

-- unique indexes

create unique index IDX_TORTURI_MAGAZINE_UK_COD on TORTURI_MAGAZINE (COD) ^

and the entity source:


@NamePattern("%s %s|denumire,email")
@Table(name = "TORTURI_MAGAZINE")
@Entity(name = "torturi$Magazine")
public class Magazine extends BaseLongIdEntity implements HasUuid, Versioned, Updatable, Creatable {
    private static final long serialVersionUID = -8769324859242655139L;

    @Column(name = "COD", nullable = false, unique = true, length = 3)
    protected String cod;

    @Column(name = "DENUMIRE", nullable = false, length = 30)
    protected String denumire;

    @Column(name = "ADRESA", nullable = false, length = 50)
    protected String adresa;

    @Column(name = "EMAIL", length = 60)
    private String email;

    @Column(name = "UUID")
    private UUID uuid;

    public void setEmail(String email) {
        this.email = email;
    }

    public String getEmail() {
        return email;
    }

    @Override
    public void setUuid(UUID uuid) {
        this.uuid = uuid;
    }

    @Override
    public UUID getUuid() {
        return uuid;
    }


    public void setCod(String cod) {
        this.cod = cod;
    }

    public String getCod() {
        return cod;
    }

    public void setDenumire(String denumire) {
        this.denumire = denumire;
    }

    public String getDenumire() {
        return denumire;
    }

    public void setAdresa(String adresa) {
        this.adresa = adresa;
    }

    public String getAdresa() {
        return adresa;
    }

}

In the past I have changed cuba.numberIdCacheSize only in the web-tier from 100 to another value (10). And made some test with this configuration, but it didn’t work. See this post: https://www.cuba-platform.com/discuss/t/problem-with-increment-when-changing-cuba-numberidcachesize. After that, I gave it up and set it back to it’s default value 100. Maybe it has to do with this.

It’s very weird because it doesn’t happen all the time.

Thank you for the explanation. We’ll try to find out the reason a bit later. Please let us know if you have any additional details.