`PG::Error: cannot set single row mode` when using precompiled version of `pg` gem

[dentarg]

(Originally opened at jeremyevans/sequel_pg#60)

This is my code

# frozen_string_literal: true

require "bundler/inline"

gemfile do
  source "https://rubygems.org"
  gem "sequel", "5.96.0"
  gem "sequel_pg", "1.17.2", require: "sequel"
  gem "pg", "1.6.2", force_ruby_platform: %w[1 true].include?(ENV["force_ruby_platform"])
end

DB_NAME = "foo_#{rand(100)}"

url = ENV.fetch("URL") do
  system "createdb #{DB_NAME}"
  "postgres://localhost/#{DB_NAME}"
end

begin
  puts "Connecting to #{url}"
  Sequel.connect(url) do |db|
    puts db.select { version.function }.all
    db.extension :pg_streaming
    db.run "CREATE TABLE foo (id serial)"
    db[:foo].stream.each {}
  end
ensure
  system "dropdb #{DB_NAME}"
end

This works

$ force_ruby_platform=1 ruby repro.rb
Connecting to postgres://localhost/foo_81
{version: "PostgreSQL 16.7 (Homebrew) on aarch64-apple-darwin23.6.0, compiled by Apple clang version 16.0.0 (clang-1600.0.26.6), 64-bit"}

This does not

$ force_ruby_platform=0 ruby repro.rb
Connecting to postgres://localhost/foo_73
{version: "PostgreSQL 16.7 (Homebrew) on aarch64-apple-darwin23.6.0, compiled by Apple clang version 16.0.0 (clang-1600.0.26.6), 64-bit"}
/Users/dentarg/.arm64_rubies/3.4.3/lib/ruby/gems/3.4.0/gems/sequel_pg-1.17.2/lib/sequel/extensions/pg_streaming.rb:92:in 'Sequel::Postgres::Adapter#set_single_row_mode': PG::Error: cannot set single row mode (Sequel::DatabaseError)
	from /Users/dentarg/.arm64_rubies/3.4.3/lib/ruby/gems/3.4.0/gems/sequel_pg-1.17.2/lib/sequel/extensions/pg_streaming.rb:92:in 'Sequel::Postgres::Streaming::AdapterMethods#execute_query'
	from /Users/dentarg/.arm64_rubies/3.4.3/lib/ruby/gems/3.4.0/gems/sequel-5.96.0/lib/sequel/adapters/postgres.rb:159:in 'block in Sequel::Postgres::Adapter#execute'
	from /Users/dentarg/.arm64_rubies/3.4.3/lib/ruby/gems/3.4.0/gems/sequel-5.96.0/lib/sequel/adapters/postgres.rb:136:in 'Sequel::Postgres::Adapter#check_disconnect_errors'
	from /Users/dentarg/.arm64_rubies/3.4.3/lib/ruby/gems/3.4.0/gems/sequel-5.96.0/lib/sequel/adapters/postgres.rb:159:in 'Sequel::Postgres::Adapter#execute'
	from /Users/dentarg/.arm64_rubies/3.4.3/lib/ruby/gems/3.4.0/gems/sequel-5.96.0/lib/sequel/adapters/postgres.rb:532:in 'Sequel::Postgres::Database#_execute'
	from /Users/dentarg/.arm64_rubies/3.4.3/lib/ruby/gems/3.4.0/gems/sequel_pg-1.17.2/lib/sequel/extensions/pg_streaming.rb:49:in 'Sequel::Postgres::Streaming#_execute'
	from /Users/dentarg/.arm64_rubies/3.4.3/lib/ruby/gems/3.4.0/gems/sequel-5.96.0/lib/sequel/adapters/postgres.rb:348:in 'block (2 levels) in Sequel::Postgres::Database#execute'
	from /Users/dentarg/.arm64_rubies/3.4.3/lib/ruby/gems/3.4.0/gems/sequel-5.96.0/lib/sequel/adapters/postgres.rb:555:in 'Sequel::Postgres::Database#check_database_errors'
	from /Users/dentarg/.arm64_rubies/3.4.3/lib/ruby/gems/3.4.0/gems/sequel-5.96.0/lib/sequel/adapters/postgres.rb:348:in 'block in Sequel::Postgres::Database#execute'
	from /Users/dentarg/.arm64_rubies/3.4.3/lib/ruby/gems/3.4.0/gems/sequel-5.96.0/lib/sequel/connection_pool/timed_queue.rb:90:in 'Sequel::TimedQueueConnectionPool#hold'
	from /Users/dentarg/.arm64_rubies/3.4.3/lib/ruby/gems/3.4.0/gems/sequel-5.96.0/lib/sequel/database/connecting.rb:283:in 'Sequel::Database#synchronize'
	from /Users/dentarg/.arm64_rubies/3.4.3/lib/ruby/gems/3.4.0/gems/sequel-5.96.0/lib/sequel/adapters/postgres.rb:348:in 'Sequel::Postgres::Database#execute'
	from /Users/dentarg/.arm64_rubies/3.4.3/lib/ruby/gems/3.4.0/gems/sequel-5.96.0/lib/sequel/dataset/actions.rb:1197:in 'Sequel::Dataset#execute'
	from /Users/dentarg/.arm64_rubies/3.4.3/lib/ruby/gems/3.4.0/gems/sequel_pg-1.17.2/lib/sequel/extensions/pg_streaming.rb:124:in 'Sequel::Postgres::Streaming::DatasetMethods#fetch_rows'
	from /Users/dentarg/.arm64_rubies/3.4.3/lib/ruby/gems/3.4.0/gems/sequel-5.96.0/lib/sequel/dataset/actions.rb:164:in 'Sequel::Dataset#each'
	from /Users/dentarg/.arm64_rubies/3.4.3/lib/ruby/gems/3.4.0/gems/sequel_pg-1.17.2/lib/sequel_pg/sequel_pg.rb:83:in 'Sequel::Postgres::Dataset#each'
	from repro.rb:26:in 'block in <main>'
	from /Users/dentarg/.arm64_rubies/3.4.3/lib/ruby/gems/3.4.0/gems/sequel-5.96.0/lib/sequel/database/connecting.rb:56:in 'Sequel::Database.connect'
	from /Users/dentarg/.arm64_rubies/3.4.3/lib/ruby/gems/3.4.0/gems/sequel-5.96.0/lib/sequel/core.rb:124:in 'Sequel::SequelMethods#connect'
	from repro.rb:22:in '<main>'
/Users/dentarg/.arm64_rubies/3.4.3/lib/ruby/gems/3.4.0/gems/sequel_pg-1.17.2/lib/sequel/extensions/pg_streaming.rb:92:in 'Sequel::Postgres::Adapter#set_single_row_mode': cannot set single row mode (PG::Error)
	from /Users/dentarg/.arm64_rubies/3.4.3/lib/ruby/gems/3.4.0/gems/sequel_pg-1.17.2/lib/sequel/extensions/pg_streaming.rb:92:in 'Sequel::Postgres::Streaming::AdapterMethods#execute_query'
	from /Users/dentarg/.arm64_rubies/3.4.3/lib/ruby/gems/3.4.0/gems/sequel-5.96.0/lib/sequel/adapters/postgres.rb:159:in 'block in Sequel::Postgres::Adapter#execute'
	from /Users/dentarg/.arm64_rubies/3.4.3/lib/ruby/gems/3.4.0/gems/sequel-5.96.0/lib/sequel/adapters/postgres.rb:136:in 'Sequel::Postgres::Adapter#check_disconnect_errors'
	from /Users/dentarg/.arm64_rubies/3.4.3/lib/ruby/gems/3.4.0/gems/sequel-5.96.0/lib/sequel/adapters/postgres.rb:159:in 'Sequel::Postgres::Adapter#execute'
	from /Users/dentarg/.arm64_rubies/3.4.3/lib/ruby/gems/3.4.0/gems/sequel-5.96.0/lib/sequel/adapters/postgres.rb:532:in 'Sequel::Postgres::Database#_execute'
	from /Users/dentarg/.arm64_rubies/3.4.3/lib/ruby/gems/3.4.0/gems/sequel_pg-1.17.2/lib/sequel/extensions/pg_streaming.rb:49:in 'Sequel::Postgres::Streaming#_execute'
	from /Users/dentarg/.arm64_rubies/3.4.3/lib/ruby/gems/3.4.0/gems/sequel-5.96.0/lib/sequel/adapters/postgres.rb:348:in 'block (2 levels) in Sequel::Postgres::Database#execute'
	from /Users/dentarg/.arm64_rubies/3.4.3/lib/ruby/gems/3.4.0/gems/sequel-5.96.0/lib/sequel/adapters/postgres.rb:555:in 'Sequel::Postgres::Database#check_database_errors'
	from /Users/dentarg/.arm64_rubies/3.4.3/lib/ruby/gems/3.4.0/gems/sequel-5.96.0/lib/sequel/adapters/postgres.rb:348:in 'block in Sequel::Postgres::Database#execute'
	from /Users/dentarg/.arm64_rubies/3.4.3/lib/ruby/gems/3.4.0/gems/sequel-5.96.0/lib/sequel/connection_pool/timed_queue.rb:90:in 'Sequel::TimedQueueConnectionPool#hold'
	from /Users/dentarg/.arm64_rubies/3.4.3/lib/ruby/gems/3.4.0/gems/sequel-5.96.0/lib/sequel/database/connecting.rb:283:in 'Sequel::Database#synchronize'
	from /Users/dentarg/.arm64_rubies/3.4.3/lib/ruby/gems/3.4.0/gems/sequel-5.96.0/lib/sequel/adapters/postgres.rb:348:in 'Sequel::Postgres::Database#execute'
	from /Users/dentarg/.arm64_rubies/3.4.3/lib/ruby/gems/3.4.0/gems/sequel-5.96.0/lib/sequel/dataset/actions.rb:1197:in 'Sequel::Dataset#execute'
	from /Users/dentarg/.arm64_rubies/3.4.3/lib/ruby/gems/3.4.0/gems/sequel_pg-1.17.2/lib/sequel/extensions/pg_streaming.rb:124:in 'Sequel::Postgres::Streaming::DatasetMethods#fetch_rows'
	from /Users/dentarg/.arm64_rubies/3.4.3/lib/ruby/gems/3.4.0/gems/sequel-5.96.0/lib/sequel/dataset/actions.rb:164:in 'Sequel::Dataset#each'
	from /Users/dentarg/.arm64_rubies/3.4.3/lib/ruby/gems/3.4.0/gems/sequel_pg-1.17.2/lib/sequel_pg/sequel_pg.rb:83:in 'Sequel::Postgres::Dataset#each'
	from repro.rb:26:in 'block in <main>'
	from /Users/dentarg/.arm64_rubies/3.4.3/lib/ruby/gems/3.4.0/gems/sequel-5.96.0/lib/sequel/database/connecting.rb:56:in 'Sequel::Database.connect'
	from /Users/dentarg/.arm64_rubies/3.4.3/lib/ruby/gems/3.4.0/gems/sequel-5.96.0/lib/sequel/core.rb:124:in 'Sequel::SequelMethods#connect'
	from repro.rb:22:in '<main>'

[larskanis]

I can not repro this on x86_64-linux. The precompiled gem work equally to the source gem there:

$ ruby test-sequel-single-row-mode.rb 
Connecting to postgres:///foo_72
{version: "PostgreSQL 17.6 (Ubuntu 17.6-1.pgdg24.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 13.3.0-6ubuntu2~24.04) 13.3.0, 64-bit"}

[jeremyevans]

@dentarg Sorry I didn't think of this last night, but I think the issue may be that the precompiled pg gem statically links libpq. If so, what may be happening is that static libpq version in the pg gem differs from libpq on the system that is dynamically linked to the sequel_pg gem. This will likely not cause issues if they are from the same major PostgreSQL version and/or use the compatible compilation flags, but otherwise, I could see problems happening. This would explain why it is only an issue in certain cases.

[dentarg]

It works after installing postgresql@17 and reinstalling the gems

$ brew info postgresql@17
==> postgresql@17: stable 17.6 (bottled) [keg-only]
Object-relational database system
https://www.postgresql.org/
Installed
/opt/homebrew/Cellar/postgresql@17/17.6 (3,821 files, 72MB)
  Poured from bottle using the formulae.brew.sh API on 2025-09-23 at 11:26:26
From: https://github.com/Homebrew/homebrew-core/blob/HEAD/Formula/p/postgresql@17.rb
...

$ gem uninstall --all --force sequel_pg pg
Successfully uninstalled sequel_pg-1.17.2
Successfully uninstalled pg-1.6.2-arm64-darwin

$ ruby repro.rb
Fetching gem metadata from https://rubygems.org/........
Resolving dependencies...
Fetching pg 1.6.2 (arm64-darwin)
Installing pg 1.6.2 (arm64-darwin)
Fetching sequel_pg 1.17.2
Installing sequel_pg 1.17.2 with native extensions
Connecting to postgres://localhost/foo_75
{version: "PostgreSQL 17.6 (Homebrew) on aarch64-apple-darwin24.4.0, compiled by Apple clang version 17.0.0 (clang-1700.0.13.3), 64-bit"}

https://github.com/ged/ruby-pg/blob/c75ed8da19c356b52d83da2627295760780d3d36/CHANGELOG.md does say

Update native binary gems to OpenSSL-3.5.2, krb5-1.22.1 and PostgreSQL-17.6.

[jeremyevans]

Thanks for testing. I added documentation to sequel_pg to address this: jeremyevans/sequel_pg@1895441

I don't think changes are needed in pg, this is just a consequence of mixing static and dynamic linking to the same library.

[dentarg]

Agree, it is just unfortunate that the user can trip on this if they don't keep certain pieces of the puzzle in sync. It is good to have the workaround documented. I will leave this open for a while (but maintainers can close if they want) in case there are any others out there that runs into this.

[larskanis]

IMHO the best solution would be to use the plain pg.gem by sequel. At least since pg-1.1.0 (anno 2018) there is almost everything implemented directly in pg, what sequel_pg adds. Implementing type casts with the help of pg's type maps should result in the same performance as sequel_pg. And pg also has methods for streaming and all kind of output variations. And if there are missing features, I'm open to help to get them. So sequel_pg is mostly obsolete when these features are used by sequel.