Skip to content

Instantly share code, notes, and snippets.

@DmitryTsepelev

DmitryTsepelev/Triggers.md

Last active November 25, 2019 13:54
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save DmitryTsepelev/82a44170b2317442df2d54a4c848cb6f to your computer and use it in GitHub Desktop.
Save DmitryTsepelev/82a44170b2317442df2d54a4c848cb6f to your computer and use it in GitHub Desktop.
Download ZIP
Raw
Triggers.md

How to run examples:

  1. Run $ createdb railstestdb to create DB
  2. Run examples with Ruby $ ruby demo.rb
Raw
triggers1.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
Raw
triggers2.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"
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
Raw
triggers3.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"
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
Raw
triggers4.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"
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
Raw
triggers5.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"
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment