PostreSQL.org

PostgreSQL Conference Europe 2019 in Milan, Italy, on October 15-18 is now open for registrations.

Early Bird discounted tickets are available until September 1st. However, as they are limited to 75 tickets, only a few are still left! Register quickly if you want to secure one of them!

The Tuesday training sessions have also been finalized, and are now available for registration. Six sessions in a mix of full and half day sessions are available:

• PostgreSQL Business Continuity
• The PostgreSQL Optimizer and indexes unleashed
• Migration to PostgreSQL
• PostgreSQL Security
• Implementing your first Postgres extension: from coding to distribution
• The "default" postgresql.config, step by step

See the website for more details!

Training sessions are available at an extra cost of €150 per half day. Attendees of training sessions will also receive a €90 discount on the regular conference fee. Seats to these sessions are limited to smaller groups, so make sure you register early!

Our call for papers is still open! If you have already submitted a talk, or are planning to submit one, we suggest you wait to register until you have received a confirmation if the talk was accepted or not. If your talk is accepted, attendance is of course free!

We are accepting proposals for talks in English. Each session will last 45 minutes, and may be on any topic related to PostgreSQL. The submission deadline is July 15th. Selected speakers will be notified before August 10th.

Please submit your proposals by going to the submission form and following the instructions.

The proposals will be considered by committee who will produce a schedule to be published nearer the conference date. The members of the committee are listed on the website linked above.

And finally, our Call for Sponsors is also still open. Our Platinum sponsor slots are sold out at this time, but we have free slots at all other levels remaining. Take your chance to present your services or products to the PostgreSQL community. All sponsorship levels also include one or more free entrance tickets, depending on level.

As usual, if you have any questions, don't hesitate to contact us at contact@pgconf.eu.

We look forward to seeing you in Milan in October!

PgBouncer 1.10.0 is out. The main change is improved support for TLS 1.3. See

https://pgbouncer.github.io/2019/07/pgbouncer-1-10-0

for more information, the detailed changelog, and download links.

PgBouncer is a lightweight connection pooler for PostgreSQL.

We are very glad to announce the 3.1.0 version of E-Maj.

E-Maj is a PostgreSQL extension which enables fine-grained write logging and time travel on subsets of the database.

This new version supports from 9.5 to v12 PostgreSQL versions.

With the version, the E-Maj administrator workload is reduced. The enabled application triggers are now automatically disabled during the E-Maj rollback operations (unless explicitely set as "not to be automatically disabled"). And there is no need to specify neither alternate log schemas nor E-Maj names prefix for tables with very long names.

Note that the log tables and other related objects have been renamed and moved to other log schemas. A new function provides a stable API to get the current log table name for a given application table.

This new version also includes 4 fixes.

The Emaj_web web client has been improved and now takes benefit from these features.

As usual, the full documentation is available on line. The core extension is available at pgxn.org or github.org.

The Emaj_web application is also available at github.org.

Have fun with E-Maj !

Oxford, United Kingdom - June 27, 2019

2ndQuadrant today announced the release of repmgr 4.4 This major release includes several important enhancements.

repmgr enhances and complements the built-in replication capabilities in PostgreSQL and is the most popular tool for PostgreSQL replication and failover management. repmgr makes it easy to set up and manage standby servers, check replication status, and perform administrative tasks such as switchover operations.The repmgrd daemon provides automatic failover capability to maximize database uptime, and can easily be integrated with other tools such as PgBouncer for high application availability.

repmgr 4.4 provides two key enhancements for automatic failover with repmgrd:

• On the primary node, repmgrd is now able to monitor standby connections and, if the number of nodes connected falls below a certain (configurable) value, execute a custom script. This provides additional possibilities for fencing an isolated primary node and/or taking other action if one or more standys become disconnected.
• In a failover situation, repmgrd nodes on the standbys of the failed primary are now able confirm among themselves that none can still see the primary before continuing with the failover.

repmgr 4.4 presents the following main client enhancements:

• --siblings-follow option added to repmgr standby promote
• --repmgrd-force-unpause option added to repmgr standby switchover
• Additional output when running repmgr standby switchover and repmgr standby promote with the --dry-run option
• repmgr cluster show and repmgr daemon status output has been made more consistent, and both now emit additional warnings about unexpected node status
• repmgr cluster show now displays each node's timeline ID (PostgreSQL 9.6 and later only)
• Safeguards added to prevent a standby being cloned from a witness server, and prevent a witness server being installed on the main replication cluster

Fixes for a number of issues have also been provided.

