DbUnit does not update postgresql sequences on insert

Question

I am using DbUnit to run some test on a postgreSql database. In order to be able to run my test, I bring the database into a well known state by repopulating the database tables before each test, running a clean insert. Therefore I use the FlatXmlDataSet definition below (compare with the attached SQL schema).

However, if I run the testCreateAvatar() test case, I get an exception because of a status code mismatch, which is caused by a failed sql insert, because of an already existing primary key (id field). A look into my database shows me, that the insert of the test datasets does not update the corresponding *avatars_id_seq* and *users_id_seq* sequence tables, which are used to generate the id fields (mechanism of postgresql to generate auto-increment values).

That means, that the auto-increment value is not updated, if I define static IDs in the FlatXmlDataSet definitions. So my question is how I could change this behavior or set the auto-increment value on my own (using DbUnit).

Avatar creation test case

@Test
public void testCreateAvatar() throws Exception {
    // Set up the request url.
    final HttpPost request = new HttpPost(
            "http://localhost:9095/rest/avatars");

    // Setup the JSON blob, ...
    JSONObject jsonAvatar = new JSONObject();
    jsonAvatar.put("imageUrl", "images/dussel.jpg");

    // ... add it to the post request ...
    StringEntity input = new StringEntity(jsonAvatar.toString());
    input.setContentType("application/json");
    request.setEntity(input);

    // ... and execute the request.
    final HttpResponse response = HttpClientBuilder.create().build()
            .execute(request);

    // Verify the result.
    assertThat(response.getStatusLine().getStatusCode(),
            equalTo(HttpStatus.SC_CREATED));

    // Fetch dussel duck from the database ...
    Avatar dussel = getServiceObjDao().queryForFirst(
                getServiceObjDao().queryBuilder().where()
         .eq("image_url", "images/dussel.jpg")
         .prepare());

    // ... and verify that the object was created correctly.
    assertThat(dussel, notNullValue());
    assertThat("images/dussel.jpg", equalTo(dussel.getImageUrl()));
}

The DbUnit dataset

<?xml version='1.0' encoding='UTF-8'?>
<dataset>
   <!-- Avatars -->
   <avatars 
      id="1" 
      image_url="images/donald.jpg" />
   <avatars 
      id="2" 
      image_url="images/daisy.jpg" />

   <!-- Users -->
   <users 
      id = "1"
      name = "Donald Duck"
      email = "donald.duck@entenhausen.de"
      password = "quack" />
   <users 
      id = "2"
      name = "Daisy Duck"
      email = "daisy.duck@entenhausen.de"
      password = "flower" />
</dataset>

The users and avatars table schema

CREATE TABLE avatars (
   id BIGSERIAL PRIMARY KEY,
   cdate TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
   mdate TIMESTAMP,
   image_url VARCHAR(200),
   UNIQUE (image_url)
);

CREATE TABLE users (
   id BIGSERIAL PRIMARY KEY,
   cdate TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
   mdate TIMESTAMP,
   name VARCHAR(160) NOT NULL,
   email VARCHAR (355) UNIQUE NOT NULL,
   password VARCHAR(30) NOT NULL,
   avatar_id BIGINT,
   UNIQUE (name),
   CONSTRAINT user_avatar_id FOREIGN KEY (avatar_id)
      REFERENCES avatars (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
);

Answer 1

The function below finds all sequences in a database, extracts the name of the corresponding table from the sequence name and finally updates the current value of the sequences based on the maximum id value in the corresponding table. As there has been no better solution yet, this seems to be the way to go. Hope, this helps someone.

Simple solution based on harmic's suggestion

@Before
public void resetSequence() {
    Connection conn = null;
    try {
        // Establish a database connection.
        conn = DriverManager.getConnection(
                this.props.getProperty("database.jdbc.connectionURL"),
                this.props.getProperty("database.jdbc.username"), 
                this.props.getProperty("database.jdbc.password"));

        // Select all sequence names ...
        Statement seqStmt = conn.createStatement();
        ResultSet rs = seqStmt.executeQuery("SELECT c.relname FROM pg_class c WHERE c.relkind = 'S';");

        // ... and update the sequence to match max(id)+1.
        while (rs.next()) {
            String sequence = rs.getString("relname");
            String table = sequence.substring(0, sequence.length()-7);
            Statement updStmt = conn.createStatement();
            updStmt.executeQuery("SELECT SETVAL('" + sequence + "', (SELECT MAX(id)+1 FROM '" + table + "'));");
        }
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        try {
            conn.close();
        } catch (SQLException e) {
        }
    }
}

Answer 2

You can set the value of a sequence using setval, for example

SELECT SETVAL('sequence_name', 1000);

Where sequence_name is the name of the sequence, visible in psql using /dt on the table, and 1000 is the value you want to set it to. You would probably want to set it to the Max value of Id in the table.

What I don't really know is how to get DbUnit to emit this SQL.