Inconsistant data queried from postgres in two different go applications

Question

I've built two go applications, say app1 and app2, they both connected to the same postgres database. For each application, I use gorm as ORM.

app1 is used to update the databse and app2 is an API server created in gin that queries data from the database.

In app2 I create a database connection pool at the beginning of the program and it is reused in every HTTP endpoint handlers. Inside the handler function, I execute a SQL statement using gorm and returns the result.

The old return value of the SQL statement is like

select count(*) from t_publications --returns--> 2641

When I insert 4 new records in app1, I can get correct data both in app1 and psql cli, so it's like

select count(*) from t_publications --returns--> 2645

However when I check the API endpoint of app2, which executes the same SQL statement, it returns the old data

curl localhost:8080/getPublications --returns--> 2641

If I restart app2, I can then get the correct data that I need. I'm not sure whether it is a problem regards to the lifetime of connections inside the connection pool, can someone explain this problem? Thanks.

----edit----

Here is one of my handler function for app2

func GetOverview(db *gorm.DB) gin.HandlerFunc {
    var overview Overview
    // the query is just a simple select statement
    err := db.Raw(dbquery.GetOverview).Scan(&overview).Error
    return func(c *gin.Context) {
        if err == nil {
            c.JSON(http.StatusOK, gin.H{
                "status": "ok",
                "data":   overview,
            })
        } else {
            c.JSON(http.StatusInternalServerError, gin.H{
                "status": "internal server error",
                "data":   err,
            })
        }
    }
}

Answer 1

You're executing the query exactly once in your GetOverview and then you're returning the result of that query in a closure that's used as the gin.HandlerFunc, essentially caching the result of the query, at the time of registering the handler, for ever.

The solution is to move the query code into the actual handler:

func GetOverview(db *gorm.DB) gin.HandlerFunc {
    return func(c *gin.Context) {
        var overview Overview
        // the query is just a simple select statement
        err := db.Raw(dbquery.GetOverview).Scan(&overview).Error
        if err == nil {
            c.JSON(http.StatusOK, gin.H{
                "status": "ok",
                "data":   overview,
            })
        } else {
            c.JSON(http.StatusInternalServerError, gin.H{
                "status": "internal server error",
                "data":   err,
            })
        }
    }
}