Additionally, the repmgr documentation has been converted to XML, which makes it possible to easily provide the documentation in other formats such as PDF.

For a detailed list of all changes, please read the complete release notes here.

Upgrading to this version is highly recommended for all repmgr users. Upgrade instructions are available here.

Packages and installation documentation are available via 2ndQuadrant’s public RPM and APT repositories from 2ndQuadrant here

Source files can be downloaded here and installation instructions are available here.

repmgr is distributed under GPL v3.

For more information, please send an email to info@2ndQuadrant.com

The JDBC project is proud to announce the latest version 42.2.6. This long overdue release includes the following:

Added

• CI tests with Java 11, and Java EA
• Support temporary replication slots in ReplicationCreateSlotBuilder PR 1306
• Support PostgreSQL 11, 12
• Return function (PostgreSQL 11) columns in PgDatabaseMetaData#getFunctionColumns
• Return information on create replication slot, now the snapshot_name is exported to allow a consistent snapshot in some uses cases. PR 1335

Fixed

• Fixed async copy performance (1ms per op) in SSL mode PR 1314
• Return Double.NaN for 'NaN'::numeric PR 1304
• Performance issue in PgDatabaseMetaData#getTypeInfo with lots of types in DB PR 1302
• PGCopyInputStream#read should cap values to [0, 255], -1 PR 1349
• Fixes LocalDateTime handling of BC dates PR 1388
• Release savepoints in autosave mode to prevent out of shared memory errors at the server side PR 1409
• Fix execution with big decimal in simple query mode. PR 1463
• Fix rounding for timestamps truncated to dates before 1970 PR 1502

Many people participated in this release and the project extends our gratitude to all who helped out. The full list can be seen at the bottom of the Changelog

The PostgreSQL Global Development Group has released an update to all supported versions of our database system, including 11.4, 10.9, 9.6.14, 9.5.18, and 9.4.23, as well as the second beta of PostgreSQL 12. This release fixes one security issue and over 25 bugs since the previous cumulative update in May.

This release is made outside of the normal update release schedule as the security vulnerability was determined to be critical enough to distribute the fix as quickly as possible. Users who are running PostgreSQL 10, PostgreSQL 11, or the PostgreSQL 12 beta should upgrade as soon as possible.

All other users should plan to apply this update at the next scheduled downtime.

Security Issues

This release closes one security vulnerability:

• CVE-2019-10164: Stack-based buffer overflow via setting a password

Versions affected: 10, 11, 12 beta.

An authenticated user could create a stack-based buffer overflow by changing their own password to a purpose-crafted value. In addition to the ability to crash the PostgreSQL server, this could be further exploited to execute arbitrary code as the PostgreSQL operating system account.

Additionally, a rogue server could send a specifically crafted message during the SCRAM authentication process and cause a libpq-enabled client to either crash or execute arbitrary code as the client's operating system account.

This issue is fixed by upgrading and restarting your PostgreSQL server as well as your libpq installations. All users running PostgreSQL 10, 11, and 12 beta are encouraged to upgrade as soon as possible.

The PostgreSQL Project thanks Alexander Lakhin for reporting this problem.

Bug Fixes and Improvements

This update also fixes over 25 bugs since the previous cumulative update in May. Some of these issues affect only version 11, but many affect all supported versions.

Some of these fixes include:

• Fix assorted errors in run-time partition pruning that could lead to wrong answers in queries on partitioned tables
• pg_dump now recreates table partitions using CREATE TABLE and ALTER TABLE .. ATTACH PARTITION rather than including PARTITION OF in the creation command
• Improve how initdb determines which system time zone to select if there are equivalent names for the time zone. Also explicitly prefer UTC over UCT
• Fix possible crash while trying to copy trigger definitions to a new partition
• Fix failure of ALTER TABLE .. ALTER COLUMN TYPE when the table has a partial exclusion constraint
• Fix failure of COMMENT command for comments on domains
• Several fixes related to aggregation
• Fix faulty generation of merge-append plans that could lead to "could not find pathkey item to sort" errors
• Fix failures on dump/restore where views contained queries with duplicate join names
• Fix conversion of JSON string literals to JSON-type output columns in json_to_record() and json_populate_record()
• Fix incorrect optimization of {1,1} quantifiers in regular expressions
• Fix issue for B-tree indexes during edge case failure involving columns covered with the INCLUDE clause, which manifests itself with errors during VACUUM. If you are affected by this issue, you will need to reindex the specific index
• Fix race condition in check to see whether a pre-existing shared memory segment is still in use by a conflicting postmaster
• Fix for the walreceiver process that avoids a crash or deadlock on shutdown
• Avoid possible hang in libpq if using SSL and OpenSSL's pending-data buffer contains an exact multiple of 256 bytes
• Fix ordering of GRANT commands emitted by pg_dump and pg_dumpall for databases and tablespaces
• Fix misleading error reports from reindexdb
• Ensure that vacuumdb returns correct status if an error occurs while using parallel jobs
• Fix contrib/auto_explain to not cause problems in parallel queries, which resulted in failures like "could not find key N in shm TOC"
• Account for possible data modifications by local BEFORE ROW UPDATE triggers in contrib/postgres_fdw
• On Windows, avoid failure when the database encoding is set to SQL_ASCII and we attempt to log a non-ASCII string

