Version 11.1.7 v11

New features, enhancements, bug fixes, and other changes in EDB Postgres Advanced Server 11 include:

TypeCategoryDescription
Upstream MergeMerged with community PostgreSQL 11. See the community Release Notes for details.
FeatureGeneral FunctionalityAdvanced Server no longer creates the dbo system catalog in its databases.
FeatureGeneral FunctionalityAdvanced Server now supports data redaction. Data redaction is a technique for limiting the exposure of sensitive data to certain users. Data redaction results in the alteration of the displayed data to such users. This is accomplished by defining redaction policies for tables with sensitive data.
FeatureGeneral FunctionalityYou can use the edb_filter_log.redact_password_commands extension to instruct the server to redact stored passwords from the log file.
FeatureGeneral FunctionalityAdvanced Server now supports EDB wait states, which is a background worker that probes each running session at a regular interval collecting information such as the database connection, the logged in user, the running query, and the wait events.
FeatureGeneral FunctionalityAdvanced Server now permits an infinite number of tries for custom plan generation if you set the edb_custom_plan_tries parameter to -1.
FeatureGeneral FunctionalityThe output format of the version() function has been changed to appear more consistent with the PostgreSQL community version output.
FeatureGeneral FunctionalityAdvanced Server now supports SPL standalone procedure overloading. Note that this feature is not compatible with Oracle databases.
FeatureGeneral FunctionalityAdvanced Server now supports the PRAGMA AUTONOMOUS_TRANSACTION directive within any SPL block to provide the autonomous transaction capability.
FeatureGeneral FunctionalityAdvanced server now offers performance improvements to libpq dblink and the OCI.
EnhancementPartitioningAllow faster partition elimination during query processing; this speeds access to partitioned tables with many partitions.
EnhancementPartitioningAllow partition elimination during query execution.
EnhancementPartitioningAllow the creation of partitions based on hashing a key.
EnhancementPartitioningAllow updated rows to automatically move to new partitions based on the new row contents.
EnhancementPartitioningAllow partitioned tables to have a default partition.
EnhancementPartitioningAllow UNIQUE indexes on partitioned tables if the partition key guarantees uniqueness.
EnhancementPartitioningAllow indexes on a partitioned table to be automatically created in any child partitions. The new command ALTER INDEX ATTACH PARTITION allows indexes to be attached to partitions. This does not behave as a global index since the contents are private to each index. WARN WHEN USING AN EXISTING INDEX?
EnhancementPartitioningAllow foreign keys on partitioned tables.
EnhancementPartitioningAllow INSERT, UPDATE, and COPY on partitioned tables to properly route rows to foreign partitions. This is supported by postgres_fdw foreign tables.
EnhancementPartitioningAllow FOR EACH ROW triggers on partitioned tables.
EnhancementPartitioningAllow equality joins between partitioned tables with identically partitioned child tables to join the child tables directly.
EnhancementPartitioningPerform aggregation on each partition, and then merge the results.
EnhancementPartitioningAllow postgres_fdw to push down aggregates to foreign tables that are partitions.
EnhancementParallel QueriesAllow B-tree indexes to be built in parallel.
EnhancementParallel QueriesAllow hash joins to be performed in parallel using a shared hash table.
EnhancementParallel QueriesAllow UNION to run each SELECT in parallel if the individual SELECTs cannot be parallelized.
EnhancementParallel QueriesAllow partition scans to more efficiently use parallel workers.
EnhancementParallel QueriesAllow LIMIT to be passed to parallel workers.
EnhancementParallel QueriesAllow single-evaluation queriea (for example, WHERE clause aggregate queries) and functions in the target list to be parallelized.
EnhancementParallel QueriesAdd server option parallel_leader_participation to control if the leader executes subplans.
EnhancementParallel QueriesAllow parallelization of commands CREATE TABLE .. AS, SELECT INTO, and CREATE MATERIALIZED VIEW.
EnhancementParallel QueriesImprove performance of sequential scans with many parallel workers.
EnhancementParallel QueriesAdd reporting of parallel worker sort activity to EXPLAIN.
EnhancementIndexesAllow indexes to INCLUDE columns that are not part of the unique constraint but are available for index-only scans. This is also useful for including columns that don't have B-tree support.
EnhancementIndexesRemember the highest B-tree index page to optimize future monotonically increasing index additions.
EnhancementIndexesAllow entire hash index pages to be scanned. Previously for each hash index entry, we need to refind the scan position within the page. This cuts down on lock/unlock traffic.
EnhancementIndexesAdd predicate locking for hash, GiST and GIN indexes.
EnhancementIndexesAllow heap-only-tuple (HOT) updates for expression indexes when the values of the expressions are unchanged.
EnhancementSP-GistAdd TEXT prefix operator ^@ which is supported by SP-GiST.
EnhancementSP-GistAllow polygons to be indexed with SP-GiST.
EnhancementSP-GistAllow SP-GiST to use lossy representation of leaf keys.
EnhancementOptimizerImprove the selection of the optimizer statistics' most-common-values.
EnhancementOptimizerImprove selectivity estimates for >= and <= when the constants are not common values.
EnhancementOptimizerOptimize var = var to var IS NOT NULL where equivalent.
EnhancementOptimizerImprove row count optimizer estimates for EXISTS and NOT EXISTS queries.
EnhancementOptimizerAdd optimizer selectivity costs for HAVING clauses.
EnhancementPerformanceAdd Just-in-Time (JIT) compilation of some parts of query plans to improve execution speed.
EnhancementPerformanceAllow bitmap scans to perform index-only scans when possible.
EnhancementPerformanceUpdate the free space map during vacuum.
EnhancementPerformanceAllow vacuum to avoid unnecessary index scans.
EnhancementPerformanceImprove performance of committing multiple concurrent transactions.
EnhancementPerformanceReduce memory usage for queries using set-returning functions in their target lists.
EnhancementPerformanceAllow postgres_fdw to push UPDATEs and DELETEs using joins to foreign servers.
EnhancementMonitoringShow memory usage in log_statement_stats, log_parser_stats, log_planner_stats, log_executor_stats.
EnhancementMonitoringAdd pg_stat_activity.backend_type now shows the type of background worker.
EnhancementMonitoringAdd bgw_type to the background worker C structure. This is displayed to the user in pg_stat_activity.backend_type and ps output.
EnhancementMonitoringHave log_autovacuum_min_duration log skipped tables that are concurrently being dropped.
EnhancementInformation SchemaAdd information_schema columns related to table constraints and triggers.
EnhancementAuthenticationAllow the server to specify more complex LDAP specifications in search+bind mode.
EnhancementAuthenticationAllow LDAP authentication to use ldaps.
EnhancementAuthenticationImprove LDAP logging of errors.
EnhancementPermissionsAdd default roles which control file system access.
EnhancementPermissionsAllow access to file system functions to be controlled by GRANT/REVOKE permissions, rather than superuser checks.
EnhancementPermissionsUse GRANT/REVOKE to control access to lo_import() and lo_export().
EnhancementPermissionsCompile-time option ALLOW_DANGEROUS_LO_FUNCTIONS has been removed.
EnhancementPermissionsUse viewowner not session owner when preventing non-password access to postgres_fdw tables.
EnhancementPermissionsFix invalid locking permission check in SELECT FOR UPDATE on views.
EnhancementServer ConfigurationAdd server setting ssl_passphrase_command to allow supplying of the passphrase for SSL key files.
EnhancementServer ConfigurationAdd storage parameter toast_tuple_target to control the minimum length before TOAST storage will be considered for new rows.
EnhancementServer ConfigurationAllow server options related to memory and file sizes to be specified as a number of bytes.
EnhancementWrite-Ahead Log (WAL)Allow the WAL file size to be set via initdb.
EnhancementWrite-Ahead Log (WAL)Do not retain WAL that spans two checkpoints.
EnhancementWrite-Ahead Log (WAL)Fill the unused portion of force-switched WAL segment files with zeros for improved compressibility.
EnhancementBase Backup and Streaming ReplicationReplicate TRUNCATE activity when using logical replication.
EnhancementBase Backup and Streaming ReplicationPass prepared transaction information to logical replication subscribers.
EnhancementBase Backup and Streaming ReplicationExclude unlogged tables, temporary tables, and pg_internal.init files from streaming base backups.
EnhancementBase Backup and Streaming ReplicationAllow heap pages checksums to be checked during streaming base backup.
EnhancementBase Backup and Streaming ReplicationAllow replication slots to be advanced programmatically rather than be consumed by subscribers.
EnhancementBase Backup and Streaming ReplicationAdd timeline information to the backup_label file.
EnhancementBase Backup and Streaming ReplicationAdd host and port connection information to the pg_stat_wal_receiver system view.
EnhancementWindow FunctionsAdd window function features to complete SQL:2011 compliance.
EnhancementUtility CommandsAllow ALTER TABLE to add a column with a non-null default without a table rewrite.
EnhancementUtility CommandsAllow views to be locked by locking the underlying tables.
EnhancementUtility CommandsAllow ALTER INDEX to set statistics-gathering targets for expression indexes.
EnhancementUtility CommandsIn psql, \d+ now shows the statistics target for indexes.
EnhancementUtility CommandsAllow multiple tables to be specified in one VACUUM or ANALYZE command. Also, if any table mentioned in VACUUM uses a column list, then the ANALYZE keyword must be supplied; previously, ANALYZE was implied in such cases.
EnhancementUtility CommandsAdd parenthesized options syntax to ANALYZE. This is similar to the syntax supported by VACUUM.
EnhancementUtility CommandsAdd CREATE AGGREGATE option to specify the behavior of the aggregate finalization function.
EnhancementData TypesAllow the creation of arrays of domains.
EnhancementData TypesSupport domains over composite types.
EnhancementData TypesAdd casts from jsonb scalars to numeric and boolean data types.
EnhancementFunctionsAdd SHA-2 family of hash functions; specifically, sha224(), sha256(), sha384(), sha512() were added.
EnhancementFunctionsAdd support for 64-bit non-cryptographic hash functions.
EnhancementFunctionsAllow to_char() and to_timestamp() to specify the time zone's hours and minutes from UTC.
EnhancementFunctionsImprove the speed of aggregate computations.
EnhancementFunctionsAdd text search function websearch_to_tsquery() that supports a query syntax similar to that used by web search engines.
EnhancementFunctionsAdd function json(b)_to_tsvector() to create a text search query for matching JSON/JSONB values.
EnhancementServer-Side LanguagesAdd SQL-level procedures, which can start and commit their own transactions. They are created with the new CREATE PROCEDURE command and invoked via CALL. The new ALTER/DROP ROUTINE commands allows altering/dropping of procedures, functions, and aggregates.
EnhancementServer-Side LanguagesAdd transaction control to PL/pgSQL, PL/Perl, PL/Python, PL/Tcl, and SPI server-side languages.
EnhancementServer-Side LanguagesAdd the ability to define PL/pgSQL record types as not null, constant, or with initial values.
EnhancementServer-Side LanguagesAllow PL/pgSQL to handle changes to composite types (e.g. record, row) that happen between the first and later function executions in the same session. Previously such circumstances generated errors.
EnhancementServer-Side LanguagesAdd extension jsonb_plpython to transform JSONB to/from PL/Python types.
EnhancementServer-Side LanguagesAdd extension jsonb_plperl to transform JSONB to/from PL/Perl types.
EnhancementClient InterfacesChange libpq to disable compression by default.
EnhancementClient InterfacesAdd DO CONTINUE action to the ECPG WHENEVER statement.
EnhancementClient InterfacesAdd ecpg mode to enable Oracle Pro*C handling of char arrays. This mode is enabled with -C.
EnhancementpsqlAdd psql command \gdesc to display the column names and types of the query output.
EnhancementpsqlAdd psql variables to report query activity and errors.
EnhancementpsqlAllow psql to test for the existence of a variable.
EnhancementpsqlAdd PSQL_PAGER to control psql's pager.
EnhancementpsqlHave psql \d+ always show the partition information.
EnhancementpsqlHave psql report the proper user name before the password prompt.
EnhancementpsqlAllow quit and exit to exit psql when used in an empty buffer.
EnhancementpsqlHave psql hint at using control-D when \q is entered alone on a line but ignored.
EnhancementpsqlImprove tab-completion for ALTER INDEX RESET/SET.
EnhancementpsqlAdd infrastructure to allow psql to customize tab completion queries based on the server version. Previously tab completion queries could fail.
EnhancementpgbenchAdd pgbench expressions support for NULLs, booleans, and some functions and operators.
EnhancementpgbenchAdd \if conditional support to pgbench.
EnhancementpgbenchAllow the use of non-ASCII characters in pgbench variable names.
EnhancementpgbenchAdd pgbench option --init-steps to control the initialization steps performed.
EnhancementpgbenchAdd an approximated Zipfian-distributed random generator to pgbench.
EnhancementpgbenchAllow the random seed to be set in pgbench.
EnhancementpgbenchAllow pgbench to do exponentiation with pow() and power().
EnhancementpgbenchAdd hashing functions to pgbench.
EnhancementpgbenchMake pgbench statistics more accurate when using --latency-limit and --rate.
EnhancementServer ApplicationsAdd an option to pg_basebackup that creates a named replication slot. The option --create-slot creates the named replication slot (--slot) when the WAL streaming method (--wal-method=stream) is used.
EnhancementServer ApplicationsAllow initdb to set group read access to the data directory.
EnhancementServer ApplicationsAdd pg_verify_checksums tool to verify database checksums while offline.
EnhancementServer ApplicationsAllow pg_resetwal to change the WAL segment size via --wal-segsize.
EnhancementServer ApplicationsAdd long options to pg_resetwal and pg_controldata.
EnhancementServer ApplicationsAdd pg_receivewal option --no-sync to prevent synchronous WAL writes, for testing.
EnhancementServer ApplicationsAdd pg_receivewal option --endpos to specify when WAL receiving should stop.
EnhancementServer ApplicationsAllow pg_ctl to send the SIGKILL signal to processes.
EnhancementServer ApplicationsReduce the number of files copied by pg_rewind.
EnhancementServer ApplicationsPrevent pg_rewind from running as root.
Enhancementpg_dump, pg_dumpall, pg_restoreAdd pg_dumpall option --encoding to control encoding.
Enhancementpg_dump, pg_dumpall, pg_restoreAdd pg_dump option --load-via-partition-root to force loading of data into the partition's root table rather than the original partitions.
Enhancementpg_dump, pg_dumpall, pg_restoreAdd an option to suppress dumping and restoring comments.
EnhancementSource CodeAdd support for large pages on Windows.
EnhancementSource CodeAdd support for ARMv8 hardware CRC calculations.
EnhancementSource CodeConvert documentation to DocBook XML.
EnhancementSource CodeUse stdbool.h to define type bool on platforms where it's suitable.
EnhancementSource CodeAdd ability to use channel binding when using SCRAM authentication.
EnhancementSource CodeOverhaul the way system tables are defined for bootstrap use.
EnhancementSource CodeAllow background workers to attach to databases that normally disallow connections.
EnhancementSource CodeSpeed up lookups of built-in function names matching OIDs.
EnhancementSource CodeSpeed up construction of query results.
EnhancementSource CodeImprove access speed to system caches.
EnhancementSource CodeAdd a generational memory allocator which is optimized for serial allocation/deallocation.
EnhancementSource CodeMake the computation of system column pg_class.reltuples consistent.
EnhancementSource CodeUpdate to use perltidy version.
EnhancementAdditional ModulesAllow extension pg_prewarm to restore the previous shared buffer contents on startup.
EnhancementAdditional ModulesAdd pg_trgm function strict_word_similarity() to compute the similarity of whole words.
EnhancementAdditional ModulesAllow creation of indexes on citext extension columns that can be used by LIKE comparisons.
EnhancementAdditional ModulesAllow btree_gin to index bool, bpchar, name and uuid data types.
EnhancementAdditional ModulesAllow cube and seg extensions using GiST indexes to perform index-only scans.
EnhancementAdditional ModulesAllow retrieval of negative cube coordinates using the ~> operator.
EnhancementAdditional ModulesAdd Vietnamese letter detection to the unaccent extension.
EnhancementAdditional ModulesEnhance amcheck to check that each heap tuple has an index entry.
EnhancementAdditional ModulesHave adminpack use the new default file system access roles.
EnhancementAdditional ModulesIncrease pg_stat_statement's query id to 64 bits.
EnhancementAdditional ModulesInstall errcodes.txt to provide access to the error codes reported by PostgreSQL.
EnhancementAdditional ModulesPrevent extensions from creating custom server variables that take a quoted list of values.
EnhancementAdditional ModulesRemoved contrib/start-scripts/osx.
EnhancementAdditional ModulesRemoved the chkpass extension.

Deprecated Features

The PL/Java package is deprecated in EDB Postgres Advanced Server 11 and will be unavailable in EDB Postgres Advanced Server 12.

Advanced Server no longer supports the Infinite Cache feature. All related components have been removed such as the functions, scripts, configuration parameters, and columns from statistical tables and views.

Component Certification

The following components are included in the EDB Postgres Advanced Server v12 release:

  • Procedural Language Packs – PL/Perl 5.26, PL/Python 3.7, PL/TCL 8.6
  • CloneSchema 1.10
  • EDB_Wait_States 1.0 (formerly known as Edb-Modules)
  • Parallel Clone 1.5
  • pgAgent 4.15
  • Slony 2.2.8
  • Connectors JDBC 42.2.8, ODBC 12.00.0000 .NET 4.0.10.1, OCI 11.0.3.1
  • pgAdmin 4.15
  • pgBouncer 1.12.0
  • pgPool-II & pgPool-IIExtensions 4.0.6
  • MTK 53.0.0
  • EDBPlus 38.0.0
  • PostGIS-JDBC 2.2.1