gp toolkit schema

Greenplum Database provides an administrative schema called gp_toolkit that you can use to query the system catalogs, log files, and operating environment for system status information. The gp_toolkit schema contains a number of views that you can access using SQL commands. The gp_toolkit schema is accessible to all database users, although some objects may require superuser permissions. For convenience, you may want to add the gp_toolkit schema to your schema search path.
 For example:

=> ALTER ROLE myrole SET search_path TO myschema,gp_toolkit;

This documentation describes the most useful views in gp_toolkit. You may notice other objects (views, functions, and external tables) within the gp_toolkit schema that are not described in this documentation (these are supporting objects to the views described in this section).
gp_toolkit schema contains some important views that can be queried by DBA's to get important statistical information. We will discuss some of the frequently uses views in
gpadmin=# \dv gp_toolkit.*
                               List of relations
   Schema   |                  Name                  | Type |  Owner  | Storage 
------------+----------------------------------------+------+---------+---------
 gp_toolkit | __gp_fullname                          | view | gpadmin | none
 gp_toolkit | __gp_is_append_only                    | view | gpadmin | none
 gp_toolkit | __gp_number_of_segments                | view | gpadmin | none
 gp_toolkit | __gp_user_data_tables                  | view | gpadmin | none
 gp_toolkit | __gp_user_data_tables_readable         | view | gpadmin | none
 gp_toolkit | __gp_user_namespaces                   | view | gpadmin | none
 gp_toolkit | __gp_user_tables                       | view | gpadmin | none
 gp_toolkit | gp_bloat_diag                          | view | gpadmin | none
 gp_toolkit | gp_bloat_expected_pages                | view | gpadmin | none
 gp_toolkit | gp_locks_on_relation                   | view | gpadmin | none
 gp_toolkit | gp_locks_on_resqueue                   | view | gpadmin | none
 gp_toolkit | gp_log_command_timings                 | view | gpadmin | none
 gp_toolkit | gp_log_database                        | view | gpadmin | none
 gp_toolkit | gp_log_master_concise                  | view | gpadmin | none
 gp_toolkit | gp_log_system                          | view | gpadmin | none
 gp_toolkit | gp_param_settings_seg_value_diffs      | view | gpadmin | none
 gp_toolkit | gp_pgdatabase_invalid                  | view | gpadmin | none
 gp_toolkit | gp_resq_activity                       | view | gpadmin | none
 gp_toolkit | gp_resq_activity_by_queue              | view | gpadmin | none
 gp_toolkit | gp_resq_priority_backend               | view | gpadmin | none
 gp_toolkit | gp_resq_priority_statement             | view | gpadmin | none
 gp_toolkit | gp_resq_role                           | view | gpadmin | none
 gp_toolkit | gp_resqueue_status                     | view | gpadmin | none
 gp_toolkit | gp_roles_assigned                      | view | gpadmin | none
 gp_toolkit | gp_size_of_all_table_indexes           | view | gpadmin | none
 gp_toolkit | gp_size_of_database                    | view | gpadmin | none
 gp_toolkit | gp_size_of_index                       | view | gpadmin | none
 gp_toolkit | gp_size_of_partition_and_indexes_disk  | view | gpadmin | none
 gp_toolkit | gp_size_of_schema_disk                 | view | gpadmin | none
 gp_toolkit | gp_size_of_table_and_indexes_disk      | view | gpadmin | none
 gp_toolkit | gp_size_of_table_and_indexes_licensing | view | gpadmin | none
 gp_toolkit | gp_size_of_table_disk                  | view | gpadmin | none
 gp_toolkit | gp_size_of_table_uncompressed          | view | gpadmin | none
 gp_toolkit | gp_skew_coefficients                   | view | gpadmin | none
 gp_toolkit | gp_skew_idle_fractions                 | view | gpadmin | none
 gp_toolkit | gp_stats_missing                       | view | gpadmin | none
 gp_toolkit | gp_table_indexes                       | view | gpadmin | none
 gp_toolkit | gp_workfile_entries                    | view | gpadmin | none
 gp_toolkit | gp_workfile_usage_per_query            | view | gpadmin | none
 gp_toolkit | gp_workfile_usage_per_segment          | view | gpadmin | none
(40 rows)

gpadmin=# 

gpadmin=# \df gp_toolkit.*
List of functions
Schema | Name | Result data type | Argument data types
| Type
------------+--------------------------+-------------------------------------+-------------------------------------------------------------------------------
------------------+--------
gp_toolkit | __gp_param_local_setting | SETOF gp_toolkit.gp_param_setting_t | character varying
| normal
gp_toolkit | __gp_skew_coefficients | SETOF gp_toolkit.gp_skew_analysis_t |
| normal
gp_toolkit | __gp_skew_idle_fractions | SETOF gp_toolkit.gp_skew_analysis_t |
| normal
gp_toolkit | gp_bloat_diag | record | btdrelpages integer, btdexppages integer, aotable boolean, OUT bltidx integer,
OUT bltdiag text | normal
gp_toolkit | gp_param_setting | SETOF gp_toolkit.gp_param_setting_t | character varying
| normal
gp_toolkit | gp_param_settings | SETOF gp_toolkit.gp_param_setting_t |
| normal
gp_toolkit | gp_skew_coefficient | record | targetoid oid, OUT skcoid oid, OUT skccoeff numeric
| normal
gp_toolkit | gp_skew_details | SETOF gp_toolkit.gp_skew_details_t | oid
| normal
gp_toolkit | gp_skew_idle_fraction | record | targetoid oid, OUT sifoid oid, OUT siffraction numeric
| normal
(9 rows)




sachi=# 
External table "gp_toolkit.__gp_log_master_ext"
Column             |           Type           | Modifiers 
----------------+--------------------------+-----------
 logtime                | timestamp with time zone | 
 loguser                | text                     | 
 logdatabase         | text                     | 
 logpid                  | text                     | 
 logthread              | text                     | 
 loghost                | text                     | 
 logport                | text                     | 
 logsessiontime     | timestamp with time zone | 
 logtransaction         | integer                  | 
 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        | integer                  | 
 logcontext             | text                     | 
 logdebug               | text                     | 
 logcursorpos       | integer                  | 
 logfunction        | text                     | 
 logfile                | text                     | 
 logline                | integer                  | 
 logstack          | text                     | 
