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

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@aklaver.com