In the past I running often in the case, that iView is sending a email that the disc is full respectively my licensed storage space is exhausted (Why the hell does this info not go to an administrated administrator but to the email address of the person who registered the product, but this is another thing).
The strange thing is/was, that this will happend very fast (1 or 2 month) and on the disc there are not a lot space used. Only the database grew extremely large.
So I have login to the database ( Advanced Shell -> psql -U nobody iviewdb ) and make a Query for the largest tables:
SELECT nspname || '.' || relname AS "relation",
pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
AND C.relkind <> 'i'
AND nspname !~ '^pg_toast'
ORDER BY pg_total_relation_size(C.oid) DESC
LIMIT 5;The result was, that there was one table which was extremly large (yesterday it was 67 GB). The Strange thing: A query to count the rows give me a result of zero.
Most time a VACUUM (or VACUUM FULL) helps. In this case (67 GB) a DROPING and RECREATING the TABLE was much fast (and helps also).
I' am sorry, but I never have prtocoll, which tables it was (I don't realy know why, maybe, because I wasn't able to make this automaticly without CRON and so I have checked it every time), but I think, it was the Table 'webv9_4hr'. On the next, also the tables _12hr and _24hr was also every time empty (but not such big).
So yesterday I have start to find the reason, why the _4hr Table will blow so up without any content (even after I have recreate the table).
After searching logfile etc I have found some interessting entry in the LOGFILE of GARNER (this ist I think the Process, that collect the Messages from UTM and XG, put it to the Database but also triggers the filling of the statistik tables). In this logfile I found entrys like this:
Jun 23 06:07:14: OPPGTRIGGER: oppgtrigger_db_check_status: denied_web_content_categorization_data_4hr: last trigger execution is not completed yet
So, why the Trigger is not completed. I searching if there is a Flag which maybe means that the process is still running but I found nothing. But under /conf/dynamicfiles/garner/conf/oppgtrigger.conf I found the query ('Select denied_web_content_categorization_data_proc_4hr_009();') and this one I started in the psql console.
An know, I have an Error Message. During creating new statistic tables, the function (respectively Postgres) was to do this because Index Name was to long and so, the second index has the same name as the first on:
ERROR: relation "deniedweb_app_category_domain_host_url_userv9_24hr_ts_201901_id" already existis.
The Functions wants create the Index
deniedweb_app_category_domain_host_url_userv9_24hr_ts_201901_idx
and
deniedweb_app_category_domain_host_url_userv9_24hr_ts_201901_idx_1
Postgres Index Names can only be 63 Characters long. Everything what is longer will truncate. The result you can see here.
So I have change the FUNCTION create_table_and_index so that the Index Name is Shorter then 63 and know, the Error is away and It looks so, that the table, which has blow up in the past has now also Data.
Here is the new modificated FUNCTION. I have made the changes very simple and maybe, there are much more tables with this problem. I have it only with two tables (deniedweb_app_category_domain_host_url_userv9_24hr_ts_xxxxxx and virus_app_dst_hst_mailvrs_rcpt_sndr_sbj_usrv8_24hr_ts_xxxxxx). You can copy this directly to the Postgres console:
DROP FUNCTION create_table_and_index(t_name text, tabletimestamp text);
CREATE FUNCTION create_table_and_index(t_name text, tabletimestamp text) RETURNS void
LANGUAGE plpgsql
AS $$
DECLARE
timestamp_arr varchar(10)[];
tbname varchar(128);
tbidxname varchar(128);
qry_ts text;
BEGIN
tbname = (trim(t_name) || '_ts_'|| trim(tabletimestamp));
tbidxname = replace(tbname,'_app_category_','_app_cat_');
tbidxname = replace(tbidxname,'virus_app_dst_hst_mailvrs_rcpt_sndr_sbj','vrsapp_dst_hst_mlvrs_to_frm_sbj');
if NOT EXISTS ( SELECT tablename FROM pg_tables WHERE schemaname = 'public' and tablename like tbname ) THEN
EXECUTE 'create table ' || tbname || ' (like '|| t_name ||') INHERITS ('|| t_name ||')';
if EXISTS (select column_name from information_schema.columns where table_name = trim(t_name) and column_name = '5mintime' ) THEN
EXECUTE 'create index ' || tbidxname || '_idx ON ' || tbname || ' ("5mintime")' ;
else
if EXISTS (select column_name from information_schema.columns where table_name = trim(t_name) and column_name = 'time' ) THEN
EXECUTE 'create index ' || tbidxname || '_idx ON ' || tbname || ' (time)' ;
end if;
end if;
EXECUTE 'create index ' || tbidxname || '_idx_1 ON ' || tbname || ' (appid)' ;
end if;
END;
$$;
You have also modified the file '/conf/DB/reportproc.sql' (the File under /_conf/DB/reportproc.sql is Readonly and can not be changed). I think, this file will be used every time when Postgres will be started.
So maybe, there are people here that have the same problems and have know a bugfix. Maybe also SOPHOS will make a Bugfix in the future because I wonder that no one at SOPHOS (and also here in the community) ever has seen such a big bug.
This thread was automatically locked due to age.
