returning a value with jOOQ and PostgreSQL serial and RETURNING

[Garret Wilson]

I tried to post this earlier but it disappeared; sorry if it shows up twice. I also posted it elsewhere.

I have a PostgreSQL `uris` table with serial (autoincrementing) `uri_id` column and string `uri` column. I can query the table fine using jOOQ:

    createDSLContext().select(fieldByName("uri_id")).from(tableByName("uris"))
        .where(fieldByName("uri").equal(uri.toString())).fetchOne(0, Integer.class))

That returns a Java `Integer`. But when I insert a new URI, I want to get back the generated `uri_id` key, so I try this:

    createDSLContext().insertInto(tableByName("uris"), fieldByName("uri"))
        .values(uri.toString()).returning(fieldByName("uri_id")).fetchOne().getValue(0, Integer.class)

This time I get an error:

    Exception in thread "main" java.lang.IllegalArgumentException: Field 0 is not contained in list

Just as a test, I tried supplying a literal value for `uri_id` in the `INSERT` statement, but still got the error.

It looks like the correct SQL is being generated:

    insert into "uris" ("uri") values ('http://example.com/') returning "uri_id"

But the returned record is empty. This is true even when I specify a literal `uri_id` in the insert statement.

How can I retrieve an auto-generated column from a PostgreSQL `INSERT` statement using jOOQ?

[Garret Wilson]

Everyone, I really need an answer to this quickly. In another table I had a workaround in that I would query the URI value, which was unique, and it would return me the ID. Very inefficient, but workable.

But now I have another table that does not have any unique values other than the generated ID. So if RETURNING doesn't work, how can I find out the ID that was generated?

I should be able to use CURRVAL, and indeed DSLContext seems to have a currval(), but it requires a Sequence. Where do I get a sequence? I don't seem to be able to do this:

...sequenceByName("entity_id")

It seems there is also a LASTVAL, but DSLContext doesn't have a lastVal(). It has a lastID(), but the documentation says it is not supported on PostgreSQL.

So I'm stuck. Why is something so simple so hard?

[Garret Wilson]

Arg, does no one have any hints? This is killing me!

I try to manually execute CURRVAL after an insert:

dslContext.resultQuery("select CURRVAL(uriid)").fetch()

That gives me: ERROR: column "uriid" does not exist

So I try to indicate a table:

dslContext.resultQuery("select CURRVAL(uriid) from uris").fetch()

I get: ERROR: could not open relation with OID 1

Maybe it's because jOOQ automatically closed the connection after the previous INSERT.

I think I'm going to have to throw in the towel for the day. I need some direction here.

I just need the generated sequence value. I'm getting the feeling that with normal SQL this would be a piece of cake. Why is jOOQ making it harder than SQL?

[Estevão Freitas]

I think you didn't read the manual. Read chapter 6 of jooq manual and you see how generate tables and sequences.

Sent with MailTrack

Estevão de Freitas Góes
------------------------------------
Analista de Sistemas
Bel. Ciências da Computação (UFMA 2010)

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Garret Wilson]

On Tuesday, April 15, 2014 9:07:11 PM UTC-3, Estevão Góes wrote:

I think you didn't read the manual. Read chapter 6 of jooq manual and you see how generate tables and sequences.

Thanks for the reply, Estevão, but you may have missed the fact that I'm not using jOOQ in generated-class mode. Thus chapter 6 is entirely irrelevant to this discussion. You might want to read section 3.3.1 and chapter 4 of the jOOQ manual.

In any case, my use of returning() should be completely independent of whether I am using generated classes, and should return a non-empty result.
 

[Lukas Eder]

Hi guys

(a lot of mails to catch up with)

Garret, you're running into this issue here:

https://github.com/jOOQ/jOOQ/issues/2374

We're hoping to finally fix this for jOOQ 3.4 (and then merge to 3.3 and 3.2), but we cannot promise this, as identifying an IDENTITY column at runtime without relevant information from generated tables is a bit tricky.

Everyone, I really need an answer to this quickly.

If time is of the essence, then let me suggest our support services, which we're offering at competitive rates to customers running projects on tight schedules:

http://www.jooq.org/support

Do note also that a basic support package is included in the jOOQ Professional Edition and jOOQ Enterprise Edition licenses. Both licenses can also be purchased if you're using jOOQ with an Open Source database.

 

