PostgreSQL 12 Release Notes

Although PostgreSQL 12 has not yet been released, the development team has published its first draft release notes. According to the official introduction, this is a very important version. Let’s look at the changes that are worth paying attention to. For users migrating from an older version to PostgreSQL 12, you need to use  pg_dumpall  or  pg_upgrade  for dump/restore operations.

PostgreSQL 12 also contains a number of changes that may affect compatibility with older versions:

• Remove the special behavior of OID columns (Andres Freund, John Naylor)

  Previously, a normally-invisible OID column could be specified during table creation using WITH OIDS; that ability has been removed. Columns can still be explicitly specified as type OID. Operations on tables that have columns named OID (created using WITH OIDS) will need adjustment. The many system tables with such columns will now display those columns with SELECT * by default.

• Remove data types abstime, reltime, and tinterval (Andres Freund)
• Remove the timetravel extension (Andres Freund)
• Move recovery.conf settings into postgresql.conf (Fujii Masao, Simon Riggs, Abhijit Menon-Sen, Sergei Kornilov)

  recovery.conf is no longer used, and the server will not start if the file exists. recovery.signal and standby.signal are now used to switch into non-primary mode. trigger_file has been renamed to promote_trigger_file. The standby_mode setting has been removed.

• Do not allow multiple different recovery_target* specifications (Peter Eisentraut)

  Previously, multiple different recovery_target* variables could be specified, and the last one specified was honored. Now, only one can be specified, though the same one can be specified multiple times and the last specification is honored.

• Cause recovery to advance to the latest timeline by default (Peter Eisentraut)

  Specifically, recovery_target_time now defaults to latest. Previously, it defaulted to current.
• Refactor geometric functions and operators (Emre Hasegeli)

  This could lead to more accurate, but slightly different, results from previous releases.

• Restructure geometric types to handle NaN, underflow, overflow and division by zero more consistently (Emre Hasegeli)
• Improve behavior and error reporting for the line data type (Emre Hasegeli)
• Avoid performing unnecessary rounding of REAL and DOUBLE PRECISION values (Andrew Gierth)

  This dramatically speeds up processing of floating-point values but causes additional trailing digits to potentially be displayed. Users wishing to have output that is rounded to match the previous behavior can set extra_float_digits=0, which is no longer the default.
• Change XML functions like xpath() to never pretty-print their output (Tom Lane)

  Previously, this happened in some rare cases. ACCURATE? HOW TO GET PRETTY PRINT OUTPUT?

• Rename command-line tool pg_verify_checksums to pg_checksums (Michaël Paquier)
• Require specification of pg_restore -f - to send the dump contents to stdout (Euler Taveira)

  The previous behavior was considered confusing.

• Disallow psql‘s \pset format command from using non-unique abbreviations (Daniel Vérité)

  Previously, \pset format a chose aligned; it will now error.

• The maximum btree index length is now reduced by eight bytes; a REINDEX operation could potentially fail.
• Allow data type name to use non-C collations (Tom Lane, Daniel Vérité)

  It still uses the “C” collation by default, rather than defaulting to the database collation. Regular expression operations on data type name columns will now use the “C” collation; you will need to override the collation if you want to use locale-specific regular expression patterns, e.g., \w.

• Cause DROP IF EXISTS FUNCTION/PROCEDURE/AGGREGATE/ROUTINE to generate an error if no arguments are supplied and there are multiple matching objects (David Rowley)

  Also improve the error message in such cases.
• Remove obsolete pg_constraint.consrc column (Peter Eisentraut)
• Remove obsolete pg_attrdef.adsrc column (Peter Eisentraut)
• Remove the ability to disable dynamic shared memory (Kyotaro Horiguchi)

  Specifically, dynamic_shared_memory_type can no longer be set to none.

• More…