Recent Greenplum Database blog post

Recent Greenplum Database Blog Posts

  • 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
  • Checking list of security definer functions in GPDB testdb=# \d pg_proc       Table "pg_catalog.pg_proc"     Column     |   Type    | Modifiers ----------------+-----------+----------- proname        | name      | not null pronamespace   | oid       | not null proowner       | oid       | not null prolang        | oid       | not null proisagg       | boolean ...
    Posted May 11, 2016, 4:55 AM 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 for a specific user/role gpadmin=# select * from pg_roles where rolname='sachi';-[ RECORD 1 ]-----+---------rolname           | sachirolsuper          | frolinherit        | trolcreaterole     | frolcreatedb       | frolcatupdate      | frolcanlogin       | frolconnlimit      | -1rolpassword       | ********rolvaliduntil     | rolconfig         | rolresqueue ...
    Posted May 20, 2016, 5:53 PM 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 May 30, 2015, 1:39 PM 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 Dec 2, 2014, 6:43 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
  • Log related views in the gp_toolkit schema sachi=# \d gp_toolkit.gp_log*View "gp_toolkit.gp_log_command_timings" Column | Type | Modifiers -------------+--------------------------+----------- logsession | text | logcmdcount | text | logdatabase | text | loguser | text | logpid | text | logtimemin | timestamp with time ...
    Posted Nov 5, 2014, 3:09 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 Nov 3, 2014, 7:19 PM 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
  • Understanding gp_skew_coefficients and gp_skew_idle_fractions gp_toolkit views to monitor Skew in Greenplum 1. gp_skew_coefficientsgpadmin=# \d gp_toolkit.gp_skew_coefficientsView "gp_toolkit.gp_skew_coefficients"Column    |  Type   | Modifiers --------------+---------+----------- skcoid       | oid     |  skcnamespace | name    |  skcrelname   | name    |  skccoeff     | numeric | View definition ...
    Posted Oct 19, 2014, 7:01 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
  • Partition Information 1. List of partition tables in all schema in your db.sachi=# select distinct tablename from pg_partitions; tablename ----------- cms_part cms_qlz cms_zlib cms_zlib9 sales(5 rows ...
    Posted Oct 17, 2014, 5:34 PM by Sachchida Ojha
  • Worst Performing SQL (taking more than 30 minutes) in Greenplum in last 2 days select sh.ctime,query_text,username, db,rsqname,avg(tfinish-tstart) as run_time,min(to_char(sh.ctime, 'Month')) as "month",min(to_char(sh.ctime, 'dd')) as "day ...
    Posted Oct 17, 2014, 5:09 PM by Sachchida Ojha
Showing posts 1 - 50 of 217. View more »

Comments