Re: Change of format of returned flat value after prepareThreshold - Mailing list pgsql-jdbc

W dniu 15.10.2015 o 17:34, Vladimir Sitnikov pisze:
>> but I think that those numbers can be returned as large integers
>> just like it was with 9.3 drivers or just like it is
>
> I'm afraid it is not possible at jdbc driver level. You need to
> change application code.

I have changed that query. One of programmer didn't know
Java well and used such query to get number of milliseconds
since epoch start (1970-01-01). I changed those codes
to use System.currentTimeMillis(). Problem disappeared.


> As far as I can see from your output, backend reports float8. That
> means, we have just a "double" at the client side, and we have no
> idea what string representation is "expected".

Of course I understand it.

The only thing I do not understand is:
why JDBC or database backend changes format of returned value
after my use count reaches prepareThreshold parameter.

If I set prepareThreshold=0 then I always get float in normal
notation.

If I set prepareThreshold=5 then I get float in normal
notation up to 5th try, and then from 6th try I get this
value in scientific notation.

If I set prepareThreshold=10 then I get float in normal
notation ut to 10th try, and then from 11th try I get this
value in scientific notation.

Why such change?

ResultSetMetaData do not changes and report that value
to be of float8 type (SQL_TYPE=8).

So what changed?

I do not observe this with JDBC drivers in version 9.3,
so I think something changed in driver code, but maybe
it is change in the database?

I use:
PostgreSQL 9.4.5 on x86_64-unknown-linux-gnu, compiled by gcc (GCC)
4.4.7 20120313 (Red Hat 4.4.7-16), 64-bit



> If you want a "epoch time", I would recommend using
> resultSet.getLong(). Additionally, you might want to cast the
> selected expression to int8, so you do not mess with floating point
> numerics.

At first I casted it to bigint, then I simplified code that now
is even more portable (as we work with other databases)
and now I use System.currentTimeMillis() :-)


> Is there a specific reason why you expect non-scientific output of
> a _floating_ value out of .getString()?

This code is part of our reporting engine where all results are read
by rs.getString(). I know that I could use ResultSetMetaData,
but getString() simply works.

I was looking for similar queries that behaves the same way,
i.e. changes format of returned value after use count reaches
prepareThreshold, but I was not able to find such query.

In my previous mail there was attachment with Java code
that shows that behaviour and shell script that tests
it with various JDBC drivers.

There is Jython code that does the same thing:

<code>

#!/usr/bin/env jython
# -*- coding: utf8 -*-

__version__ = '$Id: jdbc_pg_float_time.py 2521 2015-10-16 05:24:43Z mn $'

import sys
import time
import traceback

from java.sql import DriverManager
from java.lang import Class

Class.forName("org.postgresql.Driver")


def test(db_url, user, passwd):
    try:
        print("\n\n%s\n--------------" % (db_url))
        db = DriverManager.getConnection(db_url, user, passwd)
        try:
            pstmt = db.prepareStatement("SELECT version()")
            rs = pstmt.executeQuery()
            while rs.next():
                s = rs.getString(1)
                print(s)
            rs.close()
            pstmt.close()

            dbmi = db.getMetaData()
            print("JDBC driver version is '%s'; major: %s; minor: %s" %
(dbmi.getDriverVersion(), dbmi.getDriverMajorVersion(),
dbmi.getDriverMinorVersion()))

            last_s = ''
            i = 0
            t0 = time.time()
            while 1:
                i += 1
                pstmt = db.prepareStatement("SELECT (EXTRACT(EPOCH FROM
timeofday()::timestamp(3))*1000) AS timestamp")
                rs = pstmt.executeQuery()
                try:
                    if i == 1:
                        rsmd = rs.getMetaData()
                        type_id = rsmd.getColumnType(1)
                        type_name = rsmd.getColumnTypeName(1)
                        print('column type: %s (%d)' % (type_name, type_id))
                    while rs.next():
                        s = rs.getString('timestamp')
                        if s != last_s:
                            t2 = time.time()
                            if t2 - t0 > 2:
                                print('epoch: %s; tries: %d' % (s, i))
                                t0 = t2
                            if '.' in s:
                                print('last value: %s; try: %d' % (last_s, i))
                                print('%s !!!' % (s))
                                return
                            last_s = s
                finally:
                    rs.close()
                    pstmt.close()
        finally:
            db.close()
    except:
        print("there were errors!")
        s = traceback.format_exc()
        sys.stderr.write("%s\n" % (s))


test('jdbc:postgresql://test-baza.heuthesd:5494/isof_test?stringtype=unspecified&prepareThreshold=10',
'postgres', 'postgres')


</code>

And results on my machine:

jdbc:postgresql://test-baza.heuthesd:5494/isof_test?stringtype=unspecified&prepareThreshold=10
--------------
PostgreSQL 9.4.5 on x86_64-unknown-linux-gnu, compiled by gcc (GCC)
4.4.7 20120313 (Red Hat 4.4.7-16), 64-bit
JDBC driver version is 'PostgreSQL 9.4 JDBC4.2 (build 1204)'; major:
9; minor: 4
column type: float8 (8)
last value: 1444980175621; try: 11
1.444980175621E12 !!!


When you change prepareThreshold=10 to something else you will
get scientific format earlier or later.


--
Regards,
Michał Niklas