How to run examples:
- Run
$ createdb railstestdb
to create DB - Run examples with Ruby
$ ruby demo.rb
How to run examples:
$ createdb railstestdb
to create DB$ ruby demo.rb
begin | |
require "bundler/inline" | |
rescue LoadError => e | |
$stderr.puts "Bundler version 1.10 or later is required. Please update your Bundler" | |
raise e | |
end | |
gemfile(true) do | |
source "https://rubygems.org" | |
gem "rails" | |
gem "pg" | |
end | |
require "active_record" | |
require "action_controller/railtie" | |
ActiveRecord::Base.establish_connection(adapter: "postgresql", database: "railstestdb") | |
ActiveRecord::Base.logger = Logger.new(STDOUT) | |
ActiveRecord::Schema.define do | |
enable_extension "plpgsql" | |
create_table "orders", force: :cascade do |t| | |
t.bigint "user_id", null: false | |
t.decimal "amount" | |
t.datetime "created_at", precision: 6, null: false | |
t.datetime "updated_at", precision: 6, null: false | |
t.index ["user_id"], name: "index_orders_on_user_id" | |
end | |
create_table "users", force: :cascade do |t| | |
t.datetime "created_at", precision: 6, null: false | |
t.datetime "updated_at", precision: 6, null: false | |
end | |
add_foreign_key "orders", "users" | |
end | |
class User < ActiveRecord::Base | |
has_many :orders | |
end | |
class Order < ActiveRecord::Base | |
belongs_to :user | |
end | |
User.insert_all(10000.times.map { |id| { created_at: Time.now, updated_at: Time.now } }) | |
Order.insert_all(100000.times.map { |id| { user_id: rand(1000) + 1, amount: rand(1000) / 10.0, created_at: Time.now, updated_at: Time.now } }) | |
query = <<~SQL | |
EXPLAIN ANALYZE SELECT users.id, SUM(orders.amount), COUNT(orders.id) | |
FROM users JOIN orders ON orders.user_id = users.id | |
GROUP BY users.id | |
HAVING SUM(orders.amount) > 100 AND COUNT(orders.id) > 1 | |
ORDER BY SUM(orders.amount) | |
LIMIT 50 | |
SQL | |
puts ActiveRecord::Base.connection.execute(query).to_a |
begin | |
require "bundler/inline" | |
rescue LoadError => e | |
$stderr.puts "Bundler version 1.10 or later is required. Please update your Bundler" | |
raise e | |
end | |
gemfile(true) do | |
source "https://rubygems.org" | |
gem "rails" | |
gem "pg" | |
gem "hairtrigger", git: "https://github.com/DmitryTsepelev/hair_trigger", branch: "rails-6" | |
end | |
require "active_record" | |
require "action_controller/railtie" | |
ActiveRecord::Base.establish_connection(adapter: "postgresql", database: "railstestdb") | |
ActiveRecord::Base.logger = Logger.new(STDOUT) | |
ActiveRecord::Schema.define do | |
enable_extension "plpgsql" | |
create_table "orders", force: :cascade do |t| | |
t.bigint "user_id", null: false | |
t.decimal "amount" | |
t.datetime "created_at", precision: 6, null: false | |
t.datetime "updated_at", precision: 6, null: false | |
t.index ["user_id"], name: "index_orders_on_user_id" | |
end | |
create_table "user_stats", force: :cascade do |t| | |
t.integer "user_id", null: false | |
t.decimal "orders_amount" | |
t.integer "orders_count" | |
t.index ["user_id"], name: "index_user_stats_on_user_id", unique: true | |
end | |
create_table "users", force: :cascade do |t| | |
t.datetime "created_at", precision: 6, null: false | |
t.datetime "updated_at", precision: 6, null: false | |
end | |
add_foreign_key "orders", "users" | |
create_trigger("orders_after_insert_update_row_tr", :generated => true, :compatibility => 1). | |
on("orders"). | |
after(:insert, :update) do | |
<<-SQL_ACTIONS | |
INSERT INTO user_stats (user_id, orders_amount, orders_count) | |
SELECT | |
NEW.user_id as user_id, | |
SUM(orders.amount) as orders_amount, | |
COUNT(orders.id) as orders_count | |
FROM orders WHERE orders.user_id = NEW.user_id | |
ON CONFLICT (user_id) DO UPDATE | |
SET | |
orders_amount = EXCLUDED.orders_amount, | |
orders_count = EXCLUDED.orders_count; | |
SQL_ACTIONS | |
end | |
end | |
class User < ActiveRecord::Base | |
has_many :orders | |
has_one :user_stat | |
end | |
class UserStat < ActiveRecord::Base | |
belongs_to :user | |
end | |
class Order < ActiveRecord::Base | |
belongs_to :user | |
trigger.after(:insert) do | |
<<~SQL | |
INSERT INTO user_stats (user_id, orders_amount, orders_count) | |
SELECT | |
NEW.user_id as user_id, | |
SUM(orders.amount) as orders_amount, | |
COUNT(orders.id) as orders_count | |
FROM orders WHERE orders.user_id = NEW.user_id | |
ON CONFLICT (user_id) DO UPDATE | |
SET | |
orders_amount = EXCLUDED.orders_amount, | |
orders_count = EXCLUDED.orders_count; | |
SQL | |
end | |
end | |
user = User.create | |
threads = [] | |
4.times do | |
threads << Thread.new(user.id) do |user_id| | |
user = User.find(user_id) | |
user.orders.create(amount: rand(1000) / 10.0) | |
end | |
end | |
threads.each(&:join) | |
inconsistent_stats = UserStat.joins(user: :orders) | |
.where(user_id: user.id) | |
.having("user_stats.orders_amount <> SUM(orders.amount)") | |
.group("user_stats.id") | |
if inconsistent_stats.any? | |
calculated_amount = UserStat.find_by(user: user).orders_amount | |
real_amount = Order.where(user: user).sum(:amount).to_f | |
puts | |
puts "Race condition detected:" | |
puts "calculated amount: #{calculated_amount}" | |
puts "real amount: #{real_amount}." | |
else | |
puts | |
puts "Data is consistent." | |
end |
begin | |
require "bundler/inline" | |
rescue LoadError => e | |
$stderr.puts "Bundler version 1.10 or later is required. Please update your Bundler" | |
raise e | |
end | |
gemfile(true) do | |
source "https://rubygems.org" | |
gem "rails" | |
gem "pg" | |
gem "hairtrigger", git: "https://github.com/DmitryTsepelev/hair_trigger", branch: "rails-6" | |
end | |
require "active_record" | |
require "action_controller/railtie" | |
ActiveRecord::Base.establish_connection(adapter: "postgresql", database: "railstestdb") | |
ActiveRecord::Base.logger = Logger.new(STDOUT) | |
ActiveRecord::Schema.define do | |
enable_extension "plpgsql" | |
create_table "orders", force: :cascade do |t| | |
t.bigint "user_id", null: false | |
t.decimal "amount" | |
t.datetime "created_at", precision: 6, null: false | |
t.datetime "updated_at", precision: 6, null: false | |
t.index ["user_id"], name: "index_orders_on_user_id" | |
end | |
create_table "user_stats", force: :cascade do |t| | |
t.integer "user_id", null: false | |
t.decimal "orders_amount", index: true | |
t.integer "orders_count", index: true | |
t.index ["user_id"], name: "index_user_stats_on_user_id", unique: true | |
end | |
create_table "users", force: :cascade do |t| | |
t.datetime "created_at", precision: 6, null: false | |
t.datetime "updated_at", precision: 6, null: false | |
end | |
add_foreign_key "orders", "users" | |
create_trigger("orders_after_insert_update_row_tr", :generated => true, :compatibility => 1). | |
on("orders"). | |
after(:insert, :update) do | |
<<-SQL_ACTIONS | |
PERFORM pg_advisory_xact_lock(NEW.user_id); | |
INSERT INTO user_stats (user_id, orders_amount, orders_count) | |
SELECT | |
NEW.user_id as user_id, | |
SUM(orders.amount) as orders_amount, | |
COUNT(orders.id) as orders_count | |
FROM orders WHERE orders.user_id = NEW.user_id | |
ON CONFLICT (user_id) DO UPDATE | |
SET | |
orders_amount = EXCLUDED.orders_amount, | |
orders_count = EXCLUDED.orders_count; | |
SQL_ACTIONS | |
end | |
end | |
class User < ActiveRecord::Base | |
has_many :orders | |
has_one :user_stat | |
end | |
class UserStat < ActiveRecord::Base | |
belongs_to :user | |
end | |
class Order < ActiveRecord::Base | |
belongs_to :user | |
trigger.after(:insert) do | |
<<~SQL | |
PERFORM pg_advisory_xact_lock(NEW.user_id); | |
INSERT INTO user_stats (user_id, orders_amount, orders_count) | |
SELECT | |
NEW.user_id as user_id, | |
SUM(orders.amount) as orders_amount, | |
COUNT(orders.id) as orders_count | |
FROM orders WHERE orders.user_id = NEW.user_id | |
ON CONFLICT (user_id) DO UPDATE | |
SET | |
orders_amount = EXCLUDED.orders_amount, | |
orders_count = EXCLUDED.orders_count; | |
SQL | |
end | |
end | |
user = User.create | |
threads = [] | |
4.times do | |
threads << Thread.new(user.id) do |user_id| | |
user = User.find(user_id) | |
user.orders.create(amount: rand(1000) / 10.0) | |
end | |
end | |
threads.each(&:join) | |
inconsistent_stats = UserStat.joins(user: :orders) | |
.where(user_id: user.id) | |
.having("user_stats.orders_amount <> SUM(orders.amount)") | |
.group("user_stats.id") | |
if inconsistent_stats.any? | |
puts | |
puts "Race condition detected:" | |
puts "calculated amount: #{UserStat.find_by(user: user).orders_amount}" | |
puts "real amount: #{Order.where(user: user).sum(:amount).to_f}." | |
else | |
puts | |
puts "Data is consistent." | |
end | |
User.insert_all(10000.times.map { |id| { created_at: Time.now, updated_at: Time.now } }) | |
Order.insert_all(100000.times.map { |id| { user_id: rand(1000) + 1, amount: rand(1000) / 10.0, created_at: Time.now, updated_at: Time.now } }) | |
query = <<~SQL | |
EXPLAIN ANALYZE SELECT user_id, orders_amount, orders_count | |
FROM user_stats | |
WHERE orders_amount > 100 AND orders_count > 1 | |
ORDER BY orders_amount | |
LIMIT 50 | |
SQL | |
puts ActiveRecord::Base.connection.execute(query).to_a |
begin | |
require "bundler/inline" | |
rescue LoadError => e | |
$stderr.puts "Bundler version 1.10 or later is required. Please update your Bundler" | |
raise e | |
end | |
gemfile(true) do | |
source "https://rubygems.org" | |
gem "rails" | |
gem "pg" | |
gem "hairtrigger", git: "https://github.com/DmitryTsepelev/hair_trigger", branch: "rails-6" | |
end | |
require "active_record" | |
require "action_controller/railtie" | |
ActiveRecord::Base.establish_connection(adapter: "postgresql", database: "railstestdb") | |
ActiveRecord::Base.logger = Logger.new(STDOUT) | |
ActiveRecord::Schema.define do | |
enable_extension "plpgsql" | |
create_table "orders", force: :cascade do |t| | |
t.bigint "user_id", null: false | |
t.decimal "amount" | |
t.datetime "created_at", precision: 6, null: false | |
t.datetime "updated_at", precision: 6, null: false | |
t.index ["user_id"], name: "index_orders_on_user_id" | |
end | |
create_table "user_stats", force: :cascade do |t| | |
t.integer "user_id", null: false | |
t.decimal "orders_amount", index: true | |
t.integer "orders_count", index: true | |
t.index ["user_id"], name: "index_user_stats_on_user_id", unique: true | |
end | |
create_table "users", force: :cascade do |t| | |
t.datetime "created_at", precision: 6, null: false | |
t.datetime "updated_at", precision: 6, null: false | |
end | |
add_foreign_key "orders", "users" | |
create_trigger("orders_after_insert_update_row_tr", :generated => true, :compatibility => 1). | |
on("orders"). | |
after(:insert, :update) do | |
<<-SQL_ACTIONS | |
INSERT INTO user_stats (user_id, orders_amount, orders_count) | |
SELECT | |
NEW.user_id as user_id, | |
NEW.amount as orders_amount, | |
1 as orders_count | |
ON CONFLICT (user_id) DO UPDATE | |
SET | |
orders_amount = user_stats.orders_amount + EXCLUDED.orders_amount, | |
orders_count = user_stats.orders_count + EXCLUDED.orders_count; | |
SQL_ACTIONS | |
end | |
end | |
class User < ActiveRecord::Base | |
has_many :orders | |
has_one :user_stat | |
end | |
class UserStat < ActiveRecord::Base | |
belongs_to :user | |
end | |
class Order < ActiveRecord::Base | |
belongs_to :user | |
trigger.after(:insert) do | |
<<~SQL | |
INSERT INTO user_stats (user_id, orders_amount, orders_count) | |
SELECT | |
NEW.user_id as user_id, | |
NEW.amount as orders_amount, | |
1 as orders_count | |
ON CONFLICT (user_id) DO UPDATE | |
SET | |
orders_amount = user_stats.orders_amount + EXCLUDED.orders_amount, | |
orders_count = user_stats.orders_count + EXCLUDED.orders_count; | |
SQL | |
end | |
end | |
user = User.create | |
threads = [] | |
4.times do | |
threads << Thread.new(user.id) do |user_id| | |
user = User.find(user_id) | |
user.orders.create(amount: rand(1000) / 10.0) | |
end | |
end | |
threads.each(&:join) | |
inconsistent_stats = UserStat.joins(user: :orders) | |
.where(user_id: user.id) | |
.having("user_stats.orders_amount <> SUM(orders.amount)") | |
.group("user_stats.id") | |
if inconsistent_stats.any? | |
puts | |
puts "Race condition detected:" | |
puts "calculated amount: #{UserStat.find_by(user: user).orders_amount}" | |
puts "real amount: #{Order.where(user: user).sum(:amount).to_f}." | |
else | |
puts | |
puts "Data is consistent." | |
end | |
User.insert_all(10000.times.map { |id| { created_at: Time.now, updated_at: Time.now } }) | |
Order.insert_all(100000.times.map { |id| { user_id: rand(1000) + 1, amount: rand(1000) / 10.0, created_at: Time.now, updated_at: Time.now } }) | |
query = <<~SQL | |
EXPLAIN ANALYZE SELECT user_id, orders_amount, orders_count | |
FROM user_stats | |
WHERE orders_amount > 100 AND orders_count > 1 | |
ORDER BY orders_amount | |
LIMIT 50 | |
SQL | |
puts ActiveRecord::Base.connection.execute(query).to_a |
begin | |
require "bundler/inline" | |
rescue LoadError => e | |
$stderr.puts "Bundler version 1.10 or later is required. Please update your Bundler" | |
raise e | |
end | |
gemfile(true) do | |
source "https://rubygems.org" | |
gem "rails" | |
gem "pg" | |
gem "hairtrigger", git: "https://github.com/DmitryTsepelev/hair_trigger", branch: "rails-6" | |
end | |
require "active_record" | |
require "action_controller/railtie" | |
ActiveRecord::Base.establish_connection(adapter: "postgresql", database: "railstestdb") | |
ActiveRecord::Base.logger = Logger.new(STDOUT) | |
ActiveRecord::Schema.define do | |
enable_extension "plpgsql" | |
create_table "orders", force: :cascade do |t| | |
t.bigint "user_id", null: false | |
t.decimal "amount" | |
t.datetime "created_at", precision: 6, null: false | |
t.datetime "updated_at", precision: 6, null: false | |
t.index ["user_id"], name: "index_orders_on_user_id" | |
end | |
create_table "user_stats", force: :cascade do |t| | |
t.integer "user_id", null: false | |
t.decimal "orders_amount", index: true | |
t.integer "orders_count", index: true | |
t.index ["user_id"], name: "index_user_stats_on_user_id", unique: true | |
end | |
create_table "users", force: :cascade do |t| | |
t.datetime "created_at", precision: 6, null: false | |
t.datetime "updated_at", precision: 6, null: false | |
end | |
add_foreign_key "orders", "users" | |
create_trigger("orders_after_insert_update_row_tr", :generated => true, :compatibility => 1). | |
on("orders"). | |
after(:insert, :update) do | |
<<-SQL_ACTIONS | |
<<insert_update>> | |
LOOP | |
UPDATE user_stats | |
SET orders_count = orders_count + 1, | |
orders_amount = orders_amount + NEW.amount | |
WHERE user_id = NEW.user_id; | |
EXIT insert_update WHEN found; | |
BEGIN | |
INSERT INTO user_stats ( | |
user_id, orders_amount, orders_count | |
) VALUES ( | |
NEW.user_id, 1, NEW.amount | |
); | |
EXIT insert_update; | |
EXCEPTION | |
WHEN UNIQUE_VIOLATION THEN | |
-- do nothing | |
END; | |
END LOOP insert_update; | |
SQL_ACTIONS | |
end | |
end | |
class User < ActiveRecord::Base | |
has_many :orders | |
has_one :user_stat | |
end | |
class UserStat < ActiveRecord::Base | |
belongs_to :user | |
end | |
class Order < ActiveRecord::Base | |
belongs_to :user | |
trigger.after(:insert) do | |
<<~SQL | |
<<insert_update>> | |
LOOP | |
UPDATE user_stats | |
SET orders_count = orders_count + 1, | |
orders_amount = orders_amount + NEW.amount | |
WHERE user_id = NEW.user_id; | |
EXIT insert_update WHEN found; | |
BEGIN | |
INSERT INTO user_stats ( | |
user_id, orders_amount, orders_count | |
) VALUES ( | |
NEW.user_id, 1, NEW.amount | |
); | |
EXIT insert_update; | |
EXCEPTION | |
WHEN UNIQUE_VIOLATION THEN | |
-- do nothing | |
END; | |
END LOOP insert_update; | |
SQL | |
end | |
end | |
user = User.create | |
threads = [] | |
4.times do | |
threads << Thread.new(user.id) do |user_id| | |
user = User.find(user_id) | |
user.orders.create(amount: rand(1000) / 10.0) | |
end | |
end | |
threads.each(&:join) | |
inconsistent_stats = UserStat.joins(user: :orders) | |
.where(user_id: user.id) | |
.having("user_stats.orders_amount <> SUM(orders.amount)") | |
.group("user_stats.id") | |
if inconsistent_stats.any? | |
puts | |
puts "Race condition detected:" | |
puts "calculated amount: #{UserStat.find_by(user: user).orders_amount}" | |
puts "real amount: #{Order.where(user: user).sum(:amount).to_f}." | |
else | |
puts | |
puts "Data is consistent." | |
end | |
User.insert_all(10000.times.map { |id| { created_at: Time.now, updated_at: Time.now } }) | |
Order.insert_all(100000.times.map { |id| { user_id: rand(1000) + 1, amount: rand(1000) / 10.0, created_at: Time.now, updated_at: Time.now } }) | |
query = <<~SQL | |
EXPLAIN ANALYZE SELECT user_id, orders_amount, orders_count | |
FROM user_stats | |
WHERE orders_amount > 100 AND orders_count > 1 | |
ORDER BY orders_amount | |
LIMIT 50 | |
SQL | |
puts ActiveRecord::Base.connection.execute(query).to_a |