yandex
failover on error
Hi, does it possible to get automatic failover in case of connection error:
- have two database hosts master=>slave
- slave preferred query executing
- if master error like connection refused/connection reset by peer and other network errors => retry on another slave slave
- if master preferred query execution - retries on different host if that possible.
so application does not receive any error if another server reachable.
or how can i get my use-case worked with this package?
Hey, Vasiliy, sorry for long response. That's a case which I've thought about some time ago. I think we can leverage iter over func feature here. We can add new *hasql.Cluster method, similar to Node, the only difference is this new method will return sequence func over Node[T] filtered by given criterion. Something like this (method name is subject to change):
// NodesIter returns iterator func that yields nodes sorted by given criterion.
// It guarantees to yield only non-nil nodes, but in some cases no nodes could
// be returned at all.
func (c *Cluster[T]) NodesIter(criterion Criterion) iter.Seq[Node[T]]
Than in your code you can call it like this:
type Store struct {
// cluster contains 1 primary and 2 standbys
cluster *hasql.Cluster[*sql.DB]
}
func (s *Store) FindUserByID(ctx context.Context, id int64) (*User, error) {
// iterate over nodes in sequence [standby, standby, primary]
for node := range s.cluster.NodesIter(hasql.PreferStandby) {
var user User
err := node.DB().
QueryRowContext(`SELECT login, email FROM users WHERE id = $1`, id).
Scan(&user.Login, &user.Email)
if err == nil {
return &user, nil
}
}
return nil, errors.New("all attempts to fetch user have failed")
}
What do you this about that?
Looks interesting, so if all slaves are not available - we can use master ? One more question - in our setup we have 1 master , 1 slave and 1 disaster recovery slave. So first slave prefers to answer queries over wal-log apply, and the second slave (DR) prefers apply wal-log over answering questions. How we deal with such setup and nodes iter ?
About function may be EachNode or something like this? Or if we have only two cases (Primary/Standby) create two functions like in redis https://pkg.go.dev/github.com/redis/go-redis/v9#ClusterClient.ForEachSlave ?
How we deal with such setup and nodes iter ?
This can be achieved with custom NodePicker implementation. You can write your own stateful NodePicker similar to RoundRobinNodePicker.
There is one complication: for this approach to work it is necessary to incapsulate NodePicker into iterator to attach picker state to iterator. I will try to build PoC and return with results later.
create two functions like in redis
I would rather not as hasql already successfully use criterion pattern
@vtolstov well, better late than never https://github.com/yandex/go-hasql/pull/19 :)