index_many_schemas.rb

#!/usr/bin/env ruby

#
# Demonstration of deadlocks when creating concurrent indexes simultaneously on different tablespaces
#

TEST_DATABASE='test_index_many_schemas'
TOTAL_SCHEMAS=10
TOTAL_ROWS=10000

def psql(sql)
  %x(psql #{TEST_DATABASE} -c "#{sql}")
end

def each_schema(&block)
  1.upto(TOTAL_SCHEMAS).each do |i|
    schema = "test_schema_#{i}"
    table = "#{schema}.test_table"
    block.call schema, table
  end
end

puts "Hi there, let me show you some deadlocks."

puts "creating #{TEST_DATABASE}"
system "createdb #{TEST_DATABASE}"

puts "creating #{TOTAL_SCHEMAS} schemas, each with a table with #{TOTAL_ROWS}"

each_schema do |schema, table|
  psql("CREATE SCHEMA #{schema};")
  psql("CREATE TABLE #{table} as select s, md5(random()::text) as stuff from generate_series(1,#{TOTAL_ROWS}) s;")
end

pids = []

puts "creating indexes concurrently, (with parallelism)"
each_schema do |schema, table|
  pids << fork do 
    psql("CREATE INDEX CONCURRENTLY ON #{table} (stuff)")
  end
end

pids.each { |pid| Process.waitpid(pid) }

puts "dropping #{TEST_DATABASE}"
system "dropdb #{TEST_DATABASE}"


puts "done!"

some_example_output.txt

Hi there, let me show you some deadlocks.
creating test_index_many_schemas
creating 10 schemas, each with a table with 10000
creating indexes concurrently, (with parallelism)
ERROR:  deadlock detected
DETAIL:  Process 55509 waits for ShareLock on virtual transaction 4/548; blocked by process 55502.
Process 55502 waits for ShareLock on virtual transaction 11/12; blocked by process 55509.
HINT:  See server log for query details.
ERROR:  deadlock detected
DETAIL:  Process 55500 waits for ShareLock on virtual transaction 4/548; blocked by process 55502.
Process 55502 waits for ShareLock on virtual transaction 2/10379; blocked by process 55500.
HINT:  See server log for query details.
ERROR:  deadlock detected
DETAIL:  Process 55502 waits for ShareLock on virtual transaction 3/1472; blocked by process 55501
Process 55501 waits for ShareLock on virtual transaction 4/548; blocked by process 55502.
HINT:  See server log for query details.
ERROR:  deadlock detected
DETAIL:  Process 55508 waits for ShareLock on virtual transaction 3/1472; blocked by process 55501
Process 55501 waits for ShareLock on virtual transaction 10/12; blocked by process 55508.
HINT:  See server log for query details.
ERROR:  deadlock detected
DETAIL:  Process 55501 waits for ShareLock on virtual transaction 8/12; blocked by process 55506.
Process 55506 waits for ShareLock on virtual transaction 3/1472; blocked by process 55501.
HINT:  See server log for query details.
ERROR:  deadlock detected
DETAIL:  Process 55506 waits for ShareLock on virtual transaction 6/285; blocked by process 55504.
Process 55504 waits for ShareLock on virtual transaction 8/12; blocked by process 55506.
HINT:  See server log for query details.
ERROR:  deadlock detected
DETAIL:  Process 55507 waits for ShareLock on virtual transaction 6/285; blocked by process 55504.
Process 55504 waits for ShareLock on virtual transaction 9/12; blocked by process 55507.
HINT:  See server log for query details.
ERROR:  deadlock detected
DETAIL:  Process 55504 waits for ShareLock on virtual transaction 7/92; blocked by process 55505.
Process 55505 waits for ShareLock on virtual transaction 6/285; blocked by process 55504.
HINT:  See server log for query details.
dropping test_index_many_schemas
done!