Updating

All PostgreSQL update releases are cumulative. As with other minor releases, users are not required to dump and reload their database or use pg_upgrade in order to apply this update release; you may simply shutdown PostgreSQL and update its binaries.

If one of your B-tree indexes that uses an INCLUDE clause is affected by the issue mentioned above, you will need to reindex any affected indexes. The issue manifests itself based on errors that occur during a VACUUM. You can read more about reindexing here:

https://www.postgresql.org/docs/current/sql-reindex.html

Users who have skipped one or more update releases may need to run additional, post-update steps; please see the release notes for earlier versions for details.

PostgreSQL 9.4 will stop receiving fixes on February 13, 2020. Please see our versioning policy for more information.

Beta Schedule

This includes the second beta release of version 12. The PostgreSQL Project will release additional betas as required for testing, followed by one or more release candidates, until the final release in late 2019. For further information please see the Beta Testing page.

Links

• Download
• Release Notes
• Security Page
• Versioning Policy
• Beta Testing Information
• PostgreSQL 12 Beta Release Notes
• PostgreSQL 12 Open Issues
• Submit a Bug
• Follow @postgresql on Twitter

PostgresCompare, the comparison and deployment tool for PostgreSQL, has been released.

PostgresCompare is a user friendly, cross platform tool that makes finding schema differences between environments simple and fast. Compare the definitions of tables, views, functions, procedures and more. PostgresCompare also saves you time by generating the SQL to deploy the changes.

Learn more or download your fully featured free trial at https://www.postgrescompare.com

The PL/R team is pleased to announce that PL/R version 8.4 is officially released. Much of the work was done by Mikhail Titov. Thank you for your contributions!

Notable Changes

• PostgreSQL 12 support. @davecramer
• Inline language handler and basic syntax checking validator.@mlt
• AppVeyor build artifacts & CodeCov coverage.@mlt

see the changelog for all of the changes

Windows releases can be found here

The Call For Papers for PostgresOpen 2019 is now open!

PostgresOpen, the community recognized conference series in the United States, is back! Our eighth annual conference will be held September 11th - 13th, 2019 in Orlando, Florida at the Rosen Centre Hotel.

If you are working with PostgreSQL, please go here and submit a talk!

Presentations can be on any topic related to PostgreSQL, including, but not limited to:

• case studies
• experiences
• tools and utilities
• migration stories
• existing features
• new feature development
• benchmarks
• performance tuning
• data science
• diversity & inclusion

Anyone and everyone in the PostgreSQL community - or outside of it! - is encouraged to submit a talk. Talks will be accepted up until June 30th, Anywhere on Earth (AoE), also known as: 2019-06-30 23:59:59-12:00.

Speakers will be notified by July 7th, 2019 AoE, with the schedule to be published once selected speakers have confirmed.

Become a Sponsor

The 2019 PostgresOpen Committee looks forward to bringing the best PostgreSQL presentations and tutorials from speakers around the world to Orlando, Florida - we're only able to do that with support from our great sponsors!

Please visit this page for the prospectus if your company is interested in giving back to the community through sponsoring PostgresOpen 2019.

PostgresOpen is an associated project of the United States PostgreSQL Association (PgUS). Any profits from the event go to PgUS (an IRS 501C3 non-profit) to fund future PostgresOpen events and support the PgUS mission. Please visit their official website and consider becoming a member to help promote the growth and education of PostgreSQL in the US!

Early Bird Registration

Early Bird registration for PostgresOpen 2019 will open on June 10th! If you want to get a head start on saving your room at the conference, you can find more information on our venue and conference group rate here.

Sustainability

This year for the first time, PostgresOpen has committed to providing an environmentally friendly conference experience, involving an organized approach to integrating sustainable materials and processes throughout the conference. Our goal is to introduce small, manageable changes working our way to more effective permanent changes to how our conference is managed; through these actions, we intend to contribute in a meaningful way towards a greener and more sustainable future.