In another table I had a workaround in that I would query the URI value, which was unique, and it would return me the ID. Very inefficient, but workable.

But now I have another table that does not have any unique values other than the generated ID. So if RETURNING doesn't work, how can I find out the ID that was generated?

I should be able to use CURRVAL, and indeed DSLContext seems to have a currval(), but it requires a Sequence. Where do I get a sequence? I don't seem to be able to do this:

...sequenceByName("entity_id")

You're right, this method should be added for API completeness. I have added a feature request for this:

https://github.com/jOOQ/jOOQ/issues/3189

As a workaround, you can instanciate the internal (but public) type org.jooq.impl.SequenceImpl instead.

 

It seems there is also a LASTVAL, but DSLContext doesn't have a lastVal(). It has a lastID(), but the documentation says it is not supported on PostgreSQL.

I wasn't aware of LASTVAL(), but you can probably use currval() on your sequence. The org.jooq.Sequence type provides a currval() method.

I try to manually execute CURRVAL after an insert:

dslContext.resultQuery("select CURRVAL(uriid)").fetch()

That gives me: ERROR: column "uriid" does not exist

CURRVAL() takes a sequence name as a string (or more precisely as a regclass type):

http://www.postgresql.org/docs/9.3/static/functions-sequence.html

If uriid is your sequence name, try CURRVAL('uriid') instead, if you want to do this yourself with plain SQL.

 

So I try to indicate a table:

dslContext.resultQuery("select CURRVAL(uriid) from uris").fetch()

I get: ERROR: could not open relation with OID 1

Maybe it's because jOOQ automatically closed the connection after the previous INSERT.

jOOQ doesn't automatically close any connections.

 

I think I'm going to have to throw in the towel for the day. I need some direction here.

I just need the generated sequence value. I'm getting the feeling that with normal SQL this would be a piece of cake. Why is jOOQ making it harder than SQL?

Garret, I can understand that you may have been frustrated with the issues that you have encountered. Yes, there is an open issue #2374 preventing you from fetching IDs. Yes, there are some peculiarities in PostgreSQL syntax. Yes, we may have made a bad decision in early product stages regarding dependency management (jOOU, etc.). This is software. It has issues, and we're working very hard to not only fix these issues but to make the jOOQ experience a most enjoyable one for all of our users and customers.

We strongly believe that there is currently no other SQL API on the market that:

- Adds as much value to customers as jOOQ does

- Takes SQL as seriously as jOOQ does

- Has as many features as jOOQ does

I'm more than happy to point out some of the many benefits you will get down the line of your project, once that project matures, *because* you are using an internal DSL that builds an AST representation of your SQL statement in memory. These benefits include things like multi-tenancy, row-level security, optimistic locking, SQL AST transformation, and much more.

But these benefits might only be noticeable *down the line*, which means that I hope you will stay with us and that you will be patient with us.

Looking forward to hearing from you,

Lukas

[Lukas Eder]

I should be able to use CURRVAL, and indeed DSLContext seems to have a currval(), but it requires a Sequence. Where do I get a sequence? I don't seem to be able to do this:

...sequenceByName("entity_id")

You're right, this method should be added for API completeness. I have added a feature request for this:

https://github.com/jOOQ/jOOQ/issues/3189

As a workaround, you can instanciate the internal (but public) type org.jooq.impl.SequenceImpl instead.

This is implemented on GitHub master and will be included in jOOQ 3.4.

Cheers

Lukas 

[Garret Wilson]

On Wednesday, April 16, 2014 6:05:07 AM UTC-3, Lukas Eder wrote:

As a workaround, you can instanciate the internal (but public) type org.jooq.impl.SequenceImpl instead.

I had already attempted this, but doesn't the constructor require all sort of higher order classes like schemas and such, forcing me to construct classes for additional entities in the database?

[Lukas Eder]

Hi Garret,

An example implementation of such a workaround can be seen here, in the commit of our recent implementation of DSL.sequenceByName():

https://github.com/jOOQ/jOOQ/commit/c915b67eda1afd933d8e30901dca18b96d7e549a#diff-71e1fb4ed47f60931aed360e82f98823R5082

Hope this helps

Lukas

[Garret Wilson]

