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.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28 require 'postgres'
29 require 'tsort'
30 require 'pp'
31 PGconn.translate_results = true
32
33 $PG_CLASSES = {}
34 class PgDependencyGraph
35 class DBObject
36 attr_accessor :row, :o_type, :nsp
37 def initialize(conn, class_id, obj_id, sub_id)
38 @o_type = $PG_CLASSES[class_id]
39 @row = conn.query(sql_for(@o_type, obj_id, sub_id)).first
40 @nsp = row['nsp']
41 if @o_type == 'pg_proc'
42 arg_types = row.last.split(" ")
43 unless arg_types.empty?
44 arg_type_names = arg_types.map {|oid| "format_type(#{oid}, -1)"}.join(", ")
45 row[-1] = "("+conn.query("SELECT #{arg_type_names}").first.join(", ") +")"
46 else
47 row[-1] = "()"
48 end
49 end
50 end
51 def sql_for(pg_class, obj_id, sub_id)
52 base_sql = case pg_class
53 when 'pg_type'
54 "select (case
55 when typtype = 'b' then 'BASE '
56 when typtype = 'c' then 'COMPOSITE '
57 when typtype = 'd' then 'DOMAIN '
58 when typtype = 'p' then 'PSEUDO '
59 end) || coalesce(
60 (select (CASE WHEN relkind = 'r' THEN 'TABLE'
61 WHEN relkind = 'v' THEN 'VIEW'
62 WHEN relkind = 'i' THEN 'INDEX'
63 WHEN relkind = 'S' THEN 'SEQUENCE'
64 WHEN relkind = 's' THEN 'SPECIAL'
65 WHEN relkind = 't' THEN 'TOAST'
66 WHEN relkind = 'c' THEN ' '
67 END) from pg_class c where c.oid = typrelid), ' '),
68 (select nspname from pg_namespace n where n.oid = typnamespace) as nsp, typname as obj_name,
69 #{sub_id} from pg_type "
70 when 'pg_proc'
71 "select (select nspname from pg_namespace n where n.oid = pronamespace) as nsp, proname as obj_name, proargtypes from pg_proc "
72 when 'pg_class'
73 "select
74 (CASE WHEN relkind = 'r' THEN 'TABLE'
75 WHEN relkind = 'v' THEN 'VIEW'
76 WHEN relkind = 'i' THEN 'INDEX'
77 WHEN relkind = 'S' THEN 'SEQUENCE'
78 WHEN relkind = 's' THEN 'SPECIAL'
79 WHEN relkind = 't' THEN 'TOAST'
80 END) , (select nspname from pg_namespace n where n.oid = relnamespace) as nsp,
81 relname as obj_name,
82 (select attname from pg_attribute where attrelid = #{obj_id} and attnum = #{sub_id}) from pg_class"
83 when 'pg_rewrite'
84 "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"
85 else
86 puts "IGNORE: #{pg_class}, #{obj_id}, #{sub_id}"
87 nil
88 end
89 base_sql = base_sql ? base_sql << " WHERE oid = #{obj_id} ": nil
90
91 end
92
93 def to_s
94 case @o_type
95 when 'pg_proc' : "FUNCTION #{@row[0]}.#{@row[1]}#{@row[2]}"
96 when 'pg_type': "TYPE: #{@row[0]} #{@row[1]}.#{@row[2]}"
97 when 'pg_class' : "#{@row[0]} #{@row[1]}.#{@row[2]}" + (row[3] ? " COLUMN #{@row[3]}" : "")
98 when 'pg_rewrite' : @row[1] == '_RETURN' ? "VIEW #{@row[0]}.#{@row[2]}" : "RULE #{@row[1]} ON #{@row[0]}.#{@row[2]}"
99 end
100 end
101 end
102 attr_accessor :depend_graph
103 ACCEPTED = ['pg_type', 'pg_proc', 'pg_class', 'pg_rewrite']
104 def initialize(conn)
105 conn.query("select distinct classid, relname from pg_class c join pg_depend d on (c.oid = d.classid)").each do |row|
106 $PG_CLASSES[row['classid']] = row['relname']
107 end
108 conn.query("select distinct refclassid, relname from pg_class c join pg_depend d on (c.oid = d.refclassid)").each do |row|
109 $PG_CLASSES[row['refclassid']] = row['relname']
110 end
111 dep_graphs = {}
112 conn.query("SELECT * FROM pg_catalog.pg_depend where true or ( deptype<> 'i' and deptype <> 'p')").each do |row|
113 if ACCEPTED.include?($PG_CLASSES[row['classid']]) && ACCEPTED.include?($PG_CLASSES[row['refclassid']])
114 this_obj = DBObject.new(conn, row['classid'], row['objid'], row['objsubid'])
115 that_obj = DBObject.new(conn, row['refclassid'], row['refobjid'], row['refobjsubid'])
116
117 dep_string = this_obj.nsp !~ /^(information_schema|pg_catalog|pg_toast)$/ ? this_obj.to_s : nil
118
119 if dep_string && this_obj.to_s !~ /^INDEX /
120 dep_graphs[that_obj.to_s] ||=[]
121 dep_graphs[that_obj.to_s] << dep_string
122 if that_obj.to_s =~ /^(VIEW|TABLE) (.+?) COLUMN /m
123 ds2 = that_obj.to_s.sub(/ COLUMN.*/m, '')
124 dep_graphs[ds2] ||=[]
125 dep_graphs[ds2] << that_obj.to_s unless that_obj.to_s == ds2
126 elsif that_obj.to_s =~ /^TYPE: COMPOSITE (TABLE|VIEW) /
127 ds2 = that_obj.to_s.sub(/^TYPE: COMPOSITE (TABLE|VIEW) /, '\1 ')
128 dep_graphs[ds2] ||=[]
129 dep_graphs[ds2] << that_obj.to_s unless that_obj.to_s == ds2
130 end
131 end
132 end
133 end
134 dep_graphs.each do |that, values|
135 values.uniq!
136 values.reject! {|item| item == that}
137 end
138 @depend_graph = dep_graphs
139 end
140 def list_dependencies(obj)
141 dep_list = []
142 if @depend_graph[obj]
143 @depend_graph[obj].each do |v|
144 k = list_dependencies(v)
145 k.empty? ? dep_list << v : dep_list << [v, k]
146 end
147 end
148 dep_list
149 end
150 end
151
152 class DG
153 include TSort
154 def initialize(dep_graph, node_list)
155 @nodes = node_list
156 @dg = dep_graph
157 end
158 def tsort_each_node(&block)
159 @nodes.each {|x| yield x}
160 end
161 def tsort_each_child(node, &block)
162 (@dg[node]||[]).each(&block)
163 end
164 end
165
166 class Function
167 attr_reader :typed_head
168 def initialize(conn, tuple)
169 @name = tuple['namespace'] + "." + tuple['function_name']
170 @language = tuple['language_name']
171 @src = tuple['source_code']
172 @returns_set = tuple['returns_set']
173 @return_type = format_type(conn, tuple['return_type'])
174 @tipes = tuple['function_args'].split(" ")
175 if tuple['function_arg_names'] && tuple['function_arg_names'] =~ /^\{(.*)\}$/
176 @arnames = $1.split(',')
177 elsif tuple['function_arg_names'].is_a? Array
178 @arnames = tuple['function_arg_names']
179 else
180 @arnames = [""] * @tipes.length
181 end
182 alist = []
183 atypelist = []
184 @tipes.each_with_index do |typ,idx|
185 ft = format_type(conn, typ)
186 alist << (@arnames[idx] +" " + ft)
187 atypelist << ft
188 end
189 @arglist = alist.join(" , ")
190 @strict = tuple['proisstrict'] ? ' STRICT' : ''
191 @secdef = tuple['prosecdef'] ? ' SECURITY DEFINER' : ''
192 @volatile = case tuple['provolatile']
193 when 'i' then ' IMMUTABLE'
194 when 's' then ' STABLE'
195 else ''
196 end
197 @typed_head = @name+"("+atypelist.join(", ")+")"
198 end
199 def signature
200 "#{@name}(#{@arglist})"
201 end
202 def definition
203 <<-EOT
204 CREATE OR REPLACE FUNCTION #{@name} (#{@arglist}) RETURNS #{@returns_set ? 'SETOF' : ''} #{@return_type} AS $_$#{@src}$_$ LANGUAGE '#{@language}' #{@volatile}#{@strict}#{@secdef};
205 EOT
206 end
207 def == (other)
208 definition == other.definition
209 end
210 def format_type(conn, oid)
211 t_query = <<-EOT
212 SELECT pg_catalog.format_type(pg_type.oid, typtypmod) AS type_name
213 FROM pg_catalog.pg_type
214 JOIN pg_catalog.pg_namespace ON (pg_namespace.oid = typnamespace)
215 WHERE pg_type.oid =
216 EOT
217 return conn.query(t_query + oid.to_s)[0][0]
218 end
219 def self.find(conn, schema, name, args)
220 func_query = <<-EOT
221 SELECT proname AS function_name
222 , nspname AS namespace
223 , lanname AS language_name
224 , pg_catalog.obj_description(pg_proc.oid, 'pg_proc') AS comment
225 , proargtypes AS function_args
226 , proargnames AS function_arg_names
227 , prosrc AS source_code
228 , proretset AS returns_set
229 , prorettype AS return_type,
230 provolatile, proisstrict, prosecdef
231 FROM pg_catalog.pg_proc
232 JOIN pg_catalog.pg_language ON (pg_language.oid = prolang)
233 JOIN pg_catalog.pg_namespace ON (pronamespace = pg_namespace.oid)
234 JOIN pg_catalog.pg_type ON (prorettype = pg_type.oid)
235 WHERE pg_namespace.nspname !~ 'pg_catalog|information_schema|pg_temp_'
236 AND nspname = $1
237 AND proname = $2
238 AND oidvectortypes(proargtypes) = $3
239 EOT
240
241 Function.new(conn, conn.query(func_query, schema, name, args).first)
242 end
243 end
244
245 conn = PGconn.new(ARGV[0])
246 graph = PgDependencyGraph.new(conn)
247
248 dep_list = []
249 ARGV[1].split(/\|/).each do |obj_id|
250 dep_list += graph.list_dependencies(obj_id).flatten.uniq
251 end
252 dep_list.uniq!
253
254 top_sorted = DG.new(graph.depend_graph, dep_list).tsort
255 top_sorted.each do |line|
256 case line
257 when /^TYPE:/, /^VIEW (\S+) COLUMN/;
258 when /^VIEW (.+)/ then puts "DROP VIEW #$1;"
259 when /^FUNCTION (.+)/ then puts "DROP FUNCTION #$1;"
260 when /^RULE / then puts "DROP "+line+";"
261 else
262 puts "-- SKIP #{line}"
263 end
264 end
265
266 puts "", "--- ", "--- ALTER: ", "---"
267 puts ARGV[2]
268 puts "---", "---", ""
269 def view_def(conn, name)
270 conn.select_value("SELECT pg_catalog.pg_get_viewdef('#{name}'::regclass, true)")
271 end
272 def rule_def(conn, tablename, rule_name)
273 conn.select_value("select definition from pg_rules where schemaname || '.' || tablename = $1 and rulename = $2", tablename, rule_name)
274 end
275
276 def func_def(conn, *args)
277 Function.find(conn, *args).definition
278 end
279
280
281 top_sorted.reverse.each do |line|
282 case line
283 when /^TYPE:/, /^VIEW (\S+) COLUMN/ then puts "-- SKIP #{line}"
284 when /^VIEW (.+)/ then puts "CREATE VIEW #$1 AS "+view_def(conn, $1)
285 when /^FUNCTION (\w+)\.(\w+)\((.*)\)/ then puts func_def(conn, $1, $2, $3)
286 when /^RULE (.+) ON (.+)/ then puts "CREATE "+rule_def(conn, $2, $1)
287 else
288 puts "-- SKIP #{line}"
289 end
290 puts
291 end
292
293
294