E.29. Release 9.3

E.29.2.1. Server Settings

• Rename replication_timeout to wal_sender_timeout (Amit Kapila)

  This setting controls the WAL sender timeout.

• Require superuser privileges to set commit_delay because it can now potentially delay other sessions (Simon Riggs)

• Allow in-memory sorts to use their full memory allocation (Jeff Janes)

  Users who have set work_mem based on the previous behavior may need to revisit that setting.

E.29.2.2. Other

• Throw an error if a tuple to be updated or deleted has already been updated or deleted by a BEFORE trigger (Kevin Grittner)

  Formerly, the originally-intended update was silently skipped, resulting in logical inconsistency since the trigger might have propagated data to other places based on the intended update. Now an error is thrown to prevent the inconsistent results from being committed. If this change affects your application, the best solution is usually to move the data-propagation actions to an AFTER trigger.

  This error will also be thrown if a query invokes a volatile function that modifies rows that are later modified by the query itself. Such cases likewise previously resulted in silently skipping updates.

• Change multicolumn ON UPDATE SET NULL/SET DEFAULT foreign key actions to affect all columns of the constraint, not just those changed in the UPDATE (Tom Lane)

  Previously, we would set only those referencing columns that correspond to referenced columns that were changed by the UPDATE. This was what was required by SQL-92, but more recent editions of the SQL standard specify the new behavior.

• Force cached plans to be replanned if the search_path changes (Tom Lane)

  Previously, cached plans already generated in the current session were not redone if the query was re-executed with a new search_path setting, resulting in surprising behavior.

• Fix to_number() to properly handle a period used as a thousands separator (Tom Lane)

  Previously, a period was considered to be a decimal point even when the locale says it isn't and the D format code is used to specify use of the locale-specific decimal point. This resulted in wrong answers if FM format was also used.

• Fix STRICT non-set-returning functions that have set-returning functions in their arguments to properly return null rows (Tom Lane)

  A null value passed to the strict function should result in a null output, but instead, that output row was suppressed entirely.

• Store WAL in a continuous stream, rather than skipping the last 16MB segment every 4GB (Heikki Linnakangas)

  Previously, WAL files with names ending in FF were not used because of this skipping. If you have WAL backup or restore scripts that took this behavior into account, they will need to be adjusted.

• In pg_constraint.confmatchtype, store the default foreign key match type (non-FULL, non-PARTIAL) as s for "simple" (Tom Lane)

  Previously this case was represented by u for "unspecified".

E.29.3.1. Server

E.29.3.2. Replication and Recovery

• Allow a streaming replication standby to follow a timeline switch (Heikki Linnakangas)

  This allows streaming standby servers to receive WAL data from a slave newly promoted to master status. Previously, other standbys would require a resync to begin following the new master.

• Add SQL functions pg_is_in_backup() and pg_backup_start_time() (Gilles Darold)

  These functions report the status of base backups.

• Improve performance of streaming log shipping with synchronous_commit disabled (Andres Freund)

• Allow much faster promotion of a streaming standby to primary (Simon Riggs, Kyotaro Horiguchi)

• Add the last checkpoint's redo location to pg_controldata's output (Fujii Masao)

  This information is useful for determining which WAL files are needed for restore.

• Allow tools like pg_receivexlog to run on computers with different architectures (Heikki Linnakangas)

  WAL files can still only be replayed on servers with the same architecture as the primary; but they can now be transmitted to and stored on machines of any architecture, since the streaming replication protocol is now machine-independent.

• Make pg_basebackup --write-recovery-conf output a minimal recovery.conf file (Zoltán Böszörményi, Magnus Hagander)

  This simplifies setting up a standby server.

• Allow pg_receivexlog and pg_basebackup --xlog-method to handle streaming timeline switches (Heikki Linnakangas)

• Add wal_receiver_timeout parameter to control the WAL receiver's timeout (Amit Kapila)

  This allows more rapid detection of connection failure.

• Change the WAL record format to allow splitting the record header across pages (Heikki Linnakangas)

  The new format is slightly more compact, and is more efficient to write.

E.29.3.3. Queries

• Implement SQL-standard LATERAL option for FROM-clause subqueries and function calls (Tom Lane)

  This feature allows subqueries and functions in FROM to reference columns from other tables in the FROM clause. The LATERAL keyword is optional for functions.

• Add support for piping COPY and psql \copy data to/from an external program (Etsuro Fujita)

