Recent Greenplum Database blog post

Finding Bad table name in Greenplum Due to some bug in the older version of greenplum some of table contains (-) or (.) in the name. You can find all those tables names with following SQL.select relname ...
Posted May 27, 2022, 11:58 AM by Sachchida Ojha

Understanding Greenplum Bloat on heap, AO and AO columnar tables by examples and how to remove it. Step 1: Create heap, ao and ao columnar (column oriented) table drop table IF EXISTS sachi.vac_test_heap; drop table IF EXISTS sachi.vac_test_ao; drop table IF ...
Posted Apr 22, 2022, 3:14 PM by Sachchida Ojha

MADLib and MADLib pivot function Apache MADlib is a SQL-based open source library for scalable in-database analytics that supports Greenplum Database. The library offers data scientists numerous distributed implementations of mathematical, statistical and ...
Posted Oct 6, 2016, 12:25 PM by Sachchida Ojha

Connecting as another user without their password In Oracle we use to run (from privileged user)alter session set current_schema=abc;Lets discuss how we can achieve same thing in Greenplum using SET ROLE and with ...
Posted Jan 15, 2016, 12:41 AM by Sachchida Ojha

Enable PQO (Pivotal Query Optimizer) at the database level Step 1: Check the status of the optimizergpadmin=# show optimizer; optimizer ----------- off(1 row)gpadmin=# show optimizer_control; optimizer_control ------------------- on(1 row)Step 1: Check state of the ...
Posted May 20, 2016, 5:52 PM by Sachchida Ojha

Select runs but insert failing in Greenplum database Few days back I faced a unique problem. In one environment of Greenplum database system, inserts on a table (ITAS) was failing but select was not. When I executed the ...
Posted May 20, 2016, 5:52 PM by Sachchida Ojha

New Parameters in GPDB 4.3 and onward There were 22 new parameters introduced in Greenplum starting GPDB 4.3.0. Mostly these parameters are related to specific features. Click here to learn more about these 22 parameters
Posted May 27, 2015, 4:13 PM by Sachchida Ojha

List Append Only / Append Organized table details in Greenplum sachi=# SELECT n.nspname AS schema_name ,c.relname AS table_name FROM pg_catalog.pg_appendonly a ,pg_catalog.pg_class c ,pg_catalog.pg_namespace n WHERE c ...
Posted Sep 14, 2018, 10:48 AM by Sachchida Ojha

Spill or workfile configuration parameters Greenplum introduced 2 server configuration parameter. 1. gp_workfile_limit_per_query – This sets the maximum disk size (in kilobytes) an individual query is allowed to use for creating temporary ...
Posted May 25, 2015, 9:17 AM by Sachchida Ojha

Summary Of New Features in Greenplum Database 4.3.X.X Summary of new Features (Family 4.3.X.X)For details, visit http://www.greenplumdba.com/greenplum-database---new-features 1. Query Optimizer and Management of query execution (4.3 ...
Posted May 20, 2016, 5:25 PM by Sachchida Ojha

Copy table and data from dev (half DCA rac) to test env (full DCA rac) with copy Note: This method is good for small tables. Large table should use greenplum parallel unload/load method.Test CASE: Copy a small table from DEV database to TEST database running ...
Posted Feb 25, 2017, 4:42 AM by Sachchida Ojha

Making a duplicate copy of a schema in the same database without data in Greenplum Easiest way to make a duplicate copy of a schema without data in the same database. Lets say I have an schema in dbadmin in sachi database. I want another ...
Posted Feb 26, 2015, 11:14 AM by Sachchida Ojha

pg_stat_activity not showing current_query text pg_stat_activity table is most often used table to query the current activity of the database.sachi=# select * from pg_stat_activity; datid | datname | procpid | sess_id | usesysid | usename ...
Posted Feb 26, 2015, 9:08 AM by Sachchida Ojha

Fixing ERROR: index "pg_class_relname_nsp_index" contains unexpected zero page at block 64 (seg46 sdw8:40004 pid=15677) DCA was down due to segment failure. DBA's recovered the segments. After DCA is brought up, developers starts getting following error when creating tables. Sequence of events1. DBA ...
Posted Feb 24, 2015, 5:11 AM by Sachchida Ojha

SQL query to list group role along with its members There is a table in the gp_toolkit schema that list this information.sachi=# sachi=# \d gp_toolkit.gp_roles_assignedView "gp_toolkit.gp_roles_assigned" Column | Type | Modifiers ...
Posted Jan 28, 2015, 7:16 AM by Sachchida Ojha

Troubleshooting BOE connection error to GPDB using Data direct driver after SSL is turned on on GPDB Sample Default odbc.ini FileThe following is a sample odbc.ini file that Setup installs in the installation directory. All occurrences of ODBCHOME are replaced with your installation directory ...
Posted Jan 22, 2015, 8:09 PM by Sachchida Ojha

