gpdbguru

psql -d dbname -c "select name as \"Postgres.conf GUC\", setting \"Current Setting\" from pg_settings where name='vacuum_freeze_min_age'" psql -d dbname -c "select c.oid::regclass as \"Table\", greatest(age(c.relfrozenxid), age(t.relfrozenxid)) as \"Age\" from pg_class c left join pg_class t on c.reltoastrelid = t.oid where c.relkind = 'r' order by 2 desc limit 10"

 To view  AO table bloat view  click here SCHEMANAME TABLENAME env PGOPTIONS="-c client_min_messages=WARNING" psql $PSQL_CONN_PARMS -c "select nspname as \"Schema\",  relname as \"Table\", count(*) as \"# files\", sum(case when compaction_possible='t' then 1 else 0 end) as \"# files compactible\", max(percent_hidden) as \"Max % of Hidden Rows\" from (select n.nspname, c.relname, (gp_toolkit.__gp_aovisimap_compaction_info(c.oid)).* from pg_class c join pg_namespace n on n.oid = c.relnamespace where nspname like lower('$SCHEMANAME') and relname like lower('$TABLENAME') and array_to_string(c.reloptions,',') like '%appendonly=true%' ) a group by nspname, relname order by nspname, 5 desc"

 fn_exists=$(psql -d <dbname> -At -c "select case when (exists( select * from pg_proc where proname = '__gp_aovisimap_compaction_info')) then 't' else 'f' end") if [[ $fn_exists == 'f' ]]; then psql -d -f $GPHOME/share/postgresql/compaction_info.sql fi

Note: This report's accuracy is based on up to date statistics. For best results run this report after analyze is performed. select bdinspname as "Schema", bdirelname as "Table", bdidiag "Bloat Diagnosis" from gp_toolkit.gp_bloat_diag order by 1,2 desc

Err: Invalid memory alloc request size Orca issue in GP6.11.0  Due to ORCA memory error, it caused user function failure alone with segments going down. DTXRollbackAndReleaseCurrentSubTransaction dispatch failed causing DTM error and makes segments to go down. Orca error is causing a memory to not be properly released from the database and potentially impacting other concurrent processes that requrie reserved memory. Failure with union clauses and the metrics collector causing 'Invalid Memory Alloc' have been fixed in GP6.12.0.

SQL SELECT n.nspname||'.'||c.relname FROM pg_catalog.pg_class As c INNER JOIN pg_catalog.pg_namespace AS n ON c.relnamespace = n.oid AND n.nspname = '<schemaname>' AND  c.relkind IN ('r','v') AND c.relstorage != 'x' INNER JOIN pg_catalog.pg_roles AS r ON c.relowner = r.oid AND r.rolname = '<userid>' EXCEPT SELECT partitionschemaname||'.'||partitiontablename FROM pg_catalog.pg_partitions ORDER BY 1; Here <schemaname> and  <userid> can be supplied accordingly to fetch details.