Hitachi Vantara Pentaho Community Forums
Results 1 to 9 of 9

Thread: Insert into dimension on portgresql: return of autogenerated keys is not supported

  1. #1
    Join Date
    Jul 2007
    Posts
    2,498

    Question Insert into dimension on portgresql: return of autogenerated keys is not supported

    Hello.

    It's the first time I use the dimension insert/update step; I'm just doing a table input-> dimension update in postgres. I chose a sequence for my tech key, but I still get this error.

    Tips appreciated and thanks in advance

    Code:
    2007/10/10 18:55:04 - Dimension lookup/update.0 - Starting to run...
    2007/10/10 18:55:05 - Dimension lookup/update.0 - ERROR (version 3.0.0-RC1, build 299003 from 2007/10/01 23:14:52) : Because of an error this step can't continue: 
    2007/10/10 18:55:05 - Dimension lookup/update.0 - ERROR (version 3.0.0-RC1, build 299003 from 2007/10/01 23:14:52) : Unable to prepare dimension insert :
    2007/10/10 18:55:05 - Dimension lookup/update.0 - ERROR (version 3.0.0-RC1, build 299003 from 2007/10/01 23:14:52) : INSERT INTO bioclients( bioclientspk, version, date_from, date_to, id, id_fornecedor_unique, nome, morada, telefone, ncontr, fax, codpostal, localidade, contacto, email, idprimavera, "zone", zonename, groupid, groupname, periodicidade, closedon, district, obs, council, disabled, driverid, drivername, externalcode, hasprotocol) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )
    2007/10/10 18:55:05 - Dimension lookup/update.0 - ERROR (version 3.0.0-RC1, build 299003 from 2007/10/01 23:14:52) : Returning autogenerated keys is not supported.
    2007/10/10 18:55:05 - Dimension lookup/update.0 - ERROR (version 3.0.0-RC1, build 299003 from 2007/10/01 23:14:52) : org.pentaho.di.core.exception.KettleDatabaseException: 
    2007/10/10 18:55:05 - Dimension lookup/update.0 - ERROR (version 3.0.0-RC1, build 299003 from 2007/10/01 23:14:52) : Unable to prepare dimension insert :
    2007/10/10 18:55:05 - Dimension lookup/update.0 - ERROR (version 3.0.0-RC1, build 299003 from 2007/10/01 23:14:52) : INSERT INTO bioclients( bioclientspk, version, date_from, date_to, id, id_fornecedor_unique, nome, morada, telefone, ncontr, fax, codpostal, localidade, contacto, email, idprimavera, "zone", zonename, groupid, groupname, periodicidade, closedon, district, obs, council, disabled, driverid, drivername, externalcode, hasprotocol) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )
    2007/10/10 18:55:05 - Dimension lookup/update.0 - ERROR (version 3.0.0-RC1, build 299003 from 2007/10/01 23:14:52) : Returning autogenerated keys is not supported.
    2007/10/10 18:55:05 - Dimension lookup/update.0 - ERROR (version 3.0.0-RC1, build 299003 from 2007/10/01 23:14:52) :
    Pedro Alves
    Meet us on ##pentaho, a FreeNode irc channel

  2. #2
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    We turned on auto-generated key support in Postgres, but that support is only partially implemented on the database side.
    It's fine for inserts, but if you want to grab the value of the inserted key, the JDBC driver complains about it.

    It's wiser and cleaner to specify (and create) a SEQUENCE name to use.
    Postgres uses sequences internally and doesn't really have the concept of auto-generated keys.

    HTH,

    Matt

  3. #3
    Join Date
    Jul 2007
    Posts
    2,498

    Default

    Quote Originally Posted by MattCasters View Post
    It's wiser and cleaner to specify (and create) a SEQUENCE name to use.

    Matt

    But I did, matt. I created a sequence to use and I still get that error
    Pedro Alves
    Meet us on ##pentaho, a FreeNode irc channel

  4. #4
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    Can you send or attach the transformation?

  5. #5
    Join Date
    Jul 2007
    Posts
    2,498

    Default

    sent in attachment
    Attached Files Attached Files
    Pedro Alves
    Meet us on ##pentaho, a FreeNode irc channel

  6. #6
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    Thanks! I've just re-installed PG 8.2 so I'll do some tests myself as well.

  7. #7
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    Confirmed and fixed the problem for RC2 : http://jira.pentaho.org/browse/PDI-288

  8. #8
    Join Date
    May 2009
    Posts
    4

    Default Add sequence does not fully replace return auto-generated key

    Add sequence generates a new sequence while I am looking for the key/sequence of a row that just got inserted (which I believe is what “return auto-generated key” does).

    I am inserting into two Postgres tables A and B from a single CSV file. A has a primary key of type SERIAL (DB auto-increment), which B uses as a foreign key. I have one step inserting into A, followed by an “add sequence” step of the primary key of A with the "use DB to generate the sequence" option, followed by another step inserting into B. Unfortunately, the sequence numbers got added and inserted into B are newly generated (vs. already generated ones in A, which are what I want).
    So is there a way to get the sequence of a row that just got inserted or am I totally doing this wrong I am a newbie.

    I suppose I could try the "use the transformation counter to generate sequence" option but that's not desirable as that sequence number is not linked across different loads.

  9. #9
    Join Date
    May 2009
    Posts
    4

    Default

    I think I figured it out. The correct flow should be to add sequence first, then followed by the steps of inserting A and inserting B in parallel with the sequence number.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.