Problem with table sequence autoincrement

Support
#1

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.

#2

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.

#3

Hi Tudor,

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

#4

Exactly.

#5

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

#6

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: [url=]https://www.cuba-platform.com/discuss/t/problem-with-increment-when-changing-cuba-numberidcachesize[/url]. 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.

#7

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.