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-10</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 <link linkend="logicaldecoding">Logical decoding</link> allows database
33 changes to be streamed out in a customizable 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>
56 to edit the <filename>postgresql.conf</> configuration file
62 Reduce lock levels of some <link
63 linkend="SQL-ALTERTABLE"><command>ALTER TABLE</></link> commands
70 The above items are explained in more detail in the sections below.
77 <title>Migration to Version 9.4</title>
80 A dump/restore using <link
81 linkend="APP-PG-DUMPALL"><application>pg_dumpall</></link>, or use
82 of <link linkend="pgupgrade"><application>pg_upgrade</></link>, is
83 required for those wishing to migrate data from any previous release.
87 Version 9.4 contains a number of changes that may affect compatibility
88 with previous releases. Observe the following incompatibilities:
95 Cause consecutive whitespace in <link
96 linkend="functions-formatting-table"><function>to_timestamp()</></link>
97 and <function>to_date()</> format strings to consume a corresponding
98 number of characters in the input string (whitespace or not), then
99 conditionally consume adjacent whitespace if not in <literal>FX</>
104 Previously consecutive whitespace characters in a non-<literal>FX</>
105 format string behaved like a single whitespace character and consumed
106 all adjacent whitespace in the input string. For example, previously
107 format string space-space-space would consume only the first space in
108 ' 12', while it will now consume all three characters.
114 Tighten checks for multi-dimensional <link
115 linkend="arrays">array</link> input (Bruce Momjian)
119 Previously an input array string that started with a single-element
120 array dimension could later contain multi-dimensional segments,
121 e.g. <literal>'{{1}, {2,3}}'::int[]</>.
127 Change empty arrays returned by <application>intarray</> to be
128 zero-dimensional arrays (Bruce Momjian)
132 Previously, empty arrays were returned as one-dimensional empty arrays
133 whose text representation looked the same as zero-dimensional arrays
134 (<literal>{}</>). <application>intarray</>'s behavior in this area
135 now matches the built-in array operators.
142 linkend="xfunc-sql-variadic-functions"><literal>VARIADIC</></link>
143 function arguments (Pavel Stehule)
147 Such arguments must be cast to an array data type.
153 <link linkend="SQL-SHOW"><command>SHOW TIME ZONE</></link> now
154 outputs constant time zone offsets in <acronym>POSIX</>-style zone
159 Previously it was returned in <link
160 linkend="datatype-interval-output"><type>INTERVAL</></link> format.
161 The new format can be passed to <command>SET TIME ZONE</>.
167 Rename <link linkend="SQL-EXPLAIN"><command>EXPLAIN
168 ANALYZE</></link>'s "total runtime" output to "execution time"
173 This was required now that planning time is also reported.
180 linkend="textsearch-functions-table"><function>ts_rank_cd()</></link>
181 to ignore stripped lexemes (Alex Hill)
185 Previously stripped lexemes got a default location and could be
186 considered if mixed with non-stripped lexemes.
193 linkend="ddl-constraints-check-constraints"><literal>CHECK</></link>
194 constraints from referencing system columns, except
195 <structfield>tableoid</> (Amit Kapila)
199 Restoring such check constraints will lead to restore errors.
205 Use the last specified <link
206 linkend="recovery-target"><varname>recovery_target</></link> if
207 multiple are specified (Heikki Linnakangas)
213 On Windows, automatically preserve quotes in command strings supplied
214 by the user (Heikki Linnakangas)
218 User commands that did their own quote preservation might need
219 adjustment, e.g. <link
220 linkend="guc-archive-command">archive_command</link>, <link
221 linkend="restore-command">restore_command</link>, <command>COPY TO/FROM
228 Remove system column <link
229 linkend="catalog-pg-class"><structfield>pg_class.reltoastidxid</></link>
236 Remove support for native <application>krb5</> authentication
241 The proper way to use <application>Kerberos</> authentication is
242 with <acronym>GSSAPI</>.
248 Handle domains over arrays like plain arrays in PL/Python
253 Previously they were treated as strings.
260 linkend="libpq-pqconnectdbparams"><function>PQconnectdbParams()</></link>
262 linkend="libpq-pqpingparams"><function>PQpingParams()</></link>
263 functions process zero-length strings as defaults (Adrian
268 Previously, these functions treated zero-length string values as
269 defaults only in some cases.
275 Remove system column <link
276 linkend="catalog-pg-rewrite"><structfield>pg_rewrite.ev_attr</></link>
281 Per-column rules have not been supported since
282 <application>PostgreSQL</> 7.3.
288 <link linkend="pgupgrade"><application>pg_upgrade</></link> now
289 uses <option>-U</> to specify the user name (Bruce Momjian)
293 Previously <option>-u</> was used to specify the user name.
294 Also spell the long option as <option>--username</>, again for
295 consistency with other tools.
301 The maximum number of <link linkend="bgworker">background workers</link>
302 that can be registered
303 by <function>RegisterBackgroundWorker()</function> is now limited to
304 <link linkend="guc-max-worker-processes"><varname>max_worker_processes</></link>
310 <command>DISCARD ALL</> now also discards sequence state.
319 <title>Changes</title>
322 Below you will find a detailed account of the changes between
323 <productname>PostgreSQL</productname> 9.4 and the previous major
328 <title>Server</title>
334 Have <link linkend="SQL-VACUUM"><command>VACUUM</></link> properly
335 report dead but not removable rows to the statistics collector
340 Previously these were reported as live rows.
345 Allow <link linkend="bgworker">background workers</link> to be
346 dynamically registered, started and terminated (Robert Haas)
350 <function>worker_spi_launch()</> in <application>worker_spi</>
351 shows an example of its use.
357 Allow dynamic allocation of shared memory segments (Robert Haas,
362 This is illustrated in <link
363 linkend="test-shm-mq"><application>test_shm_mq</></link>.
369 Improve <acronym>SSL</> renegotiation handling (Álvaro
376 During immediate shutdown send uncatchable termination signals
377 to child processes that have not already shutdown (MauMau,
378 Álvaro Herrera)
382 This reduces the likelihood of orphaned child processes after
383 <link linkend="app-postmaster"><application>postmaster</></link>
390 Improve randomness of the database system identifier (Tom Lane)
397 <title>Indexes</title>
403 Improve speed of multi-key <link
404 linkend="GIN"><acronym>GIN</></link> lookups (Alexander Korotkov,
411 Reduce <link linkend="GIN"><acronym>GIN</></link> index size
412 (Alexander Korotkov, Heikki Linnakangas)
416 Indexes upgraded via <link
417 linkend="pgupgrade"><application>pg_upgrade</></link> will work
418 fine but will use the old larger <acronym>GIN</> format; <link
419 linkend="SQL-REINDEX"><command>REINDEX</></link> will recreate
420 the index in the new format.
426 Add <link linkend="GiST"><acronym>GiST</></link> index support
427 for <link linkend="datatype-inet"><type>INET</></link> and
428 <link linkend="datatype-cidr"><type>CIDR</></link> data types
433 Such indexes improve <link
434 linkend="cidr-inet-operators-table">subnet</link> and supernet
435 lookups and ordering comparisons.
441 Fix race condition in B-tree page deletion (Heikki Linnakangas)
447 Make the handling of interrupted B-tree page splits more robust
457 <title>General Performance</title>
463 Allow multiple backends to simultaneously insert into <link
464 linkend="wal"><acronym>WAL</></link> buffers (Heikki Linnakangas)
468 This improves parallel write performance.
474 Conditionally write only the modified portion of updated rows to
475 <link linkend="wal"><acronym>WAL</></link> (Amit Kapila)
481 Improve performance of <link
482 linkend="syntax-window-functions"><literal>WINDOW</></link>
483 aggregate functions (David Rowley, Florian Pflug, Tom Lane)
489 Improve speed of aggregates which use <link
490 linkend="datatype-numeric"><type>NUMERIC</></link>s (Hadi
495 These include <function>SUM()</>, <function>AVG()</>, <link
496 linkend="functions-aggregate-statistics-table"><function>STDDEV()</></link>,
497 and <function>VARIANCE()</>.
503 Attempt to <link linkend="vacuum-for-wraparound">freeze</link>
504 tuples when tables are rewritten with <link
505 linkend="SQL-CLUSTER"><command>CLUSTER</></link> or <link
506 linkend="SQL-VACUUM"><command>VACUUM FULL</></link> (Robert Haas,
511 This can avoid the need to freeze the tuples in the future.
517 Improve speed of <link linkend="SQL-COPY"><command>COPY</></link>
518 with <literal>DEFAULT</> <link
519 linkend="functions-sequence-table"><function>nextval()</></link>
520 columns (Simon Riggs)
526 Improve speed of accesessing many different <link
527 linkend="SQL-CREATESEQUENCE">sequences</link> in the same session
534 Allow sorting and B-tree <link linkend="SQL-CREATEINDEX">index
535 builds</link> to use over four gigabytes of memory (Noah Misch)
541 Reduce memory allocated by <link
542 linkend="SQL-DO"><literal>DO</></link> blocks (Tom Lane)
548 Have the optimizer be more aggressive in creating restrictions
549 from mixed <literal>AND</>/<literal>OR</> clauses (Tom Lane)
555 Auto-resize the catalog cache (Heikki Linnakangas)
559 This reduces memory consumption for backends accessing only a few
560 tables, and improves performance for backend accessing many tables.
569 <title>Monitoring</title>
575 Expose the estimation of number of changed tuples since last <link
576 linkend="vacuum-for-statistics">analyze</link> (Mark Kirkwood)
580 This appears in <link
581 linkend="pg-stat-all-tables-view"><structfield>pg_stat_all_tables.n_mod_since_analyze</></link>.
588 linkend="pg-stat-archiver-view"><structname>pg_stat_archiver</></link>
589 system view to report <link linkend="wal"><acronym>WAL</></link>
590 archiver activity (Gabriele Bartolini)
596 Add <varname>backend_xid</> and <varname>backend_xmin</> columns to
597 the system view <link linkend="pg-stat-activity-view"><structname>pg_stat_activity</></link>
598 and <varname>backend_xmin</> to <link linkend="pg-stat-replication-view"><structname>pg_stat_replication</></link>
608 <title><acronym>SSL</></title>
614 Add support for <acronym>SSL</> <acronym>ECDH</> key exchange
619 This allows use of Elliptic Curve keys for server authentication.
620 Such keys are faster and have improved security over <acronym>RSA</> keys.
621 The new configuration parameter
622 <link linkend="guc-ssl-ecdh-curve"><varname>ssl_ecdh_curve</></link>
623 controls which curve is used for <acronym>ECDH</>.
629 Improve the default <link
630 linkend="guc-ssl-ciphers"><varname>ssl_ciphers</></link> value
637 Allow the server to specify the preferred <acronym>SSL</> cipher
642 This is controlled by the new configuration parameter <link
643 linkend="guc-ssl-prefer-server-ciphers"><varname>ssl_prefer_server_ciphers</></link>.
650 linkend="guc-log-connections"><varname>log_connections</></link>
651 show <acronym>SSL</> encryption information (Andreas Kunert)
660 <title>Server Settings</title>
666 Add <acronym>SQL</>-level <link
667 linkend="SQL-ALTERSYSTEM"><command>ALTER SYSTEM</></link> command
668 to adjust server-wide settings (Amit Kapila)
672 Previously such settings could only be changed by
673 editing <filename>postgresql.conf</> at the file system level.
679 Add <link linkend="guc-huge-pages"><varname>huge_pages</></link>
680 configuration parameter to use huge memory pages on Linux
681 (Christian Kruse, Richard Poole, Abhijit Menon-Sen)
685 This can improve performance on large memory systems.
691 Show <acronym>PID</>s of lock holders and
692 waiters and improve relation information in <link
693 linkend="guc-log-lock-waits"><varname>log_lock_waits</></link>
694 log messages (Christian Kruse)
701 linkend="guc-autovacuum-work-mem"><varname>autovacuum_work_mem</></link>
702 to control the amount of memory used by autovacuum workers
710 linkend="guc-max-worker-processes"><varname>max_worker_processes</></link>
711 to limit the number of background workers (Robert Haas)
715 This is helpful in configuring the standby server to have the
716 required same number of worker processes as the primary.
722 Add configuration parameter <link
723 linkend="guc-wal-log-hints"><varname>wal_log_hints</></link>
724 to enable logging of hint bits (Sawada Masahiko)
728 Hint bits are not normally logged, except when checksums are enabled.
729 This is useful for external tools like <application>pg_rewind</>.
735 Allow printf-style space padding to be specified in <link
736 linkend="guc-log-line-prefix"><varname>log_line_prefix</></link>
743 Add superuser-only <link
744 linkend="guc-session-preload-libraries"><varname>session_preload_libraries</></link>
745 configuration parameter to load libraries at session start
751 to <link linkend="guc-local-preload-libraries"><varname>local_preload_libraries</></link>
752 this parameter can load any shared library, not just those in
753 the <filename>$libdir/plugins</> directory.
759 Reduce server logging level when loading shared libraries (Peter
764 The previous level was <literal>LOG</>, which was too verbose
765 for per-session libraries.
772 linkend="guc-work-mem"><varname>work_mem</></link> and <link
773 linkend="guc-maintenance-work-mem"><varname>maintenance_work_mem</></link>
774 defaults by four times (Bruce Momjian)
778 The new defaults are 4MB and 64MB respectively.
784 Increase the default setting of <link
785 linkend="guc-effective-cache-size"><varname>effective_cache_size</></link>
786 to 4GB (Bruce Momjian, Tom Lane)
792 Allow terabyte units to be specified for configuration variable
799 Have Windows <acronym>ASCII</>-encoded databases and server process
800 (e.g. <link linkend="app-postmaster">postmaster</>) emit messages
801 in the <envar>LC_CTYPE</>-defined language (Alexander Law,
806 Previously these messages were output using the Windows
807 <acronym>ANSI</> code page.
818 <title>Replication and Recovery</title>
824 Add <link linkend="recovery-config"><filename>recovery.conf</></link>
825 parameter <link linkend="recovery-min-apply-delay"><varname>recovery_min_apply_delay</></link>
826 to delay replication (Robert Haas, Fabrízio de Royes Mello,
831 This is useful for delaying the replay of user errors on standby
839 linkend="recovery-target"><varname>recovery_target</></link>
840 option <option>immediate</> to stop <link
841 linkend="wal"><acronym>WAL</></link> recovery as soon as a
842 consistent state is reached (MauMau, Heikki Linnakangas)
848 Improve recovery target processing (Heikki Linnakangas)
852 The timestamp reported
853 by <link linkend="functions-recovery-info-table"><function>pg_last_xact_replay_timestamp()</></link>
854 now shows information about already-committed records, not of transactions
855 about to be committed. Recovering to a restore point now replays the
856 restore point, rather than stopping just before the restore point.
863 linkend="functions-admin-backup-table"><function>pg_switch_xlog()</></link>
864 now clears the trailing unused space in the <acronym>WAL</> file
869 This improves the compression ratio for <acronym>WAL</> files.
875 Add <link linkend="streaming-replication-slots">replication
876 slots</link> to coordinate activity on streaming standbys with the
877 node they are streaming from (Andres Freund, Robert Haas)
881 Replication slots allow preservation of resources like
882 <acronym>WAL</> files on the primary that are needed by standby
889 Reduce spinlock contention during <acronym>WAL</> replay (Heikki
896 Report failure return codes from <link
897 linkend="archive-recovery-settings">external recovery commands</>
904 Write <acronym>WAL</> records of running transactions more
905 frequently (Andres Freund)
909 This allows standby servers to start faster and cleanup resources
917 <title><link linkend="logicaldecoding">Logical Decoding</></title>
920 Logical decoding allows database changes to be optionally streamed in a
921 configurable format. The data is read from
922 the <link linkend="wal"><acronym>WAL</></link> and transformed into the
923 desired target format. To implement this feature, the following changes
928 <!-- FIXME: This imo needs a pretty fundamental rewrite -->
931 Add new <option>logical</> <link
932 linkend="guc-wal-level"><varname>wal_level</></link> to enable
933 logical change-set encoding in <acronym>WAL</> (Andres Freund)
937 This interacts with <link
938 linkend="catalog-pg-class"><literal>REPLICA IDENTITY</></link>.
945 linkend="app-pgrecvlogical"><application>pg_recvlogical</></link>
946 to receive data logical decoding data (Andres Freund)
953 linkend="test-decoding"><application>test_decoding</></link>
954 module to illustrate logical decoding at the <acronym>SQL</>
955 level (Andres Freund)
961 Allow logical decoding via the walsender interface ? (Andres
968 Add table-level parameter <link
969 linkend="catalog-pg-class"><literal>REPLICA IDENTITY</></link>
970 to control logical replication (Andres Freund)
976 Add relation option <link
977 linkend="SQL-CREATETABLE-storage-parameters"><option>user_catalog_table</></link>
978 to identify user-created tables involved in logical change-set
979 encoding (Andres Freund)
990 <title>Queries</title>
996 Add <link linkend="queries-tablefunctions"><literal>ROWS
997 FROM()</></link> syntax to allow horizontal concatenation of
998 set-returning functions in the <literal>FROM</>-clause (Andrew Gierth)
1004 Add <link linkend="queries-tablefunctions"><literal>WITH
1005 ORDINALITY</></link> syntax to number rows returned from
1006 <literal>FROM</>-clause functions (Andrew Gierth, David Fetter)
1010 This is particularly useful for functions like
1011 <function>unnest()</>.
1017 Allow <link linkend="SQL-SELECT"><command>SELECT</></link> with
1018 an empty target list (Tom Lane)
1022 This was added so views that select from a table with zero columns
1023 can be dumped correctly.
1032 <title>Utility Commands</title>
1038 Add <link linkend="SQL-DISCARD"><command>DISCARD
1039 SEQUENCES</></link> command to discard cached sequence information
1040 (Fabrízio de Royes Mello, Robert Haas)
1044 <command>DISCARD ALL</> will now also discard such information.
1050 Add <command>FORCE NULL</> option
1051 to <link linkend="SQL-COPY"><command>COPY FROM</></link> which causes
1052 quoted strings matching the null string to be converted to NULL in
1053 <literal>CSV</> mode (Ian Barwick, Michael Paquier)
1057 Previously only unquoted matching strings would be imported
1064 Issue warnings for commands used outside of transaction blocks
1065 because they have no effect (Bruce Momjian)
1069 The cases are <literal>SET
1070 LOCAL</>, <literal>SET CONSTRAINTS</>, <literal>SET TRANSACTION</> and
1078 <title><link linkend="SQL-EXPLAIN"><command>EXPLAIN</></link></title>
1084 Have <command>EXPLAIN ANALYZE</> output planning time (Andreas
1091 Have <command>EXPLAIN</> print the grouping columns in Agg and
1092 Group nodes (Tom Lane)
1098 Have <command>EXPLAIN ANALYZE</> show bitmap heap scan exact/lossy
1099 block information (Etsuro Fujita)
1108 <title>Views</title>
1114 Allow materialized views to be refreshed without blocking reads
1119 This is done with <link
1120 linkend="SQL-REFRESHMATERIALIZEDVIEW"><command>REFRESH MATERIALIZED
1121 VIEW CONCURRENTLY</></link>.
1127 Allow views to be <link
1128 linkend="SQL-CREATEVIEW-updatable-views">automatically
1129 updated</link> even if they contain some non-updatable columns
1134 Previously the presence of non-updatable columns such as
1135 expressions, literals, and function calls prevented automatic
1136 updates. Now <command>INSERT</>s, <command>UPDATE</>s and
1137 <command>DELETE</>s are supported, provided that they do not
1138 attempt to assign new values to any of the non-updatable columns.
1144 Allow control over whether <command>INSERT</>s and
1145 <command>UPDATE</>s can add rows to an auto-updatable view that
1146 would no longer appear in the view (Dean Rasheed)
1150 This is controlled with the new <link
1151 linkend="SQL-CREATEVIEW"><literal>WITH CHECK OPTION</></link>.
1157 Allow <link linkend="rules-privileges">security barrier views</>
1158 to be automatically updatable (Dean Rasheed)
1169 <title>Object Manipulation</title>
1175 Allow triggers on <link linkend="SQL-CREATEFOREIGNTABLE">foreign
1176 tables</> (Ronan Dunklau)
1182 Fix <command>DROP IF EXISTS</> to more consistently not error
1183 for non-existent objects (Pavel Stehule, Dean Rasheed)
1189 Improve how system-level relations are designated (Andres Freund,
1194 Previously, relations once moved into the system catalog schema could
1195 no longer be modified or dropped.
1201 Allow <link linkend="SQL-ALTERTABLESPACE"><command>ALTER
1202 TABLESPACE</></link> options to be also set by <link
1203 linkend="SQL-CREATETABLESPACE"><command>CREATE TABLESPACE</></link>
1210 Allow <link linkend="SQL-CREATEAGGREGATE"><command>CREATE
1211 AGGREGATE</></link> to supply the size of the aggregate's
1212 transition state data (Hadi Moshayedi)
1216 This allows the optimizer to better estimate how much memory will be
1224 <title><command>ALTER</></title>
1230 Allow moving groups of objects from one tablespace to another
1231 using <link linkend="SQL-ALTERTABLESPACE"><command>ALTER
1232 TABLESPACE</></link> ... <literal>MOVE</> (Stephen Frost)
1238 Allow changing foreign key constraint deferrability via <link
1239 linkend="SQL-ALTERTABLE"><command>ALTER TABLE</></link>
1240 ... <literal>ALTER CONSTRAINT</> (Simon Riggs)
1246 Reduce lock levels of some <link
1247 linkend="SQL-ALTERTABLE"><command>ALTER TABLE</></link> commands
1248 (Simon Riggs, Noah Misch, Robert Haas)
1252 Specifically, <literal>VALIDATE CONSTRAINT</>, <literal>CLUSTER
1253 ON</>, <literal>SET WITHOUT CLUSTER</>, <literal>ALTER COLUMN
1254 SET STATISTICS</>, <literal>ALTER COLUMN</> <literal>SET</>
1255 <option>(attribute_option)</>, <literal>ALTER COLUMN RESET</>
1256 <option>(attribute_option)</> no longer require <literal>ACCESS
1268 <title>Data Types</title>
1274 Fully implement the <link
1275 linkend="datatype-line"><type>line</></link> data type (Peter
1280 The line <emphasis>segment</> data type (<link
1281 linkend="datatype-lseg"><type>LSEG</></link>) has always been
1282 fully supported. The previous <type>line</> data type (enabled
1283 only via a compile-time option) is not binary or dump-compatible.
1289 Add <link linkend="datatype-pg-lsn"><type>PG_LSN</></link>
1290 data type to represent a <acronym>WAL</> log sequence number
1291 (<acronym>LSN</>) (Robert Haas, Michael Paquier)
1297 Allow single-point <link
1298 linkend="datatype-polygon"><type>POLYGON</></link>s to be converted
1299 to <link linkend="datatype-circle"><type>CIRCLE</></link>s
1306 Allow 5+ digit years for non-<acronym>ISO</> <link
1307 linkend="datatype-datetime"><type>TIMESTAMP</></link> and
1308 <type>DATE</> strings, where appropriate (Bruce Momjian)
1314 Add checks for overflow/underflow of <link
1315 linkend="datatype-datetime"><type>INTERVAL</></link> values
1323 <title><link linkend="datatype-json"><type>JSON</></link></title>
1329 Add structured (non-text) data type (<link
1330 linkend="datatype-json"><type>JSONB</></link>) for storing
1331 <type>JSON</> data (Oleg Bartunov, Teodor Sigaev, Alexander
1332 Korotkov, Peter Geoghegan, and Andrew Dunstan)
1336 This allows for faster access to values in the <type>JSON</>
1337 document and faster and more useful indexing of <type>JSON</>.
1338 Scalar values in <type>JSONB</> documents are typed as appropriate
1345 Add new <type>JSON</> functions to allow for the construction
1346 of arbitrarily complex json trees (Andrew Dunstan, Laurence Rowe)
1350 New functions include <link
1351 linkend="functions-json-processing-table"><function>json_array_elements_text()</></link>,
1352 <function>json_build_array()</>, <function>json_object()</>,
1353 <function>json_object_agg()</>, <function>json_to_record()</>,
1354 and <function>json_to_recordset()</>.
1361 linkend="functions-json-processing-table"><function>json_typeof()</></link>
1362 to return the data type of a <type>JSON</> value (Andrew Tipton)
1374 <title>Functions</title>
1381 linkend="functions-datetime-delay"><function>pg_sleep_for(interval)</></link>
1382 and <function>pg_sleep_until(timestamp)</> to specify sophisticated
1383 delays (Vik Fearing, Julien Rouhaud)
1387 <function>pg_sleep()</> only supports delays specified in seconds.
1394 linkend="array-functions-table"><function>cardinality()</></link>
1395 function for arrays (Marko Tiikkaja)
1399 This returns the total number of elements in the array, or zero
1400 for an array with no elements.
1406 Add <acronym>SQL</> functions to allow <link
1407 linkend="lo-interfaces">large object reads/writes</link> at
1408 arbitrary offsets (Pavel Stehule)
1415 linkend="array-functions-table"><function>unnest()</></link>
1416 to take multiple arguments, which are individually unnested then
1417 horizontally concatenated (Andrew Gierth)
1423 Add functions to construct <type>time</>s, <type>date</>s,
1424 <type>timestamp</>s, <type>timestamptz</>s, and <type>interval</>s
1425 from individual values, rather than strings (Pavel Stehule)
1429 These functions are prefixed with <literal>make_</>,
1430 e.g. <link linkend="functions-datetime-table"><function>make_date()</></link>.
1437 linkend="functions-formatting-table"><function>to_char(<literal>TZ</>)</></link>
1438 return the proper value for constant time zone offsets (Tom Lane)
1442 Previously, <literal>to_char(CURRENT_TIMESTAMP, 'TZ')</> returned
1443 NULL if the <literal>TIME ZONE</> was set to a constant like
1450 Add timezone offset output option <literal>OF</> to <link
1451 linkend="functions-formatting-table"><function>to_char()</></link>
1458 Improve the random seed used for <link
1459 linkend="functions-math-random-table"><function>random()</></link>
1467 <title>System Information Functions</title>
1473 Add functions for looking up objects in <structname>pg_class</>,
1474 <structname>pg_proc</>, <structname>pg_type</>, and
1475 <structname>pg_operator</> which do not generate errors for
1476 non-existent objects (Yugo Nagata, Nozomi Anzai,
1482 linkend="functions-info-catalog-table"><function>to_regclass()</></link>
1483 does lookups of <structname>pg_class</> and returns NULL for
1484 non-existent objects.
1491 linkend="functions-admin-dblocation"><function>pg_filenode_relation()</></link>
1492 to allow for more efficient lookup of relation names from filenodes (Andres
1500 linkend="information-schema"><structname>information_schema</></link>
1502 linkend="infoschema-parameters"><structfield>parameters.parameter_default</></link>
1510 linkend="infoschema-schemata"><structfield>information_schema.schemata</></link>
1511 show all accessible schema (Peter Eisentraut)
1515 Previously it only showed <emphasis>owned</> schemas.
1524 <title>Aggregates</title>
1530 Add aggregates <link
1531 linkend="functions-orderedset-table"><function>percentile_cont()</></link>,
1532 <function>percentile_disc()</>, <link
1533 linkend="functions-hypothetical-table"><function>rank()</></link>,
1534 <function>dense_rank()</>, <function>percent_rank()</>,
1535 <function>cume_dist()</>, and <link
1536 linkend="functions-orderedset-table"><function>mode()</></link>
1537 (Atri Sharma, Andrew Gierth)
1543 Add control over which values are passed
1544 into aggregate functions using the <link
1545 linkend="syntax-aggregates"><literal>FILTER</></link> clause
1552 Support ordered-set (<link
1553 linkend="syntax-aggregates"><literal>WITHIN GROUP</></link>)
1554 aggregates (Atri Sharma, Andrew Gierth, Tom Lane)
1558 This allows aggregates that require sorted input.
1564 Allow polymorphic aggregates to have non-polymorphic state data
1568 This allows the declaration of aggregates like the built-in
1569 aggregate <function>array_agg()</> in SQL.
1576 linkend="xfunc-sql-variadic-functions"><literal>VARIADIC</></link>
1577 aggregate functions (Tom Lane)
1588 <title>Server-Side Languages</title>
1594 Add event trigger support to <link linkend="plperl">PL/Perl</>
1595 and <link linkend="pltcl">PL/Tcl</> (Dimitri Fontaine)
1602 <title><link linkend="plpgsql">PL/pgSQL</link> Server-Side Language</title>
1608 Add ability to store the PL/PgSQL
1609 call stack into a variable using <link
1610 linkend="plpgsql-get-diagnostics-context"><literal>PG_CONTEXT</></link>
1611 (Pavel Stehule, Stephen Frost)
1618 linkend="plpgsql-statements-assignment"><option>print_strict_params</></link>
1619 to output parameters passed to queries generating <link
1620 linkend="plpgsql-statements-sql-onerow"><literal>STRICT</></link>
1621 errors (Marko Tiikkaja)
1628 linkend="plpgsql-extra-checks"><varname>plpgsql.extra_warnings</></link>
1629 and <varname>plpgsql.extra_errors</> to enable additional PL/pgSQL
1630 warnings and errors (Marko Tiikkaja, Petr Jelinek)
1634 Currently only shadowed variable errors/warnings are available.
1643 <title><link linkend="plpython">PL/Python</link> Server-Side Language</title>
1649 Convert <link linkend="datatype-numeric"><type>NUMERIC</></link>s
1650 to <type>decimal</> values in PL/Python (Szymon Guz, Ronan Dunklau)
1654 Previously these were converted to <type>float</>s.
1665 <title><link linkend="libpq"><application>libpq</></link></title>
1670 Add libpq function <link
1671 linkend="libpq-pqhostaddr"><function>PQhostaddr()</></link>
1672 to return the server's <acronym>IP</> address (Fujii Masao)
1679 linkend="libpq-pqconndefaults"><function>PQconndefaults()</></link>
1680 ignore invalid service files (Steve Singer, Bruce Momjian)
1684 Previously it returned NULL.
1690 Allow libpq to support <acronym>TLS</> versions beyond
1691 <literal>TLSv1</> (Marko Kreen)
1697 Document that libpq's <link
1698 linkend="libpq-pqclientencoding"><function>PQclientEncoding()</></link>
1699 returns <literal>-1</> for an encoding lookup failure (Bruce
1709 <title>Client Applications</title>
1716 linkend="APP-CREATEUSER"><application>createuser</></link>
1717 <option>-g</> to specify role membership (Chistopher Browne)
1723 Add <link linkend="APP-VACUUMDB"><application>vacuumdb</></link>
1724 option <option>--analyze-in-stages</> to analyze in stages of
1725 increasing granularity (Peter Eisentraut)
1729 This allows minimal statistics to be created quickly.
1735 Allow <link linkend="pgxlogdump"><application>pg_xlogdump</></link>
1736 to report a live log stream with <option>--follow</> (Heikki
1743 Have <link linkend="APP-PGRESETXLOG"><command>pg_resetxlog
1744 -n</></link> output current and potentially changed values
1751 Allow <function>sizeof()</> in <link linkend="ecpg">ecpg</link>
1752 C array definitions (Michael Meskes)
1758 Have <link linkend="ecpg">ecpg</link> properly handle nesting
1759 requirements in C and <acronym>SQL</> mode for C-style comments
1766 Have <link linkend="app-pg-ctl"><application>pg_ctl</></link>
1767 return <literal>4</> for an inaccessible data directory
1768 specification (Amit Kapila, Bruce Momjian)
1772 This more closely matches the Linux Standard Base (<acronym>LSB</>)
1779 On Windows, interpret <link
1780 linkend="app-pg-ctl"><application>pg_ctl</></link>'s non-absolute
1781 path <option>-D</> directories relative to <application>pg_ctl</>'s
1782 current directory (Kumar Rajeev Rastogi)
1789 <title><link linkend="APP-PSQL"><application>psql</></link></title>
1795 Add ability to wrap long lines in <application>psql</>'s
1796 <literal>expanded</> mode by using <command>\pset format wrapped</>
1803 Suppress "No rows" in <application>psql</> <link
1804 linkend="APP-PSQL-meta-commands"><option>expanded</></link>
1805 mode when the footer is disabled (Bruce Momjian)
1811 Allow Control-C to abort <application>psql</> hung at connection
1812 startup (Peter Eisentraut)
1819 <title><link linkend="APP-PSQL-meta-commands">Backslash Commands</link></title>
1825 Have <application>psql</> <command>\db+</> show tablespace options
1832 Have <application>psql</> <command>\do+</> display the functions
1833 which implement the operators (Marko Tiikkaja)
1839 Have <application>psql</> <command>\d+</> output an
1840 <literal>OID</> line only if an oid column exists in a table
1845 Previously, the presence or absence of an oid column was always
1852 Have <command>\d</> display disabled system triggers (Bruce
1857 Previously if you disabled all triggers, only user triggers
1858 would show as disabled.
1864 Fix <application>psql</> <command>\copy</> to no longer require
1865 a space between <literal>stdin</> and a semicolon (Etsuro Fujita)
1871 Output the row count at the end of
1872 <application>psql</> <command>\copy</> just like <link
1873 linkend="SQL-COPY"><command>COPY</></link> (Kumar Rajeev Rastogi)
1879 Fix <application>psql</> <command>\conninfo</> to display the
1880 server's <acronym>IP</> address for clients that connect using
1881 <literal>hostaddr</> (Fujii Masao)
1885 Previously <command>\conninfo</> could not display the server's
1886 <acronym>IP</> address in such cases.
1892 Mention the <acronym>SSL</> protocol version in
1893 <application>psql</>'s <command>\conninfo</> (Marko Kreen)
1899 Add <application>psql</> tab completion for <command>\pset</>
1906 Allow <application>psql</> <command>\pset</> with no arguments
1907 to show all settings (Gilles Darold)
1913 In <application>psql</>, output the written history file name
1914 (<command>\s</>) without adding an absolute path prefix (Tom Lane)
1918 The previous output was inconsistent.
1929 <title><link linkend="APP-PGDUMP"><application>pg_dump</></link></title>
1936 linkend="APP-PGRESTORE"><application>pg_restore</></link> options
1937 <option>-I</>, <option>-P</>, <option>-T</> and <option>-n</>
1938 to be specified multiple times (Heikki Linnakangas)
1942 This allows multiple restore object to be specified.
1948 Add <literal>IF EXISTS</> clauses when removing old objects during
1949 dump and restore (Pavel Stehule)
1953 This suppresses errors when removing old objects.
1954 The new <option>--if-exists</> option to <link
1955 linkend="APP-PGDUMP"><application>pg_dump</></link>, <link
1956 linkend="APP-PG-DUMPALL"><application>pg_dumpall</></link>, and
1957 <link linkend="APP-PGRESTORE"><application>pg_restore</></link>
1958 is only available when <option>--clean</> is also specified.
1967 <title><link linkend="app-pgbasebackup"><application>pg_basebackup</></link></title>
1973 Add <application>pg_basebackup</> option <option>--xlogdir</>
1974 to specify the <filename>pg_xlog</> direction location (Haribabu
1981 Allow <application>pg_basebackup</> to relocate tablespaces in
1982 the backup copy (Steeve Lennmark)
1986 This is particularly useful for using <application>pg_basebackup</>
1987 on the same machine as the primary.
1993 Allow network-streams base backups to be throttled (Antonin Houska)
1997 This can be controlled with the <application>pg_basebackup</>
1998 <option>--max-rate</> parameter.
2009 <title>Source Code</title>
2015 Improve the way tuples are frozen, to preserve forensic information
2016 (Robert Haas, Andres Freund)
2020 Code that inspects tuple flag bits will need to be modified.
2026 No longer require function prototypes for function marked with the
2027 <link linkend="xfunc-c"><function>PG_FUNCTION_INFO_V1</></link>
2028 macro (Peter Eisentraut)
2032 This eliminates needless boilerplate prototypes whose lack
2033 generates compiler warnings.
2039 Remove <function>SnapshotNow()</> and
2040 <function>HeapTupleSatisfiesNow()</> (Robert Haas)
2044 All existing uses have been switched to more appropriate snapshot
2045 types. Catalog scans now use <acronym>MVCC</> snapshots.
2051 Add <acronym>API</> for memory allocations over four gigabytes
2058 Add <function>psprintf()</> to simplify memory allocation during
2059 string composition (Peter Eisentraut, Tom Lane)
2065 Add <function>printf()</> modifier "z" to specify <type>size_t</>
2066 values (Andres Freund)
2072 Change <acronym>API</> of <function>appendStringInfoVA()</>
2073 to better use <function>vsnprintf()</> (David Rowley, Tom Lane)
2079 Allow new types of external toast datums to be created (Andres
2086 Add single-reader, single-writer, lightweight shared message queue
2093 Improve spinlock speed on x86_64 <acronym>CPU</>s (Heikki
2100 Remove spinlock support for unsupported platforms
2101 <productname>SINIX</>, <productname>Sun3</>, and
2102 <productname>NS32K</> (Robert Haas)
2108 Remove <acronym>IRIX</> port (Robert Haas)
2114 Reduce the number of semaphores required by
2115 <option>--disable-spinlocks</> (Robert Haas)
2121 Rewrite <application>duplicate_oids</> Unix shell script in
2122 <application>Perl</> (Andrew Dunstan)
2128 Add Test Anything Protocol (<acronym>TAP</>) tests for client
2129 programs (Peter Eisentraut)
2135 Add make targets <option>check-tests</> and
2136 <option>installcheck-tests</> which allow per-test specification
2143 Remove <option>maintainer-check</> makefile rule (Peter Eisentraut)
2147 Its functionality is now done by the default build rules.
2153 Improve support for <envar>VPATH</> builds of <acronym>PGXS</>
2154 modules (Cédric Villemain, Andrew Dunstan)
2160 Upgrade to Autoconf 2.69 (Peter Eisentraut)
2166 Add configure flag that appends custom text to the
2167 <envar>PG_VERSION</> string (Oskari Saarenmaa)
2171 This is useful for packagers building custom binaries.
2177 Improve DocBook <acronym>XML</> validity (Peter Eisentraut)
2183 Various security and sanity fixes reported by the
2184 <productname>Coverity</> scanner (Stephen Frost)
2190 Improve <application>valgrind</> error reporting (Noah Misch)
2196 Improve <application>Emacs</> configuration file
2197 <filename>emacs.samples</> (Peter Eisentraut)
2201 Also add <filename>.dir-locals.el</> to the top of the source tree.
2207 Allow <application>pgindent</> to be supplied a command-line list
2208 of typedefs (Bruce Momjian)
2212 <application>Pgindent</> is also now smarter about blank lines
2213 around preprocessor conditionals.
2219 Avoid most uses of dlltool in <productname>Cygwin</> and
2220 <productname>Mingw</> builds (Marco Atzeri, Hiroshi Inoue)
2226 Allow client-only installs for <acronym>MSVC</> (Windows) builds (MauMau)
2235 <title>Additional Modules</title>
2241 Add <link linkend="pgprewarm"><application>pg_prewarm</></link>
2242 extension to preload relation data into the shared buffer cache
2247 This is useful at server start to produce more consistent
2254 Add <acronym>UUID</> random number generator
2255 <function>gen_random_uuid()</> to <link
2256 linkend="pgcrypto"><application>pgcrypto</></link> (Oskari
2261 This allows the creation of version 4 <acronym>UUID</>s without
2262 requiring the installation of uuid-ossp.
2268 Add option to include trigger execution time to <link
2269 linkend="auto-explain"><application>auto_explain</></link>
2276 Fix <link linkend="pgstattuple"><application>pgstattuple</></link>
2277 to not report rows from uncommitted transactions as dead
2284 Have <link linkend="pgstattuple"><application>pgstattuple</></link>
2285 functions use regclass-type arguments (Satoshi Nagayasu)
2289 While text-type arguments are still supported, they will be
2290 removed in a later major release.
2296 Improve consistency of <link
2297 linkend="pgrowlocks"><application>pgrowlocks</></link> output to
2298 honor snapshot rules more consistently (Robert Haas)
2304 Improve <link linkend="pgtrgm"><application>pg_trgm</>'s</link>
2305 generation of trigrams for indexed regular expression searches by
2306 discouraging the indexing of trigrams containing whitespace (Alexander
2313 Store <link linkend="cube"><type>cube</></link> data more compactly
2318 Existing data must be dumped/restored to use the new format.
2319 The old format can still be read.
2325 Reduce <link linkend="vacuumlo"><application>vacuumlo</></link>
2326 client-side memory usage by using a cursor (Andrew Dunstan)
2332 Dramatically reduce memory consumption in <link
2333 linkend="pgupgrade"><application>pg_upgrade</></link> (Bruce
2340 Pass <link linkend="pgupgrade"><application>pg_upgrade</></link>
2341 user names (<option>-U</>) to analyze scripts (Bruce Momjian)
2348 <title><link linkend="pgbench"><application>pgbench</></link></title>
2354 Remove line length limit for <application>pgbench</> scripts (Sawada
2359 The previous line limit was <envar>BUFSIZ</>.
2365 Add <application>pgbench</> option (<option>--rate</>) to control
2366 the transaction rate (Fabien Coelho)
2372 Add <option>--progress</> output option to <application>pgbench</> (Fabien Coelho)
2378 Add long options to <application>pgbench</> (Fabien Coelho)
2387 <title><link linkend="pgstatstatements"><application>pg_stat_statements</></link></title>
2393 Have <application>pg_stat_statements</> use a flat file for query
2394 text storage (Peter Geoghegan)
2398 This removes the limitation on query text length, and allows a
2399 higher default number of unique statements to be tracked.
2405 Allow reporting of <application>pg_stat_statements</>'s internal
2406 query hash identifier (Daniel Farina, Sameer Thakur, Peter
2413 Add the ability to retrieve all <application>pg_stat_statements</>
2414 information except the query text (Peter Geoghegan)
2418 This allows monitoring tools to only fetch query text for newly
2419 created entries, as reported in <structfield>queryid</>.