[teiid-dev] PostgreSQL Sequences

[Michael Walker]

Hi Jimmy,

Looks like you're using Hibernate + Teiid. As you've discovered, you won't be able to use Hibernate's sequence-based generator with Postgres. I was recently looking into the same issue (http://community.jboss.org/thread/153074?tstart=0). Besides the fact that sequences aren't exposed by Teiid, you'd also run into problems if sequences from two federated data sources overlap, since it could cause duplicate primary keys. In general, you'll want to avoid using the sequence-based strategy.

A simple alternative would be to change the generator strategy to 'native', and ensure you are using the Teiid Dialect for Hibernate in your Hibernate configuration. Hibernate will then use the hilo strategy, which does not rely on database sequences. 'Native' is intended to be more portable than the 'sequence'-based strategy -- see the Hibernate docs (http://docs.jboss.org/hibernate/stable/core/reference/en/html_single/#mapping-declaration-id) for details.

If you want to get fancy, you can use their enhanced generator strategies (http://docs.jboss.org/hibernate/stable/core/reference/en/html_single/#mapping-declaration-id-enhanced) -- the TableGenerator will also work nicely with Teiid. 

One final consideration is legacy data that must continue to support applications that create new records outside of Hibernate. In that case, you'll need to ensure that the PKs continue to be unique, regardless of which system is creating them. The trump strategy to consider in this case is the UUID strategy. Hope this helps.

--Mike


________________________________________
From: teiid-dev-bounces at lists.jboss.org [teiid-dev-bounces at lists.jboss.org] On Behalf Of Schappet, James C [james-schappet at uiowa.edu]
Sent: Tuesday, June 22, 2010 8:27 AM
To: Steven Hawkins
Cc: teiid-dev at lists.jboss.org
Subject: Re: [teiid-dev] PostgreSQL Sequences

I guess short term, I would like to see support for the nextval and curval functions.

With Postgres it is possible to also support an mysql style auto increment.


Either one of these would work.





What I am getting right now is a hibernate error, if I use the Teiid Dialect:
org.hibernate.MappingException: Dialect does not support identity key generation



Here is the SQL I am using to create the table in Postgres:

CREATE TABLE labbook.person
(
  person_id serial NOT NULL,
  first_name text NOT NULL,
  last_name text NOT NULL,
  last_modified_by text,
  last_modified timestamp without time zone,
  CONSTRAINT person_pkey PRIMARY KEY (person_id)
);

ALTER TABLE labbook.person ADD COLUMN person_id integer;
ALTER TABLE labbook.person ALTER COLUMN person_id SET NOT NULL;
ALTER TABLE labbook.person ALTER COLUMN person_id SET DEFAULT nextval('labbook.person_person_id_seq'::regclass);



--
James Schappet
james-schappet at uiowa.edu<mailto:james-schappet at uiowa.edu>



On Jun 21, 2010, at 2:51 PM, Steven Hawkins wrote:

Hi Jimmy,

What type of sequence support are you looking for?  Full language support?

For Oracle, in designer a sequence may be modeled as a table with a name in source of dual and columns with the name in source set to <sequence name>.[nextval|currentval].  You can use a sequence as the default value for insert columns by setting the column to autoincrement and the name in source to <element name>:SEQUENCE=<sequence name>.<sequence value>.  The latter is more of a workaround though, rather than a good solution.

It seems like we would need to extend that support for PostgreSQL and other databases supporting sequences.  We should also think about getting sequence information as part of dynamic vdb metadata so this isn't a manual exercise.

For documentation we're always looking for more wiki content, examples, etc.  The newer documents, like the admin guide, may be a little lite.  So if you see anything omitted, let us know.

Steve

----- Original Message -----
From: "James C Schappet" <james-schappet at uiowa.edu<mailto:james-schappet at uiowa.edu>>
To: teiid-dev at lists.jboss.org<mailto:teiid-dev at lists.jboss.org>
Sent: Monday, June 21, 2010 8:54:47 AM GMT -06:00 US/Canada Central
Subject: [teiid-dev] PostgreSQL Sequences

Quick question:  Will teiid ever support sequences in postgres or oracle?


BTW, Theres lots of good work going on here -- CR2 looks good.

Is there some documentation I could help writing?

--Jimmy
--
James Schappet
james-schappet at uiowa.edu<mailto:james-schappet at uiowa.edu>




_______________________________________________
teiid-dev mailing list
teiid-dev at lists.jboss.org
https://lists.jboss.org/mailman/listinfo/teiid-dev