Type: readable
Encoding: UTF8
Format type: csv
Format options: delimiter ',' null '' escape '"' quote '"'
Command: cat $GP_SEG_DATADIR/pg_log/*.csv
Execute on: master segment

 External table "gp_toolkit.__gp_log_segment_ext"
     Column     |           Type           | Modifiers 
----------------+--------------------------+-----------
 logtime        | timestamp with time zone | 
 loguser        | text                     | 
 logdatabase    | text                     | 
 logpid         | text                     | 
 logthread      | text                     | 
 loghost        | text                     | 
 logport        | text                     | 
 logsessiontime | timestamp with time zone | 
 logtransaction | integer                  | 
 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    | integer                  | 
 logcontext     | text                     | 
 logdebug       | text                     | 
 logcursorpos   | integer                  | 
 logfunction    | text                     | 
 logfile        | text                     | 
 logline        | integer                  | 
 logstack       | text                     | 
Type: readable
Encoding: UTF8
Format type: csv
Format options: delimiter ',' null '' escape '"' quote '"'
Command: cat $GP_SEG_DATADIR/pg_log/*.csv
Execute on: all segments


gpadmin=# \df gp_toolkit.*
                                                                                                                              List of functions
   Schema   |              Name               |             Result data type             |                                                                            Argument dat
a types                                                                            |  Type  
------------+---------------------------------+------------------------------------------+----------------------------------------------------------------------------------------
 gp_toolkit | gp_param_setting                | SETOF gp_toolkit.gp_param_setting_t      | character varying                                                                                         | normal
 gp_toolkit | gp_param_settings               | SETOF gp_toolkit.gp_param_setting_t      |       | normal
 gp_toolkit | gp_skew_coefficient             | record                                   | targetoid oid, OUT skcoid oid, OUT skccoeff numeric                                                                     | normal
 gp_toolkit | gp_skew_details                 | SETOF gp_toolkit.gp_skew_details_t       | oid                                                                                                             | normal
 gp_toolkit | gp_skew_idle_fraction           | record                                   | targetoid oid, OUT sifoid oid, OUT siffraction numeric                                                   | normal
 gp_toolkit | gp_workfile_cache_clear         | SETOF integer                            |                                                                                                        | normal
 gp_toolkit | gp_workfile_cache_clear_segment | SETOF integer                            | content integer                                                                         | normal
 gp_toolkit | __gp_aocsseg                    | SETOF record                             | oid                                                                                                   | normal
 gp_toolkit | __gp_aocsseg_history            | SETOF record                             | oid                                                                                               | normal
 gp_toolkit | __gp_aocsseg_name               | SETOF record                             | text                                                                                            | normal
 gp_toolkit | __gp_aoseg_history              | SETOF record                             | oid                                                                                                | normal
 gp_toolkit | __gp_aoseg_name                 | SETOF record                             | text                                                                                             | normal
 gp_toolkit | __gp_aovisimap                  | SETOF record                             | oid                                                                                                 | normal
  gp_toolkit | __gp_aovisimap_entry            | SETOF record                             | oid                                                                                              | normal
 gp_toolkit | __gp_aovisimap_entry_name       | SETOF record                             | text                                                                                        | normal
 gp_toolkit | __gp_aovisimap_hidden_info      | SETOF record                             | oid                                                                                           | normal
 gp_toolkit | __gp_aovisimap_hidden_info_name | SETOF record                             | text                                                                                     | normal
 gp_toolkit | __gp_aovisimap_hidden_typed     | SETOF gp_toolkit.__gp_aovisimap_hidden_t | oid                                                                                    | normal
 gp_toolkit | __gp_aovisimap_name             | SETOF record                             | text                                                                                                     | normal
 gp_toolkit | __gp_param_local_setting        | SETOF gp_toolkit.gp_param_setting_t      | character varying                                      | normal
 gp_toolkit | __gp_skew_coefficients          | SETOF gp_toolkit.gp_skew_analysis_t      |                                                                                                                | normal
 gp_toolkit | __gp_skew_idle_fractions        | SETOF gp_toolkit.gp_skew_analysis_t      |                                                                                                               | normal
 gp_toolkit | __gp_workfile_cache_clear_f     | SETOF integer                            | content integer                                                                                                   | normal
 gp_toolkit | __gp_workfile_entries_f         | SETOF record                             |                                                                                                                             | normal
 gp_toolkit | gp_bloat_diag                   | record      | btdrelpages integer, btdexppages integer, aotable boolean, OUT bltidx integer, OUT bltd iag text                      | normal
 gp_toolkit | __gp_aovisimap_compaction_info  | SETOF record | ao_oid oid, OUT content integer, OUT datafile integer, OUT compaction_possible boolean, OUT hidden_tupcount bigint,                                                                                                                                                                             OUT total_tupcount bigint, OUT percent_hidden numeric | normal

(26 rows)

gpadmin=# 

Subscribe to posts

gp_toolkit.gp_param_settings_seg_value_diffs

posted Jan 20, 2014, 2:13 PM by Sachchida Ojha

gpadmin=# \d gp_toolkit.gp_param_settings_seg_value_diffs
View "gp_toolkit.gp_param_settings_seg_value_diffs"
Column | Type | Modifiers 
----------+--------+-----------
psdname | text | 
psdvalue | text | 
psdcount | bigint | 
View definition:
SELECT gp_param_settings.paramname AS psdname, gp_param_settings.paramvalue AS psdvalue, count(*) AS psdcount
FROM gp_toolkit.gp_param_settings() gp_param_settings(paramsegment, paramname, paramvalue)
WHERE gp_param_settings.paramname <> ALL (ARRAY['config_file'::text, 'data_directory'::text, 'gp_contentid'::text, 'gp_dbid'::text, 'hba_file'::text, 'ident_file'::text, 'port'::text])
GROUP BY gp_param_settings.paramname, gp_param_settings.paramvalue
HAVING count(*) < (( SELECT __gp_number_of_segments.numsegments
FROM gp_toolkit.__gp_number_of_segments))
ORDER BY gp_param_settings.paramname, gp_param_settings.paramvalue, count(*);

Server configuration parameters that are classified as local parameters (meaning each segment gets the parameter value from its own postgresql.conf file), should be set identically on all segments. This view shows local parameter settings that are inconsistent. Parameters that are supposed to have different values (such as port) are not included. This view is accessible to all users.

psdname=>The name of the parameter.
psdvalue=>The value of the parameter.
psdcount=>The number of segments that have this value.

gpadmin=# select * from  gp_toolkit.gp_param_settings_seg_value_diffs;
 psdname | psdvalue | psdcount 
---------+----------+----------
(0 rows)

gp_toolkit.gp_log_system

posted Jan 20, 2014, 1:55 PM by Sachchida Ojha

gpadmin=# \d gp_toolkit.gp_log_system
View "gp_toolkit.gp_log_system"
Column | Type | Modifiers 
----------------+--------------------------+-----------
logtime | timestamp with time zone | 
loguser | text | 
logdatabase | text | 
logpid | text | 
logthread | text | 
loghost | text | 
logport | text | 
logsessiontime | timestamp with time zone | 
logtransaction | integer | 
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 | integer | 
logcontext | text | 
logdebug | text | 
logcursorpos | integer | 
logfunction | text | 
logfile | text | 
logline | integer | 
logstack | text | 
View definition:
SELECT __gp_log_segment_ext.logtime, __gp_log_segment_ext.loguser, __gp_log_segment_ext.logdatabase, __gp_log_segment_ext.logpid, __gp_log_segment_ext.logthread, __gp_log_segment_ext.loghost, __gp_log_segment_ext.logport, __gp_log_segment_ext.logsessiontime, __gp_log_segment_ext.logtransaction, __gp_log_segment_ext.logsession, __gp_log_segment_ext.logcmdcount, __gp_log_segment_ext.logsegment, __gp_log_segment_ext.logslice, __gp_log_segment_ext.logdistxact, __gp_log_segment_ext.loglocalxact, __gp_log_segment_ext.logsubxact, __gp_log_segment_ext.logseverity, __gp_log_segment_ext.logstate, __gp_log_segment_ext.logmessage, 
__gp_log_segment_ext.logdetail, __gp_log_segment_ext.loghint, __gp_log_segment_ext.logquery, __gp_log_segment_ext.logquerypos, __gp_log_segment_ext.logcontext, __gp_log_segment_ext.logdebug, __gp_log_segment_ext.logcursorpos, __gp_log_segment_ext.logfunction, __gp_log_segment_ext.logfile, __gp_log_segment_ext.logline, __gp_log_segment_ext.logstack
FROM ONLY gp_toolkit.__gp_log_segment_ext
UNION ALL 
SELECT __gp_log_master_ext.logtime, __gp_log_master_ext.loguser, __gp_log_master_ext.logdatabase, __gp_log_master_ext.logpid, __gp_log_master_ext.logthread,__gp_log_master_ext.loghost, __gp_log_master_ext.logport, __gp_log_master_ext.logsessiontime, __gp_log_master_ext.logtransaction, __gp_log_master_ext.logsession, __gp_log_master_ext.logcmdcount, __gp_log_master_ext.logsegment, __gp_log_master_ext.logslice, __gp_log_master_ext.logdistxact, __gp_log_master_ext.loglocalxact, __gp_log_master_ext.logsubxact, __gp_log_master_ext.logseverity, __gp_log_master_ext.logstate, __gp_log_master_ext.logmessage, __gp_log_master_ext.logdetail, __gp_log_master_ext.loghint, __gp_log_master_ext.logquery, __gp_log_master_ext.logquerypos, __gp_log_master_ext.logcontext, __gp_log_master_ext.logdebug, __gp_log_master_ext.logcursorpos, __gp_log_master_ext.logfunction, __gp_log_master_ext.logfile, __gp_log_master_ext.logline, __gp_log_master_ext.logstack
FROM ONLY gp_toolkit.__gp_log_master_ext
ORDER BY 1;

logtime=>The timestamp of the log message.
loguser=>The name of the database user.
logdatabase=>The name of the database.
logpid=>The associated process id (prefixed with "p").
logthread=>The associated thread count (prefixed with "th").
loghost=>The segment or master host name.
logport=>The segment or master port.
logsessiontime=>Time session connection was opened.
logtransaction=>Global transaction id.
logsession=>The session identifier (prefixed with "con").
logcmdcount=>The command number within a session (prefixed with "cmd").
logsegment=>The segment content identifier (prefixed with "seg" for primary or "mir" for mirror. The master always has a content id of -1).
logslice=>The slice id (portion of the query plan being executed).
logdistxact=>Distributed transaction id.
loglocalxact=>Local transaction id.
logsubxact=>Subtransaction id.
logseverity=>LOG, ERROR, FATAL, PANIC, DEBUG1 or DEBUG2.
logstate=>SQL state code associated with the log message.
logmessage=>Log or error message text.
logdetail=>Detail message text associated with an error message.
loghint=>Hint message text associated with an error message.
logquery=>The internally-generated query text.
logquerypos=>The cursor index into the internally-generated query text.
logcontext=>The context in which this message gets generated.
logdebug=>Query string with full detail for debugging.
logcursorpos=>The cursor index into the query string.
logfunction=>The function in which this message is generated.
logfile=>The log file in which this message is generated.
logline=>The line in the log file in which this message is generated.
logstack=>Full text of the stack trace associated with this message.

This view uses an external table to read the server log files of the entire Greenplum system (master, segments, and mirrors) and lists all log entries. Associated log entries can be identified by the session id (logsession) and command id (logcmdcount). The use of this view requires superuser permissions.

gpadmin=# select * from gp_toolkit.gp_log_system limit 10;
logtime | loguser | logdatabase | logpid | logthread | loghost | logport | logsessiontime | logtransaction | logsession | logcmdcou
nt | logsegment | logslice | logdistxact | loglocalxact | logsubxact | logseverity | logstate | 
logmessage | logdetail | loghint | logquery | logquerypos | lo
gcontext | logdebug | logcursorpos | logfunction | logfile | logline | logstack 
-------------------------------+---------+-------------+--------+--------------+---------+---------+----------------+----------------+------------+----------
---+------------+----------+-------------+--------------+------------+-------------+----------+--------------------------------------------------------------
----------------------------------------------------------------------------------------------------------+-----------+---------+----------+-------------+---
---------+----------+--------------+-------------+---------+---------+----------
2013-11-27 12:51:33.08036-05 | | | p10227 | th-509053184 | | | | 0 | | 
| seg-1 | | | | | LOG | 00000 | database system was shut down at 2013-11-27 12:51:32 EST 
| | | | | 
| | 0 | | xlog.c | 6394 | 
2013-11-27 12:51:33.080475-05 | | | p10227 | th-509053184 | | | | 0 | | 
| seg-1 | | | | | LOG | 00000 | checkpoint record is at 0/8ABA78 
| | | | | 
| | 0 | | xlog.c | 6493 | 
2013-11-27 12:51:33.080507-05 | | | p10227 | th-509053184 | | | | 0 | | 
| seg-1 | | | | | LOG | 00000 | redo record is at 0/8ABA78; undo record is at 0/0; shutdown T
RUE | | | | | 
| | 0 | | xlog.c | 6602 | 
2013-11-27 12:51:33.08053-05 | | | p10227 | th-509053184 | | | | 0 | | 
| seg-1 | | | | | LOG | 00000 | next transaction ID: 0/902; next OID: 10901 
| | | | | 
| | 0 | | xlog.c | 6606 | 
2013-11-27 12:51:33.080551-05 | | | p10227 | th-509053184 | | | | 0 | | 
| seg-1 | | | | | LOG | 00000 | next MultiXactId: 1; next MultiXactOffset: 0 
| | | | | 
| | 0 | | xlog.c | 6609 | 
2013-11-27 12:51:33.08059-05 | | | p10227 | th-509053184 | | | | 0 | | 
| seg-1 | | | | | LOG | 00000 | end of transaction log location is 0/8ABAF0 
| | | | | 
| | 0 | | xlog.c | 6840 | 
2013-11-27 12:51:33.081056-05 | | | p10227 | th-509053184 | | | | 0 | | 
| seg-1 | | | | | LOG | 00000 | Oldest active transaction from prepared transactions 902 
| | | | | 
| | 0 | | xlog.c | 6202 | 
2013-11-27 12:51:33.346109-05 | | | p10227 | th-509053184 | | | | 0 | | 
| seg-1 | | | | | LOG | 00000 | database system is ready 
| | | | | 
| | 0 | | xlog.c | 6231 | 
2013-11-27 12:51:33.346179-05 | | | p10227 | th-509053184 | | | | 0 | | 
| seg-1 | | | | | LOG | 00000 | PostgreSQL 8.2.15 (Greenplum Database 4.2.2.4 build 1 Communi
ty Edition) on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.4.2 compiled on Oct 17 2012 11:48:43 | | | | | 
| | 0 | | xlog.c | 6241 | 
2013-11-27 12:51:33.432896-05 | | | p10227 | th-509053184 | | | | 0 | | 
| seg-1 | | | | | LOG | 00000 | Finished normal startup for clean shutdown case 
| | | | | 
| | 0 | | xlog.c | 7071 | 
(10 rows)

gp_toolkit.gp_log_master_concise

posted Jan 20, 2014, 1:54 PM by Sachchida Ojha

gpadmin=# \d gp_toolkit.gp_log_master_concise
View "gp_toolkit.gp_log_master_concise"
Column | Type | Modifiers 
-------------+--------------------------+-----------
logtime | timestamp with time zone | 
logdatabase | text | 
logsession | text | 
logcmdcount | text | 
logseverity | text | 
logmessage | text | 
View definition:
SELECT __gp_log_master_ext.logtime, __gp_log_master_ext.logdatabase, __gp_log_master_ext.logsession, __gp_log_master_ext.logcmdcount, __gp_log_master_ext.logseverity, __gp_log_master_ext.logmessage
FROM ONLY gp_toolkit.__gp_log_master_ext;

logtime=>The timestamp of the log message.
logdatabase=>The name of the database.
logsession=>The session identifier (prefixed with "con").
logcmdcount=>The command number within a session (prefixed with "cmd").
logmessage=>Log or error message text.

This view uses an external table to read a subset of the log fields from the master log file. 
The use of this view requires superuser permissions.

gpadmin=# select * from gp_toolkit.gp_log_master_concise limit 10;
logtime | logdatabase | logsession | logcmdcount | logseverity | 
logmessage 
-------------------------------+-------------+------------+-------------+-------------+----------------------------------------------------------------------
--------------------------------------------------------------------------------------------------
2013-11-27 12:51:33.08036-05 | | | | LOG | database system was shut down at 2013-11-27 12:51:32 EST
2013-11-27 12:51:33.080475-05 | | | | LOG | checkpoint record is at 0/8ABA78
2013-11-27 12:51:33.080507-05 | | | | LOG | redo record is at 0/8ABA78; undo record is at 0/0; shutdown TRUE
2013-11-27 12:51:33.08053-05 | | | | LOG | next transaction ID: 0/902; next OID: 10901
2013-11-27 12:51:33.080551-05 | | | | LOG | next MultiXactId: 1; next MultiXactOffset: 0
2013-11-27 12:51:33.08059-05 | | | | LOG | end of transaction log location is 0/8ABAF0
2013-11-27 12:51:33.081056-05 | | | | LOG | Oldest active transaction from prepared transactions 902
2013-11-27 12:51:33.346109-05 | | | | LOG | database system is ready
2013-11-27 12:51:33.346179-05 | | | | LOG | PostgreSQL 8.2.15 (Greenplum Database 4.2.2.4 build 1 Community Editi
on) on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.4.2 compiled on Oct 17 2012 11:48:43
2013-11-27 12:51:33.432896-05 | | | | LOG | Finished normal startup for clean shutdown case
(10 rows)

gpadmin=#

gp_toolkit.gp_log_database

posted Jan 20, 2014, 1:53 PM by Sachchida Ojha

gpadmin=# \d gp_toolkit.gp_log_database
View "gp_toolkit.gp_log_database"
Column | Type | Modifiers 
----------------+--------------------------+-----------
logtime | timestamp with time zone | 
loguser | text | 
logdatabase | text | 
logpid | text | 
logthread | text | 
loghost | text | 
logport | text | 
logsessiontime | timestamp with time zone | 
logtransaction | integer | 
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 | integer | 
logcontext | text | 
logdebug | text | 
logcursorpos | integer | 
logfunction | text | 
logfile | text | 
logline | integer | 
logstack | text | 
View definition:
SELECT gp_log_system.logtime, gp_log_system.loguser, gp_log_system.logdatabase, gp_log_system.logpid, gp_log_system.logthread, gp_log_system.loghost, gp_log
_system.logport, gp_log_system.logsessiontime, gp_log_system.logtransaction, gp_log_system.logsession, gp_log_system.logcmdcount, gp_log_system.logsegment, g
p_log_system.logslice, gp_log_system.logdistxact, gp_log_system.loglocalxact, gp_log_system.logsubxact, gp_log_system.logseverity, gp_log_system.logstate, gp
_log_system.logmessage, gp_log_system.logdetail, gp_log_system.loghint, gp_log_system.logquery, gp_log_system.logquerypos, gp_log_system.logcontext, gp_log_s
ystem.logdebug, gp_log_system.logcursorpos, gp_log_system.logfunction, gp_log_system.logfile, gp_log_system.logline, gp_log_system.logstack
FROM gp_toolkit.gp_log_system
WHERE gp_log_system.logdatabase = current_database()::text

logtime=>The timestamp of the log message.
loguser=>The name of the database user.
logdatabase=>The name of the database.
logpid=>The associated process id (prefixed with "p").
logthread=>The associated thread count (prefixed with "th").
loghost=>The segment or master host name.
logport=>The segment or master port.
logsessiontime=>Time session connection was opened.
logtransaction=>Global transaction id.
logsession=>The session identifier (prefixed with "con").
logcmdcount=>The command number within a session (prefixed with "cmd").
logsegment=>The segment content identifier (prefixed with "seg" for primary or "mir" for mirror. The master always has a content id of -1).
logslice=>The slice id (portion of the query plan being executed).
logdistxact=>Distributed transaction id.
loglocalxact=>Local transaction id.
logsubxact=>Subtransaction id.
logseverity=>LOG, ERROR, FATAL, PANIC, DEBUG1 or DEBUG2.
logstate=>SQL state code associated with the log message.
logmessage=>Log or error message text.
logdetail=>Detail message text associated with an error message.
loghint=>Hint message text associated with an error message.
logquery=>The internally-generated query text.
logquerypos=>The cursor index into the internally-generated query text.
logcontext=>The context in which this message gets generated.
logdebug=>Query string with full detail for debugging.
logcursorpos=>The cursor index into the query string.
logfunction=>The function in which this message is generated.
logfile=>The log file in which this message is generated.
logline=>The line in the log file in which this message is generated.
logstack=>Full text of the stack trace associated with this message.

This view uses an external table to read the server log files of the entire Greenplum system (master, segments, and mirrors) and lists log entries associated with the current database. Associated log entries can be identified by the session id (logsession) and command id (logcmdcount). 
The use of this view requires superuser permissions.

gpadmin=# select * from gp_toolkit.gp_log_database limit 10;
logtime | loguser | logdatabase | logpid | logthread | loghost | logport | logsessiontime | logtransaction | logsessi
on | logcmdcount | logsegment | logslice | logdistxact | loglocalxact | logsubxact | logseverity | logstate | 
logmessage | logdetail | loghint | logquery | logquerypos | logcontext | 
logdebug | logcursorpos | logfunction | logfile | logline | logstack 
-------------------------------+---------+-------------+--------+---------------+--------------+---------+------------------------+----------------+---------
---+-------------+------------+----------+-------------+--------------+------------+-------------+----------+------------------------------------------------
----------------------------------------------------------------------------+-----------+---------+----------+-------------+------------+--------------------
---------------------------------------------------------------------------------------------+--------------+-------------+------------+---------+----------
2013-11-27 12:55:25.721427-05 | gpadmin | gpadmin | p19653 | th1344132864 | [local] | | 2013-11-27 12:55:25-05 | 0 | con11 
| | seg-1 | | | | | FATAL | 3D000 | database "gpadmin" does not exist 
| | | | | | 
| 0 | | postinit.c | 404 | 
2013-11-27 14:20:29.534951-05 | gpadmin | gpadmin | p26167 | th1318504192 | [local] | | 2013-11-27 14:20:19-05 | 1111 | con8 
| cmd1 | seg-1 | | dx3 | x1111 | sx1 | LOG | 00000 | statement: alter role sachi login; 
| | | | | | alter role sachi lo
gin; | 0 | | postgres.c | 1542 | 
2013-11-27 14:20:29.542075-05 | gpadmin | gpadmin | p26171 | th-1635629312 | 192.168.1.13 | 59892 | 2013-11-27 14:20:29-05 | 975 | con8 
| | seg0 | | | x975 | sx1 | LOG | 00000 | binding to 192.168.1.13 only 
| | | | | | 
| 0 | | ic_tcp.c | 230 | 
2013-11-27 14:20:29.549326-05 | gpadmin | gpadmin | p26173 | th-460380416 | 192.168.1.13 | 43424 | 2013-11-27 14:20:29-05 | 975 | con8 
| | seg1 | | | x975 | sx1 | LOG | 00000 | binding to 192.168.1.13 only 
| | | | | | 
| 0 | | ic_tcp.c | 230 | 
2013-11-27 14:21:12.910452-05 | gpadmin | gpadmin | p26167 | th1318504192 | [local] | | 2013-11-27 14:20:19-05 | 1113 | con8 
| cmd2 | seg-1 | | dx4 | x1113 | sx1 | LOG | 00000 | statement: CREATE RESOURCE QUEUE perf_test ACTI
VE THRESHOLD 2 COST THRESHOLD 100000000 IGNORE THRESHOLD 100000 OVERCOMMIT; | | | | | | CREATE RESOURCE QUE
UE perf_test ACTIVE THRESHOLD 2 COST THRESHOLD 100000000 IGNORE THRESHOLD 100000 OVERCOMMIT; | 0 | | postgres.c | 1542 | 
2013-11-27 14:21:12.914221-05 | gpadmin | gpadmin | p26208 | th-1635629312 | 192.168.1.13 | 59897 | 2013-11-27 14:21:12-05 | 978 | con8 
| | seg0 | | | x978 | sx1 | LOG | 00000 | binding to 192.168.1.13 only 
| | | | | | 
| 0 | | ic_tcp.c | 230 | 
2013-11-27 14:21:12.91816-05 | gpadmin | gpadmin | p26210 | th-460380416 | 192.168.1.13 | 43429 | 2013-11-27 14:21:12-05 | 978 | con8 
| | seg1 | | | x978 | sx1 | LOG | 00000 | binding to 192.168.1.13 only 
| | | | | | 
| 0 | | ic_tcp.c | 230 | 
2013-11-28 08:41:19.141128-05 | gpadmin | gpadmin | p29778 | th1318504192 | [local] | | 2013-11-28 08:41:06-05 | 1115 | con10 
| cmd1 | seg-1 | | dx5 | x1115 | sx1 | LOG | 00000 | statement: SELECT n.nspname AS "Name", 
| | | | | | SELECT n.nspname AS
"Name", | 0 | | postgres.c | 1542 | 

: pg_catalog.pg_get_userbyid(n.nspowner) AS "Ow
ner" : pg_catalog.pg_get
_userbyid(n.nspowner) AS "Owner" 

: FROM pg_catalog.pg_namespace n 
: FROM pg_catalog.pg_
namespace n 

: WHERE (n.nspname !~ '^pg_temp_' OR 
: WHERE (n.nspname 
!~ '^pg_temp_' OR 

: n.nspname = (pg_catalog.curren
t_schemas(true))[1]) : n.
nspname = (pg_catalog.current_schemas(true))[1]) 

: ORDER BY 1; 
: ORDER BY 1; 

2013-11-29 08:42:14.328884-05 | gpadmin | gpadmin | p10441 | th1318504192 | [local] | | 2013-11-29 08:42:06-05 | 1188 | con14 
| cmd1 | seg-1 | | dx74 | x1188 | sx1 | LOG | 00000 | statement: select version(); 
| | | | | | select version(); 
| 0 | | postgres.c | 1542 | 
2014-01-18 02:16:42.3651-05 | gpadmin | gpadmin | p30158 | th39933696 | [local] | | 2014-01-18 02:16:25-05 | 1379 | con7 
| cmd1 | seg-1 | | dx3 | x1379 | sx1 | LOG | 00000 | statement: SELECT c.oid, 
| | | | | | SELECT c.oid, 
| 0 | | postgres.c | 1542 | 

: n.nspname, 
: n.nspname, 


: c.relname 
: c.relname 


: FROM pg_catalog.pg_class c 
: FROM pg_catalog.pg_
class c 

: LEFT JOIN pg_catalog.pg_namespace n ON n.o
id = c.relnamespace : LEFT JOIN pg_c
atalog.pg_namespace n ON n.oid = c.relnamespace 

: WHERE n.nspname ~ '^(pg_catalog)#39; 
: WHERE n.nspname ~ '
^(pg_catalog)#39; 

: ORDER BY 2, 3; 
: ORDER BY 2, 3; 

(10 rows)

gpadmin=# 

gp_log_command_timings

posted Jan 20, 2014, 1:52 PM by Sachchida Ojha

gpadmin=# \d gp_toolkit.gp_log_command_timings
      View "gp_toolkit.gp_log_command_timings"
   Column    |           Type           | Modifiers 
-------------+--------------------------+-----------
 logsession  | text                     | 
 logcmdcount | text                     | 
 logdatabase | text                     | 
 loguser     | text                     | 
 logpid      | text                     | 
 logtimemin  | timestamp with time zone | 
 logtimemax  | timestamp with time zone | 
 logduration | interval                 | 
View definition:
 SELECT __gp_log_master_ext.logsession, __gp_log_master_ext.logcmdcount, __gp_log_master_ext.logdatabase, __gp_log_master_ext.loguser, __gp_log_master_ext.logpid, min(__gp_log_master_ext.logtime) AS logtimemin, max(__gp_log_master_ext.logtime) AS logtimemax, max(__gp_log_master_ext.logtime) - min(__gp_log_master_ext.logtime) AS logduration
   FROM ONLY gp_toolkit.__gp_log_master_ext
  WHERE __gp_log_master_ext.logsession IS NOT NULL AND __gp_log_master_ext.logcmdcount IS NOT NULL AND __gp_log_master_ext.logdatabase IS NOT NULL
  GROUP BY __gp_log_master_ext.logsession, __gp_log_master_ext.logcmdcount, __gp_log_master_ext.logdatabase, __gp_log_master_ext.loguser, __gp_log_master_ext.logpid;

logsession=>The session identifier (prefixed with "con").
logcmdcount=>The command number within a session (prefixed with "cmd").
logdatabase=>The name of the database.
loguser=>The name of the database user.
logpid=>The process id (prefixed with "p").
logtimemin=>The time of the first log message for this command.
logtimemax=>The time of the last log message for this command.
logduration=>Statement duration from start to end time.

This view uses an external table to read the log files on the master and report the execution time of SQL commands executed in a database session. The use of this view requires superuser permissions.

gpadmin=# select * from gp_toolkit.gp_log_command_timings;
logsession | logcmdcount | logdatabase | loguser | logpid | logtimemin | logtimemax | logduration 
------------+-------------+-------------+---------+--------+-------------------------------+-------------------------------+-----------------
con7 | cmd2702 | gpadmin | gpadmin | p30158 | 2014-01-18 02:16:44.045136-05 | 2014-01-18 02:16:44.045136-05 | 00:00:00
con7 | cmd989 | sachi | gpadmin | p10699 | 2013-12-12 12:17:59.834766-05 | 2013-12-12 12:17:59.834766-05 | 00:00:00
con7 | cmd413 | gpadmin | gpadmin | p30158 | 2014-01-18 02:16:42.856334-05 | 2014-01-18 02:16:42.856334-05 | 00:00:00
con7 | cmd2734 | gpadmin | gpadmin | p30158 | 2014-01-18 02:23:55.498894-05 | 2014-01-18 02:23:55.498894-05 | 00:00:00
con7 | cmd1546 | gpadmin | gpadmin | p30158 | 2014-01-18 02:16:43.459633-05 | 2014-01-18 02:16:43.459633-05 | 00:00:00
con12 | cmd31 | sachi | gpadmin | p10110 | 2013-11-29 08:40:27.041448-05 | 2013-11-29 08:40:27.07172-05 | 00:00:00.030272
con7 | cmd2742 | gpadmin | gpadmin | p30158 | 2014-01-19 19:32:48.948112-05 | 2014-01-19 19:32:48.948112-05 | 00:00:00
con7 | cmd1817 | gpadmin | gpadmin | p30158 | 2014-01-18 02:16:43.603791-05 | 2014-01-18 02:16:43.603791-05 | 00:00:00
con7 | cmd1136 | gpadmin | gpadmin | p30158 | 2014-01-18 02:16:43.249678-05 | 2014-01-18 02:16:43.249678-05 | 00:00:00
con7 | cmd985 | gpadmin | gpadmin | p30158 | 2014-01-18 02:16:43.179408-05 | 2014-01-18 02:16:43.179408-05 | 00:00:00
con7 | cmd3900 | gpadmin | gpadmin | p30158 | 2014-01-20 14:55:51.318356-05 | 2014-01-20 14:55:51.318356-05 | 00:00:00
con7 | cmd4539 | gpadmin | gpadmin | p30158 | 2014-01-20 14:58:13.253164-05 | 2014-01-20 14:58:13.253164-05 | 00:00:00
con9 | cmd99 | postgres | gpadmin | p18553 | 2013-11-27 12:54:23.82547-05 | 2013-11-27 12:54:23.82547-05 | 00:00:00
con7 | cmd4353 | gpadmin | gpadmin | p30158 | 2014-01-20 14:56:42.753756-05 | 2014-01-20 14:56:42.753756-05 | 00:00:00
con7 | cmd4104 | gpadmin | gpadmin | p30158 | 2014-01-20 14:56:14.832069-05 | 2014-01-20 14:56:14.832069-05 | 00:00:00
con7 | cmd469 | gpadmin | gpadmin | p30158 | 2014-01-18 02:16:42.88348-05 | 2014-01-18 02:16:42.88348-05 | 00:00:00
con8 | cmd4 | template1 | gpadmin | p18422 | 2013-11-27 12:54:22.644063-05 | 2013-11-27 12:54:22.644063-05 | 00:00:00
con7 | cmd2615 | gpadmin | gpadmin | p30158 | 2014-01-18 02:16:43.990813-05 | 2014-01-18 02:16:43.990813-05 | 00:00:00
con7 | cmd3716 | gpadmin | gpadmin | p30158 | 2014-01-20 14:55:49.594669-05 | 2014-01-20 14:55:49.594669-05 | 00:00:00
con7 | cmd182 | sachi | gpadmin | p10699 | 2013-12-12 11:32:29.719597-05 | 2013-12-12 11:32:29.719597-05 | 00:00:00
con7 | cmd4161 | gpadmin | gpadmin | p30158 | 2014-01-20 14:56:14.884239-05 | 2014-01-20 14:56:14.884239-05 | 00:00:00
con7 | cmd4108 | gpadmin | gpadmin | p30158 | 2014-01-20 14:56:14.839272-05 | 2014-01-20 14:56:14.839272-05 | 00:00:00
con7 | cmd3306 | gpadmin | gpadmin | p30158 | 2014-01-20 14:55:03.666139-05 | 2014-01-20 14:55:03.666139-05 | 00:00:00
con7 | cmd2040 | gpadmin | gpadmin | p30158 | 2014-01-18 02:16:43.702362-05 | 2014-01-18 02:16:43.702362-05 | 00:00:00
con7 | cmd665 | gpadmin | gpadmin | p30158 | 2014-01-18 02:16:43.003611-05 | 2014-01-18 02:16:43.003611-05 | 00:00:00
con11 | cmd2 | template1 | gpadmin | p3977 | 2013-11-28 11:19:39.516423-05 | 2013-11-28 11:19:39.865829-05 | 00:00:00.349406
con7 | cmd762 | sachi | gpadmin | p10699 | 2013-12-12 12:17:28.520531-05 | 2013-12-12 12:17:28.520531-05 | 00:00:00
con7 | cmd2563 | gpadmin | gpadmin | p30158 | 2014-01-18 02:16:43.963477-05 | 2014-01-18 02:16:43.963477-05 | 00:00:00
con7 | cmd1146 | sachi | gpadmin | p10699 | 2013-12-12 12:17:59.926756-05 | 2013-12-12 12:17:59.926756-05 | 00:00:00
con7 | cmd322 | sachi | gpadmin | p10699 | 2013-12-12 12:16:36.266479-05 | 2013-12-12 12:16:36.266479-05 | 00:00:00
con7 | cmd67 | gpadmin | gpadmin | p30158 | 2014-01-18 02:16:42.649847-05 | 2014-01-18 02:16:42.649847-05 | 00:00:00
con7 | cmd66 | sachi | gpadmin | p10699 | 2013-12-12 11:32:29.641545-05 | 2013-12-12 11:32:29.641545-05 | 00:00:00
con7 | cmd372 | sachi | gpadmin | p10699 | 2013-12-12 12:16:36.286566-05 | 2013-12-12 12:16:36.286566-05 | 00:00:00
con7 | cmd639 | sachi | gpadmin | p10699 | 2013-12-12 12:17:25.565363-05 | 2013-12-12 12:17:25.565363-05 | 00:00:00
con7 | cmd729 | sachi | gpadmin | p10699 | 2013-12-12 12:17:28.504424-05 | 2013-12-12 12:17:28.504424-05 | 00:00:00
con7 | cmd344 | sachi | gpadmin | p10699 | 2013-12-12 12:16:36.274324-05 | 2013-12-12 12:16:36.274324-05 | 00:00:00
con7 | cmd3269 | gpadmin | gpadmin | p30158 | 2014-01-20 14:55:03.65272-05 | 2014-01-20 14:55:03.65272-05 | 00:00:00
con7 | cmd5 | gpadmin | gpadmin | p30158 | 2014-01-18 02:16:42.534074-05 | 2014-01-18 02:16:42.534074-05 | 00:00:00
con7 | cmd775 | sachi | gpadmin | p10699 | 2013-12-12 12:17:28.524123-05 | 2013-12-12 12:17:28.524123-05 | 00:00:00
con11 | cmd20 | template1 | gpadmin | p3977 | 2013-11-28 11:19:40.355456-05 | 2013-11-28 11:19:40.384454-05 | 00:00:00.028998
con7 | cmd1741 | gpadmin | gpadmin | p30158 | 2014-01-18 02:16:43.56654-05 | 2014-01-18 02:16:43.56654-05 | 00:00:00
con7 | cmd707 | gpadmin | gpadmin | p30158 | 2014-01-18 02:16:43.022648-05 | 2014-01-18 02:16:43.022648-05 | 00:00:00
con7 | cmd4448 | gpadmin | gpadmin | p30158 | 2014-01-20 14:56:42.789864-05 | 2014-01-20 14:56:42.789864-05 | 00:00:00
con12 | cmd1 | sachi | gpadmin | p10110 | 2013-11-29 08:40:26.132038-05 | 2013-11-29 08:40:26.132038-05 | 00:00:00
con7 | cmd1228 | gpadmin | gpadmin | p30158 | 2014-01-18 02:16:43.294588-05 | 2014-01-18 02:16:43.294588-05 | 00:00:00
....
con7 | cmd711 | sachi | gpadmin | p10699 | 2013-12-12 12:17:28.49396-05 | 2013-12-12 12:17:28.49396-05 | 00:00:00
con7 | cmd322 | gpadmin | gpadmin | p30158 | 2014-01-18 02:16:42.814124-05 | 2014-01-18 02:16:42.814124-05 | 00:00:00
con7 | cmd3627 | gpadmin | gpadmin | p30158 | 2014-01-20 14:55:49.538092-05 | 2014-01-20 14:55:49.538092-05 | 00:00:00
con7 | cmd3895 | gpadmin | gpadmin | p30158 | 2014-01-20 14:55:51.315797-05 | 2014-01-20 14:55:51.315797-05 | 00:00:00
con7 | cmd1026 | gpadmin | gpadmin | p30158 | 2014-01-18 02:16:43.195032-05 | 2014-01-18 02:16:43.195032-05 | 00:00:00
con7 | cmd4012 | gpadmin | gpadmin | p30158 | 2014-01-20 14:55:51.353741-05 | 2014-01-20 14:55:51.353741-05 | 00:00:00
con7 | cmd875 | gpadmin | gpadmin | p30158 | 2014-01-18 02:16:43.111861-05 | 2014-01-18 02:16:43.111861-05 | 00:00:00
con7 | cmd1961 | gpadmin | gpadmin | p30158 | 2014-01-18 02:16:43.665132-05 | 2014-01-18 02:16:43.665132-05 | 00:00:00
con7 | cmd348 | sachi | gpadmin | p10699 | 2013-12-12 12:16:36.275108-05 | 2013-12-12 12:16:36.275108-05 | 00:00:00
con7 | cmd446 | sachi | gpadmin | p10699 | 2013-12-12 12:16:36.314844-05 | 2013-12-12 12:16:36.314844-05 | 00:00:00
con7 | cmd2611 | gpadmin | gpadmin | p30158 | 2014-01-18 02:16:43.99029-05 | 2014-01-18 02:16:43.99029-05 | 00:00:00
con7 | cmd267 | gpadmin | gpadmin | p30158 | 2014-01-18 02:16:42.790117-05 | 2014-01-18 02:16:42.790117-05 | 00:00:00
con7 | cmd3564 | gpadmin | gpadmin | p30158 | 2014-01-20 14:55:07.075346-05 | 2014-01-20 14:55:07.075346-05 | 00:00:00
con9 | cmd41 | postgres | gpadmin | p18553 | 2013-11-27 12:54:23.616744-05 | 2013-11-27 12:54:23.616744-05 | 00:00:00
con7 | cmd2842 | gpadmin | gpadmin | p30158 | 2014-01-19 20:37:23.957804-05 | 2014-01-19 20:37:23.957804-05 | 00:00:00
con7 | cmd561 | sachi | gpadmin | p10699 | 2013-12-12 12:17:25.533968-05 | 2013-12-12 12:17:25.533968-05 | 00:00:00
(3232 rows)

gp_toolkit.gp_locks_on_resqueue

posted Jan 20, 2014, 12:54 PM by Sachchida Ojha

gpadmin=# \d gp_toolkit.gp_locks_on_resqueue
View "gp_toolkit.gp_locks_on_resqueue"
     Column     |  Type   | Modifiers 
----------------+---------+-----------
 lorusename     | name    | 
 lorrsqname     | name    | 
 lorlocktype    | text    | 
 lorobjid       | oid     | 
 lortransaction | xid     | 
 lorpid         | integer | 
 lormode        | text    | 
 lorgranted     | boolean | 
 lorwaiting     | boolean | 
View definition:
 SELECT pgsa.usename AS lorusename, pgrq.rsqname AS lorrsqname, pgl.locktype AS lorlocktype, pgl.objid AS lorobjid, pgl.transaction AS lortransaction, pgl.pid AS lorpid, pgl.mode AS lormode, pgl.granted AS lorgranted, pgsa.waiting AS lorwaiting
   FROM pg_stat_activity pgsa
   JOIN pg_locks pgl ON pgsa.procpid = pgl.pid
   JOIN pg_resqueue pgrq ON pgl.objid = pgrq.oid;

lorusename=>Name of the user executing the session.
lorrsqname=>The resource queue name.
lorlocktype=>Type of the lockable object: resource queue
lorobjid=>The ID of the locked transaction.
lortransaction=>The ID of the transaction that is affected by the lock.
lorpid=>The process ID of the transaction that is affected by the lock.
lormode=>The name of the lock mode held or desired by this process.
lorgranted=>Displays whether the lock is granted (true) or not granted (false).
lorwaiting=>Displays whether or not the session is waiting.

This view shows any locks currently being held on a resource queue, and the associated session information about the query associated with the lock. This view is accessible to all users, however non-superusers will only be able to see the locks associated with their own sessions.

gpadmin=# select * from  gp_toolkit.gp_locks_on_resqueue;
 lorusename | lorrsqname | lorlocktype | lorobjid | lortransaction | lorpid | lormode | lorgranted | lorwaiting 
------------+------------+-------------+----------+----------------+--------+---------+------------+------------
(0 rows)

gp_toolkit.gp_locks_on_relation

posted Jan 20, 2014, 12:47 PM by Sachchida Ojha   [ updated Jan 20, 2014, 12:49 PM ]

 gp_toolkit.gp_locks_on_relation helps diagnose queries and sessions that are waiting to access an object due to a lock.

gpadmin=# \d gp_toolkit.gp_locks_on_relation
View "gp_toolkit.gp_locks_on_relation"
     Column      |  Type   | Modifiers 
-----------------+---------+-----------
 lorlocktype     | text    | 
 lordatabase     | oid     | 
 lorrelname      | name    | 
 lorrelation     | oid     | 
 lortransaction  | xid     | 
 lorpid          | integer | 
 lormode         | text    | 
 lorgranted      | boolean | 
 lorcurrentquery | text    | 
View definition:
 SELECT pgl.locktype AS lorlocktype, pgl.database AS lordatabase, pgc.relname AS lorrelname, pgl.relation AS lorrelation, pgl.transaction AS lortransaction, pgl.pid AS lorpid, pgl.mode AS lormode, pgl.granted AS lorgranted, pgsa.current_query AS lorcurrentquery
   FROM pg_locks pgl
   JOIN pg_class pgc ON pgl.relation = pgc.oid
   JOIN pg_stat_activity pgsa ON pgl.pid = pgsa.procpid
  ORDER BY pgc.relname;

lorlocktype=>Type of the lockable object: relation, extend, page, tuple, transactionid, object, userlock, resource queue, or advisory 
lordatabase=>Object ID of the database in which the object exists, zero if the object is a shared object.
lorrelname=>The name of the relation.
lorrelation=>The object ID of the relation.
lortransaction=>The transaction ID that is affected by the lock.
lorpid=>Process ID of the server process holding or awaiting this lock. NULL if the lock is held by a prepared transaction.
lormode=>Name of the lock mode held or desired by this process.
lorgranted=>Displays whether the lock is granted (true) or not granted (false).
lorcurrentquery=>The current query in the session.

This view shows any locks currently being held on a relation, and the associated session information about the query associated with the lock. For more information on the types of locks, see the Greenplum Database Database Administrator Guide. This view is accessible to all users, however non-superusers will only be able to see the locks for relations that they have permission to access.

gpadmin=# select * from gp_toolkit.gp_locks_on_relation;
lorlocktype | lordatabase | lorrelname | lorrelation | lortransaction | lorpid | lormode | lorgranted | lorcurrentq
uery 
-------------+-------------+-----------------------------+-------------+----------------+--------+-----------------+------------+----------------------------
---------------------
relation | 16992 | gp_locks_on_relation | 16649 | 3598 | 30158 | AccessShareLock | t | select * from gp_toolkit.g
p_locks_on_relation;
relation | 0 | pg_authid | 1260 | 3598 | 30158 | AccessShareLock | t | select * from gp_toolkit.g
p_locks_on_relation;
relation | 0 | pg_authid_oid_index | 2677 | 3598 | 30158 | AccessShareLock | t | select * from gp_toolkit.g
p_locks_on_relation;
relation | 0 | pg_authid_rolname_index | 2676 | 3598 | 30158 | AccessShareLock | t | select * from gp_toolkit.g
p_locks_on_relation;
relation | 0 | pg_authid_rolresqueue_index | 6029 | 3598 | 30158 | AccessShareLock | t | select * from gp_toolkit.g
p_locks_on_relation;
relation | 16992 | pg_class | 1259 | 3598 | 30158 | AccessShareLock | t | select * from gp_toolkit.g
p_locks_on_relation;
relation | 16992 | pg_class_oid_index | 2662 | 3598 | 30158 | AccessShareLock | t | select * from gp_toolkit.g
p_locks_on_relation;
relation | 16992 | pg_class_relname_nsp_index | 2663 | 3598 | 30158 | AccessShareLock | t | select * from gp_toolkit.g
p_locks_on_relation;
relation | 0 | pg_database | 1262 | 3598 | 30158 | AccessShareLock | t | select * from gp_toolkit.g
p_locks_on_relation;
relation | 0 | pg_database_datname_index | 2671 | 3598 | 30158 | AccessShareLock | t | select * from gp_toolkit.g
p_locks_on_relation;
relation | 0 | pg_database_oid_index | 2672 | 3598 | 30158 | AccessShareLock | t | select * from gp_toolkit.g
p_locks_on_relation;
relation | 16992 | pg_locks | 10337 | 3598 | 30158 | AccessShareLock | t | select * from gp_toolkit.g
p_locks_on_relation;
relation | 16992 | pg_stat_activity | 10405 | 3598 | 30158 | AccessShareLock | t | select * from gp_toolkit.g
p_locks_on_relation;
(13 rows)
gpadmin=# 

gp_toolkit.gp_stats_missing

posted Jan 20, 2014, 12:09 PM by Sachchida Ojha   [ updated Jan 20, 2014, 12:12 PM ]

gp_toolkit.gp_stats_missing view helps to identify tables that need routine table maintenance (VACUUM and/or ANALYZE). 

gpadmin-# \d gp_toolkit.gp_stats_missing 
View "gp_toolkit.gp_stats_missing"
  Column   |  Type   | Modifiers 
-----------+---------+-----------
 smischema | name    | 
 smitable  | name    | 
 smisize   | boolean | 
 smicols   | bigint  | 
 smirecs   | bigint  | 
View definition:
 SELECT aut.autnspname AS smischema, aut.autrelname AS smitable, 
        CASE
            WHEN aut.autrelpages = 0 OR aut.autreltuples = 0::double precision THEN false
            ELSE true
        END AS smisize, attrs.attcnt AS smicols, COALESCE(bar.stacnt, 0::bigint) AS smirecs
   FROM gp_toolkit.__gp_user_tables aut
   JOIN ( SELECT pg_attribute.attrelid, count(*) AS attcnt
           FROM pg_attribute
          WHERE pg_attribute.attnum > 0
          GROUP BY pg_attribute.attrelid) attrs ON aut.autoid = attrs.attrelid
   LEFT JOIN ( SELECT pg_statistic.starelid, count(*) AS stacnt
      FROM pg_statistic
     GROUP BY pg_statistic.starelid) bar ON aut.autoid = bar.starelid
  WHERE (aut.autrelkind = 'r'::"char" AND (aut.autrelpages = 0 OR aut.autreltuples = 0::double precision)) OR (bar.stacnt IS NOT NULL AND attrs.attcnt > bar.stacnt);

smischema => Schema name.
smitable => Table name.
smisize => Does this table have statistics? False if the table does not have row count and row sizing statistics recorded in the system catalog, which may indicate that the table needs to be analyzed. This will also be false if the table does not contain any rows. For example, the parent tables of partitioned tables are always empty and will always return a false result.
smicols=> Number of columns in the table.
smirecs=> Number of rows in the table.

gpadmin=# select * from gp_toolkit.gp_stats_missing;
 smischema | smitable | smisize | smicols | smirecs 
-----------+----------+---------+---------+---------
(0 rows)

This view shows tables that do not have statistics and therefore may require an ANALYZE be run on the table.

gp_toolkit.gp_bloat_diag

posted Jan 20, 2014, 12:05 PM by Sachchida Ojha   [ updated Jan 20, 2014, 12:15 PM ]

gp_bloat_diag view helps to identify tables that need routine table maintenance (VACUUM and/or ANALYZE). 
gpadmin-# \d gp_toolkit.gp_bloat_diag
  View "gp_toolkit.gp_bloat_diag"
   Column    |  Type   | Modifiers 
-------------+---------+-----------
 bdirelid    | oid     | 
 bdinspname  | name    | 
 bdirelname  | name    | 
 bdirelpages | integer | 
 bdiexppages | numeric | 
 bdidiag     | text    | 
View definition:
 SELECT bloatsummary.btdrelid AS bdirelid, bloatsummary.fnnspname AS bdinspname, bloatsummary.fnrelname AS bdirelname, bloatsummary.btdrelpages AS bdirelpages, bloatsummary.btdexppages AS bdiexppages, (bloatsummary.bd).bltdiag AS bdidiag
   FROM ( SELECT fn.fnoid, fn.fnnspname, fn.fnrelname, beg.btdrelid, beg.btdrelpages, beg.btdexppages, gp_toolkit.gp_bloat_diag(beg.btdrelpages, beg.btdexppages::integer, iao.iaotype) AS bd
           FROM gp_toolkit.gp_bloat_expected_pages beg, pg_class pgc, gp_toolkit.__gp_fullname fn, gp_toolkit.__gp_is_append_only iao
          WHERE beg.btdrelid = pgc.oid AND pgc.oid = fn.fnoid AND iao.iaooid = pgc.oid) bloatsummary
  WHERE (bloatsummary.bd).bltidx > 0;

bdirelid=>Table object id.
bdinspname=>Schema name.
bdirelname=>Table name.
bdirelpages=>Actual number of pages on disk.
bdiexppages=>Expected number of pages given the table data.
bdidiag=>Bloat diagnostic message.

gpadmin=# select * from  gp_toolkit.gp_bloat_diag;
 bdirelid | bdinspname | bdirelname | bdirelpages | bdiexppages | bdidiag 
----------+------------+------------+-------------+-------------+---------
(0 rows)

This view shows tables that have bloat (the actual number of pages on disk exceeds the expected number of pages given the table statistics). Tables that are bloated require a VACUUM or a VACUUM FULL in order to reclaim disk space occupied by deleted or obsolete rows. This view is accessible to all users, however non-superusers will only be able to see the tables that they have permission to access.

1-9 of 9