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.
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.
--
