Re: Odd behavior with 'currval' - Mailing list pgsql-general

Steven:


On Fri, Feb 9, 2018 at 5:58 PM, Steven Hirsch <snhirsch@gmail.com> 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.

That's an opinion, perfectly valid but just that.

For me a long returning function which has to cope with the DB nulls
has two options. Return a default value, and 0 is the one I would in
this case without doubt, or throw an exception ( NullPointer,
SQLexception, checked or not ). IMO the second one is much less
helpful, given a null value on a nullable column is not an exceptional
condition, and there are plenty of methods to do it right, i.e., test
for null first read the value then, or use (Long)getObject.

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

Bear in mind the disconnect is easier to see in the catalogs, or on a
schema dump. It's extremely difficult to debug without having access
to them. And the 0 issue does not help, as it sends people on a wild
goose chase thinking something is borked there.

Given the sequence was unowned someone had to 1.- create the sequence
and table in two steps ( maybe from an edited schema dump ) or 2.-
alter it to not owned. Those are DML, those are schema definitions,
and we didn't have them.


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

My fault them, I failed to see it, and I'm unable to see it in my
archives, and google is not finding it so ..... aha, next message in
the thread, you did not sent it to everyone. Happens a lot.

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

You are, IMO, completely wrong. Java can not override on an inferred
return value type, not even on a explicit one.

So, getLong returning a long will never cast to (Long) null.

Your second behaviour is trivial, is just (Long)(rs.getObject(n)), and
I use it a lot.

If you (trivially) derive and wrap or make the driver use jour
resultSet and you put a public Long getLongX(int n) { return
(Long)getObject(n) } in it you have your method. You just cannot call
it getLong(n) ( no overrideing with different result type ).

There is probably a good reason to make it return primitives. Remember
JDBC dates from when java had no autoboxing as was really slow.

and throwing an exception is, IMO, as I wrote above, much less helpful.

> But, now that I've been burned by it, I'll certainly never make the same
> mistake again.  I'll make other mistakes :-).

We all learn this way.


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

Commented above, common error, you did reply instead of reply to all.


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

Putting java silliness aside, the problem is that retrying a chunk of
JDBC code for testing normally needs writing a minimum program around
it and is really tedious, while replyaing psql lines is just a cut &
paste issue.

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

Not limit to it, but split them. psql is like the standard, is a very
simple program well understood by most people and is what we normally
use to try to reproduce and  investigate problems. And normally, when
a tool does not match psql it tends to be a problem in the tool.

If you ask "I get 0 for currval(null) in jdbc but null in psql" it's
very easy to point to getLong vs getLong+wasNull or getObject issues.

Francisco Olarte.