在用Python操作GPDB的时候遇到报错如下 gpmg.manager_tabl1eerror 'ERROR: relation gpmg.manager_tabl1e does not exist' in 'select pg_total_relation_size('gpmg.manager_tabl1e');'gpmg.manager_tableerror 'ERROR: current transaction is aborted, co
在用Python操作GPDB的时候遇到报错如下
gpmg.manager_tabl1e
error 'ERROR: relation "gpmg.manager_tabl1e" does not exist
' in 'select pg_total_relation_size('gpmg.manager_tabl1e');'
gpmg.manager_table
error 'ERROR: current transaction is aborted, commands ignored until end of transaction block
' in 'select pg_total_relation_size('gpmg.manager_table');'
这个在一个循环当中，收集对象大小并插入结果表，出现这个错误的原因是因为在GPDB中如果有出现任何错误，将导致后续的会话报错。
Python代码块如下
sql = "select v1,v2,v3 from test order by 2"
cur.execute(sql)
for ret in cur.fetchall():
v_schemaname = ret[0]
v_tablename = ret[1]
v_tableowner = ret[2]
v_object = v_schemaname + "." + v_tablename
try:
cur.execute("select pg_total_relation_size('" + v_object + "');")
v_totalsize = cur.fetchone()[0]
cur.execute("insert into gpmg.table_size_info (ctime,schemaname,tablename,tableowner,totalsize) values(%s,%s,%s,%s,%s)", (ctime,v_schemaname,v_tablename,v_ta
bleowner,v_totalsize))
except Exception, e:
print v_object
print e
改进之后如下
try:
for ret in cur.fetchall():
v_schemaname = ret[0]
v_tablename = ret[1]
v_tableowner = ret[2]
v_object = v_schemaname + "." + v_tablename
try:
cur.execute("select pg_total_relation_size('" + v_object + "');")
v_totalsize = cur.fetchone()[0]
cur.execute("insert into gpmg.table_size_info (ctime,schemaname,tablename,tableowner,totalsize) values(%s,%s,%s,%s,%s)", (ctime,v_schemaname,v_tablename,v_tableowner,v_totalsize))
db.commit()
except Exception, e:
db.commit()
print v_object, '--', e
continue
except Exception, e:
pass
finally:
db.commit()
db.close()
os.system('rm -rf ' + pf)
这样就算比较完美咯。
-EOF-