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 declared as type oid . Operations on tables that have columns created using WITH OIDS will need adjustment. The system catalogs that previously had hidden oid columns now have ordinary oid columns. Hence, SELECT * will now output those columns, whereas previously they would be displayed only if selected explicitly.

Remove data types abstime , reltime , and tinterval (Andres Freund) These are obsoleted by SQL-standard types such as timestamp .

Remove the timetravel extension (Andres Freund)

Move recovery.conf settings into postgresql.conf (Masao Fujii, Simon Riggs, Abhijit Menon-Sen, Sergei Kornilov) recovery.conf is no longer used, and the server will not start if that file exists. recovery.signal and standby.signal files are now used to switch into non-primary mode. The trigger_file setting has been renamed to promote_trigger_file. The standby_mode setting has been removed.

Do not allow multiple conflicting recovery_target * specifications (Peter Eisentraut) Specifically, only allow one of recovery_target, recovery_target_lsn, recovery_target_name, recovery_target_time, and recovery_target_xid. Previously, multiple different instances of these parameters could be specified, and the last one 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_timeline now defaults to latest . Previously, it defaulted to current .

Refactor code for geometric functions and operators (Emre Hasegeli) This could lead to more accurate, but slightly different, results compared to previous releases. Notably, cases involving NaN, underflow, overflow, and division by zero are handled more consistently than before.

Improve performance by using a new algorithm for output of real and double precision values (Andrew Gierth) Previously, displayed floating-point values were rounded to 6 (for real ) or 15 (for double precision ) digits by default, adjusted by the value of extra_float_digits. Now, whenever extra_float_digits is more than zero (as it now is by default), only the minimum number of digits required to preserve the exact binary value are output. The behavior is the same as before when extra_float_digits is set to zero or less. Also, formatting of floating-point exponents is now uniform across platforms: two digits are used unless three are necessary. In previous releases, Windows builds always printed three digits.

random() and setseed() now behave uniformly across platforms (Tom Lane) The sequence of random() values generated following a setseed() call with a particular seed value is likely to be different now than before. However, it will also be repeatable, which was not previously guaranteed because of interference from other uses of random numbers inside the server. The SQL random() function now has its own private per-session state to forestall that.

Change SQL-style substring() to have standard-compliant greediness behavior (Tom Lane) In cases where the pattern can be matched in more than one way, the initial sub-pattern is now treated as matching the least possible amount of text rather than the greatest; for example, a pattern such as %#"aa*#"% now selects the first group of a 's from the input, not the last group.

Do not pretty-print the result of xpath() or the XMLTABLE construct (Tom Lane) In some cases, these functions would insert extra whitespace (newlines and/or spaces) in nodeset values. This is undesirable since depending on usage, the whitespace might be considered semantically significant.

Rename command-line tool pg_verify_checksums to pg_checksums (Michaël Paquier)

In pg_restore, require specification of -f - to send the dump contents to standard output (Euler Taveira) Previously, this happened by default if no destination was specified, but that was deemed to be unfriendly.

Disallow non-unique abbreviations in psql's \pset format command (Daniel Vérité) Previously, for example, \pset format a chose aligned ; it will now fail since that could equally well mean asciidoc .

In new btree indexes, the maximum index entry length is reduced by eight bytes, to improve handling of duplicate entries (Peter Geoghegan) This means that a REINDEX operation on an index pg_upgrade'd from a previous release could potentially fail.

Cause DROP IF EXISTS FUNCTION / PROCEDURE / AGGREGATE / ROUTINE to generate an error if no argument list is supplied and there are multiple matching objects (David Rowley) Also improve the error message in such cases.

Split the pg_statistic_ext catalog into two catalogs, and add the pg_stats_ext view of it (Dean Rasheed, Tomas Vondra) This change supports hiding potentially-sensitive statistics data from unprivileged users.

Remove obsolete pg_constraint . consrc column (Peter Eisentraut) This column has been deprecated for a long time, because it did not update in response to other catalog changes (such as column renamings). The recommended way to get a text version of a check constraint's expression from pg_constraint is pg_get_expr(conbin, conrelid) . pg_get_constraintdef() is also a useful alternative.

Remove obsolete pg_attrdef . adsrc column (Peter Eisentraut) This column has been deprecated for a long time, because it did not update in response to other catalog changes (such as column renamings). The recommended way to get a text version of a default-value expression from pg_attrdef is pg_get_expr(adbin, adrelid) .

Mark table columns of type name as having “C” collation by default (Tom Lane, Daniel Vérité) The comparison operators for data type name can now use any collation, rather than always using “C” collation. To preserve the previous semantics of queries, columns of type name are now explicitly marked as having “C” collation. A side effect of this is that regular-expression operators on name columns will now use the “C” collation by default, not the database collation, to determine the behavior of locale-dependent regular expression patterns (such as \w ). If you want non-C behavior for a regular expression on a name column, attach an explicit COLLATE clause. (For user-defined name columns, another possibility is to specify a different collation at table creation time; but that just moves the non-backwards-compatibility to the comparison operators.)

Treat object-name columns in the information_schema views as being of type name , not varchar (Tom Lane) Per the SQL standard, object-name columns in the information_schema views are declared as being of domain type sql_identifier . In PostgreSQL, the underlying catalog columns are really of type name . This change makes sql_identifier be a domain over name , rather than varchar as before. This eliminates a semantic mismatch in comparison and sorting behavior, which can greatly improve the performance of queries on information_schema views that restrict an object-name column. Note however that inequality restrictions, for example SELECT ... FROM information_schema.tables WHERE table_name < 'foo'; will now use “C”-locale comparison semantics by default, rather than the database's default collation as before. Sorting on these columns will also follow “C” ordering rules. The previous behavior (and inefficiency) can be enforced by adding a COLLATE "default" clause.

Remove the ability to disable dynamic shared memory (Kyotaro Horiguchi) Specifically, dynamic_shared_memory_type can no longer be set to none .