On 02/09/2018 08:58 AM, Steven Hirsch wrote:
On Fri, 9 Feb 2018, Francisco Olarte wrote:
This may sound a bit harsh but:
The culprit is in the JDBC domain, NOT PostgreSQL! According to the
documentation I found, the ResultSet 'getLong()' method returns a
value of
zero when it sees NULL as an input. Why the JDBC libs don't treat
this as an
invalid numeric conversion is beyond me.
No, that's not a JDBC problem, that's pilot error.
Of course it is, but that doesn't change the fact that the behavior of
JDBC is not helpful at all. It also doesn't change the fact that I had
a very real and very confusing "disconnect" between the sequence and the
table. That may well have been pilot error on my part, but was
extremely subtle and unusual in its impact - as witnessed by the number
of rounds we went before David hit the issue with ownership.
JDBC has behaved like these for ever, and you have had people chasing
ghosts ( not a biggie, we are used to this ) because, instead of
providing the real chunks you were running you kept saying "when I
select currval() I get 0" and similar things. If you had said "when I
do rs.getLong(col) I get 0", or pasted a relevant chunk of code, lots
of people here would have pointed that getLong returns a primitive
java long, so rs.getObject() is needed if you may get null. JDBC has
been doing this forever, and they probably do it because in many cases
this behaviour is useful.
Please note that I _did_ post the JDBC code after being requested to do
so. It showed clearly where I was using rs.getLong() to retrieve the
id. No one commented on that as being a factor.
Given Java's usual strictness about converting null to numerics, I do
not find it useful. I find it completely broken and orthogonal to the
spirit of a type-safe language. In my opinion, this:
long i = rs.getLong(n);
should throw a type exception if NULL is fetched and this:
Long i = rs.getLong(n);
should give 'i' the value of 'null'. Getting 0 as an answer simply
makes no sense at all to me.
But, now that I've been burned by it, I'll certainly never make the same
mistake again. I'll make other mistakes :-).
This also raises suspicions about your sequence ownership problems, as
you may have being ( with your best intention ) pasting your code to
reproduce the problems instead of the real scripts from the database.
Not following this statement. After being requested to do so, I did
post the actual JDBC code. Did that not show up in the message thread?
Nothing was done to edit down or elide anything.
I remember seeing it, so I went back to look at the message. Turns out
you sent it to me only. Unfortunately I am not a Java programmer so I
did not catch the error. For the record:
"
Here is the JDBC code:
try {
conn.setAutoCommit(false);
PreparedStatement sth = null;
ResultSet rs = null;
// Insert new
sth = conn.prepareStatement(addAssetType);
sth.setString(1, name);
sth.execute();
sth = conn.prepareStatement(lastTypeId);
rs = sth.executeQuery();
if (rs.next()) {
long id = rs.getLong(1);
result.put("id", id);
result.put("name", name);
}
else {
throw new
WebApplicationException(buildError(BAD_REQUEST, "Lookup of most recent
sequence failed"));
}
conn.commit();
}
catch (SQLException e) {
conn.rollback();
throw new
WebApplicationException(buildError(BAD_REQUEST, e.getMessage()));
}
Where:
private final static String addAssetType =
"INSERT INTO udm_asset_type_definition (def_name) "
+ "VALUES (?)";
private final static String lastTypeId = "SELECT currval(
pg_get_serial_sequence('udm_asset_type_definition', 'def_id'))";
Code with this exact same structure (but different SQL, obviously) is
working just fine with five other tables using sequences. The above
code snippet always produces '0' for the id. If I use the 'INSERT ..
RETURNING..' approach, it gives the right answer.
"
In general, when you run a code chunk for problem reporting, try to do
it with psql ( which everybody has and knows how it works ) and paste
the code / result directly, without much editing. This aids a lot. In
fact, if you do it you may have noticed your text in psql returned an
empty column in currval(null), which, knowgin it is int, means it was
null ( in a string you cannot easily distinguish empty from nulls in a
simple select ).
Of course I noticed it! That's how I finally spotted the issue. If I
was a bit more on the ball, I'd have used psql from the start. So, now
that I understand the importance of doing so (and the brain-dead
behavior of JDBC) I will be sure to do so in the future.
Anyway, you report was quite nice and showed you put quite a bit of
work in it, treat these as tips to make problem reporting / solving
easier, not just as criticism.
I do not take your tone as harsh. I'm willing to take responsibility
where I created my own problems. I have learned that I must reduce
trouble reports to issues that can be reproduced in psql. It makes
sense and I'll do so in the future.
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
