TEIID 30357 nextval does not exist

[cm.kumar]

Hello,

How to insert data to postgres table which has primary key column with sequence associated ?

VDB is dynamic created out of native model of postgres database. Trying to insert via http post method by passing information in the body.

Postgres table has a id column with serial data type (which auto creates sequence and associate nextval(sequence) to the id column).

Tried inserting with or without id column, throws "TEIID 30357 nextval does not exist" error.

Was able to insert data, when sequence is removed from id column. This is to check whether http post method was able to insert and it does.

But need to work with sequence applied? Any pointers?

Thanks.

[shawkins]

What is the user sql you are issuing?

If the id column in the database has the default value set to the sequence, then you should just omit the id column from your insert.

If you need access to the sequence, it can be modeled as a function or imported automatically as one in Teiid 10.0+ [TEIID-1131] Document/expand sequence support - JBoss Issue Tracker

[cm.kumar]

Hello Steven,

Even if I don't use the nextval column for insert, the error is thrown out as such.

Attached VDB used and error log.

The approach I used to insert the data is using http 'POST' method,

URL - http://serverName:8080/dsdataserver/dsl.svc/DSPIConfig/1/DSPIConfig-ConfigSrc/ALL_PROJECTS/app_user_config

Body -

{

"collection_id":4,

"config_name":"testsummary",

"user_id":"user1",

"config_value": "test"

}

My postgres table for app_user_config is like,

CREATE TABLE public.app_user_config

(

  id integer NOT NULL DEFAULT nextval('app_uc_1_id_seq'::regclass),

  config_name character varying(255) NOT NULL,

  config_value text NOT NULL,

  collection_id integer,

  user_id character varying(255) NOT NULL,

  user_config_key character varying(255),

  CONSTRAINT uc_id PRIMARY KEY (id),

  CONSTRAINT uc_cname_user UNIQUE (config_name, user_id)

)

Regards,

Chandra

[shawkins]

It looks like eclipselink is sitting over teiid and has been configured to use a sequence identity generator.  [TEIID-5074] Support a way provide SEQUENCE in TeiidDialect - JBoss Issue Tracker together with the changes from TEIID-1131 make that work automatically when using hibernate and the major databases that support sequences.  But the automatic support is not there yet for eclipselink.  Given that your platform has been configured to expect a stored procedure by the name of nextval, you could create one on your postgresql model:

create foreign procedure nextval (seq_name string) returns long options ("teiid-rel:native-query" 'select nextval($1)')

[cm.kumar]

Thanks. At least i'm able to get the sequence next number and pass it on.

[shawkins]

Please capture an issue if there is way to build in better sequence handling to our eclipse link TeiidPlatform.