SET extra_float_digits = 3 in postgresql

Question

Whenever I start Postgresql DB engine, I have almost 7-8 queries running in the background SET extra_float_digits = 3

I am not sure why these are running all the times. I know that the extra_float_digits variable adjusts the number of digits displayed for floating-point values in Postgresql, however I am not sure why these queries run in the background when I start the DB engine.

I already have the extra_float_digits = 3 in the config file. Even if I comment it out, these queries still run in the background..

need help..Thanks

Answer 1

The queries aren't really running. As Nick says in the comments, the connections will be in idle state. pg_stat_activity shows the last statement that finished running when a query is idle.

As for the other part: I'd say you're using PgJDBC. SET extra_float_digits ensures that PgJDBC doesn't lose precision when it gets floating point values from the database. It's part of the initial connection conversation. It's normal and you can ignore it. If you're on a recent PgJDBC, send the additional connection parameter assumeMinServerVersion=9.0 and it'll go away.

So what you have there is a bunch of new, idle connections.

Look into your application / application server's configuration. Your connection pool probably doesn't have reasonable limits set.

Answer 2

Whenever a new connection is established, postgres triggers this extra_float_digits setting. Issue was with my database health check module.

Answer 3

I've had this problem with Java and Postgresql. I've resolved this issue by using PGPoolingDataSource and Close connection.

Here how I've built my classes:

//Class Factory Connection:

public class FacConn {

    public static PGPoolingDataSource getConnection2() {
    PGPoolingDataSource source = new PGPoolingDataSource();
    source.setServerName("local");
    source.setPortNumber(5432);
    source.setDatabaseName("mydbname");
    source.setUser("LoginUser");
    source.setPassword("password");
    source.setAssumeMinServerVersion("9.0");
    source.setConnectTimeout(50000);        

    return source;      
    }
}

//Class userDAO - class to interact with database

public class UsuarioDAO {

    private PGPoolingDataSource poolDS = FabConexao.getConnection2();
    private Connection con = null;

    public User searchById(Integer id){
        try{con = poolDS.getConnection();}
        catch (SQLException e){throw new RuntimeException(e);}

        String sql = "Select * from people where id_people=?";
        ResultSet rs = null;

        try (PreparedStatement smtm = con.prepareStatement(sql)){
            smtm.setInt(1, id);
            rs = smtm.executeQuery();
            People people = new People();
            if(rs.next()){
                people.setId_People(rs.getInt("id_people"));
                people.setFirtname(rs.getString("firstname"));
                people.setLastname(rs.getString("lastname"));
                people.setAge(rs.getInt("age"));
                people.setActiv(rs.getBoolean("activ"));
            } 
            smtm.close();   
            return people;
        } catch (SQLException e){
            e.printStackTrace();
            System.err.println( e.getClass().getName()+": "+ e.getMessage() );          
        }  finally {
            if (rs != null) {
                try {rs.close();} catch (SQLException e) { /* ignored */}
            }
            poolDS.close();
        }
        return null;
}