river stuck connected to read-only postgres instance

This scenario is admittedly operator error, but I am curious if there's anything River could do differently to self-heal.

We have a writer and reader in an AWS RDS cluster. Our service connects through a cluster endpoint which routes to the current writer. We needed to perform an OS patch so we upgraded the reader and then failed over so it became the writer. Then, we upgraded the former writer before failing back over to return to the original state. River's leader ended up pegged to the reader and could not do anything so we restarted the service to force a new connection which fixed the issue. The root cause is that a pgxpool connection obtained from the cluster endpoint is trusted as a writer for its entire lifetime, but Aurora flips roles without closing sockets.

Some of the errors we saw:

ERROR: cannot execute UPDATE in a read-only transaction (SQLSTATE 25006)

ERROR: cannot execute INSERT in a read-only transaction (SQLSTATE 25006)

error listening on topic "river_control": ERROR: cannot execute LISTEN during recovery (SQLSTATE 25006)

error scheduling jobs: ERROR: cannot execute SELECT FOR UPDATE in a read-only transaction (SQLSTATE 25006)

error cleaning jobs: ERROR: cannot execute DELETE in a read-only transaction (SQLSTATE 25006)

ERROR: cannot access temporary or unlogged relations during recovery (SQLSTATE 0A000)

If River could realize it's incorrectly connected to a read-only instance by inspecting errors or running something like pg_is_in_recovery(), it could attempt to reconnect and ultimately stop if it won't be successful. Again, I realize this is somewhat specific to our scenario ✌🏼

Hmm, yeah it does seem like the active leader should be able to detect this state and demote itself. Let me see what I can do. And thanks for including such detailed sample errors — very helpful.

Just making sure, the leader's lease is short and it shouldn't have kept trying to perform leadership activities beyond the expiration. Did you see any evidence to the contrary? Or are these errors just resulting from the leader continuing to try to re-elect itself despite being pinned on a read only conn?

ERROR: cannot access temporary or unlogged relations during recovery (SQLSTATE 0A000)

That came from "leadership.Elector: Error attempting to elect" which may be coming from attemptGainLeadershipLoop. I believe the leader was continuing to try to re-elect itself. While it couldn't, innerFetchLoop failed with "producer: Error fetching jobs" which is where we saw "ERROR: cannot execute UPDATE in a read-only transaction (SQLSTATE 25006)"

Thanks for taking a look!

@maxbeatty

