1 <!-- doc/src/sgml/release-9.4.sgml -->
2 <!-- See header comment in release.sgml about typical markup -->
4 <sect1 id="release-9-4">
5 <title>Release 9.4</title>
8 <title>Release Date</title>
9 <simpara>2014-??-??</simpara>
10 <simpara>Current as of 2014-05-03</simpara>
14 <title>Overview</title>
17 Major enhancements in <productname>PostgreSQL</> 9.4 include:
20 <!-- This list duplicates items below, but without authors or details-->
26 Allow materialized views to be refreshed without blocking reads
32 Logical change-set extraction allows database
33 changes to be optionally recorded in <emphasis>logical</> format
39 Allow <link linkend="bgworker">background workers</link>
40 to be dynamically registered, started and terminated
46 Add structured (non-text) data type (<link
47 linkend="datatype-json"><type>JSONB</></link>) for storing
54 Add <acronym>SQL</>-level command <link
55 linkend="SQL-ALTERSYSTEM"><command>ALTER SYSTEM</></link> command
56 to edit the <filename>postgresql.conf</> configuration file
63 The above items are explained in more detail in the sections below.
70 <title>Migration to Version 9.4</title>
73 A dump/restore using <link
74 linkend="APP-PG-DUMPALL"><application>pg_dumpall</></link>, or use
75 of <link linkend="pgupgrade"><application>pg_upgrade</></link>, is
76 required for those wishing to migrate data from any previous release.
80 Version 9.4 contains a number of changes that may affect compatibility
81 with previous releases. Observe the following incompatibilities:
88 Cause consecutive whitespace in <link
89 linkend="functions-formatting-table"><function>to_timestamp()</></link>
90 and <function>to_date()</> format strings to consume a corresponding
91 number of characters in the input string (whitespace or not), then
92 conditionally additional adjacent whitespace if not in <literal>FX</>
97 Previously consecutive whitespace characters in a non-<literal>FX</>
98 format string behaved like a single whitespace character and consumed
99 all adjacent whitespace in the input string. For example, previously
100 format string space-space-space would consume only the first space in
101 ' 12', while it will not consume all three characters.
107 Tighten checks for multi-dimensional <link
108 linkend="arrays">array</link> input (Bruce Momjian)
112 Previously an input array string that started with a single-element
113 array dimension could later contain multi-dimensional segments,
114 e.g. <literal>'{{1}, {2,3}}'::int[]</>.
120 Change empty arrays returned by <application>intarray</> to be
121 zero-dimensional arrays (Bruce Momjian)
125 Previously empty arrays were returned as one-dimensional empty arrays
126 whose text representation looked the same as zero-dimensional arrays
127 (<literal>{}</>). <application>intarray</>'s behavior in this area
128 now matches the built-in array operators.
135 linkend="xfunc-sql-variadic-functions"><literal>VARIADIC</></link>
136 function arguments are now disallowed (Pavel Stehule)
140 Such arguments must be cast to an array data type.
146 <link linkend="SQL-SHOW"><command>SHOW TIME ZONE</></link> now
147 outputs constant time zone offsets in <acronym>POSIX</>-style zone
152 Previously it was returned in <link
153 linkend="datatype-interval-output"><type>INTERVAL</></link> format.
154 The new format can be passed to <command>SET TIME ZONE</>.
160 Rename <link linkend="SQL-EXPLAIN"><command>EXPLAIN
161 ANALYZE</></link>'s "total runtime" output to "execution time"
166 This was required now that planning time is also reported.
173 linkend="textsearch-functions-table"><function>ts_rank_cd()</></link>
174 to ignore stripped lexemes (Alex Hill)
178 Previously, stripped lexemes got a default location and could be
179 considered if mixed with non-stripped lexemes.
186 linkend="ddl-constraints-check-constraints"><literal>CHECK</></link>
187 constraints from referencing system columns, except
188 <structname>tableoid</> (Amit Kapila)
192 Restoring such check constraints will lead to restore errors.
198 Use the last specified <link
199 linkend="recovery-target"><varname>recovery_target</></link> if
200 multiple are specified (Heikki Linnakangas)
206 Remove system column <link
207 linkend="catalog-pg-class"><structname>pg_class.reltoastidxid</></link>
214 Remove support for native <application>krb5</> authentication
219 The proper way to use <application>Kerberos</> authentication is
220 with <acronym>GSSAPI</>.
227 linkend="libpq-pqconnectdbparams"><function>PQconnectdbParams()</></link>
229 linkend="libpq-pqpingparams"><function>PQpingParams()</></link>
230 functions process zero-length strings as defaults (Adrian
235 Previously, these functions treated zero-length string values as
236 defaults only in some cases.
242 Remove system column <link
243 linkend="catalog-pg-rewrite"><structname>pg_rewrite.ev_attr</></link>
248 Per-column rules have not been supported since
249 <application>PostgreSQL</> 7.3.
255 <link linkend="pgupgrade"><application>Pg_upgrade</></link> now
256 uses <option>-U</> to specify the user name (Bruce Momjian)
260 Previously, <option>-u</> was used to specify the user name.
261 Also spell the long option as <option>--username</>, again for
262 consistency with other tools.
271 <title>Changes</title>
274 Below you will find a detailed account of the changes between
275 <productname>PostgreSQL</productname> 9.4 and the previous major
280 <title>Server</title>
286 Have <link linkend="SQL-VACUUM"><command>VACUUM</></link> properly
287 report dead but not removable rows to the statistics collector
292 Previously these were reported as live rows.
297 Allow <link linkend="bgworker">background workers</link> to be
298 dynamically registered, started and terminated (Robert Haas)
302 <function>worker_spi_launch()</> in <application>worker_spi</>
303 shows an example of its use.
309 Allow dynamic allocation of shared memory segments (Robert Haas,
314 This is illustrated in <link
315 linkend="test-shm-mq"><application>test_shm_mq</></link>.
321 Improve <acronym>SSL</> renegotiation handling (Álvaro
328 During immediate shutdown, send uncatchable termination signals
329 to child processes that have not already shutdown (MauMau,
330 Álvaro Herrera)
334 This reduces the likelihood of orphaned child processes after
335 <link linkend="app-postmaster"><application>postmaster</></link>
342 Improve randomness of the database system identifier (Tom Lane)
349 <title>Indexes</title>
355 Improve speed of multi-key <link
356 linkend="GIN"><acronym>GIN</></link> lookups (Alexander Korotkov,
363 Reduce <link linkend="GIN"><acronym>GIN</></link> index size
364 (Alexander Korotkov, Heikki Linnakangas)
368 Indexes upgraded via <link
369 linkend="pgupgrade"><application>pg_upgrade</></link> will work
370 fine but will use the old larger <acronym>GIN</> format; <link
371 linkend="SQL-REINDEX"><command>REINDEX</></link> will recreate
372 the index in the new format.
378 Add <link linkend="GiST"><acronym>GiST</></link> index support
379 for <link linkend="datatype-inet"><type>INET</></link> and
380 <link linkend="datatype-cidr"><type>CIDR</></link> data types
385 Such indexes improve <link
386 linkend="cidr-inet-operators-table">subnet</link> and supernet
387 lookups and ordering comparisons.
393 Fix race condition in B-tree page deletion (Heikki Linnakangas)
399 Make the handling of interrupted B-tree page splits more robust
409 <title>General Performance</title>
415 Allow multiple backends to simultaneously insert into <link
416 linkend="wal"><acronym>WAL</></link> buffers (Heikki Linnakangas)
420 This improves parallel write performance.
426 Conditionally write only the modified portion of updated rows to
427 <link linkend="wal"><acronym>WAL</></link> (Amit Kapila)
433 Improve performance of <link
434 linkend="syntax-window-functions"><literal>WINDOW</></link>
435 aggregate functions (David Rowley, Florian Pflug, Tom Lane)
441 Improve speed of aggregates which use <link
442 linkend="datatype-numeric"><type>NUMERIC</></link>s (Hadi
447 These include <function>SUM()</>, <function>AVG()</>, <link
448 linkend="functions-aggregate-statistics-table"><function>STDDEV()</></link>,
449 and <function>VARIANCE()</>.
455 <link linkend="vacuum-for-wraparound">Freeze</link>
456 tuples when tables are written with <link
457 linkend="SQL-CLUSTER"><command>CLUSTER</></link> or <link
458 linkend="SQL-VACUUM"><command>VACUUM FULL</></link> (Robert Haas,
463 This avoids the need to freeze the tuples in the future.
469 Improve speed of <link linkend="SQL-COPY"><command>COPY</></link>
470 with <literal>DEFAULT</> <link
471 linkend="functions-sequence-table"><function>nextval()</></link>
472 columns (Simon Riggs)
478 Improve speed of accesessing many different <link
479 linkend="SQL-CREATESEQUENCE">sequences</link> in the same session
486 Allow sorting and B-tree <link linkend="SQL-CREATEINDEX">index
487 builds</link> to use over four gigabytes of memory (Noah Misch)
493 Reduce memory allocated by <link
494 linkend="SQL-DO"><literal>DO</></link> blocks (Tom Lane)
500 Have the optimizer be more aggressive in creating restrictions
501 from mixed <literal>AND</>/<literal>OR</> clauses (Tom Lane)
507 Auto-resize the catalog cache (Heikki Linnakangas)
511 This reduces memory consumption for backends accessing only a few
512 tables, and improves performance for backend accessing many tables.
521 <title>Monitoring</title>
527 Expose the estimation of number of changed tuples since last <link
528 linkend="vacuum-for-statistics">analyze</link> (Mark Kirkwood)
532 This appears in <link
533 linkend="pg-stat-all-tables-view"><structname>pg_stat_all_tables.n_mod_since_analyze</></link>.
540 linkend="pg-stat-archiver-view"><structname>pg_stat_archiver</></link>
541 system view to report <link linkend="wal"><acronym>WAL</></link>
542 archiver activity (Gabriele Bartolini)
548 Add <structname>xid</> and <link
549 linkend="ddl-system-columns"><structname>xmin</></link>
550 to system views <link
551 linkend="pg-stat-activity-view"><structname>pg_stat_activity</></link>
553 linkend="pg-stat-replication-view"><structname>pg_stat_replication</></link>
563 <title><acronym>SSL</></title>
569 Add support for <acronym>SSL</> <acronym>ECDH</> key exchange
574 Such keys are faster and have improved security
575 over previous options. New variable <link
576 linkend="guc-ssl-ecdh-curve"><varname>ssl_ecdh_curve</></link>
577 controls the curve that is used.
583 Improve the default <link
584 linkend="guc-ssl-ciphers"><varname>ssl_ciphers</></link> ciphers
591 Allow the server to specify the preferred <acronym>SSL</> cipher
596 This is controlled by the new configuration parameter <link
597 linkend="guc-ssl-prefer-server-ciphers"><varname>ssl_prefer_server_ciphers</></link>.
604 linkend="guc-log-connections"><varname>log_connections</></link>
605 show <acronym>SSL</> encryption information (Andreas Kunert)
614 <title>Server Settings</title>
620 Add <acronym>SQL</>-level command <link
621 linkend="SQL-ALTERSYSTEM"><command>ALTER SYSTEM</></link> command
622 to edit the <filename>postgresql.conf</> configuration file
627 Previously <filename>postgresql.conf</> could only be edited at
628 the file system level.
634 Add <link linkend="guc-huge-pages"><varname>huge_pages</></link>
635 configuration parameter to use huge memory pages on Linux
636 (Christian Kruse, Richard Poole, Abhijit Menon-Sen)
640 This can improve performance on large memory systems.
646 Show <acronym>PID</>s of lock holders and
647 waiters and improve relation information in <link
648 linkend="guc-log-lock-waits"><varname>log_lock_waits</></link>
649 log messages (Christian Kruse)
656 linkend="guc-autovacuum-work-mem"><varname>autovacuum_work_mem</></link>
657 to control the amount of memory used by autovacuum workers
665 linkend="guc-max-worker-processes"><varname>max_worker_processes</></link>
666 to limit the number of background workers (Robert Haas)
670 This is helpful in configuring the standby server to have the
671 required same number of worker processes as the primary.
677 Add configuration parameter <link
678 linkend="guc-wal-log-hints"><varname>wal_log_hints</></link>
679 to enable logging of hint bits (Sawada Masahiko)
683 Hint bits are not normally logged, except when checksums are
684 enabled. This is useful for tools like <application>pg_rewind</>.
690 Allow printf-style space padding to be specified in <link
691 linkend="guc-log-line-prefix"><varname>log_line_prefix</></link>
698 Add superuser-only <link
699 linkend="guc-session-preload-libraries"><varname>session_preload_libraries</></link>
700 configuration parameter to load libraries at session start
705 Such libraries are auto-<link
706 linkend="SQL-LOAD"><command>LOAD</></link>'ed, unlike <link
707 linkend="guc-local-preload-libraries"><varname>local_preload_libraries</></link>.
713 Reduce server logging level when loading shared libraries (Peter
718 The previous level was <literal>LOG</>, which was too verbose
719 for per-session libraries.
726 linkend="guc-effective-cache-size"><varname>effective_cache_size</></link>
727 to be four-times shared buffers (Bruce Momjian, Tom Lane)
734 linkend="guc-work-mem"><varname>work_mem</></link> and <link
735 linkend="guc-maintenance-work-mem"><varname>maintenance_work_mem</></link>
736 defaults by four-times (Bruce Momjian)
740 The new defaults are 4MB and 64MB respectively.
746 Allow terabyte units to be specified for configuration variable
753 Have Windows <acronym>ASCII</>-encoded databases and server process
754 (e.g. <link linkend="app-postmaster">postmaster) emit messages
755 in the <envar>LC_CTYPE</></link>-defined language (Alexander Law,
760 Previously these messages were output using the Windows
761 <acronym>ANSI</> code page.
772 <title>Replication and Recovery</title>
779 linkend="recovery-config"><filename>recovery.conf</></link>
781 linkend="min-recovery-apply-delay"><varname>min_recovery_apply_delay</></link>
782 to force delayed replication (Robert Haas, Fabrízio de
783 Royes Mello, Simon Riggs)
787 This is useful for delaying replaying of user errors on standby
795 linkend="recovery-target"><varname>recovery_target</></link>
796 option <option>immediate</> option to replay
797 <link linkend="wal"><acronym>WAL</></link> stop
798 recovery when a consistent state is reached, i.e. <link
799 linkend="functions-admin-backup-table"><function>pg_stop_backup()</></link>
800 (MauMau, Heikki Linnakangas)
806 Improve recovery target processing (Heikki Linnakangas)
810 The timestamp reported by <link
811 linkend="functions-recovery-info-table"><function>pg_last_xact_replay_timestamp()</></link>
812 now shows information about committed records, not commits being
813 replayed. Recovering to restore points now replay the restore
814 point, rather than stop just before the restore point.
821 linkend="functions-admin-backup-table"><function>pg_switch_xlog()</></link>
822 now clears the trailing unused space in the <acronym>WAL</> file
827 This improves the compression ratio for <acronym>WAL</> files.
833 Add <link linkend="streaming-replication-slots">replication
834 slots</link> to report the <acronym>WAL</> activity on streaming
835 standbys (Andres Freund, Robert Haas)
839 Replication slots allow preservation of resources like
840 <acronym>WAL</> files on the primary that are needed by standby
847 Reduce spinlock contention during <acronym>WAL</> replay (Heikki
854 Report failure return codes from <link
855 linkend="archive-recovery-settings">external recovery commands</>
862 Write <acronym>WAL</> records of running transactions more
863 frequently (Andres Freund)
867 This allows standby servers to start faster and cleanup resources
875 <title><link linkend="logicaldecoding">Logical Change-Set Extraction</></title>
878 Logical change-set extraction allows database
879 changes to be optionally recorded in <emphasis>logical</> format
880 in the <link linkend="wal"><acronym>WAL</></link>. This format can
881 be easily processed by external tools. In previous releases, only
882 binary changes were recorded in the <acronym>WAL</>. To implement
883 this feature, the following changes were made:
890 Add new <option>logical</> <link
891 linkend="guc-wal-level"><varname>wal_level</></link> to enable
892 logical change-set encoding in <acronym>WAL</> (Andres Freund)
896 This interacts with <link
897 linkend="catalog-pg-class"><literal>REPLICA IDENTITY</></link>.
904 linkend="app-pgreceivexlog"><application>pg_recvlogical</></link>
905 to receive data logical decoding data (Andres Freund)
912 linkend="test-decoding"><application>test_decoding</></link>
913 module to illustrate logical decoding at the <acronym>SQL</>
914 level (Andres Freund)
920 Allow logical decoding via the walsender interface ? (Andres
927 Add table-level parameter <link
928 linkend="catalog-pg-class"><literal>REPLICA IDENTITY</></link>
929 to control logical replication (Andres Freund)
935 Add relation option <link
936 linkend="SQL-CREATETABLE-storage-parameters"><option>user_catalog_table</></link>
937 to identify user-created tables involved in logical change-set
938 encoding (Andres Freund)
949 <title>Queries</title>
955 Add <link linkend="queries-tablefunctions"><literal>ROWS
956 FROM</></link> syntax to allow horizontal concatenation of
957 <literal>FROM</>-clause set-returning functions (Andrew Gierth)
963 Add <link linkend="queries-tablefunctions"><literal>WITH
964 ORDINALITY</></link> which numbers rows returned from
965 <literal>FROM</>-clause functions (Andrew Gierth, David Fetter)
969 This is particularly useful for functions like
970 <function>unnest()</>.
976 Allow <link linkend="SQL-SELECT"><command>SELECT</></link> with
977 an empty target list (Tom Lane)
981 This was added for consistency, and so querying tables with no
982 columns would not produce an error.
991 <title>Utility Commands</title>
997 Add <link linkend="SQL-DISCARD"><command>DISCARD
998 SEQUENCES</></link> command to discard cached sequence information
999 (Fabrízio de Royes Mello, Robert Haas)
1003 <command>DISCARD ALL</> will now also discard such information.
1009 Allow quoted strings matching the null string to be converted
1010 to NULL in <link linkend="SQL-COPY"><command>COPY FROM</></link>
1011 in <literal>CSV</> mode (Ian Barwick, Michael Paquier)
1015 Previously only unquoted matching strings would be imported
1022 Issue warnings for <link linkend="SQL-SET"><command>SET</></link>
1023 outside of a transaction block, as they have no effect (Bruce
1028 The cases are <literal>SET
1029 LOCAL</>/<literal>CONSTRAINTS</>/<literal>TRANSACTION</> and
1037 <title><link linkend="SQL-EXPLAIN"><command>EXPLAIN</></link></title>
1043 Have <command>EXPLAIN ANALYZE</> output planning time (Andreas
1050 Have <command>EXPLAIN</> print the grouping columns in Agg and
1051 Group nodes (Tom Lane)
1057 Have <command>EXPLAIN ANALYZE</> show bitmap heap scan exact/lossy
1058 block information (Etsuro Fujita)
1067 <title>Views</title>
1073 Allow materialized views to be refreshed without blocking reads
1078 This is done with <link
1079 linkend="SQL-REFRESHMATERIALIZEDVIEW"><command>REFRESH MATERIALIZED
1080 VIEW CONCURRENTLY</></link>.
1086 Allow <link linkend="SQL-CREATEVIEW-updatable-views">auto-updates
1087 on views</link> where only some columns are auto-updateable
1092 Previously the presence of a non-auto-updateable column prevented
1093 all columns from being auto-updated. Deletes are now supported
1094 on suitable views even if no auto-updateable columns are present.
1100 Allow control over whether <command>INSERT</>s and
1101 <command>UPDATE</>s can add rows to an auto-updateable view that
1102 would no longer appear in the view (Dean Rasheed)
1106 This is controlled with the new <link
1107 linkend="SQL-CREATEVIEW"><literal>WITH CHECK OPTION</></link>.
1113 Allow <link linkend="rules-privileges">security barrier views</>
1114 to be automatically updateable (Dean Rasheed)
1125 <title>Object Manipulation</title>
1131 Allow triggers on <link linkend="SQL-CREATEFOREIGNTABLE">foreign
1132 tables</> (Ronan Dunklau)
1138 Fix <command>DROP IF EXISTS</> to more consistently not error
1139 for non-existent objects (Pavel Stehule, Dean Rasheed)
1145 Improve the internal definition of system relations (Andres Freund,
1150 Previously, relations moved into the system catalog schema could
1157 Allow <link linkend="SQL-ALTERTABLESPACE"><command>ALTER
1158 TABLESPACE</></link> options to be also set by <link
1159 linkend="SQL-CREATETABLESPACE"><command>CREATE TABLESPACE</></link>
1166 Allow <link linkend="SQL-CREATEAGGREGATE"><command>CREATE
1167 AGGREGATE</></link> to supply the size of the aggregate's
1168 transition state data (Hadi Moshayedi)
1175 <title><command>ALTER</></title>
1181 Allow moving groups of objects from one tablespace to another
1182 using <link linkend="SQL-ALTERTABLESPACE"><command>ALTER
1183 TABLESPACE</></link> ... <literal>MOVE</> (Stephen Frost)
1189 Allow the changing of foreign key constraint via <link
1190 linkend="SQL-ALTERTABLE"><command>ALTER TABLE</></link>
1191 ... <literal>ALTER CONSTRAINT</> (Simon Riggs)
1197 Reduce lock levels of some <link
1198 linkend="SQL-ALTERTABLE"><command>ALTER TABLE</></link> commands
1199 (Simon Riggs, Noah Misch)
1203 Specifically, <literal>VALIDATE CONSTRAINT</>, <literal>CLUSTER
1204 ON</>, <literal>SET WITHOUT CLUSTER</>, <literal>ALTER COLUMN
1205 SET STATISTICS</>, <literal>ALTER COLUMN</> <literal>SET</>
1206 <option>(attribute_option)</>, <literal>ALTER COLUMN RESET</>
1207 <option>(attribute_option)</>.
1218 <title>Data Types</title>
1224 Fully-implement the <link
1225 linkend="datatype-line"><type>line</></link> data type (Peter
1230 The line <emphasis>segment</> data type (<link
1231 linkend="datatype-lseg"><type>LSEG</></link>) has always been
1238 Add <link linkend="datatype-pg-lsn"><type>PG_LSN</></link>
1239 data type to represent a <acronym>WAL</> log sequence number
1240 (<acronym>LSN</>) (Robert Haas, Michael Paquier)
1246 Allow single-point <link
1247 linkend="datatype-polygon"><type>POLYGON</></link>s to be converted
1248 to <link linkend="datatype-circle"><type>CIRCLE</></link>s
1255 Allow 5+ digit years for non-<acronym>ISO</> <link
1256 linkend="datatype-datetime"><type>TIMESTAMP</></link> and
1257 <type>DATE</> strings, where appropriate (Bruce Momjian)
1263 Add checks for overflow/underflow of <link
1264 linkend="datatype-datetime"><type>INTERVAL</></link> values
1272 <title><link linkend="datatype-json"><type>JSON</></link></title>
1278 Add structured (non-text) data type (<link
1279 linkend="datatype-json"><type>JSONB</></link>) for storing
1280 <type>JSON</> data (Oleg Bartunov, Teodor Sigaev, Alexander
1281 Korotkov, Peter Geoghegan, and Andrew Dunstan)
1285 This allows for faster access to values in the <type>JSON</>
1286 document and faster and more useful indexing of <type>JSON</>.
1287 <type>JSONB</> values are also typed as appropriate scalar
1294 Add new <type>JSON</> functions to allow for the construction
1295 of arbitrarily complex json trees (Andrew Dunstan, Laurence Rowe)
1299 New functions include <link
1300 linkend="functions-json-processing-table"><function>json_array_elements_text()</></link>,
1301 <function>json_build_array()</>, <function>json_object()</>,
1302 <function>json_object_agg()</>, <function>json_to_record()</>,
1303 and <function>json_to_recordset()</>.
1310 linkend="functions-json-processing-table"><function>json_typeof()</></link>
1311 to return the data type of a <type>JSON</> value (Andrew Tipton)
1323 <title>Functions</title>
1330 linkend="functions-datetime-delay"><function>pg_sleep_for(interval)</></link>
1331 and <function>pg_sleep_until(timestamp)</> to specify sophisticated
1332 delays (Vik Fearing, Julien Rouhaud)
1336 <function>pg_sleep()</> only supports delays specified in seconds.
1343 linkend="array-functions-table"><function>cardinality()</></link>
1344 function for arrays (Marko Tiikkaja)
1348 This returns the total number of elements in the array, or zero
1349 for an array with no elements.
1355 Add <acronym>SQL</> functions to allow <link
1356 linkend="lo-interfaces">large object reads/writes</link> at
1357 arbitrary offsets (Pavel Stehule)
1364 linkend="array-functions-table"><function>unnest()</></link>
1365 to take multiple arguments, which are individually unnested then
1366 horizontally concatenated (Andrew Gierth)
1372 Add functions to construct <type>time</>s, <type>date</>s,
1373 <type>timestamp</>s, <type>timestamptz</>s, and <type>interval</>s
1374 from individual values, rather than strings (Pavel Stehule)
1378 The functions being with <literal>make_</>, e.g. <link
1379 linkend="functions-datetime-table"><function>make_date()</></link>.
1386 linkend="functions-formatting-table"><function>to_char(<literal>TZ</>)</></link>
1387 return the proper value for constant time zone offsets (Tom Lane)
1391 Previously, <literal>to_char(CURRENT_TIMESTAMP, 'TZ')</> returned
1392 NULL if the <literal>TIME ZONE</> was set to a constant like
1399 Add timezone offset output option <literal>OF</> to <link
1400 linkend="functions-formatting-table"><function>to_char()</></link>
1407 Improve the random seed used for <link
1408 linkend="functions-math-random-table"><function>random()</></link>
1416 <title>System Information Functions</title>
1422 Add functions for error-free <structname>pg_class</>,
1423 <structname>pg_proc</>, <structname>pg_type</>, and
1424 <structname>pg_operator</> lookups (Yugo Nagata, Nozomi Anzai,
1430 linkend="functions-info-catalog-table"><function>to_regclass()</></link>
1431 does error-free lookups of <structname>pg_class</>, and returns
1432 NULL for lookup failures.
1439 linkend="functions-admin-dblocation"><function>pg_filenode_relation()</></link>
1440 to allow for more efficient filenode to relation lookups (Andres
1448 linkend="information-schema"><structname>information_schema</></link>
1450 linkend="infoschema-parameters"><structname>parameters.parameter_default</></link>
1458 linkend="infoschema-schemata"><structname>information_schema.schemata</></link>
1459 show all accessible schema (Peter Eisentraut)
1463 Previously it only showed <emphasis>owned</> schemas.
1472 <title>Aggregates</title>
1478 Add aggregates <link
1479 linkend="functions-orderedset-table"><function>percentile_cont()</></link>,
1480 <function>percentile_disc()</>, <link
1481 linkend="functions-hypothetical-table"><function>rank()</></link>,
1482 <function>dense_rank()</>, <function>percent_rank()</>,
1483 <function>cume_dist()</>, and <link
1484 linkend="functions-orderedset-table"><function>mode()</></link>
1485 (Atri Sharma, Andrew Gierth)
1491 Add control over which values are passed
1492 into aggregate functions using the <link
1493 linkend="syntax-aggregates"><literal>FILTER</></link> clause
1500 Support ordered-set (<link
1501 linkend="syntax-aggregates"><literal>WITHIN GROUP</></link>)
1502 aggregates (Atri Sharma, Andrew Gierth, Tom Lane)
1506 This allows aggregates that require sorted input.
1512 Allow polymorphic aggregates to have non-polymorphic state data
1520 linkend="xfunc-sql-variadic-functions"><literal>VARIADIC</></link>
1521 aggregate functions (Tom Lane)
1532 <title>Server-Side Languages</title>
1538 Add event trigger support to <link linkend="plperl">PL/Perl</>
1539 and <link linkend="pltcl">PL/Tcl</> (Dimitri Fontaine)
1546 <title><link linkend="plpgsql">PL/pgSQL</link> Server-Side Language</title>
1552 Add ability to store the PL/PgSQL
1553 call stack into a variable using <link
1554 linkend="plpgsql-get-diagnostics-context"><literal>PG_CONTEXT</></link>
1555 (Pavel Stehule, Stephen Frost)
1562 linkend="plpgsql-statements-assignment"><option>print_strict_params</></link>
1563 to output parameters passed to queries generating <link
1564 linkend="plpgsql-statements-sql-onerow"><literal>STRICT</></link>
1565 errors (Marko Tiikkaja)
1572 linkend="plpgsql-extra-checks"><varname>plpgsql.extra_warnings</></link>
1573 and <varname>plpgsql.extra_errors</>to enable additional PL/pgSQL
1574 warnings and errors (Marko Tiikkaja, Petr Jelinek)
1578 Currently only shadowed variable errors/warnings are available.
1587 <title><link linkend="plpython">PL/Python</link> Server-Side Language</title>
1593 Handle domains over arrays like plain arrays in PL/Python
1598 Previously they were treated as strings.
1604 Convert <link linkend="datatype-numeric"><type>NUMERIC</></link>s
1605 to <type>decimal</> values in PL/Python (Szymon Guz, Ronan Dunklau)
1609 Previously these were converted to <type>float</>s.
1620 <title><link linkend="libpq"><application>libpq</></link></title>
1625 Add libpq function <link
1626 linkend="libpq-pqhostaddr"><function>PQhostaddr()</></link>
1627 to return the server's <acronym>IP</> address (Fujii Masao)
1634 linkend="libpq-pqconndefaults"><function>PQconndefaults()</></link>
1635 ignore invalid service files (Steve Singer, Bruce Momjian)
1639 Previously it returned NULL.
1645 Allow libpq to support <acronym>TLS</> versions beyond
1646 <literal>TLSv1</> (Marko Kreen)
1652 Document that libpq's <link
1653 linkend="libpq-pqclientencoding"><function>PQclientEncoding()</></link>
1654 returns <literal>-1</> for an encoding lookup failure (Bruce
1664 <title>Client Applications</title>
1671 linkend="APP-CREATEUSER"><application>createuser</></link>
1672 <option>-g</> to specify role membership (Chistopher Browne)
1678 Allow <link linkend="APP-VACUUMDB"><application>vacuumdb</></link>
1679 <option>--analyze-in-stages</> to analyze in stages of increasing
1680 granularity (Peter Eisentraut)
1684 This allows minimal statistics to be created quickly.
1690 Allow <link linkend="pgxlogdump"><application>pg_xlogdump</></link>
1691 to report a live log stream with <option>--follow</> (Heikki
1698 Have <link linkend="APP-PGRESETXLOG"><command>pg_resetxlog
1699 -n</></link> output current and potentially changed values
1706 Allow <function>sizeof()</> in <link linkend="ecpg">ecpg</link>
1707 C array definitions (Michael Meskes)
1713 Have <link linkend="ecpg">ecpg</link> properly handle nesting
1714 requirements in C and <acronym>SQL</> mode for C-style comments
1721 Have <link linkend="app-pg-ctl"><application>pg_ctl</></link>
1722 return <literal>4</> for an inaccessible data directory
1723 specification (Amit Kapila, Bruce Momjian)
1727 This more closely matches the Linux Standard Base (<acronym>LSB</>)
1734 On Windows, interpret <link
1735 linkend="app-pg-ctl"><application>pg_ctl</></link>'s non-absolute
1736 path <option>-D</> directories relative to <application>pg_ctl</>'s
1737 current directory (Kumar Rajeev Rastogi)
1744 <title><link linkend="APP-PSQL"><application>psql</></link></title>
1750 Allow field wrapping to <application>psql</>'s "extended" mode
1757 Suppress "No rows" in <application>psql</> <link
1758 linkend="APP-PSQL-meta-commands"><option>expanded</></link>
1759 mode when the footer is disabled (Bruce Momjian)
1765 Allow Control-C to abort <application>psql</> hung at connection
1766 startup (Peter Eisentraut)
1773 <title><link linkend="APP-PSQL-meta-commands">Backslash Commands</link></title>
1779 Have <application>psql</> <command>\db+</> show tablespace options
1786 Have <application>psql</> <command>\do+</> display the functions
1787 which implement the operators (Marko Tiikkaja)
1793 Have <application>psql</> <command>\d+</> output an
1794 <literal>OID</> line only if an oid column exists in a table
1799 Previously, the presence or absence of an oid column was always
1806 Have <command>\d</> display disabled system triggers (Bruce
1811 Previously if you disabled all triggers, only user triggers
1812 would show as disabled.
1818 Fix <application>psql</> <command>\copy</> to no longer require
1819 a space between <literal>stdin</> and a semicolon (Etsuro Fujita)
1825 Output the row count at the end of
1826 <application>psql</> <command>\copy</> just like <link
1827 linkend="SQL-COPY"><command>COPY</></link> (Kumar Rajeev Rastogi)
1833 Fix <application>psql</> <command>\conninfo</> to display the
1834 server's <acronym>IP</> address for clients that connect using
1835 <literal>hostaddr</> (Fujii Masao)
1839 Previously <command>\conninfo</> could not display the server's
1840 <acronym>IP</> address in such cases.
1846 Mention the <acronym>SSL</> protocol version in
1847 <application>psql</>'s <command>\conninfo</> (Marko Kreen)
1853 Add <application>psql</> tab completion for <command>\pset</>
1860 Allow <application>psql</> <command>\pset</> with no arguments
1861 to show all settings (Gilles Darold)
1867 In <application>psql</>, output the written history file name
1868 (<command>\s</>) without adding an absolute path prefix (Tom Lane)
1872 The previous output was inconsistent.
1883 <title><link linkend="APP-PGDUMP"><application>pg_dump</></link></title>
1890 linkend="APP-PGRESTORE"><application>pg_restore</></link> options
1891 <option>-I</>, <option>-P</>, <option>-T</> and <option>-n</>
1892 to be specified multiple times (Heikki Linnakangas)
1896 This allows multiple restore object to be specified.
1902 Add <literal>IF EXISTS</> clauses when removing old objects during
1903 dump and restore (Pavel Stehule)
1907 This suppresses errors when removing old objects.
1908 The new <option>--if-exists</> option to <link
1909 linkend="APP-PGDUMP"><application>pg_dump</></link>, <link
1910 linkend="APP-PG-DUMPALL"><application>pg_dumpall</></link>, and
1911 <link linkend="APP-PGRESTORE"><application>pg_restore</></link>
1912 is only available when <option>--clean</> is also specified.
1921 <title><link linkend="app-pgbasebackup"><application>pg_basebackup</></link></title>
1927 Add <application>pg_basebackup</> option <option>--xlogdir</>
1928 to specify the <filename>pg_xlog</> direction location (Haribabu
1935 Allow <application>pg_basebackup</> to relocate tablespaces in
1936 the backup copy (Steeve Lennmark)
1940 This is particularly useful for using <application>pg_basebackup</>
1941 on the same machine as the primary.
1947 Allow network-streams base backups to be throttled (Antonin Houska)
1951 This can be controlled with the <application>pg_basebackup</>
1952 <option>--max-rate</> parameter.
1963 <title>Source Code</title>
1969 Improve the way tuples are frozen, to preserve forensic information
1970 (Robert Haas, Andres Freund)
1974 Code that inspects tuple flag bits will need to be modified.
1980 No longer require function prototypes for function marked with the
1981 <link linkend="xfunc-c"><function>PG_FUNCTION_INFO_V1</></link>
1982 macro (Peter Eisentraut)
1986 This eliminates needless boilerplate prototypes whose lack
1987 generates compiler warnings.
1993 Remove <function>SnapshotNow()</> and
1994 <function>HeapTupleSatisfiesNow()</> (Robert Haas)
1998 All existing uses have been switched to more appropriate snapshot
1999 types. Catalog scans now use <acronym>MVCC</> snapshots.
2005 Add <acronym>API</> for memory allocations over four gigabytes
2012 Add <function>psprintf()</> to simplify memory allocation during
2013 string composition (Peter Eisentraut, Tom Lane)
2019 Add <function>printf()</> modifier "z" to specify <type>size_t</>
2020 values (Andres Freund)
2026 Change <acronym>API</> of <function>appendStringInfoVA()</>
2027 to better use <function>vsnprintf()</> (David Rowley, Tom Lane)
2033 Allow new types of external toast datums to be created (Andres
2040 Add single-reader, single-writer, lightweight shared message queue
2047 Improve spinlock speed on x86_64 <acronym>CPU</>s (Heikki
2054 Remove spinlock support for unsupported platforms
2055 <productname>SINIX</>, <productname>Sun3</>, and
2056 <productname>NS32K</> (Robert Haas)
2062 Remove <acronym>IRIX</> port (Robert Haas)
2068 Reduce the number of semaphores required by
2069 <option>--disable-spinlocks</> (Robert Haas)
2075 Rewrite <application>duplicate_oids</> Unix shell script in
2076 <application>Perl</> (Andrew Dunstan)
2082 Add Test Anything Protocol (<acronym>TAP</>) tests for client
2083 programs (Peter Eisentraut)
2089 Add make targets <option>check-tests</> and
2090 <option>installcheck-tests</> which allow per-test specification
2097 Remove <option>maintainer-check</> makefile rule (Peter Eisentraut)
2101 Its functionality is now done by the default build rules.
2107 Improve support for <envar>VPATH</> builds of <acronym>PGXS</>
2108 modules (Cédric Villemain, Andrew Dunstan)
2114 Upgrade to Autoconf 2.69 (Peter Eisentraut)
2120 Add configure flag that appends custom text to the
2121 <envar>PG_VERSION</> string (Oskari Saarenmaa)
2125 This is useful for packagers building custom binaries.
2131 Improve DocBook <acronym>XML</> validity (Peter Eisentraut)
2137 Various security and sanity fixes reported by the
2138 <productname>Coverity</> scanner (Stephen Frost)
2144 Improve <application>valgrind</> error reporting (Noah Misch)
2150 Improve <application>Emacs</> configuration file
2151 <filename>emacs.samples</> (Peter Eisentraut)
2155 Also add <filename>.dir-locals.el</> to the top of the source tree.
2161 Allow <application>pgindent</> to be supplied a command-line list
2162 of typedefs (Bruce Momjian)
2166 <application>Pgindent</> is also now smarter about blank lines
2167 around preprocessor conditionals.
2173 Avoid most uses of dlltool in <productname>Cygwin</> and
2174 <productname>Mingw</> builds (Marco Atzeri, Hiroshi Inoue)
2183 <title>Additional Modules</title>
2189 Add <link linkend="pgprewarm"><application>pg_prewarm</></link>
2190 to preload relation data into the shared buffer cache (Robert Haas)
2194 This is useful at server start to produce more consistent
2201 Add <acronym>UUID</> random number generator
2202 <function>gen_random_uuid()</> to <link
2203 linkend="pgcrypto"><application>pgcrypto</></link> (Oskari
2208 This allows the creation of version 4 <acronym>UUID</>s without
2209 requiring the installation of uuid-ossp.
2215 Add logging of trigger execution to <link
2216 linkend="auto-explain"><application>auto_explain</></link>
2223 Fix <link linkend="pgstattuple"><application>pgstattuple</></link>
2224 to not report rows from uncommitted transactions as dead
2231 Have <link linkend="pgstattuple"><application>pgstattuple</></link>
2232 functions use regclass-type arguments (Satoshi Nagayasu)
2236 While text-type arguments are still supported, they will be
2237 removed in a later major release.
2243 Improve consistency of <link
2244 linkend="pgrowlocks"><application>pgrowlocks</></link> output to
2245 honor snapshot rules more consistently (Robert Haas)
2251 Improve indexing of <link
2252 linkend="pgtrgm"><application>pg_trgm</></link> values to
2253 discourage indexing whitespace (Alexander Korotkov)
2259 Store <link linkend="cube"><type>cube</></link> data more compactly
2264 Existing data must be dumped/restored to use the new format.
2265 The old format can still be read.
2271 Reduce <link linkend="vacuumlo"><application>vacuumlo</></link>
2272 client-side memory usage by using a cursor (Andrew Dunstan)
2278 Dramatically reduce memory consumption in <link
2279 linkend="pgupgrade"><application>pg_upgrade</></link> (Bruce
2286 Pass <link linkend="pgupgrade"><application>pg_upgrade</></link>
2287 user names (<option>-U</>) to analyze scripts (Bruce Momjian)
2294 <title><link linkend="pgbench"><application>pgbench</></link></title>
2300 Allow pgbench to process script files of any line length (Sawada
2305 The previous line limit was <envar>BUFSIZ</>.
2311 Add <application>pg_bench</> option (<option>--rate</>) to control
2312 the transaction rate (Fabien Coelho)
2318 Add <option>--progress</> output option to pgbench (Fabien Coelho)
2324 Add long options to pgbench (Fabien Coelho)
2333 <title><link linkend="pgstatstatements"><application>pg_stat_statements</></link></title>
2339 Have <application>pg_stat_statements</> use a flat file for query
2340 text storage (Peter Geoghegan)
2344 This removes the limitation on query text length, and allows a
2345 higher default number of unique statements to be tracked.
2351 Allow reporting of <application>pg_stat_statements</>'s internal
2352 query hash identifier (Daniel Farina, Sameer Thakur, Peter
2359 Add the ability to retrieve all <application>pg_stat_statements</>
2360 information except the query text (Peter Geoghegan)
2364 This allows monitoring tools to only fetch query text for newly
2365 created entries, as reported in <structname>queryid</>.