4 parameters used to configure and monitor disk space alerts using emc call home feature These parameters are set in the gpperfmon.conf file located at $MASTER_DATA_DIRECTORY/gpperfmon/conf directory.1. warning_disk_space_percentage: This should be a percentage between 0 and ...
Posted Jan 22, 2015, 7:14 AM by Sachchida Ojha

Two important connection parameters for Greenplum DBA's 1. gp_connections_per_thread: Sets the number of client connections handled in each thread.2. gp_connection_send_timeout: controls the timeout value for sending data to unresponsive Greenplum ...
Posted Jan 21, 2015, 5:19 AM by Sachchida Ojha

ERROR: Function cannot execute on segment because it accesses ... Functions usage in Greenplum is limited because of its shared nothing architecture. It is possible to execute volatile and stable functions only when they can be run on the master ...
Posted Jan 15, 2015, 8:00 AM by Sachchida Ojha

Out of Memory error in Greenplum? Will adding more hosts provide relief for an OOM error?No. The memory used by each query is determined by the statement_mem parameter and it is set at query ...
Posted Jan 4, 2015, 7:08 AM by Sachchida Ojha

What should an application do if they get an “out of memory” error? For example:Out of memory (seg27 host.greenplumdba.com pid=47093)VM Protect failed to allocate 4096 bytes, 0 MB availableHere are the possible solutions: Tune query to require ...
Posted Jan 4, 2015, 7:11 AM by Sachchida Ojha

Data types to be used and avoid in a distribution in Greenplum The columns with the following data types should be avoided and not be part of any DISTRIBUTION key selected for any given tableTEXT is data type with unlimited length ...
Posted Dec 4, 2014, 3:08 PM by Sachchida Ojha

Finding Syntax of a PSQL command One of the most frequent question asked by Newbies that what is the syntax of a command like create table, create index etc. It is easily available through online help ...
Posted Dec 3, 2014, 12:06 PM by Sachchida Ojha

Finding table creation time in Greenplum What are different ways to find the table creation time in Greenplum?sachi=# create table test (like pg_stat_last_shoperation);NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using ...
Posted Dec 3, 2014, 10:10 AM by Sachchida Ojha

Understanding default values of general configuration parameters related to resource queue sachi=# show max_resource_portals_per_transaction;max_resource_portals_per_transaction -------------------------------------- 64(1 row) max_resource_portals_per_transaction Sets the maximum number of simultaneously open cursors allowed per ...
Posted Nov 24, 2018, 3:55 PM by Sachchida Ojha

pg_default resource queue pg_default is the default resource queue created when you install the greenplum database. pg_default resource queue has an active statement limit of 20, no cost limit, no memory ...
Posted Dec 2, 2014, 6:21 PM by Sachchida Ojha

NaN - Not a Numeric or Not a Number error in Greenplum Greenplum Database returns NaN (not a number) if the results of agregates are undefined. This can happen if there is a very small amount of data.nanvl : Oracle-compliant function ...
Posted Nov 17, 2014, 10:44 AM by Sachchida Ojha

Understanding Query iterator The gpperfmon database iterators_* tables store information about query plan iterators and their metrics. A query iterator refers to a node or operation in a query plan. Here are the ...
Posted Nov 16, 2014, 5:09 PM by Sachchida Ojha

OFFSET, LIMIT and ROW_NUMBER() OVER() in GREENPLUM LIMIT and OFFSET allow you to retrieve just a portion of the rows that are generated by the rest of the query: Greenplum WINDOW function ROW_NUMBER can also be ...
Posted Nov 16, 2014, 8:42 AM by Sachchida Ojha

Enable and disable indexes in Greenplum like oracle You can not disable index in Greenplum. Greenplum is a MPP database and index should be avoided.ALTER INDEXChanges the definition of an index.SynopsisALTER INDEX name RENAME ...
Posted Nov 12, 2014, 7:45 AM by Sachchida Ojha

Oracle rownum in Greenplum select row_number() over() as id, t.* from pg_tables t;works similar like oracle rownum.
Posted Nov 11, 2014, 3:48 PM by Sachchida Ojha

List all tables in your database - largest first - largest in terms of no or row or tuples SELECT n.nspname AS schemaname, c.relname AS tablename,c.relpages,c. reltuples, pg_get_userbyid(c.relowner) AS tableowner, t.spcname AS tablespace, c.relhasindex AS hasindexes, c.relhasrules ...
Posted Nov 10, 2014, 5:32 PM by Sachchida Ojha

