GPDB current transaction is aborted，gpdbaborted

GPDB current transaction is aborted，gpdbaborted

在用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, 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_tableowner,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            continueexcept Exception, e:    pass    finally:    db.commit()    db.close()    os.system('rm -rf ' + pf)

這樣就算比較完美咯。

-EOF-