WIXLIB

Greenplum Database 4.2 Release Notes Greenplum Database 4.2 - Extensions - PL/R 1.0PL/R version 8.3.0.12R version 2.13.0 Greenplum Database 4.2 - Extensions - PL/Perl 1.0PL/Perl (shipped with the database – based on GPDB)Perl (current version 5.8.8) Greenplum Database 4.2 - Extensions - Pgcrypto 1.0Pgcrypto (based on Postgresql 8.3).IPv6 supportAs the address exhaustion of Internet Protocol version 4 (IPv4) approaches, support ofits successor, Internet Protocol version 6 (IPv6), has become more and moreimportant.This release of Greenplum Database provides support for IPv6 as well as IPv4addressing. This new support is transparent to end-users.Greenplum Database 4.2 supports IPV6 only, IPV4 only, as well as dual stackenvironments for client-master connections, as follows: Exclusive IPv6 environment (Master Server, Segment Hosts, Clients) Exclusive IPv4 environment (Master Server, Segment Hosts, Clients) IPv6 with dual-stack master: Master Server and Standby Server support both IPv4 and IPv6 clientconnections (psql, odbc, jdbc, etc.) All the Segment Servers must be exclusive IPv6 gpfdist Server supports the same network protocol as Segment Servers (IPv6)Note: Performance Monitor Web Server 4.1.1.8 supports Greenplum Databaserunning on either IPv4 or IPv6 environments noted above.When using IPv6, any references to IP addresses must be enclosed in brackets [ ] asshown in the following examples:gpfdist example:CREATE EXTERNAL TABLE ext gpfdist table(num int)LOCATION AT 'TEXT' (DELIMITER ' ');Management utility (gpdbrestore) example:gpdbrestore -R[2620:0:170:610::11]:/path to/master/gpseg-1/db dumps/201108Note: Data Domain Boost does not support IPv6.New Functionality in Greenplum Database 4.26

Greenplum Database 4.2 Release NotesNew Performance Monitor TablesIn order to facilitate the collection of network statistical data, four new PerformanceMonitor tables have been added, they are: socket stats interface stats udp stats tcp statsgpfdist as a Windows ServiceIn previous versions of the Greenplum Loaders, gpfdist could not be run as aWindows service. The current Greenplum Loaders allow gpfdist to run as aWindows service.See “Greenplum Database Client Tool Packages and Performance Monitor” on page26 for detailed compatibility information for all the Greenplum client tool packages.See the Management Utility Reference Appendix of the Greenplum DatabaseAdministrator Guide 4.2 for information about running gpfdist as a WindowsService.Time-Based AuthenticationGreenplum Database enables the administrator to restrict access to the database basedon time. The restrictions are set on a role basis as part of the CREATE or ALTER ROLEcommand.Access can be restricted by day or by day and time. The constraints are removable,without deleting and recreating the role.Changed Features in Greenplum Database 4.2 New Reserved Word SQL Commands Management Utilities Server Configuration Parameters System CatalogsRefer to the Greenplum Database Administrator Guide 4.2 for details about usagechanges.New Reserved Worddecode will be converted into a reserved word in the next Service Pack release ofGreenplum Database, version 4.2.1.If you want to use decode as a function you must invoke it using the fullschema-qualified syntax or by enclosing the function name in quotes, "decode"().Greenplum recommends customers who wish to retain the use of the decode functionto take necessary steps to prepare for the change.Changed Features in Greenplum Database 4.27

Greenplum Database 4.2 Release NotesFor example: Once decode becomes a reserved word, if you have a functiondecode(a, b, c) you should invoke it either:without specifying its schema:SELECT “decode”(1, 2, 1);or, if specifying the schema for decode, you can omit the double quote, as follows:SELECT public.decode(1, 2, 1);SQL CommandsTable 1 SQL Command Changes in 4.2SQL CommandDescription of ChangeALTER ROLEprotocol 'gphdfs'Starting in Greenplum Database 4.2, gphdfs protocol privileges can no longer begranted using ALTER ROLE protocol 'gphdfs'.gphdfs privileges are now granted using:GRANT INSERT ON PROTOCOL gphdfs TO username;See the Grant Privileges for the HDFS Protocol section in the Greenplum Database4.2 Administrator Guide for more details.New syntax added to support new Time-Based Authentication feature.CREATE ROLEALTER ROLEManagement UtilitiesTable 2 Management Utility Changes in 4.2Utility NameDescription of ChangegppkgA new management utility has been created with which to install GreenplumDatabase extensions such as pgcrypto, PL/R, PL/Java, PL/Perl, PL/R, along withtheir dependencies, across an entire cluster.gpfdistIn previous versions of the Greenplum Loaders, gpfdist could not be run as aWindows service. The Greenplum Loaders version 4.1.1.3 and above allow gpfdistto run as a Windows service.gp dumpDeprecated.gp dump will be deprecated in the next major release of Greenplum Database.gpcrondumpThe following options were removed: -w, -i, -pNew Data Domain Boost support added. Note that some options work differently orare not supported when -ddboost is specified. See the Greenplum DatabaseAdministrator Guide 4.2 for details.gpinitsystem-m option removedServer Configuration Parameters New Parameters Parameters with Changed Defaults Removed ParametersChanged Features in Greenplum Database 4.28