List all tables in your database - largest first - largest by no of relpages SELECT n.nspname AS schemaname, c.relname AS tablename,c.relpages,c. reltuples, pg_get_userbyid(c.relowner) AS tableowner, t.spcname AS tablespace, c.relhasindex AS hasindexes, c.relhasrules ...
Posted Nov 10, 2014, 5:25 PM by Sachchida Ojha

How to find last ALTAR TABLE DDL on a table in Greenplum sachi=# \d sachi.abc Table "sachi.abc" Column | Type | Modifiers --------+-----------------------+----------- id | numeric | name | character varying(10) | Indexes: "abc_id" btree (id)Distributed by: (id)sachi=# alter table sachi.abc set ...
Posted Nov 4, 2014, 2:08 PM by Sachchida Ojha

A simple shell script to redistribute, index and analyze table in Greenplum [gpadmin@sachi reports]$ cat a.sh#!/bin/bashset -eset -udbconnect="psql -X --set ON_ERROR_STOP=on --set AUTOCOMMIT=off sachi"$dbconnect<<SQL\timing on\qecho Altering ...
Posted Apr 29, 2017, 9:18 AM by Sachchida Ojha

Users, Group roles, Group role user mapping, priv granted to a role etc --List databasesselect datname,datacl from pg_database; --List Schemasselect nspname from pg_catalog.pg_namespace; -- List User Rolesselect * from pg_user; -- List Group Rolesselect * from pg ...
Posted Nov 3, 2014, 7:33 PM by Sachchida Ojha

Get DDL for all schema in your database in Greenplum $pg_dump -s -c sachiwhere sachi is the database name.You can redirect it to a file $pg_dump -s -c sachi>>ddl_sachi.sql
Posted Nov 3, 2014, 7:33 PM by Sachchida Ojha

Handling schema names starting with numeric during maintenance operation The simplest solution of this problem to put your schema name in double quotes. I have seen cases where there are tables with UPPER CASE. Your maintenance script will fail ...
Posted Nov 3, 2014, 7:34 PM by Sachchida Ojha

List of GP configuration parameters supported by gpconfig List all configuration parameters supported by gpconfig:gpconfig -l[gpadmin@sachi scripts]$ gpconfig -l[name: add_missing_from] [unit: None] [context: user] [vartype: bool] [min_val: None] [max_val: None ...
Posted Oct 19, 2014, 6:31 PM by Sachchida Ojha

Changing Greenplum configuration parameter using gpconfig [gpadmin@sachi scripts]$ gpconfig --helpCOMMAND NAME: gpconfigSets server configuration parameters on all segments within a Greenplum Database system.*****************************************************SYNOPSIS*****************************************************gpconfig -c <param_name> -v <value> [-m <master_value ...
Posted Nov 30, 2014, 3:43 PM by Sachchida Ojha

Table row counts sachi=# CREATE TABLE sachitabletest (sachi(# id integer,sachi(# gender char(1),sachi(# title varchar(40) NOT NULL,sachi(# did integer NOT NULL,sachi(# date_prod date,sachi(# kind varchar(10 ...
Posted Oct 19, 2014, 5:01 PM by Sachchida Ojha

Retrieving information about tables and views -- TableSELECT n.nspname AS schemaname, c.relname AS tablename, pg_get_userbyid(c.relowner) AS tableowner, t.spcname AS tablespace, c.relhasindex AS hasindexes, c.relhasrules AS hasrules, c ...
Posted Oct 19, 2014, 3:14 PM by Sachchida Ojha

GP functions in pg_catalog schema sachi=# \df pg_catalog.gp_* List of functions Schema | Name | Result data type | Argument data types | Type ------------+-----------------------------------------------+-----------------------------------+----------------------------------------------------------------------pg_catalog | gp_activate_standby | boolean | | normal ...
Posted Oct 19, 2014, 11:33 AM by Sachchida Ojha

List no of columns in a table in Greenplum sachi=# select count(*) from information_schema.columns sachi-# where table_name='sales'; count ------- 5 (1 row)sachi=# SELECT DISTINCT a.attnum as num, a.attname as name, format_type(a ...
Posted Oct 19, 2014, 12:53 PM by Sachchida Ojha

Greenplum database ASH (active session history) Report This report displays the information related to active sessions such as No of Active Sessions No of Waiting SessionsResources (CPU, MEMORY, DISK READ, DISK WRITE, NETWORK READ, NETWORK WRITE ...
Posted Oct 19, 2014, 10:46 AM by Sachchida Ojha

Greenplum database (GPDB) AWR report light and advanced version Being Oracle DBA for almost 22 years I understand the importance of AWR report for DBA's. I have seen most Oracle DBA's use this report to understand and ...
Posted Oct 18, 2014, 9:06 AM by Sachchida Ojha

Recent Greenplum Database Blog Posts