Re: Odd behavior with 'currval'

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.

--