More information regarding our initiatives and commitments to providing an environmentally friendly conference experience will be made available soon on our website.

Contact Information

We look forward to celebrating our 8th conference year with everyone in Orlando!

Any questions? Please contact us at contact@postgresopen.org.

Sarah Conway Schnurr

PostgresOpen Conference Organizer

https://2019.postgresopen.org/

Database .NET v28 is an innovative, powerful and intuitive multiple database management tool. (Full support for PostgreSQL 8/9/10/11/12)(Free, All-In-One, Portable, Single executable file and Multi-language.)

Major New features from version 26.1 to 28.0:

• PostgreSQL 12 support
• Added support for Computed Columns in PostgreSQL 12
• Added support for Backup and Restore in PostgreSQL 12
• Added support for PostgreSQL Foreign Servers
• Added Security Manager with Grant Manager for Members (PG)
• Added Duplicating Connections
• Added Float Query Mode
• Added Locking Query Tabs
• Added Changing the schema of query tabs
• Redesign Find In Grid
• Improved Graphical User Interface
• Improved Data Import and Data Export
• Improved Data Editor and Data Browser
• Fixed SSH: Secure Shell support (PG)
• Compatible with Microsoft Windows 10 (1903)
• ...and more

The new version is immediately available for download.

The PostgreSQL Global Development Group announces that the first beta release of PostgreSQL 12 is now available for download. This release contains previews of all features that will be available in the final release of PostgreSQL 12, though some details of the release could change before then.

In the spirit of the open source PostgreSQL community, we strongly encourage you to test the new features of PostgreSQL 12 in your database systems to help us eliminate any bugs or other issues that may exist. While we do not advise you to run PostgreSQL 12 Beta 1 in your production environments, we encourage you to find ways to run your typical application workloads against this beta release.

Your testing and feedback will help the community ensure that the PostgreSQL 12 release upholds our standards of providing a stable, reliable release of the world's most advanced open source relational database.

PostgreSQL 12 Features Highlights Indexing Performance, Functionality, and Management

PostgreSQL 12 improves the overall performance of the standard B-tree indexes with improvements to the space management of these indexes as well. These improvements also provide a reduction of index size for B-tree indexes that are frequently modified, in addition to a performance gain.

Additionally, PostgreSQL 12 adds the ability to rebuild indexes concurrently, which lets you perform a REINDEX operation without blocking any writes to the index. This feature should help with lengthy index rebuilds that could cause downtime when managing a PostgreSQL database in a production environment.

PostgreSQL 12 extends the abilities of several of the specialized indexing mechanisms. The ability to create covering indexes, i.e. the INCLUDE clause that was introduced in PostgreSQL 11, has now been added to GiST indexes. SP-GiST indexes now support the ability to perform K-nearest neighbor (K-NN) queries for data types that support the distance (<->) operation.

The amount of write-ahead log (WAL) overhead generated when creating a GiST, GIN, or SP-GiST index is also significantly reduced in PostgreSQL 12, which provides several benefits to the disk utilization of a PostgreSQL cluster and features such as continuous archiving and streaming replication.

Inlined WITH queries (Common table expressions)

Common table expressions (aka WITH queries) can now be automatically inlined in a query if they a) are not recursive, b) do not have any side-effects and c) are only referenced once in a later part of a query. This removes an "optimization fence" that has existed since the introduction of the WITH clause in PostgreSQL 8.4

If need be, you can force a WITH query to materialize using the MATERIALIZED clause, e.g.

WITH c AS MATERIALIZED ( SELECT * FROM a WHERE a.x % 4 = 0 ) SELECT * FROM c JOIN d ON d.y = a.x;

Partitioning

PostgreSQL 12 improves on the performance when processing tables with thousands of partitions for operations that only need to use a small number of partitions.

PostgreSQL 12 also provides improvements to the performance of both INSERT and COPY into a partitioned table. ATTACH PARTITION can now be performed without blocking concurrent queries on the partitioned table. Additionally, the ability to use foreign keys to reference partitioned tables is now permitted in PostgreSQL 12.

JSON path queries per SQL/JSON specification

PostgreSQL 12 now allows execution of JSON path queries per the SQL/JSON specification in the SQL:2016 standard. Similar to XPath expressions for XML, JSON path expressions let you evaluate a variety of arithmetic expressions and functions in addition to comparing values within JSON documents.

A subset of these expressions can be accelerated with GIN indexes, allowing the execution of highly performant lookups across sets of JSON data.

