Repository: coroot/coroot

Issue:
New occurrence of No recovery of "Prometheus cache lag" issue
Following on from #717 we have hit the same problem again with cache lagging behind. We are on version `1.16.0`:

````
I1029 13:30:23.311383       1 main.go:47] edition: Enterprise
I1029 13:30:23.311442       1 main.go:48] version: 1.16.0
I1029 13:30:23.312838       1 main.go:69] database type: postgres
````

Attached is a new goroutines dump

[goroutines.txt](https://github.com/user-attachments/files/23211137/goroutines.txt)

Discussion:

Solution patch:
diff --git a/db/db.go b/db/db.go
index cf8b5e1..d6f5365 100644
--- a/db/db.go
+++ b/db/db.go
@@ -5,6 +5,7 @@ import (
 	"encoding/json"
 	"errors"
 	"fmt"
+	"net/url"
 	"path"
 	"strings"
 
@@ -20,6 +21,8 @@ type Type string
 const (
 	TypeSqlite   Type = "sqlite"
 	TypePostgres Type = "postgres"
+
+	defaultPostgresTimeoutSecond = "30"
 )
 
 var (
@@ -48,6 +51,10 @@ func NewSqlite(dataDir string) (*DB, error) {
 }
 
 func NewPostgres(dsn string) (*DB, error) {
+	var err error
+	if dsn, err = addPostgresConnectTimeout(dsn); err != nil {
+		return nil, err
+	}
 	db, err := sql.Open("postgres", dsn)
 	if err != nil {
 		return nil, err
@@ -55,6 +62,26 @@ func NewPostgres(dsn string) (*DB, error) {
 	return &DB{typ: TypePostgres, db: db}, nil
 }
 
+func addPostgresConnectTimeout(dsn string) (string, error) {
+	if strings.HasPrefix(dsn, "postgres://") || strings.HasPrefix(dsn, "postgresql://") {
+		u, err := url.Parse(dsn)
+		if err != nil {
+			return "", err
+		}
+		q := u.Query()
+		if q.Get("connect_timeout") == "" {
+			q.Set("connect_timeout", defaultPostgresTimeoutSecond)
+		}
+		u.RawQuery = q.Encode()
+		return u.String(), nil
+	} else {
+		if !strings.Contains(dsn, "connect_timeout=") {
+			dsn += " connect_timeout=" + defaultPostgresTimeoutSecond
+		}
+		return dsn, nil
+	}
+}
+
 func (db *DB) Type() Type {
 	return db.typ
 }



Test patch:
diff --git a/db/db_test.go b/db/db_test.go
new file mode 100644
index 0000000..f8bd719
--- /dev/null
+++ b/db/db_test.go
@@ -0,0 +1,36 @@
+package db
+
+import (
+	"testing"
+
+	"github.com/stretchr/testify/assert"
+)
+
+func TestAddPostgresConnectTimeout(t *testing.T) {
+	result, err := addPostgresConnectTimeout("postgres://user:password@localhost:5432/dbname")
+	assert.NoError(t, err)
+	assert.Equal(t, "postgres://user:password@localhost:5432/dbname?connect_timeout=30", result)
+
+	result, err = addPostgresConnectTimeout("postgres://user:password@localhost:5432/dbname?connect_timeout=60")
+	assert.NoError(t, err)
+	assert.Equal(t, "postgres://user:password@localhost:5432/dbname?connect_timeout=60", result)
+
+	result, err = addPostgresConnectTimeout("postgres://user:password@localhost:5432/dbname?sslmode=require")
+	assert.NoError(t, err)
+	assert.Equal(t, "postgres://user:password@localhost:5432/dbname?connect_timeout=30&sslmode=require", result)
+
+	result, err = addPostgresConnectTimeout("postgres://user:password@localhost:5432/dbname?sslmode=require&connect_timeout=45")
+	assert.NoError(t, err)
+	assert.Equal(t, "postgres://user:password@localhost:5432/dbname?connect_timeout=45&sslmode=require", result)
+
+	result, err = addPostgresConnectTimeout("host=localhost user=coroot password=secret dbname=coroot sslmode=require")
+	assert.NoError(t, err)
+	assert.Equal(t, "host=localhost user=coroot password=secret dbname=coroot sslmode=require connect_timeout=30", result)
+
+	result, err = addPostgresConnectTimeout("host=localhost user=coroot password=secret dbname=coroot sslmode=require connect_timeout=60")
+	assert.NoError(t, err)
+	assert.Equal(t, "host=localhost user=coroot password=secret dbname=coroot sslmode=require connect_timeout=60", result)
+
+	result, err = addPostgresConnectTimeout("postgres://invalid url with spaces")
+	assert.Error(t, err)
+}