When should I call Close on connection?

[pankajsoni19]

I am setting up the connection as

conn, _ := clickhouse.Open(
        &clickhouse.Options{
            Addr: []string{
                fmt.Sprintf("%s:9000", conf.Hostname),
            },
            MaxOpenConns:    5,
            MaxIdleConns:    1,
            ConnMaxLifetime: 10 * time.Minute,
            Auth: clickhouse.Auth{
                Database: conf.Database,
                Username: conf.Username,
                Password: conf.Password,
            }})

If I do not call close and keep using the conn object. I get this error after sometime

read: read tcp 172.31.36.2:38716->172.31.6.70:9000: read: connection reset by peer

Do I need to call conn.Close() after conn.Exec ? Do I need to call conn.Close() after conn.Select ? Do I need to call conn.Close() after conn.QueryRow().ScanStruct() ? Do I need to call conn.Close() after batch.Send ?

Or how do I return the connection to the pool?

[pankajsoni19]

Error is coming for me in conn.PrepareBatch

[jkaflik]

Do I need to call conn.Close() after conn.Exec ? Do I need to call conn.Close() after conn.Select ? Do I need to call conn.Close() after conn.QueryRow().ScanStruct() ? Do I need to call conn.Close() after batch.Send ?

The answer is no to all of these. Until you really want to close the connection pool.

Or how do I return the connection to the pool?

Connections are automatically released to the pool after use.

---

connection reset by peer

Can be caused by any network glitch. Depending on your use case, your application should be able to retry.

If you observe it intensifies for long living connections, you can decrease your ConnMaxLifetime. You can also set MaxIdleConns to zero, so no idle connections will be kept in a pool.

[pankajsoni19]

its running on AWS fargate, so I am network isn't an issue. After calling close after each operation it works. I also added a sleep timeout of 100 milliseconds between each clickhouse query call. error always happens in conn.PrepareBatch if I don't call close.

There are 10k read query ops to table A. post that I filter the result and call conn.PrepareBatch to push data into table B.

any way to get more error details out of the library?

[jkaflik]

General rule for distributed systems is network is unreliable.

any way to get more error details out of the library?

connection reset by peer is the lowest level error and is strictly related to connection being dropped either by ClickHouse server or anything that is in front of it, like network router or load balancer.

Would be great if you share a code snippet. I am not sure about how Fargate implements network, but maybe there are some settings that might cause a socket drop. What is the time delay between connection reuse?