Collations

PostgreSQL 12 now supports case-insensitive and accent-insensitive comparisons for ICU provided collations, also known as "nondeterministic collations". When used, these collations can provide convenience for comparisons and sorts, but can also lead to a performance penalty as a collation may need to make additional checks on a string.

Most-common Value Extended Statistics

CREATE STATISTICS, introduced in PostgreSQL 10 to help collect more complex statistics over multiple columns to improve query planning, now supports most-common value statistics. This leads to improved query plans for distributions that are non-uniform.

Generated Columns

PostgreSQL 12 allows the creation of generated columns that compute their values with an expression using the contents of other columns. This feature provides stored generated columns, which are computed on inserts and updates and are saved on disk. Virtual generated columns, which are computed only when a column is read as part of a query, are not implemented yet.

Pluggable Table Storage Interface

PostgreSQL 12 introduces the pluggable table storage interface that allows for the creation and use of different methods for table storage. New access methods can be added to a PostgreSQL cluster using the CREATE ACCESS METHOD command and subsequently added to tables with the new USING clause on CREATE TABLE.

A table storage interface can be defined by creating a new table access method.

In PostgreSQL 12, the storage interface that is used by default is the heap access method, which is currently is the only built-in method.

Page Checksums

The pg_verify_checkums command has been renamed to pg_checksums and now supports the ability to enable and disable page checksums across a PostgreSQL cluster that is offline. Previously, page checksums could only be enabled during the initialization of a cluster with initdb.

Authentication & Connection Security

GSSAPI now supports client-side and server-side encryption and can be specified in the pg_hba.conf file using the hostgssenc and hostnogssenc record types. PostgreSQL 12 also allows for discovery of LDAP servers based on DNS SRV records if PostgreSQL was compiled with OpenLDAP.

Noted Behavior Changes

There are several changes introduced in PostgreSQL 12 that can affect the behavior as well as management of your ongoing operations. A few of these are noted below; for information about other changes, please review the "Migrating to Version 12" section of the release notes.

1. The recovery.conf configuration file is now merged into the main postgresql.conf file. PostgreSQL will not start if it detects that recovery.conf is present. To put PostgreSQL into a non-primary mode, you can use the recovery.signal and the standby.signal files.

You can read more about archive recovery here:

https://www.postgresql.org/docs/devel/runtime-config-wal.html#RUNTIME-CONFIG-WAL-ARCHIVE-RECOVERY

1. Just-in-Time (JIT) compilation is now enabled by default.

2. OIDs can no longer be added to user created tables using the WITH OIDs clause. Operations on tables that have columns that were created using WITH OIDS (i.e. columns named "OID") will need to be adjusted.

Running a SELECT * command on a system table will now also output the OID for the rows in the system table as well, instead of the old behavior which required the OID column to be specified explicitly.

Additional Features

Many other new features and improvements have been added to PostgreSQL 12, some of which may be as or more important to specific users than what is mentioned above. Please see the Release Notes for a complete list of new and changed features.

Testing for Bugs & Compatibility

The stability of each PostgreSQL release greatly depends on you, the community, to test the upcoming version with your workloads and testing tools in order to find bugs and regressions before the general availability of PostgreSQL 12. As this is a Beta, minor changes to database behaviors, feature details, and APIs are still possible. Your feedback and testing will help determine the final tweaks on the new features, so please test in the near future. The quality of user testing helps determine when we can make a final release.

A list of open issues is publicly available in the PostgreSQL wiki. You can report bugs using this form on the PostgreSQL website:

https://www.postgresql.org/account/submitbug/

Beta Schedule

This is the first beta release of version 12. The PostgreSQL Project will release additional betas as required for testing, followed by one or more release candidates, until the final release in late 2019. For further information please see the Beta Testing page.

Links

• Download
• Beta Testing Information
• PostgreSQL 12 Beta Release Notes
• PostgreSQL 12 Open Issues
• Submit a Bug

Oxford, United Kingdom - May 17, 2019

2ndQuadrant today announced the release of Barman version 2.8, introducing support for incremental backup in geo-redundancy. It also includes various performance enhancements and bug fixes.

Barman 2.8 deprecates support for PostgreSQL 9.3 and older versions, and declares official support for Python 3 - deprecating Python 2.

Barman (Backup and Recovery Manager) is an open source administration tool for managing backup and disaster recovery of PostgreSQL servers. It allows you to perform remote backups of multiple servers in business-critical environments and helps DBAs during the recovery phase.

This release includes performance enhancements and other changes listed below:

• Add support for reuse_backup in geo-redundancy for incremental backup copy in passive nodes
• Improve performance of rsync based copy by using strptime instead of the more generic dateutil.parser
• Add --test option to barman-wal-archive and barman-wal-restore to verify the connection with the Barman server
• Complain if backup_ options is not explicitly set, as the future default value will change from exclusive_backup to concurrent _backup when PostgreSQL 9.5 will be declared EOL by the PGDG
• Display additional settings in the show-server and diagnose commands: archive_ timeout, data_ checksums, hot_ standby, max_ wal _ senders, max _ replication_ slots and wal_ compression.
• Merge the barman-cli project in Barman

We strongly recommend upgrading to Barman v2.8 at the earliest opportunity available.

Upgrade Information Users of rsync backup method on a primary server should set backup_ options to either concurrent_ backup (recommended for PostgreSQL 9.6 or higher) or exclusive_ backup (current default) before upgrading to Barman 2.8.

A complete list of changes and bug fixes is available here.

Download and installation instructions are available here.

Source files are available for download here.

Barman is developed and maintained by 2ndQuadrant and distributed under GPL v3.

For more information, please send an email to info@2ndQuadrant.com

What is Pgpool-II?

Pgpool-II is a tool to add useful features to PostgreSQL, including:

• connection pooling
• load balancing
• automatic fail over and more.

Minor releases

Pgpool Global Development Group is pleased to announce the availability of following versions of Pgpool-II:

• 4.0.5
• 3.7.10
• 3.6.17
• 3.5.21
• 3.4.24

Release notes for versions prior 3.6 are no longer included in the source code.

Please take a look at release notes.

You can download the source code and RPMs.

Devart launched the next version of dbForge Studio for PostgreSQL v.2.1 with a new query optimization feature, Greenplum DB connectivity, and a bunch of other notable improvements.

Devart, a recognized vendor of professional database management software for developers and DBAs, released dbForge Studio for PostgreSQL v2.1. The updated version has a new connectivity option, a brand new feature for optimizing queries, and a redesigned Execute Large Script Wizard, as well as Database Explorer and Code Completion improvements.

The new edition includes the following enhancements:

Greenplum DB Support — dbForge Studio for PostgreSQL broadened its database connectivity. From now on, Greenplum, an open-source massively parallel DBMS, is officially in a list of supported databases, which greatly expands the app’s capabilities.

New Query Profiler — the new Query Profiler functionality helps to locate bottlenecks and visually optimize slow SQL queries. The profiling results are neatly displayed in several tabs, providing the following profiling stats and data: plan tree and diagram of query execution, list of the top operations taking place on the server, and an execution plan in XML format.

Database Explorer Improvements — after the update, Database Explorer can display such database objects and nodes as domains, rules & triggers for views, and indexes for materialized views.

Code Completion Improvements — SQL coding became even more convenient with these useful code completion features:

• Prompting the names of existing materialized views in the REFRESH MATERIALIZED VIEW;
• Context prompting of database objects in the SELECT queries’ FROM list;
• Syntax support for the VACUUM statement;
• Prompting keywords for Functions;
• Prompting keywords in SELECT queries.

Redesign of Execute Large Script Wizard — the wizard automatically selects the connection and database specified in the connection settings by default. The database list can also be refreshed.

dbForge Studio for PostgreSQL is a GUI tool for database development and management. This IDE for PostgreSQL allows users to create, develop and execute queries, edit and adjust the code to specific requirements in a convenient and user-friendly interface. For more information about dbForge Studio for PostgreSQL, please visit https://blog.devart.com/whats-new-dbforge-studio-pgsql-21.html

About Devart

Devart is one of the leading developers of database tools and administration software, ALM solutions, data providers for various database servers, data integration and backup solutions. The company also implements Web and Mobile development projects. For additional information about Devart, visit https://www.devart.com/

The PostgreSQL Global Development Group has released an update to all supported versions of our database system, including 11.3, 10.8, 9.6.13, 9.5.17, and 9.4.22. This release fixes two security issues in the PostgreSQL server, a security issue found in two of the PostgreSQL Windows installers, and over 60 bugs reported over the last three months.

Users who installed PostgreSQL using the Windows installers from EnterpriseDB and BigSQL respectively should upgrade as soon as possible. Similarly, users who are running any version of PostgreSQL 9.5, 9.6, 10, and 11 should also plan to upgrade as soon as possible.

All other users should plan to apply this update at the next scheduled downtime.

Security Issues

Four security vulnerabilities have been closed by this release:

• CVE-2019-10127: BigSQL Windows installer does not clear permissive ACL entries
• CVE-2019-10128: EnterpriseDB Windows installer does not clear permissive ACL entries

Due to both the EnterpriseDB and BigSQL Windows installers not locking down the permissions of the PostgreSQL binary installation directory and the data directory, an unprivileged Windows user account and an unprivileged PostgreSQL account could cause the PostgreSQL service account to execute arbitrary code.

This vulnerability is present in all supported versions of PostgreSQL for these installers, and possibly exists in older versions. Both sets of installers have fixed the permissions for these directories for both new and existing installations. If you have installed PostgreSQL on Windows using other methods, we advise that you check that your PostgreSQL binary directories are writable only to trusted users and that your data directories are only accessible to trusted users.

The PostgreSQL project thanks Conner Jones for reporting this problem.

• CVE-2019-10129: Memory disclosure in partition routing

Prior to this release, a user running PostgreSQL 11 can read arbitrary bytes of server memory by executing a purpose-crafted INSERT statement to a partitioned table.

• CVE-2019-10130: Selectivity estimators bypass row security policies

PostgreSQL maintains statistics for tables by sampling data available in columns; this data is consulted during the query planning process. Prior to this release, a user able to execute SQL queries with permissions to read a given column could craft a leaky operator that could read whatever data had been sampled from that column. If this happened to include values from rows that the user is forbidden to see by a row security policy, the user could effectively bypass the policy. This is fixed by only allowing a non-leakproof operator to use this data if there are no relevant row security policies for the table.

This issue is present in PostgreSQL 9.5, 9.6, 10, and 11. The PostgreSQL project thanks Dean Rasheed for reporting this problem.

Bug Fixes and Improvements

This update also fixes over 60 bugs that were reported in the last several months. Some of these issues affect only version 11, but many affect all supported versions.

Some of these fixes include:

• Several catalog corruption fixes, including one related to running ALTER TABLE on a partitioned table
• Several fixes for partitioning
• Avoid server crash when an error occurs while trying to persist a cursor query across a transaction commit
• Avoid O(N^2) performance issue when rolling back a transaction that created many tables
• Fix possible “could not access status of transaction” failures in txid_status()
• Fix updatable views to handle explicit DEFAULT items in INSERT .. VALUES statements where there are multiple VALUES rows
• Fix CREATE VIEW to allow zero-column views
• Add missing support for the CREATE TABLE IF NOT EXISTS .. AS EXECUTE .. statement
• Ensure that sub-SELECTs appearing in row-level-security policy expressions are executed with the correct user's permissions
• Accept XML documents as valid values of type xml when xmloption is set to content, as required by SQL:2006 and later
• Fix incompatibility of GIN-index WAL records that were introduced in 11.2, 10.7, 9.6.12, 9.5.16, and 9.4.21 that affected replica servers running these versions reading in changes to GIN indexes from primary servers of older versions
• Several memory leak fixes as well as fixes to management of dynamic shared memory
• Relax panics on fsync and sync_file_range failures for certain cases where a failure indicated "operation not supported"
• Several fixes to the query planner, several of which should lead to planning improvements
• Fix race condition in which a hot-standby postmaster could fail to shut down after receiving a smart-shutdown request
• Several fixes for SCRAM authentication
• Fix handling of lc_time settings that imply an encoding different from the database's encoding
• Create the current_logfiles file with the same permissions as other files in the server's data directory
• Several ecpg fixes
• Make pg_verify_checksums verify that the data directory it's pointed at is of the right PostgreSQL version
• Several fixes for contrib/postgres_fdw, including one for remote partitions where an UPDATE could lead to incorrect results or a crash
• Several Windows fixes

This update also contains tzdata release 2019a for DST law changes in Palestine and Metlakatla, plus historical corrections for Israel. Etc/UCT is now a backward-compatibility link to Etc/UTC, instead of being a separate zone that generates the abbreviation UCT, which nowadays is typically a typo. PostgreSQL will still accept UCT as an input zone abbreviation, but it won't output it.

Updating

All PostgreSQL update releases are cumulative. As with other minor releases, users are not required to dump and reload their database or use pg_upgrade in order to apply this update release; you may simply shutdown PostgreSQL and update its binaries.

Users who have skipped one or more update releases may need to run additional, post-update steps; please see the release notes for earlier versions for details.

PostgreSQL 9.4 will stop receiving fixes on February 13, 2020. Please see our versioning policy for more information.

Links

• Download
• Release Notes
• Security Page
• Versioning Policy
• Follow @postgresql on Twitter