Greenplum Database 4.2 Release NotesNew ParametersTable 3 New Server Configuration Parameters in 4.2DescriptionSetClassificationsonThis parameter enables plansthat can dynamically eliminatescanning of partitions.mastersessionreloadnumber oflostkeepalives2Maximum number of TCPkeepalive retransmits forFileRep connection.localsystemrestartgp filerep tcp keepalives idlenumber ofseconds1 minSeconds between issuing TCPkeepalives for FileRepconnection.localsystemrestartgp filerep tcp keepalives intervalnumber ofseconds30 secSeconds between TCPkeepalive retransmits forFileRep connection.localsystemrestartgp hadoop xnet versiongphd-xnet1.0.0.0 orgpmr-xnet1.0.0.0gphd-xnet1.0.0.0The installed Hadoop CrossConnect version.localsessionreloadgp hadoop homeSame valueasNULLThe location where Hadoop isinstalled in each segment.localsessionreloadParameter NameValueRangeDefaultValuegp dynamic partition pruningon/offgp filerep tcp keepalives countHADOOP HOMEParameters with Changed DefaultsTable 4 Changed Server Configuration Parameters in 4.2Parameter NameOld ValueNew ValueDescriptionfrom collapse limit1620Sets the FROM-list size beyond whichsubqueries are not collapsed.gp connections per thread51264Sets the number of client connectionshandled in each thread.gp fts probe timeout3 mins10 sMaximum time (in seconds) allowed forFTS to complete probing a segment.gp interconnect setup timeout20 s5 minTimeout (in minutes) on interconnectsetup that occurs at query startgp resqueue memory policyautoeager freeSets the policy for memory allocation ofqueries.gp segment connect timeout3 mins10 minMaximum time (in minutes) allowed fora new worker process to start or amirror to respond.join collapse limit1620Sets the FROM-list size beyond whichJOIN constructs are not collapsed.Changed Features in Greenplum Database 4.29

Greenplum Database 4.2 Release NotesTable 4 Changed Server Configuration Parameters in 4.2Parameter NameOld ValueNew ValueDescriptionmax appendonly tables204810000Maximum number of different(unrelated) append only tables that canparticipate in writing data concurrently.max connections25250 on Master,750 onSegmentsSets the maximum number ofconcurrent connections.max prepared transactions50250Sets the maximum number ofsimultaneously prepared transactions.Removed ParametersThe following server configuration parameters were deprecated in GreenplumDatabase 4.1 and have been removed in release 4.2.Table 5 Removed Server Configuration ParametersParameter Namemaintenance work memmax work memwork memSystem CatalogsRemoved Catalog ObjectsThe following catalog objects were deprecated in Greenplum Database 4.1 and havebeen removed in release 4.2.Table 6 Removed Catalog ObjectsObject TypeSchemaCatalog NameDescriptiontablepg catalogpg resqueueFunctionality moved to the pg resqueuecapabilitytableviewpg catalogpg resqueue statusUse the gp toolkit.gp resqueue status viewinsteadChanged Features in Greenplum Database 4.210