• Allow a multirow VALUES clause in a rule to reference OLD/NEW (Tom Lane)

E.29.3.4. Object Manipulation

• Add support for event triggers (Dimitri Fontaine, Robert Haas, Álvaro Herrera)

  This allows server-side functions written in event-enabled languages to be called when DDL commands are run.

• Allow foreign data wrappers to support writes (inserts/updates/deletes) on foreign tables (KaiGai Kohei)

• Add CREATE SCHEMA ... IF NOT EXISTS clause (Fabrízio de Royes Mello)

• Make REASSIGN OWNED also change ownership of shared objects (Álvaro Herrera)

• Make CREATE AGGREGATE complain if the given initial value string is not valid input for the transition datatype (Tom Lane)

• Suppress CREATE TABLE's messages about implicit index and sequence creation (Robert Haas)

  These messages now appear at DEBUG1 verbosity, so that they will not be shown by default.

• Allow DROP TABLE IF EXISTS to succeed when a non-existent schema is specified in the table name (Bruce Momjian)

  Previously, it threw an error if the schema did not exist.

• Provide clients with constraint violation details as separate fields (Pavel Stehule)

  This allows clients to retrieve table, column, data type, or constraint name error details. Previously such information had to be extracted from error strings. Client library support is required to access these fields.

E.29.3.5. Data Types

• Increase the maximum size of large objects from 2GB to 4TB (Nozomi Anzai, Yugo Nagata)

  This change includes adding 64-bit-capable large object access functions, both in the server and in libpq.

• Allow text timezone designations, e.g. "America/Chicago", in the "T" field of ISO-format timestamptz input (Bruce Momjian)

E.29.3.6. Functions

• Add array_remove() and array_replace() functions (Marco Nenciarini, Gabriele Bartolini)

• Allow concat() and format() to properly expand VARIADIC-labeled arguments (Pavel Stehule)

• Improve format() to provide field width and left/right alignment options (Pavel Stehule)

• Make to_char(), to_date(), and to_timestamp() handle negative (BC) century values properly (Bruce Momjian)

  Previously the behavior was either wrong or inconsistent with positive/AD handling, e.g. with the format mask "IYYY-IW-DY".

• Make to_date() and to_timestamp() return proper results when mixing ISO and Gregorian week/day designations (Bruce Momjian)

• Cause pg_get_viewdef() to start a new line by default after each SELECT target list entry and FROM entry (Marko Tiikkaja)

  This reduces line length in view printing, for instance in pg_dump output.

• Fix map_sql_value_to_xml_value() to print values of domain types the same way their base type would be printed (Pavel Stehule)

  There are special formatting rules for certain built-in types such as boolean; these rules now also apply to domains over these types.

E.29.3.7. Server-Side Languages

E.29.3.8. Server Programming Interface (SPI)

• Prevent leakage of SPI tuple tables during subtransaction abort (Tom Lane)

  At the end of any failed subtransaction, the core SPI code now releases any SPI tuple tables that were created during that subtransaction. This avoids the need for SPI-using code to keep track of such tuple tables and release them manually in error-recovery code. Failure to do so caused a number of transaction-lifespan memory leakage issues in PL/pgSQL and perhaps other SPI clients. SPI_freetuptable() now protects itself against multiple freeing requests, so any existing code that did take care to clean up shouldn't be broken by this change.

• Allow SPI functions to access the number of rows processed by COPY (Pavel Stehule)

E.29.3.9. Client Applications

• Add command-line utility pg_isready to check if the server is ready to accept connections (Phil Sorber)

• Support multiple --table arguments for pg_restore, clusterdb, reindexdb, and vacuumdb (Josh Kupershmidt)

  This is similar to the way pg_dump's --table option works.

• Add --dbname option to pg_dumpall, pg_basebackup, and pg_receivexlog to allow specifying a connection string (Amit Kapila)

• Add libpq function PQconninfo() to return connection information (Zoltán Böszörményi, Magnus Hagander)

E.29.3.10. Source Code

• Add infrastructure to allow plug-in background worker processes (Álvaro Herrera)

• Create a centralized timeout API (Zoltán Böszörményi)

• Create libpgcommon and move pg_malloc() and other functions there (Álvaro Herrera, Andres Freund)

  This allows libpgport to be used solely for portability-related code.

• Add support for list links embedded in larger structs (Andres Freund)

• Use SA_RESTART for all signals, including SIGALRM (Tom Lane)

• Ensure that the correct text domain is used when translating errcontext() messages (Heikki Linnakangas)

• Standardize naming of client-side memory allocation functions (Tom Lane)

• Provide support for "static assertions" that will fail at compile time if some compile-time-constant condition is not met (Andres Freund, Tom Lane)

• Support Assert() in client-side code (Andrew Dunstan)

• Add decoration to inform the C compiler that some ereport() and elog() calls do not return (Peter Eisentraut, Andres Freund, Tom Lane, Heikki Linnakangas)

• Allow options to be passed to the regression test output comparison utility via PG_REGRESS_DIFF_OPTS (Peter Eisentraut)

• Add isolation tests for CREATE INDEX CONCURRENTLY (Abhijit Menon-Sen)

• Remove typedefs for int2/int4 as they are better represented as int16/int32 (Peter Eisentraut)

• Fix install-strip on Mac OS X (Peter Eisentraut)

• Remove configure flag --disable-shared, as it is no longer supported (Bruce Momjian)

• Rewrite pgindent in Perl (Andrew Dunstan)

• Provide Emacs macro to set Perl formatting to match PostgreSQL's perltidy settings (Peter Eisentraut)

• Run tool to check the keyword list whenever the backend grammar is changed (Tom Lane)

• Change the way UESCAPE is lexed, to significantly reduce the size of the lexer tables (Heikki Linnakangas)

• Centralize flex and bison make rules (Peter Eisentraut)

  This is useful for pgxs authors.

• Change many internal backend functions to return object OIDs rather than void (Dimitri Fontaine)

  This is useful for event triggers.

• Invent pre-commit/pre-prepare/pre-subcommit events for transaction callbacks (Tom Lane)

  Loadable modules that use transaction callbacks might need modification to handle these new event types.

• Add function pg_identify_object() to produce a machine-readable description of a database object (Álvaro Herrera)

• Add post-ALTER-object server hooks (KaiGai Kohei)

• Implement a generic binary heap and use it for Merge-Append operations (Abhijit Menon-Sen)

• Provide a tool to help detect timezone abbreviation changes when updating the src/timezone/data files (Tom Lane)

• Add pkg-config support for libpq and ecpg libraries (Peter Eisentraut)

• Remove src/tools/backend, now that the content is on the PostgreSQL wiki (Bruce Momjian)

• Split out WAL reading as an independent facility (Heikki Linnakangas, Andres Freund)

• Use a 64-bit integer to represent WAL positions (XLogRecPtr) instead of two 32-bit integers (Heikki Linnakangas)

  Generally, tools that need to read the WAL format will need to be adjusted.

• Allow PL/Python to support platform-specific include directories (Peter Eisentraut)

• Allow PL/Python on OS X to build against custom versions of Python (Peter Eisentraut)

E.29.3.11. Additional Modules

• Add a Postgres foreign data wrapper contrib module to allow access to other Postgres servers (Shigeru Hanada)

  This foreign data wrapper supports writes.

• Add pg_xlogdump contrib program (Andres Freund)

• Add support for indexing of regular-expression searches in pg_trgm (Alexander Korotkov)

• Improve pg_trgm's handling of multibyte characters (Tom Lane)

  On a platform that does not have the wcstombs() or towlower() library functions, this could result in an incompatible change in the contents of pg_trgm indexes for non-ASCII data. In such cases, REINDEX those indexes to ensure correct search results.

• Add a pgstattuple function to report the size of the pending-insertions list of a GIN index (Fujii Masao)

• Make oid2name, pgbench, and vacuumlo set fallback_application_name (Amit Kapila)

• Improve output of pg_test_timing (Bruce Momjian)

• Improve output of pg_test_fsync (Peter Geoghegan)

• Create a dedicated foreign data wrapper, with its own option validator function, for dblink (Shigeru Hanada)

  When using this FDW to define the target of a dblink connection, instead of using a hard-wired list of connection options, the underlying libpq library is consulted to see what connection options it supports.

E.29.3.12. Documentation

• Allow EPUB-format documentation to be created (Peter Eisentraut)

• Update FreeBSD kernel configuration documentation (Brad Davis)

• Improve WINDOW function documentation (Bruce Momjian, Florian Pflug)

• Add instructions for setting up the documentation tool chain on macOS (Peter Eisentraut)

• Improve commit_delay documentation (Peter Geoghegan)