ActiveRecord::ReadOnlyError when writing to user record on sign in

[clarkjim2]

Moving a new Rails 8.0.1 app from development to production. When testing the authentication for the first time in production, Devise cannot write to the user table when signing in a user:

Information for cause: ActiveRecord::ReadOnlyError (Write query attempted while in readonly mode: UPDATE "users" SET "sign_in_count" = $1, "current_sign_in_at" = $2, "last_sign_in_at" = $3, "current_sign_in_ip" = $4, "last_sign_in_ip" = $5, "updated_at" = $6 WHERE "users"."id" = $7)

The problem occurs in the session controller on this line:
sign_in(resource_name, resource)

To test, I can create and save a user record in the rails console. Until debugging this, I never knew of all the various options concerning primary and replica databases. I have only a primary Postgresql database (and others for cache, queue, and cable) and have added this to the application.rb file:

    config.active_record.writing_role = :primary
    config.active_record.reading_role = :primary
    
    config.after_initialize do
      ActiveRecord::Base.connected_to(role: :writing) do
        ActiveRecord::Base.connection.execute("SET SESSION CHARACTERISTICS AS TRANSACTION READ WRITE")
        ActiveRecord::Base.connection.execute("SET default_transaction_read_only = OFF")
      end
    end

And this is the updated database.yml section for the production database:

production: &production
  primary: &primary_production
    <<: *default
    database: app_production
    username: <%= ENV["POSTGRES_USER"] %>
    password: <%= ENV["POSTGRES_PASSWORD"] %>
    replica: false
    primary: true
    read_only: false
    variables:
      statement_timeout: 5000
      lock_timeout: 5000
      idle_in_transaction_session_timeout: 5000
      default_transaction_read_only: 'off'

Still no joy though and the user record continues to be blocked from writing in Devise. What do I need to do in production to allow it to be written to? Thanks!

[UmerQaisar]

Check Your Database Connection in Production

Run the following in the Rails production console (rails console -e production):

ActiveRecord::Base.connection.execute("SHOW transaction_read_only;")
If it returns on, your database is still in read-only mode, meaning your settings haven't taken effect.

To manually set it to read/write, you can run:

ActiveRecord::Base.connection.execute("SET default_transaction_read_only = OFF;")
If this works, your database is in read-only mode when the Rails app starts, and you need to fix that at the PostgreSQL level.

[ja-andrews]

The above solution works if you have reads and writes in the same datacenter. However, if you load balance reads across geographic zones then updates will still fail.