SQLSTATE58030, sqlstate28000

SQLSTATE58030, sqlstate28000

An execution error occurred while optimizing an SQL statement.

gtlions=# select t1.telnumber,t2.ua,t2.url,t1.apply_name,t2.apply_name from tb1 t1 gtlions-# left outer join tb2 t2 on  t1.sid = t2.ipsid gtlions-# where t1.sum_date=20141128 and t1.sys_reported_time >= '2014-11-28 03:55:00' and t1.sys_reported_time <= '2014-11-28 04:00:00';ERROR:  could not create temporary file base/20350/pgsql_tmp/workfile_set_HashJoin_Slice0.XXXXva5ffL/spillfile_f261961:Too many open filesERROR:  could not seek in temporary file: Bad file descriptor

CorrespondingSQLSTATEYes58030This is described in the document.

58030 io error io_error

Check the number of opened files and system restrictions.

[root@mdw ~]# lsof -n|wc -l           23583[root@mdw ~]# ulimit -Hn131072[root@mdw ~]# ulimit -Sn131072  

Check the number of opened files during execution again and find that it is far beyond the system limit (14xxx). The solution is to modify the GPDB parameter.[Statement_mem]The value can be set at the session level to be large enough to accommodate temporary operations or sorted data. Of course, statistical analysis should also be performed on the table to ensure accurate statistical information.

For4.2.8.2Special parameters can be set at the beginning of the version.[Gp_workfile_limit_files_per_query]Set the number of opened files.

-EOF-