The PostgreSQL Project is excited to announce that we have been accepted by Google to participate in the first Google Season of Docs (GSoD)!

This program's goal is to provide a framework for experienced technical writers and open source projects to work together towards the common goal of improving an open source project's documentation.

Between now and May 28th 2019, interested technical writers should discuss their project ideas with the project, after which proposals will be made through the GSoD system.

For more information about GSoD, please visit the GSoD website:

https://developers.google.com/season-of-docs/

For more information about working with the PostgreSQL project as part of GSoD, please visit our GSoD website:

https://wiki.postgresql.org/wiki/GSoD

PostgreSQL Conference Europe 2019 takes place in Milan, Italy, on October 15-18. Our Call for Papers is now open.

We are accepting proposals for talks in English. Each session will last 45 minutes, and may be on any topic related to PostgreSQL. The submission deadline is July 15th. Selected speakers will be notified before August 10th, 2019.

Please submit your proposals by going to 2019.pgconf.eu/callforpapers/ and following the instructions.

The proposals will be considered by committee who will produce a schedule to be published nearer the conference date. The members of the committee are listed on the website linked above.

All selected speakers will get free entry to the conference (excluding training sessions). We do not in general cover travel and accommodations for speakers, but may be able to do so in limited cases. If you require assistance with funding to be able to attend, please make a note of this in the submission notes field or contact us separately before the submission deadline.

And finally, our Call for Sponsorship is also open. Take your chance to present your services or products to the PostgreSQL community - or see it as a give back opportunity. All sponsorship levels also include one or more free entrance tickets, depending on the level. Please head to 2019.pgconf.eu/becomesponsor/ for more details.

Please note that the hotel room situation in Milan is tense, we advise you to book your room as early as possible. We are working with hotels around the venue to provide more options.

As usual, if you have any questions, don't hesitate to contact us at contact@pgconf.eu.

We look forward to seeing you in Milan in October!

The PostgreSQL Code of Conduct Committee has delivered their 2018 Annual Report summarizing the types of complaints received and actions taken since the Code of Conduct took effect in 2018 through the end of the year. All complaints and actions are anonymized to protect the identities of all parties involved.

You can view a copy of the report here: https://www.postgresql.org/about/policies/coc/reports/2018/

There is widespread mention in the media of a security vulnerability in PostgreSQL, registered as CVE-2019-9193. The PostgreSQL Security Team would like to emphasize that this is not a security vulnerability. We believe the CVE entry was filed in error. We have contacted the reporter to investigate the issue.

The COPY .. PROGRAM feature explicitly states that it can only be executed by database users that have been granted superuser privileges or the default role pg_execute_server_program. By design, this feature allows one who is granted superuser or pg_execute_server_program to perform actions as the operating system user the PostgreSQL server runs under (normally "postgres"). The default roles pg_read_server_files and pg_write_server_files that are mentioned in the CVE do not grant permission for a database user to use COPY .. PROGRAM.

By design, there exists no security boundary between a database superuser and the operating system user the server runs under. As such, by design the PostgreSQL server is not allowed to run as an operating system superuser (e.g. "root"). The features for COPY .. PROGRAM added in PostgreSQL 9.3 did not change any of the above, but added a new command within the same security boundaries that already existed.

We encourage all users of PostgreSQL to follow the best practice that is to never grant superuser access to remote or otherwise untrusted users. This is a standard security operating procedure that is followed in system administration and extends to database administration as well.

If you have more questions about this, we invite you to reach out to members of the community through one of our support resources:

https://www.postgresql.org/support/

Links

• COPY Documentation
• CVE-2019-9193
• Security Page
• Support
• Follow @postgresql on Twitter

New milestone PostgresDAC release is out! Now with PostgreSQL 11 and RAD Studio 10.3 Rio support

PostgresDAC is a direct access component suite for RAD Studio (Delphi and C++Builder)/FreePascal/Lazarus and PostgreSQL, EnterpriseDB, Amazon RDS, PostgresPro and Heroku Postgres.

Full changelog:

• [!] New installer introduced
• [!] RAD Studio 10.3 Tokyo (Delphi and C++ Builder) support introduced
• [+] TPSQQLQuery.BeforeExecSQL event added
• [-] "NUMERIC caused AV in Delphi 6" bug fixed

Download

You're welcome to download the PostgresDAC v3.5 right now at: http://microolap.com/products/connectivity/postgresdac/download/ or login to your private area on our site at http://microolap.com/my/downloads/

Feedback

Please don't hesitate to ask any questions or report bugs with our Support Ticketing system available at http://www.microolap.com/support/