WORKAROUND SOLVED!

As Lukas pointed out, this is a jOOQ bug. In the meantime, I can indeed use CURRVAL(), but I have to know what I'm doing, and until now, I didn't know what I was doing.

If I create a serial in table `uris` named `uriid`, I'm really creating a sequence named `uris_uriid_seq`, and this I didn't understand. To use CURRVAL(), I have to specify the name of the sequence, not the column name; that is:

dslContext.resultQuery("select CURRVAL('\"uris_uriid_seq\"')").fetch(0, Integer.class)

Note that one must be very, very careful with the single and double quotes; the best way is to have double quotes inside single quotes to prevent case changes.

[Garret Wilson]

On Wednesday, April 16, 2014 11:33:33 AM UTC-3, Lukas Eder wrote:

An example implementation of such a workaround can be seen here, in the commit of our recent implementation of DSL.sequenceByName():

https://github.com/jOOQ/jOOQ/commit/c915b67eda1afd933d8e30901dca18b96d7e549a#diff-71e1fb4ed47f60931aed360e82f98823R5082

After correcting my understanding of sequence vs serial column (see my other reply), I was finally able to get jOOQ's currval() to work using the SequenceImpl workaround:

dslContext.currval(new SequenceImpl<Integer>("uris_uriid_seq", null, getDataType(Integer.class)))

Thank you so much! Now I have to do much frenzied coding to try to get back on track after this entire day lost...

[Garret Wilson]

Lukas, you might think of adding a utility function that determines a sequence name from its table name and serial column name, as PostgreSQL generates sequence names automatically in the background. Just a suggestion. Thanks.

[Lukas Eder]

Hi Garret,

Glad to know that the workaround works for you and that you can be productive again!

2014-04-16 17:11 GMT+02:00 Garret Wilson <gar...@globalmentor.com>:

Lukas, you might think of adding a utility function that determines a sequence name from its table name and serial column name, as PostgreSQL generates sequence names automatically in the background. Just a suggestion. 

Note that the PostgreSQL serial type is merely DDL syntactic sugar for 

DEFAULT nextval('sequence_name'::regclass)

You can have more complex DEFAULT expressions in your DDL statement (e.g. combining the value of two sequences, or hashing sequence values). You can also have several sequence-generated values, or you can generate sequence values on INSERT using triggers (which is what you'd do in Oracle). Other databases don't support sequences, but IDENTITY or AUTO_INCREMENT columns.

Eventually, there are so many features that I think the default case of correlating auto-generated sequence name to the underlying table name might just not be very reliable.

Note, if you were using the code generator, then much of this information would really be available on your tables - e.g. the identity column, in this case.

Cheers

Lukas

[Garret Wilson]

On Wednesday, April 16, 2014 12:42:54 PM UTC-3, Lukas Eder wrote:

...

Note, if you were using the code generator, then much of this information would really be available on your tables - e.g. the identity column, in this case.

Yes, I want to use the code generator. But for this (aggressive) iteration of our product I wanted to as quickly as possible show that it was viable to use jOOQ. I figured I could get the same functionality and save lots of time by not figuring out how to set up classpaths and build scripts and integrate the generator into Maven, etc. I just wanted a quick-and-straightforward way I could test and prove jOOQ, and then if it worked I could come back in the next iteration and set up code generation going forward.

I'll have to admit the results were mixed: not only was a major, useful feature broken (RETURNING), the workaround (curval()) wasn't implemented without doing yet another workaround (SequenceImpl). In hindsight maybe it would have been quicker to learn how to generate the classes. But that is only because of jOOQ limitations---how was I supposed to know the features I needed didn't work without class generation? Anyway, that's the story. Back to coding. Thanks again for the help.

[Lukas Eder]

We generally always recommend to use the code generator. You'll get 100s of features that you don't have without it. It's not too late. You won't regret it, I promise.

The main reason for those many tableByName() methods and similar is that some users may simply not know the schema beforehand.

Cheers

Lukas

[Garret Wilson]

On Wednesday, April 16, 2014 9:00:07 AM UTC-7, Lukas Eder wrote:

...

We generally always recommend to use the code generator. You'll get 100s of features that you don't have without it. It's not too late.

OK, you convinced me.
 

You won't regret it, I promise.

Um... I'm not so sure about that.