Useful Greenplum SQLs

Below Greenplum SQLs can help author troubleshoot issues, that is why they are here.

1. Master/Segment logs => Table

CREATE EXTERNAL WEB TABLE testbydba_ext
(
    logtime timestamp,
    loguser text,
    logdatabase text,
    logpid text,
    logthread text,
    loghost text,
    logport text,
    logsessiontime timestamp,
    logtransaction int,
    logsession text,
    logcmdcount text,
    logsegment text,
    logslice text,
    logdistxact text,
    loglocalxact text,
    logsubxact text,
    logseverity text,
    logstate text,
    logmessage text,
    logdetail text,
    loghint text,
    logquery text,
    logquerypos int,
    logcontext text,
    logdebug text,
    logcursorpos int,
    logfunction text,
    logfile text,
    logline int,
    logstack text
)
EXECUTE E'cat /data/tmp/gpdb-2011-02-25_000000.csv' on master
FORMAT 'CSV' (DELIMITER AS ',' NULL AS '' QUOTE AS '"');

create table testbydba as select * from testbydba_ext;

For example:

select logtime,loguser,logdatabase,logsession,substr(logdebug,1,100) sqltext
from testbydba where logseverity='ERROR' and logmessage='Out of memory' order by 1;

2. Utility Mode logon one segment/master

PGOPTIONS='-c gp_session_role=utility' psql

3. Print debug information for Gang allocating

When SQL fails with stacktrace when allocating gangs, use below parameter at session level to print more debug information.

set gp_log_gang='debug';

4. Allow system table modification.

set allow_system_table_mods=dml;

5. Show memory requirement estimation.

set gp_resqueue_print_operator_memory_limits=on;

For example:

db=# explain select * from test;
                                   QUERY PLAN
--------------------------------------------------------------------------------
 Gather Motion 2:1  (slice1; segments: 2)  (cost=0.00..8.38 rows=219 width=226)
   ->  Seq Scan on test  (cost=0.00..8.38 rows=219 width=226)
(2 rows)

db=# set gp_resqueue_print_operator_memory_limits=on;
SET
db=# explain select * from test;
                                             QUERY PLAN
----------------------------------------------------------------------------------------------------
 Gather Motion 2:1  (slice1; segments: 2)  (cost=0.00..8.38 rows=219 width=226) (operatorMem=100KB)
   ->  Seq Scan on test  (cost=0.00..8.38 rows=219 width=226) (operatorMem=100KB)
(2 rows)

6. Show deleted rows(before vacuum is run on that table)

set gp_select_invisible=on;

7. Check the status of the segments

echo "getStatus" | gp_primarymirror -h <segment_host> -p <segment_port>

For example:

gpadmin@mdw $ echo "getStatus" | gp_primarymirror -h sdw1 -p 40004
mode: PrimarySegment
segmentState: Ready
dataState: InSync
faultType: NotInitialized
mode: PrimarySegment
segmentState: Ready
dataState: InSync
faultType: NotInitialized

8. Workfile

set gp_workfile_caching_loglevel='LOG';

9. gptransfer example

gptransfer  -t db.public.testbydb --dest-host=mdw --dest-port=48000 --source-host=smdw --source-port=4280 --source-map-file=mapfile

mapfile is:

[gpadmin@smdw ~]$ cat mapfile
  sdw5,172.28.8.5
  sdw6,172.28.8.6
  sdw7,172.28.8.7
  sdw8,172.28.8.8 

10. Execute SQL on each primary segment

PGOPTIONS='-c gp_session_role=utility' psql -d template1 -Atc "copy (select dbid, hostname, port from gp_segment_configuration where role = 'p' and content != -1) to stdout delimiter ' '" | while read dbid host port; do
echo "echo DBID: $dbid"
echo "PGOPTIONS='-c gp_session_role=utility' psql -h $host -p $port -d template1 -c 'select 1;'"
done > test.sh

11. Clean temp schemas before gpcheckcat

psql -Atc "select datname from pg_database where datname != 'template0'" | while read a; do echo "check for ${a}";psql -Atc "select 'drop schema if exists ' || nspname || ' cascade;' from (select nspname from pg_namespace where nspname like 'pg_temp%' union select nspname from gp_dist_random('pg_namespace') where nspname like 'pg_temp%' except select 'pg_temp_' || sess_id::varchar from pg_stat_activity) as foo" ${a}; done

==