Getting DEFAULT values to work in a batchInsert() mode?

[Daniel Einspanjer]

I have a collection of tuples that need to be inserted into a table as new records.

I was trying to create a List of TableRecord objects built off of these Tuples via MYTABLE.newRecord() and then insert them with batchInsert(), but the fields I am not setting are getting sent to Postgres 9.6 as NULL instead of DEFAULT which causes a constraint violation.

I tried calling record.setValue(MYTABLE.ACOLUMN, DSL.defaultValue(MYTABLE.ACOLUMN)), but that gives a compile error that the type for T can't be determined.

I am currently trying to switch to using insertInto().columns(<just the ones I have>) and then looping over the records to build a series of .values() calls, but it seems like I have to pull the values out of the records manually since .values() doesn't have a form that takes a TableRecord object.

What am I doing wrong here?

-Daniel

[Lukas Eder]

Hi Daniel,

Thanks for your message

2017-05-29 16:19 GMT+02:00 Daniel Einspanjer <daniel.e...@designet.com>:

I have a collection of tuples that need to be inserted into a table as new records.

I was trying to create a List of TableRecord objects built off of these Tuples via MYTABLE.newRecord() and then insert them with batchInsert(), but the fields I am not setting are getting sent to Postgres 9.6 as NULL instead of DEFAULT which causes a constraint violation.

Interesting, how are you "not setting" them, I.e. after creating the record, do you set the values individually, or do you load the record from a POJO using Record.from(Object)?

Note that with a batch insertion, since the desired behaviour is to have only a single JDBC statement, all the values are set explicitly. If you wanted DEFAULT behaviour, then it would be quite likely that each insertion would generate a new JDBC statement, and thus batching is useless compared to individual insertions.

I wonder if we should go through the argument list and check if at least one record has its changed() flag set to true though. Perhaps, if all the records are consistent with respect to changed() flags, we could indeed have DEFAULT behaviour across the batch. I've created a feature request for this:

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

 

I tried calling record.setValue(MYTABLE.ACOLUMN, DSL.defaultValue(MYTABLE.ACOLUMN)), but that gives a compile error that the type for T can't be determined.

This doesn't work indeed, because currently, only actual values are allowed as record values, not Field expressions.

Allowing field expressions would break quite a bit of API, including record.get(MYTABLE.ACOLUMN), which isn't able to return expressions. Also the changed flag would always need to be set to true, because the expression might be non-deterministic and would have to be evaluated on each INSERT / UPDATE...

Sounds too tricky to support.

I am currently trying to switch to using insertInto().columns(<just the ones I have>) and then looping over the records to build a series of .values() calls, but it seems like I have to pull the values out of the records manually since .values() doesn't have a form that takes a TableRecord object.

No, values() doesn't but insertInto(TABLE).set(record) is certainly possible. And then, you can call newRecord() to add another record to the bulk insert statement.

Hope this helps,
Lukas

[Daniel Einspanjer]

Thank you for the reply.  I am successfully using a series of explicitly set .value() calls now, but here are some follow-up comments in-line.

On Monday, May 29, 2017 at 11:38:42 AM UTC-4, Lukas Eder wrote:

 

I have a collection of tuples that need to be inserted into a table as new records.

I was trying to create a List of TableRecord objects built off of these Tuples via MYTABLE.newRecord() and then insert them with batchInsert(), but the fields I am not setting are getting sent to Postgres 9.6 as NULL instead of DEFAULT which causes a constraint violation.

Interesting, how are you "not setting" them, I.e. after creating the record, do you set the values individually, or do you load the record from a POJO using Record.from(Object)?

After the .newRecord(), I was just explicitly calling .setXXX() on the fields I was interested in and did not touch the ones I wanted to be default.  There was no loading of the data from a POJO.  I did try calling .reset(Field) on the fields to see if it might help. 

 

Note that with a batch insertion, since the desired behaviour is to have only a single JDBC statement, all the values are set explicitly. If you wanted DEFAULT behaviour, then it would be quite likely that each insertion would generate a new JDBC statement, and thus batching is useless compared to individual insertions.

I wonder if we should go through the argument list and check if at least one record has its changed() flag set to true though. Perhaps, if all the records are consistent with respect to changed() flags, we could indeed have DEFAULT behaviour across the batch. I've created a feature request for this:

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

 

This sounds like it would probably work well for my use case.

 

I tried calling record.setValue(MYTABLE.ACOLUMN, DSL.defaultValue(MYTABLE.ACOLUMN)), but that gives a compile error that the type for T can't be determined.

This doesn't work indeed, because currently, only actual values are allowed as record values, not Field expressions.

Allowing field expressions would break quite a bit of API, including record.get(MYTABLE.ACOLUMN), which isn't able to return expressions. Also the changed flag would always need to be set to true, because the expression might be non-deterministic and would have to be evaluated on each INSERT / UPDATE...

Sounds too tricky to support.

That is fine.  I think I was just looking in the wrong area for a solution here.

 

I am currently trying to switch to using insertInto().columns(<just the ones I have>) and then looping over the records to build a series of .values() calls, but it seems like I have to pull the values out of the records manually since .values() doesn't have a form that takes a TableRecord object.

No, values() doesn't but insertInto(TABLE).set(record) is certainly possible. And then, you can call newRecord() to add another record to the bulk insert statement.

I did notice the .set() variant, but because the JavaDoc or the manual specifically mentioned that it came out of MySQL, I wasn't sure if it would work as desired for Postgres.

So if I used:

stmt = insertInto(TABLE).columns(X,Y,Z);

records.forEach(record -> stmt.set(record));

stmt.execute();

Would it pick out only the columns that I specified from the collection of records that was passed in?  Is it any improvement over the same code with stmt.values(r.getX(), r.getY(), r.getZ()) instead?

-Daniel

[Lukas Eder]

Hi Daniel

2017-05-29 19:30 GMT+02:00 Daniel Einspanjer <daniel.e...@designet.com>:

I did notice the .set() variant, but because the JavaDoc or the manual specifically mentioned that it came out of MySQL, I wasn't sure if it would work as desired for Postgres.

Thanks for the pointer. Hmm, indeed - we should improve that manual section to be clear that the clause is being emulated for other dialects.

 

So if I used:

stmt = insertInto(TABLE).columns(X,Y,Z);

records.forEach(record -> stmt.set(record));

stmt.execute();

Don't forget to call stmt.newRecord() in between.

 

Would it pick out only the columns that I specified from the collection of records that was passed in?

Yes, indeed.

 

 Is it any improvement over the same code with stmt.values(r.getX(), r.getY(), r.getZ()) instead?

Yes, if you're passing in only a few columns, there's a lot less to write and no risk of messing up the order of columns...

Hope this helps,
Lukas