@maxbeatty OOC, did this happen on the very latest version of River or an older one? (We did some refactoring in the elector recently, and just wondering if that might've affected things one way or the other.)

This occurred using version v0.34.0

@maxbeatty

@maxbeatty Okay thanks. That's before our most recent refactor of the leadership infra. It's not a guarantee of a fix, but you may want to try latest to see if it makes any difference given the refactor was aimed at stability.

@bgentry

I took a stab at what it might look like if we counted successive producer errors (likely due to a chronic problem like a read-only pool) and once they reach a certain threshold we reset the internal connection pool with pgxpool.Pool.Reset.

A problem I ran into though is that even if Reset is invoked, it doesn't reset checked out connections, so the connections internalized by River will still be stale. It also feels a little out of the realm of River's responsibility to have it doing this level of surgery on the internal connection pool.

I wonder if instead we should have something like a hook that's invoked on a producer error (or two hooks? with one also invoked on a leader election error), then we could show an example of some code that'd track error rate over time, and if it crossed a certain threshold, exit the process running River, thereby forcing a restart with new connection pool and fresh state. A full reset of that level would more reliably fix the problem.

cc @bgentry In case you have any other ideas too.

We ran into this updating postgres in aws - we used the blue / green rds update and somehow many of our services ended up connected to the original database which is put into read-only mode. This is definitely unexpected, although now we've seen it happen more than once. But we would have expected the original connections to be severed and not restored somewhere along the way. So the problem is not unique to river queue; but determining how best to respond to the situation is hard if your service doesn't do much beyond run jobs, because river is not giving us good ways to notice something is wrong.

I would prefer a hook to look for this; my intention would be to restart the pod after a certain number of consecutive producer: Error fetching jobs errors.

@jswidler

@jswidler +1. Okay thanks for weighing in. I think some sort of error hook should be possible here.

@maxbeatty

Actually, as I was working through this, I realized that I think we can pretty much get a similar result to what I was adding without making any core River changes by periodically checking your DB pool's health.

Start with a River client being initialized the normal way:

	dbPool, err := pgxpool.New(ctx, riversharedtest.TestDatabaseURL())
	if err != nil {
		panic(err)
	}
	defer dbPool.Close()

	workers := river.NewWorkers()
	river.AddWorker(workers, &NoOpWorker{})

	riverClient, err := river.NewClient(riverpgxv5.New(dbPool), &river.Config{
		Logger: slog.New(slog.NewTextHandler(os.Stdout, &slog.HandlerOptions{Level: slog.LevelWarn, ReplaceAttr: slogutil.NoLevelTime})),
		Queues: map[string]river.QueueConfig{
			river.QueueDefault: {MaxWorkers: 100},
		},
		Workers:  workers,
	})
	if err != nil {
		panic(err)
	}

Then, boot up a goroutine that does a periodic write against the pool. When it detects a readonly transaction, have it exit the program (so it can be restarted by the pod/PaaS/whatever):

import (
	...

	"github.com/jackc/pgerrcode"
	"github.com/jackc/pgx/v5/pgconn"
	"github.com/jackc/pgx/v5/pgxpool"

	...

	// Start a background goroutine that periodically checks whether the
	// database is still writable. In a real application, the check interval
	// would be longer (e.g. 30 seconds), and the goroutine would run for the
	// lifetime of the process.
	go func() {
		ticker := time.NewTicker(10 * time.Second)
		defer ticker.Stop()

		for {
			select {
			case <-ctx.Done():
				return
			case <-ticker.C:
				// Use a lightweight write operation to verify the database
				// accepts writes. A temporary table scoped to the transaction
				// avoids any lasting side effects.
				if _, err := dbPool.Exec(ctx, "CREATE TEMP TABLE river_write_check () ON COMMIT DROP"); err != nil {
					var pgErr *pgconn.PgError
					if errors.As(err, &pgErr) && pgErr.Code == pgerrcode.ReadOnlySQLTransaction {
						fmt.Fprintf(os.Stderr, "river: read-only transaction error, exiting: %s\n", err)
						os.Exit(1)
					}
				}
			}
		}
	}()

The nice thing about this approach is that it works beyond River — any other use of the database will also be affected by the readonly problem, so it makes sense to check for it beyond just River.

Here's a full example:
https://gist.github.com/brandur/4962c085d1a790bf70703a5048918e2e

@maxbeatty @jswidler Can you check that this would work for you?

I might be misunderstanding, but if the database failover changes DNS, then new connections from the db pool might be ok (the pool will make new connections as needed, right?). The issue is that existing connections held by River and the pool are not ok. So we need a way for River to release its connection from the pool and/or reconnect the entire pool.

If you run a new query like in your example above, it may succeed but the River held connection is still broken.

Remember that by virtue of performing that check, there should be at least one extra connection in the DB pool that will also be tainted like River's connections are. That's in addition to any other connections in there that your non-River code is using.

It is possible that a connection could be extracted from the pool right after a failover and right after the pool destructed all other open connections. That's probabilistically unlikely, but if you're worried about it you could change the code above to give the goroutine a permanently procured DB connection acquired from the pool. That way, it'll be as old as anything River is holding onto and susceptible to the same effects.

Hi, yes i have consider this and it works just fine; i just thought it would be interesting to tie the job server's health directly to the ability of river queue to obtain jobs rather than indirectly from this. It would make sense to me to fail a healthcheck based upon the fact river is failing to acquire jobs. Practically to me it does not make much difference beyond the extra sql query every 10 seconds

@jswidler

@jswidler Okay, cool. I'm also not totally against the idea of have this live more internally — I build out an implementation for that already and it's not half bad. I almost tried to ship it too before realizing that it we were expanding the API for something that'd be functionally almost identical from a functional standpoint if it lived outside of River instead of in it.

Let's see how it works out for people here and can potentially reconsider if new information comes up.

Navigation Menu

Repository navigation

river stuck connected to read-only postgres instance #1225

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

river stuck connected to read-only postgres instance #1225

Description

Activity

brandur commented on Apr 22, 2026

bgentry commented on Apr 22, 2026

maxbeatty commented on Apr 22, 2026

brandur commented on Apr 22, 2026

maxbeatty commented on Apr 27, 2026

brandur commented on Apr 27, 2026

brandur commented on Apr 28, 2026

jswidler commented on Apr 29, 2026

brandur commented on Apr 30, 2026

brandur commented on May 1, 2026

pgr0ss commented on May 1, 2026

brandur commented on May 1, 2026

jswidler commented on May 18, 2026

brandur commented on May 22, 2026

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions