Skip to content

Instantly share code, notes, and snippets.

@yahonda

yahonda/disable_prepared_statement_from_rails.md

Created March 25, 2024 23:45
Show Gist options
  • Save yahonda/2c2d6ac7a955886a305750eecfd07c5e to your computer and use it in GitHub Desktop.
Save yahonda/2c2d6ac7a955886a305750eecfd07c5e to your computer and use it in GitHub Desktop.
Download ZIP
Raw
disable_prepared_statement_from_rails.md
  • active_record2_non_prepared.rb that disables prepared statement prepared_statements: false
# frozen_string_literal: true

require "bundler/inline"

gemfile(true) do
  source "https://rubygems.org"

  git_source(:github) { |repo| "https://github.com/#{repo}.git" }

  # gem "rails", path: "/home/yahonda/src/github.com/rails/rails"
  # If you want to test against edge Rails replace the previous line with this:
  gem "rails", github: "rails/rails", branch: "main"

  gem "pg"
end

require "active_record"
require "minitest/autorun"
require "logger"

# This connection will do for database-independent bug reports.
ActiveRecord::Base.establish_connection(adapter: "postgresql", database: "test", prepared_statements: false)
ActiveRecord::Base.logger = Logger.new(STDOUT)

ActiveRecord::Schema.define do
  create_table :posts, force: true do |t|
  end
end

class Post < ActiveRecord::Base
end

class BugTest < Minitest::Test
  def test_post_count
    (1..10).each do |i|
      post = Post.create!
      post = Post.where(id: (1 .. i).to_a)
      assert_equal i, post.count
    end
  end
end
  • Set query_id_const_merge_threshold = 5 and run pg_stat_statements_reset()
postgres=# SET pg_stat_statements.query_id_const_merge_threshold = 5;
SET
postgres=# show pg_stat_statements.query_id_const_merge_threshold;
 pg_stat_statements.query_id_const_merge_threshold
---------------------------------------------------
 5
(1 row)

postgres=# select pg_stat_statements_reset();
   pg_stat_statements_reset
-------------------------------
 2024-03-26 08:42:09.542416+09
(1 row)
  • Run active_record2_non_prepared.rb
$ ruby active_record2_non_prepared.rb
Fetching https://github.com/rails/rails.git
Fetching gem metadata from https://rubygems.org/..........
Resolving dependencies...
-- create_table(:posts, {:force=>true})
D, [2024-03-26T08:42:19.969679 #66177] DEBUG -- :    (5.1ms)  DROP TABLE IF EXISTS "posts"
D, [2024-03-26T08:42:19.977081 #66177] DEBUG -- :    (7.0ms)  CREATE TABLE "posts" ("id" bigserial primary key)
   -> 0.0281s
D, [2024-03-26T08:42:20.005974 #66177] DEBUG -- :   ActiveRecord::InternalMetadata Load (1.0ms)  SELECT * FROM "ar_internal_metadata" WHERE "ar_internal_metadata"."key" = 'environment' ORDER BY "ar_internal_metadata"."key" ASC LIMIT 1
Run options: --seed 56637

# Running:

D, [2024-03-26T08:42:20.145230 #66177] DEBUG -- :   TRANSACTION (0.3ms)  BEGIN
D, [2024-03-26T08:42:20.146221 #66177] DEBUG -- :   Post Create (1.1ms)  INSERT INTO "posts" DEFAULT VALUES RETURNING "id"
D, [2024-03-26T08:42:20.149417 #66177] DEBUG -- :   TRANSACTION (2.8ms)  COMMIT
D, [2024-03-26T08:42:20.161027 #66177] DEBUG -- :   Post Count (0.7ms)  SELECT COUNT(*) FROM "posts" WHERE "posts"."id" = 1
D, [2024-03-26T08:42:20.161881 #66177] DEBUG -- :   TRANSACTION (0.2ms)  BEGIN
D, [2024-03-26T08:42:20.162257 #66177] DEBUG -- :   Post Create (0.6ms)  INSERT INTO "posts" DEFAULT VALUES RETURNING "id"
D, [2024-03-26T08:42:20.163562 #66177] DEBUG -- :   TRANSACTION (1.1ms)  COMMIT
D, [2024-03-26T08:42:20.164789 #66177] DEBUG -- :   Post Count (0.5ms)  SELECT COUNT(*) FROM "posts" WHERE "posts"."id" IN (1, 2)
D, [2024-03-26T08:42:20.165490 #66177] DEBUG -- :   TRANSACTION (0.1ms)  BEGIN
D, [2024-03-26T08:42:20.165826 #66177] DEBUG -- :   Post Create (0.5ms)  INSERT INTO "posts" DEFAULT VALUES RETURNING "id"
D, [2024-03-26T08:42:20.167304 #66177] DEBUG -- :   TRANSACTION (1.0ms)  COMMIT
D, [2024-03-26T08:42:20.168368 #66177] DEBUG -- :   Post Count (0.5ms)  SELECT COUNT(*) FROM "posts" WHERE "posts"."id" IN (1, 2, 3)
D, [2024-03-26T08:42:20.169010 #66177] DEBUG -- :   TRANSACTION (0.1ms)  BEGIN
D, [2024-03-26T08:42:20.169369 #66177] DEBUG -- :   Post Create (0.5ms)  INSERT INTO "posts" DEFAULT VALUES RETURNING "id"
D, [2024-03-26T08:42:20.170511 #66177] DEBUG -- :   TRANSACTION (0.9ms)  COMMIT
D, [2024-03-26T08:42:20.171747 #66177] DEBUG -- :   Post Count (0.4ms)  SELECT COUNT(*) FROM "posts" WHERE "posts"."id" IN (1, 2, 3, 4)
D, [2024-03-26T08:42:20.172581 #66177] DEBUG -- :   TRANSACTION (0.1ms)  BEGIN
D, [2024-03-26T08:42:20.172901 #66177] DEBUG -- :   Post Create (0.5ms)  INSERT INTO "posts" DEFAULT VALUES RETURNING "id"
D, [2024-03-26T08:42:20.174123 #66177] DEBUG -- :   TRANSACTION (1.0ms)  COMMIT
D, [2024-03-26T08:42:20.175004 #66177] DEBUG -- :   Post Count (0.4ms)  SELECT COUNT(*) FROM "posts" WHERE "posts"."id" IN (1, 2, 3, 4, 5)
D, [2024-03-26T08:42:20.176334 #66177] DEBUG -- :   TRANSACTION (0.2ms)  BEGIN
D, [2024-03-26T08:42:20.176630 #66177] DEBUG -- :   Post Create (0.8ms)  INSERT INTO "posts" DEFAULT VALUES RETURNING "id"
D, [2024-03-26T08:42:20.177982 #66177] DEBUG -- :   TRANSACTION (1.2ms)  COMMIT
D, [2024-03-26T08:42:20.178790 #66177] DEBUG -- :   Post Count (0.4ms)  SELECT COUNT(*) FROM "posts" WHERE "posts"."id" IN (1, 2, 3, 4, 5, 6)
D, [2024-03-26T08:42:20.179378 #66177] DEBUG -- :   TRANSACTION (0.2ms)  BEGIN
D, [2024-03-26T08:42:20.179646 #66177] DEBUG -- :   Post Create (0.5ms)  INSERT INTO "posts" DEFAULT VALUES RETURNING "id"
D, [2024-03-26T08:42:20.181139 #66177] DEBUG -- :   TRANSACTION (1.0ms)  COMMIT
D, [2024-03-26T08:42:20.183735 #66177] DEBUG -- :   Post Count (1.2ms)  SELECT COUNT(*) FROM "posts" WHERE "posts"."id" IN (1, 2, 3, 4, 5, 6, 7)
D, [2024-03-26T08:42:20.186097 #66177] DEBUG -- :   TRANSACTION (0.2ms)  BEGIN
D, [2024-03-26T08:42:20.186633 #66177] DEBUG -- :   Post Create (0.7ms)  INSERT INTO "posts" DEFAULT VALUES RETURNING "id"
D, [2024-03-26T08:42:20.189440 #66177] DEBUG -- :   TRANSACTION (2.4ms)  COMMIT
D, [2024-03-26T08:42:20.190905 #66177] DEBUG -- :   Post Count (0.6ms)  SELECT COUNT(*) FROM "posts" WHERE "posts"."id" IN (1, 2, 3, 4, 5, 6, 7, 8)
D, [2024-03-26T08:42:20.192160 #66177] DEBUG -- :   TRANSACTION (0.2ms)  BEGIN
D, [2024-03-26T08:42:20.192573 #66177] DEBUG -- :   Post Create (1.0ms)  INSERT INTO "posts" DEFAULT VALUES RETURNING "id"
D, [2024-03-26T08:42:20.193876 #66177] DEBUG -- :   TRANSACTION (1.0ms)  COMMIT
D, [2024-03-26T08:42:20.195831 #66177] DEBUG -- :   Post Count (0.5ms)  SELECT COUNT(*) FROM "posts" WHERE "posts"."id" IN (1, 2, 3, 4, 5, 6, 7, 8, 9)
D, [2024-03-26T08:42:20.196569 #66177] DEBUG -- :   TRANSACTION (0.1ms)  BEGIN
D, [2024-03-26T08:42:20.196908 #66177] DEBUG -- :   Post Create (0.5ms)  INSERT INTO "posts" DEFAULT VALUES RETURNING "id"
D, [2024-03-26T08:42:20.198108 #66177] DEBUG -- :   TRANSACTION (1.0ms)  COMMIT
D, [2024-03-26T08:42:20.199430 #66177] DEBUG -- :   Post Count (0.4ms)  SELECT COUNT(*) FROM "posts" WHERE "posts"."id" IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
.

Finished in 0.066898s, 14.9482 runs/s, 149.4821 assertions/s.
1 runs, 10 assertions, 0 failures, 0 errors, 0 skips
  • Select pg_stat_statements and found the in clause values are not normalized Even if prepared_statements: false configured, this output looks like shows prepared statements.
postgres=# select query,calls from pg_stat_statements where query like 'SELECT COUNT%' order by 1;
                                            query                                             | calls
----------------------------------------------------------------------------------------------+-------
 SELECT COUNT(*) FROM "posts" WHERE "posts"."id" = $1                                         |     1
 SELECT COUNT(*) FROM "posts" WHERE "posts"."id" IN ($1, $2)                                  |     1
 SELECT COUNT(*) FROM "posts" WHERE "posts"."id" IN ($1, $2, $3)                              |     1
 SELECT COUNT(*) FROM "posts" WHERE "posts"."id" IN ($1, $2, $3, $4)                          |     1
 SELECT COUNT(*) FROM "posts" WHERE "posts"."id" IN ($1, $2, $3, $4, $5)                      |     1
 SELECT COUNT(*) FROM "posts" WHERE "posts"."id" IN ($1, $2, $3, $4, $5, $6)                  |     1
 SELECT COUNT(*) FROM "posts" WHERE "posts"."id" IN ($1, $2, $3, $4, $5, $6, $7)              |     1
 SELECT COUNT(*) FROM "posts" WHERE "posts"."id" IN ($1, $2, $3, $4, $5, $6, $7, $8)          |     1
 SELECT COUNT(*) FROM "posts" WHERE "posts"."id" IN ($1, $2, $3, $4, $5, $6, $7, $8, $9)      |     1
 SELECT COUNT(*) FROM "posts" WHERE "posts"."id" IN ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10) |     1
(10 rows)

postgres=#
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment