Determine dependencies on given table/view or their columns, types (base/domain/composite), functions, rules and show DROP/ALTER/CREATE series to update their definitions.
require 'postgres'
require 'tsort'
require 'pp'
PGconn.translate_results = true
$PG_CLASSES = {}
class PgDependencyGraph
class DBObject
attr_accessor :row, :o_type, :nsp
def initialize(conn, class_id, obj_id, sub_id)
@o_type = $PG_CLASSES[class_id]
@row = conn.query(sql_for(@o_type, obj_id, sub_id)).first
@nsp = row['nsp']
if @o_type == 'pg_proc'
arg_types = row.last.split(" ")
unless arg_types.empty?
arg_type_names = arg_types.map {|oid| "format_type(#{oid}, -1)"}.join(", ")
row[-1] = "("+conn.query("SELECT #{arg_type_names}").first.join(", ") +")"
else
row[-1] = "()"
end
end
end
def sql_for(pg_class, obj_id, sub_id)
base_sql = case pg_class
when 'pg_type'
"select (case
when typtype = 'b' then 'BASE '
when typtype = 'c' then 'COMPOSITE '
when typtype = 'd' then 'DOMAIN '
when typtype = 'p' then 'PSEUDO '
end) || coalesce(
(select (CASE WHEN relkind = 'r' THEN 'TABLE'
WHEN relkind = 'v' THEN 'VIEW'
WHEN relkind = 'i' THEN 'INDEX'
WHEN relkind = 'S' THEN 'SEQUENCE'
WHEN relkind = 's' THEN 'SPECIAL'
WHEN relkind = 't' THEN 'TOAST'
WHEN relkind = 'c' THEN ' '
END) from pg_class c where c.oid = typrelid), ' '),
(select nspname from pg_namespace n where n.oid = typnamespace) as nsp, typname as obj_name,
#{sub_id} from pg_type "
when 'pg_proc'
"select (select nspname from pg_namespace n where n.oid = pronamespace) as nsp, proname as obj_name, proargtypes from pg_proc "
when 'pg_class'
"select
(CASE WHEN relkind = 'r' THEN 'TABLE'
WHEN relkind = 'v' THEN 'VIEW'
WHEN relkind = 'i' THEN 'INDEX'
WHEN relkind = 'S' THEN 'SEQUENCE'
WHEN relkind = 's' THEN 'SPECIAL'
WHEN relkind = 't' THEN 'TOAST'
END) , (select nspname from pg_namespace n where n.oid = relnamespace) as nsp,
relname as obj_name,
(select attname from pg_attribute where attrelid = #{obj_id} and attnum = #{sub_id}) from pg_class"
when 'pg_rewrite'
"select (select nspname from pg_namespace n where n.oid = (select relnamespace from pg_class c where c.oid = ev_class) ) as nsp, rulename, (select relname from pg_class c where c.oid = ev_class) from pg_rewrite"
else
puts "IGNORE: #{pg_class}, #{obj_id}, #{sub_id}"
nil
end
base_sql = base_sql ? base_sql << " WHERE oid = #{obj_id} ": nil
end
def to_s
case @o_type
when 'pg_proc' : "FUNCTION #{@row[0]}.#{@row[1]}#{@row[2]}"
when 'pg_type': "TYPE: #{@row[0]} #{@row[1]}.#{@row[2]}"
when 'pg_class' : "#{@row[0]} #{@row[1]}.#{@row[2]}" + (row[3] ? " COLUMN #{@row[3]}" : "")
when 'pg_rewrite' : @row[1] == '_RETURN' ? "VIEW #{@row[0]}.#{@row[2]}" : "RULE #{@row[1]} ON #{@row[0]}.#{@row[2]}"
end
end
end
attr_accessor :depend_graph
ACCEPTED = ['pg_type', 'pg_proc', 'pg_class', 'pg_rewrite']
def initialize(conn)
conn.query("select distinct classid, relname from pg_class c join pg_depend d on (c.oid = d.classid)").each do |row|
$PG_CLASSES[row['classid']] = row['relname']
end
conn.query("select distinct refclassid, relname from pg_class c join pg_depend d on (c.oid = d.refclassid)").each do |row|
$PG_CLASSES[row['refclassid']] = row['relname']
end
dep_graphs = {}
conn.query("SELECT * FROM pg_catalog.pg_depend where true or ( deptype<> 'i' and deptype <> 'p')").each do |row|
if ACCEPTED.include?($PG_CLASSES[row['classid']]) && ACCEPTED.include?($PG_CLASSES[row['refclassid']])
this_obj = DBObject.new(conn, row['classid'], row['objid'], row['objsubid'])
that_obj = DBObject.new(conn, row['refclassid'], row['refobjid'], row['refobjsubid'])
dep_string = this_obj.nsp !~ /^(information_schema|pg_catalog|pg_toast)$/ ? this_obj.to_s : nil
if dep_string && this_obj.to_s !~ /^INDEX /
dep_graphs[that_obj.to_s] ||=[]
dep_graphs[that_obj.to_s] << dep_string
if that_obj.to_s =~ /^(VIEW|TABLE) (.+?) COLUMN /m
ds2 = that_obj.to_s.sub(/ COLUMN.*/m, '')
dep_graphs[ds2] ||=[]
dep_graphs[ds2] << that_obj.to_s unless that_obj.to_s == ds2
elsif that_obj.to_s =~ /^TYPE: COMPOSITE (TABLE|VIEW) /
ds2 = that_obj.to_s.sub(/^TYPE: COMPOSITE (TABLE|VIEW) /, '\1 ')
dep_graphs[ds2] ||=[]
dep_graphs[ds2] << that_obj.to_s unless that_obj.to_s == ds2
end
end
end
end
dep_graphs.each do |that, values|
values.uniq!
values.reject! {|item| item == that}
end
@depend_graph = dep_graphs
end
def list_dependencies(obj)
dep_list = []
if @depend_graph[obj]
@depend_graph[obj].each do |v|
k = list_dependencies(v)
k.empty? ? dep_list << v : dep_list << [v, k]
end
end
dep_list
end
end
class DG
include TSort
def initialize(dep_graph, node_list)
@nodes = node_list
@dg = dep_graph
end
def tsort_each_node(&block)
@nodes.each {|x| yield x}
end
def tsort_each_child(node, &block)
(@dg[node]||[]).each(&block)
end
end
class Function
attr_reader :typed_head
def initialize(conn, tuple)
@name = tuple['namespace'] + "." + tuple['function_name']
@language = tuple['language_name']
@src = tuple['source_code']
@returns_set = tuple['returns_set']
@return_type = format_type(conn, tuple['return_type'])
@tipes = tuple['function_args'].split(" ")
if tuple['function_arg_names'] && tuple['function_arg_names'] =~ /^\{(.*)\}$/
@arnames = $1.split(',')
elsif tuple['function_arg_names'].is_a? Array
@arnames = tuple['function_arg_names']
else
@arnames = [""] * @tipes.length
end
alist = []
atypelist = []
@tipes.each_with_index do |typ,idx|
ft = format_type(conn, typ)
alist << (@arnames[idx] +" " + ft)
atypelist << ft
end
@arglist = alist.join(" , ")
@strict = tuple['proisstrict'] ? ' STRICT' : ''
@secdef = tuple['prosecdef'] ? ' SECURITY DEFINER' : ''
@volatile = case tuple['provolatile']
when 'i' then ' IMMUTABLE'
when 's' then ' STABLE'
else ''
end
@typed_head = @name+"("+atypelist.join(", ")+")"
end
def signature
"#{@name}(#{@arglist})"
end
def definition
<<-EOT
CREATE OR REPLACE FUNCTION #{@name} (#{@arglist}) RETURNS #{@returns_set ? 'SETOF' : ''} #{@return_type} AS $_$#{@src}$_$ LANGUAGE '#{@language}' #{@volatile}#{@strict}#{@secdef};
EOT
end
def == (other)
definition == other.definition
end
def format_type(conn, oid)
t_query = <<-EOT
SELECT pg_catalog.format_type(pg_type.oid, typtypmod) AS type_name
FROM pg_catalog.pg_type
JOIN pg_catalog.pg_namespace ON (pg_namespace.oid = typnamespace)
WHERE pg_type.oid =
EOT
return conn.query(t_query + oid.to_s)[0][0]
end
def self.find(conn, schema, name, args)
func_query = <<-EOT
SELECT proname AS function_name
, nspname AS namespace
, lanname AS language_name
, pg_catalog.obj_description(pg_proc.oid, 'pg_proc') AS comment
, proargtypes AS function_args
, proargnames AS function_arg_names
, prosrc AS source_code
, proretset AS returns_set
, prorettype AS return_type,
provolatile, proisstrict, prosecdef
FROM pg_catalog.pg_proc
JOIN pg_catalog.pg_language ON (pg_language.oid = prolang)
JOIN pg_catalog.pg_namespace ON (pronamespace = pg_namespace.oid)
JOIN pg_catalog.pg_type ON (prorettype = pg_type.oid)
WHERE pg_namespace.nspname !~ 'pg_catalog|information_schema|pg_temp_'
AND nspname = $1
AND proname = $2
AND oidvectortypes(proargtypes) = $3
EOT
Function.new(conn, conn.query(func_query, schema, name, args).first)
end
end
conn = PGconn.new(ARGV[0])
graph = PgDependencyGraph.new(conn)
dep_list = []
ARGV[1].split(/\|/).each do |obj_id|
dep_list += graph.list_dependencies(obj_id).flatten.uniq
end
dep_list.uniq!
top_sorted = DG.new(graph.depend_graph, dep_list).tsort
top_sorted.each do |line|
case line
when /^TYPE:/, /^VIEW (\S+) COLUMN/;
when /^VIEW (.+)/ then puts "DROP VIEW #$1;"
when /^FUNCTION (.+)/ then puts "DROP FUNCTION #$1;"
when /^RULE / then puts "DROP "+line+";"
else
puts "-- SKIP #{line}"
end
end
puts "", "--- ", "--- ALTER: ", "---"
puts ARGV[2]
puts "---", "---", ""
def view_def(conn, name)
conn.select_value("SELECT pg_catalog.pg_get_viewdef('#{name}'::regclass, true)")
end
def rule_def(conn, tablename, rule_name)
conn.select_value("select definition from pg_rules where schemaname || '.' || tablename = $1 and rulename = $2", tablename, rule_name)
end
def func_def(conn, *args)
Function.find(conn, *args).definition
end
top_sorted.reverse.each do |line|
case line
when /^TYPE:/, /^VIEW (\S+) COLUMN/ then puts "-- SKIP #{line}"
when /^VIEW (.+)/ then puts "CREATE VIEW #$1 AS "+view_def(conn, $1)
when /^FUNCTION (\w+)\.(\w+)\((.*)\)/ then puts func_def(conn, $1, $2, $3)
when /^RULE (.+) ON (.+)/ then puts "CREATE "+rule_def(conn, $2, $1)
else
puts "-- SKIP #{line}"
end
puts
end