Greenplum Database 4.2 Release NotesResolved Issues in Greenplum Database 4.2The following tables list customer reported issues that are now resolved in GreenplumDatabase 4.2. Table 7, “Resolved Issues in 4.2 (not previously documented)”This table lists issues that were not previously documented in the release notes forany patch or feature release of Greenplum Database. For issues resolved inprevious releases, including patch releases to Greenplum Database 4.1 or 4.0.x,see the corresponding release notes, available from Powerlink. Table 8, “Resolved Issues in 4.2 (previously documented as Known Issues)”This table lists resolved issues that were documented as Known Issues in previousrelease notes.Table 7 Resolved Issues in 4.2 (not previously adersgpload: not-null constraint violationsgpload was throwing not-null constraint violations when using reusabletemporary tables.This issue has been resolved in this release.145454.2ManagementScriptsgpexpand failed with tablenames that contain spacesgpexpand did not handle spaces in tablenames for partition tables.This issue has been resolved in this release.142444.2QueryExecutionWindow function with EXCLUDE returned incorrect results.The EXCLUDE clause was not implemented and returned incorrect resultsrather than the appropriate error message.This issue has been resolved in this release.141244.2QueryExecutionMemory usage and performance issues with Nested Loop JoinThe performance of Nested Loop Join against an append-onlycolumn-oriented table was much slower than the performance of a NestedLoop Join on a heap table.This issue has been resolved in this release.140924.2QueryExecutionQueries with LIMIT and ORDER BY returned truncated resultsQueries with LIMIT and ORDER BY returned truncated results when thenumber of produced tuples (LIMIT predicate) is high relatively to the usedmemory (statement mem).This issue has been resolved in this release.140014.2Functions andLanguagesAuto-analyze did not work from within PL/pgSQL functionsIn a PL/pgSQL Function, after truncating a table and inserting into it, thetable statistics remained empty.This issue has been resolved in this release.138184.2BackupRestoregp dump failed during backupgpcrondump (and the underlying gp dump) failed when excluding (usingthe -T option) tables that contain sequences.This issue has been resolved in this release.DescriptionResolved Issues in Greenplum Database 4.211

Greenplum Database 4.2 Release NotesTable 7 Resolved Issues in 4.2 (not previously L/DML:PartitioningAdding unbound partition to a table failedAdding an unbound partition to the beginning of a partitioned table failed ifthe end value is next to the start of the first partition, but succeeded ifthere was a gap.This issue has been resolved in this release.137504.2DDL/DML:PartitioningAdding a partition failed if a partial index existedAdding a partition to a table failed with a relation . does not existerror if there was a partial index on the partitioned table. Without thepartial index (if there was no index or the index was not partial) thestatement succeeded.This issue has been resolved in this release.137184.2DispatchSession deadlockIn rare circumstances, a session deadlock occurred (only one sessionaffected) when the handling server process on the master tried to acquirea lock it already held.This issue has been resolved in this release.135924.2QueryExecutionERROR: seqserver socket is closedDescriptionWhen a sequence reached its limit (max or min), the sequence serverprocess would error out. This was closing the connection with the client,causing the client to print out a "socket closed" error. Next time the clientwould try to connect to the sequence server, seqserver would crashbecause of uncleaned state left from erroring out.This issue has been resolved in this release.135764.2QueryExecutionRunning a query with concurrency caused database lockdownIn rare circumstances, concurrently run queries encountered an internaldeadlock.This issue has been resolved in this release.134524.2Backup andrestorePostgresql reserved wordsUsing reserved postgresql keywords such as range in column names wascausing failures in backup / restore and analyze operations.This issue has been resolved in this release.133994.2LoadersPWX/gpload did not create table including defaultsWhen adding a column with default values into a table, the loader was notusing the INCLUDING DEFAULTS option, causing NULL to get loaded intothe column.This issue has been resolved in this release.133954.2DDL/DML:PartitioningALTER TABLE DROP PARTITION failedUnable to drop a partition when bitmap indexes were present.This issue has been resolved in this release.132734.2Client ToolsSELECT statement from an external hadoop table returned wrongnumber of rowsThis issue has been resolved in this release.132644.2QueryExecutionIncorrect data distribution when using window functionIn some circumstances, using a window function within a CREATE TABLEAS SELECT statement led to incorrect data distribution.This issue has been resolved in this release.Resolved Issues in Greenplum Database 4.212

Greenplum Database 4.2 Release NotesTable 7 Resolved Issues in 4.2 (not previously L ParserDifference in behavior of to date WITH to char built-in functionsIn some releases of Greenplum Database the to date and to charfunctions didn’t process leading spaces the same way.This issue has been resolved in this release.131904.2Functions andLanguagesTimezone type not working with WIT (Asia/Jakarta)For systems configured with specific timezones (like WIT), FTS failed toupdate catalog tables.This issue has been resolved in this release.131534.2Backup andRestoregpdbrestore -L failed131194.2OLAPDescriptiongpdbrestore –L failed for dump files with excessively long lines, due to alimitation of awk on the Solaris platform.This issue has been resolved in this release.ROLLUP query failedQueries with ROLLUP on a constant expression in the FROM clause failed.This issue has been resolved in this release.128034.2Catalog andMetadataThe pg catalog.pg max external files view was incorrectly basedon an obsolete tableThe system configuration table gp configuration was obsoleted inGreenplum Database 4.0 and superseded by new tablegp segment configuration. The view now refers to the correct table.This issue has been resolved in this release.127914.2DispatchSegment failures incorrectly reportedUnder certain circumstances, Greenplum Database incorrectly reportedsegment failures when the real issue was a network connection problem.127674.2gpmapreducegpmapreduce returned incorrect exit codegpmapreduce did not always return non-zero exit code on failure.This issue has been resolved in this release.126634.2QueryExecutionUnexpected internal error: Segment process received signalSIGSEGVIn rare circumstances, queries with more than one sort key in theexecution plan resulted in this error.This issue has been resolved in this release.126374.2QueryExecutionConstraint error caused query to failQueries failed when trying to coerce a value to a domain(COERCETODOMAIN)and the value violated the domain constraint.This issue has been resolved in this release.125314.2QueryExecutionSQL UDA extremely slowPerformance issue when using SQL user-defined aggregate with perfmonenabled. Performance monitor statistics were sent out for each rowprocessed by the aggregate, which slowed down the query.This issue has been resolved in this release.Resolved Issues in Greenplum Database 4.213

Greenplum Database 4.2 Release NotesTable 7 Resolved Issues in 4.2 (not previously eryOptimizerUnexpected cache lookup failure in EXPLAINIn rare circumstances, EXPLAIN would report: NOTICE: cache lookupfailed for attribute A of relation NNNNN and then misreport thequery plan. Queries triggering this issue had an index scan directly undera nested loop.This issue has been resolved in this release.121354.2Backup andRestoregpcrondump -q not suppressing output as expectedDescriptionWhen -q flag (no screen output) was set, gpcrondump did not suppressoutput for -k and -j flags as expected.This issue has been resolved in this release.118654.2Backup andRestoreTables with indexes slow to restoreThe restore operation on a table with an index was taking place after theindex was created, which slowed the restore process. The index is nowcreated after restoring the data. The CREATE INDEX DDL is saved in aseparate post data backup file.This issue has been resolved in this release.103414.2ManagementScriptsRead operation failed on external tableUnable to query gp skew coefficients on external tables.This issue has been resolved in this release.37594.2DDL and UtilityStatementsDropping a primary key or unique constraint did not cascadeAdding a primary key or unique constraint to a partitioned table wouldcorrectly cascade the constraint to its parts, but dropping the sameconstraint did not cascade.This issue has been resolved in this release.Now dropping such a constraint from a partitioned table, eliminates theconstraint from all parts. Note, however, that simple unique indexes (thosenot associated with constraints) behave as before.Resolved Issues in Greenplum Database 4.214

Greenplum Database 4.2 Release NotesThe following Resolved Issues were documented as Known Issues in earlier releasenotes.Table 8 Resolved Issues in 4.2 (previously documented as Known oading DataUsing TRUNCATE option with MERGE or UPDATE lost dataIf TRUNCATE was set to true and the mode was UPDATE in the YAML file, allexisting data was wiped out, and nothing updated when gpload wasexecuted. All data in the target table was lost, and the target table wasempty.If TRUNCATE was set to true and the mode was MERGE in the YAML file, allexisting records were wiped out when gpload was executed, and allrecords in the source file were inserted. The target table only containedthe new records, but all the previously existing data was lost.This issue has been resolved in this release.12917129164.2Loading DataTable names could not contain special characters in the YAML fileIf a table name contained a special character or space and the YAMLconfiguration file set REUSE TABLES to true, the gpload utility failed.This issue has been resolved in this release.12915129144.2Loading DataCount of data formatting errors was incorrectIf an error tolerance was set in a YAML file, errors were successfully writtento an error table, but the log and output messages incorrectly displayedzero errors, as shown below:Descriptiondata formatting errors 0This issue has been resolved in this release.142194.2Documentation Incorrect default batch size for gpexpand in documentationThe default batch size (-B option) for the gpexpand utility was incorrectlydocumented in the help file and the Greenplum Administrator Guide as 8.The actual default batch size i

of Data Domain Boost, it eliminates the need for NFS mount management. Greenplum backup using Data Domain Boost supports Data Domain Collection . Please consult the Data Domain Compatibility Guide to confirm Data Domain Boost host OS support for your Greenplum host platform. The Data Domain Compatibility Guide is available here: