Spoon inserting into postgres yields "duplicate key value violates unique constraint"

I have a table in PosgreSQL 9.1.13 that I want to populate with data from a MSSQL. To do that I use PDI 4.4.0.

Postgres and PDI are both running in Ubuntu 13.10.

The table in Postgres is the following:

create table cobra.facultad (
    id_facultad serial primary key,
    nombre varchar(100),
    id_estado int4 references cobra.estado(id_estado),
    ultima_modificacion_usuario varchar(100),
    ultima_modificacion_fecha timestamp not null default current_timestamp
);

The steps I have are:

Table Input 1: select distinct from a MSSQL view and retrieve nombre column.
Table Input 2: select distinct from cobra.facultad table in Postgres and retrieve nombre column.
Merge Rows: merge both flows comparing by nombre and get a flagfield to determine if its new, identical or deleted.
Synchronize after merge of Postgres table including in the Update Fields only nombre column.

It tests perfect if target table is empty. However if it contained any data, I get the following exception:

2014/04/25 13:11:51 - Sync Facultad.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Because of an error, this step can't continue: 
2014/04/25 13:11:51 - Sync Facultad.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : org.pentaho.di.core.exception.KettleException: 
2014/04/25 13:11:51 - Sync Facultad.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Error inserting row into table [facultad] with values: [1], [Fac. Educación a Distancia], [new]
2014/04/25 13:11:51 - Sync Facultad.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : 
2014/04/25 13:11:51 - Sync Facultad.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Error inserting/updating row
2014/04/25 13:11:51 - Sync Facultad.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : ERROR: llave duplicada viola restricción de unicidad «facultad_pkey»
2014/04/25 13:11:51 - Sync Facultad.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :   Detail: Ya existe la llave (id_facultad)=(1).
2014/04/25 13:11:51 - Sync Facultad.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : 
2014/04/25 13:11:51 - Sync Facultad.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : 
2014/04/25 13:11:51 - Sync Facultad.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :    at org.pentaho.di.trans.steps.synchronizeaftermerge.SynchronizeAfterMerge.lookupValues(SynchronizeAfterMerge.java:484)
2014/04/25 13:11:51 - Sync Facultad.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :    at org.pentaho.di.trans.steps.synchronizeaftermerge.SynchronizeAfterMerge.processRow(SynchronizeAfterMerge.java:881)
2014/04/25 13:11:51 - Sync Facultad.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :    at org.pentaho.di.trans.step.RunThread.run(RunThread.java:50)
2014/04/25 13:11:51 - Sync Facultad.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :    at java.lang.Thread.run(Thread.java:744)
2014/04/25 13:11:51 - Sync Facultad.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Caused by: org.pentaho.di.core.exception.KettleDatabaseException: 
2014/04/25 13:11:51 - Sync Facultad.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Error inserting/updating row
2014/04/25 13:11:51 - Sync Facultad.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : ERROR: llave duplicada viola restricción de unicidad «facultad_pkey»
2014/04/25 13:11:51 - Sync Facultad.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :   Detail: Ya existe la llave (id_facultad)=(1).
2014/04/25 13:11:51 - Sync Facultad.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : 
2014/04/25 13:11:51 - Sync Facultad.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :    at org.pentaho.di.core.database.Database.insertRow(Database.java:1411)
2014/04/25 13:11:51 - Sync Facultad.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :    at org.pentaho.di.core.database.Database.insertRow(Database.java:1325)
2014/04/25 13:11:51 - Sync Facultad.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :    at org.pentaho.di.trans.steps.synchronizeaftermerge.SynchronizeAfterMerge.lookupValues(SynchronizeAfterMerge.java:142)
2014/04/25 13:11:51 - Sync Facultad.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :    ... 3 more
2014/04/25 13:11:51 - Sync Facultad.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Caused by: org.postgresql.util.PSQLException: ERROR: llave duplicada viola restricción de unicidad «facultad_pkey»
2014/04/25 13:11:51 - Sync Facultad.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :   Detail: Ya existe la llave (id_facultad)=(1).
2014/04/25 13:11:51 - Sync Facultad.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2077)
2014/04/25 13:11:51 - Sync Facultad.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1810)
2014/04/25 13:11:51 - Sync Facultad.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
2014/04/25 13:11:51 - Sync Facultad.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:498)
2014/04/25 13:11:51 - Sync Facultad.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:386)
2014/04/25 13:11:51 - Sync Facultad.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:332)
2014/04/25 13:11:51 - Sync Facultad.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :    at org.pentaho.di.core.database.Database.insertRow(Database.java:1360)
2014/04/25 13:11:51 - Sync Facultad.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :    ... 5 more

This means that even if column id_facultad was not specified for insert/update, spoon tries to insert it with value 1.

The weirdest thing is, if I run the test a second time, I get the same error, but now with value 2. I get the same error until it reaches what should be the table's primary key sequence nextval.

Naturally the next thing I tried is the Add Sequence step. First I configured it to read the next value of the sequence, then I placed it between Merge and Sync steps and finally added id_facultad column to be inserted/updated.

The result was the same... Add Sequence doesn't read the sequence at all.. it starts with 1 and increments on subsequent runs.

I have nothing left to say but HELP!

Thanks!

Solution

The SERIAL type just means that id_facultad is pointing to a sequence which starts at 1. Every time a value is retrieved from that sequence, the next value is incremented.

If you insert your own values for id_facultad, then it becomes your responsibility to update the sequence to a next unused number. Try:

ALTER SEQUENCE id_facultad_seq RESTART WITH (
    SELECT 1+MAX(id_facultad)
    FROM cobra.facultad
);