PostgreSQL (64-bit) 歷史版本列表
更新時間：2014-12-20
更新細節：
What's new in this version:
Server:
- Allow background worker processes to be dynamically registered, started and terminated
- The new worker_spi module shows an example of use of this feature.
- Allow dynamic allocation of shared memory segments
- This feature is illustrated in the test_shm_mq module.
- During crash recovery or immediate shutdown, send uncatchable termination signals (SIGKILL) to child processes that do not shut down promptly
- This reduces the likelihood of leaving orphaned child processes behind after postmaster shutdown, as well as ensuring that crash recovery can proceed if some child processes have become "stuck".
- Improve randomness of the database system identifier
- Make VACUUM properly report dead but not-yet-removable rows to the statistics collector
- Previously these were reported as live rows.
Indexes:
- Reduce GIN index size
- Indexes upgraded via pg_upgrade will work fine but will still be in the old, larger GIN format. Use REINDEX to recreate old GIN indexes in the new format
- Improve speed of multi-key GIN lookups
- Add GiST index support for inet and cidr data types
- Such indexes improve subnet and supernet lookups and ordering comparisons
- Fix rare race condition in B-tree page deletion
- Make the handling of interrupted B-tree page splits more robust
General Performance:
- Allow multiple backends to insert into WAL buffers concurrently
- This improves parallel write performance.
- Conditionally write only the modified portion of updated rows to WAL
- Improve performance of aggregate functions used as window functions
- Improve speed of aggregates that use numeric state values
- Attempt to freeze tuples when tables are rewritten with CLUSTER or VACUUM FULL
- This can avoid the need to freeze the tuples in the future.
- Improve speed of COPY with default nextval() columns
- Improve speed of accessing many different sequences in the same session
- Raise hard limit on the number of tuples held in memory during sorting and B-tree index builds
- Reduce memory allocated by PL/pgSQL DO blocks
- Make the planner more aggressive about extracting restriction clauses from mixed AND/OR clauses
- Disallow pushing volatile WHERE clauses down into DISTINCT subqueries
- Pushing down a WHERE clause can produce a more efficient plan overall, but at the cost of evaluating the clause more often than is implied by the text of the query; so don't do it if the clause contains any volatile functions.
- Auto-resize the catalog caches
- This reduces memory consumption for sessions accessing only a few tables, and improves performance for sessions accessing many tables.
Monitoring:
- Add pg_stat_archiver system view to report WAL archiver activity
- Add n_mod_since_analyze columns to pg_stat_all_tables and related system views
- These columns expose the system's estimate of the number of changed tuples since the table's last ANALYZE. This estimate drives decisions about when to auto-analyze.
- Add backend_xid and backend_xmin columns to the system view pg_stat_activity, and a backend_xmin column to pg_stat_replication
SSL:
- Add support for SSL ECDH key exchange
- This allows use of Elliptic Curve keys for server authentication. Such keys are faster and have better security than RSA keys. The new configuration parameter ssl_ecdh_curve controls which curve is used for ECDH.
- Improve the default ssl_ciphers setting
- By default, the server not the client now controls the preference order of SSL ciphers
- Previously, the order specified by ssl_ciphers was usually ignored in favor of client-side defaults, which are not configurable in most PostgreSQL clients. If desired, the old behavior can be restored via the new configuration parameter ssl_prefer_server_ciphers.
- Make log_connections show SSL encryption information (Andreas Kunert)
- Improve SSL renegotiation handling
Server Settings:
- Add new SQL command ALTER SYSTEM for changing postgresql.conf configuration file entries
- Previously such settings could only be changed by manually editing postgresql.conf.
- Add autovacuum_work_mem configuration parameter to control the amount of memory used by autovacuum workers
- Add huge_pages parameter to allow using huge memory pages on Linux
- This can improve performance on large-memory systems.
- Add max_worker_processes parameter to limit the number of background workers
- This is helpful in configuring a standby server to have the required number of worker processes (the same as the primary).
- Add superuser-only session_preload_libraries parameter to load libraries at session start
- In contrast to local_preload_libraries, this parameter can load any shared library, not just those in the $libdir/plugins directory.
- Add wal_log_hints parameter to enable WAL logging of hint-bit changes
- Hint bit changes are not normally logged, except when checksums are enabled. This is useful for external tools like pg_rewind.
- Increase the default settings of work_mem and maintenance_work_mem by four times
- The new defaults are 4MB and 64MB respectively.
- Increase the default setting of effective_cache_size to 4GB
- Allow printf-style space padding to be specified in log_line_prefix (David Rowley)
- Allow terabyte units (TB) to be used when specifying configuration variable values
- Show PIDs of lock holders and waiters and improve information about relations in log_lock_waits log messages
- Reduce server logging level when loading shared libraries
- The previous level was LOG, which was too verbose for libraries loaded per-session.
- On Windows, make SQL_ASCII-encoded databases and server processes (e.g., postmaster) emit messages in the character encoding of the server's Windows user locale
- Previously these messages were output in the Windows ANSI code page.
Replication and Recovery:
- Add replication slots to coordinate activity on streaming standbys with the node they are streaming from
- Replication slots allow preservation of resources like WAL files on the primary until they are no longer needed by standby servers.
- Add recovery parameter recovery_min_apply_delay to delay replication
- Delaying replay on standby servers can be useful for recovering from user errors.
- Add recovery_target option immediate to stop WAL recovery as soon as a consistent state is reached
- Improve recovery target processing
- The timestamp reported by pg_last_xact_replay_timestamp() now reflects already-committed records, not transactions about to be committed. Recovering to a restore point now replays the restore point, rather than stopping just before the restore point.
- pg_switch_xlog() now clears any unused trailing space in the old WAL file
- This improves the compression ratio for WAL files.
- Report failure return codes from external recovery commands
- Reduce spinlock contention during WAL replay
- Write WAL records of running transactions more frequently
- This allows standby servers to start faster and clean up resources more aggressively.
- Logical Decoding - allows database changes to be streamed in a configurable format. The data is read from the WAL and transformed into the desired target format. To implement this feature, the following changes were made:
- Add support for logical decoding of WAL data, to allow database changes to be streamed out in a customizable format
- Add new wal_level setting logical to enable logical change-set encoding in WAL
- Add table-level parameter REPLICA IDENTITY to control logical replication
- Add relation option user_catalog_table to identify user-created tables involved in logical change-set encoding
- Add pg_recvlogical application to receive logical-decoding data
- Add test_decoding module to illustrate logical decoding at the SQL level
Queries:
- Add WITH ORDINALITY syntax to number the rows returned from a set-returning function in the FROM clause
- This is particularly useful for functions like unnest().
- Add ROWS FROM() syntax to allow horizontal concatenation of set-returning functions in the FROM clause
- Allow SELECT to have an empty target list
- This was added so that views that select from a table with zero columns can be dumped and restored correctly.
- Ensure that SELECT ... FOR UPDATE NOWAIT does not wait in corner cases involving already-concurrently-updated tuples
Utility Commands:
- Add DISCARD SEQUENCES command to discard cached sequence-related state
- DISCARD ALL will now also discard such information.
- Add FORCE NULL option to COPY FROM, which causes quoted strings matching the specified null string to be converted to NULLs in CSV mode
- Without this option, only unquoted matching strings will be imported as null values.
- Issue warnings for commands used outside of transaction blocks when they can have no effect
- New warnings are issued for SET LOCAL, SET CONSTRAINTS, SET TRANSACTION and ABORT when used outside a transaction block.
EXPLAIN:
- Make EXPLAIN ANALYZE show planning time
- Make EXPLAIN show the grouping columns in Agg and Group nodes
- Make EXPLAIN ANALYZE show exact and lossy block counts in bitmap heap scans
Views:
- Allow a materialized view to be refreshed without blocking other sessions from reading the view meanwhile
- This is done with REFRESH MATERIALIZED VIEW CONCURRENTLY.
- Allow views to be automatically updated even if they contain some non-updatable columns
- Previously the presence of non-updatable output columns such as expressions, literals, and function calls prevented automatic updates. Now INSERTs, UPDATEs and DELETEs are supported, provided that they do not attempt to assign new values to any of the non-updatable columns.
- Allow control over whether INSERTs and UPDATEs can add rows to an auto-updatable view that would not appear in the view
- This is controlled with the new CREATE VIEW clause WITH CHECK OPTION.
- Allow security barrier views to be automatically updatable
Object Manipulation:
- Support triggers on foreign tables
- Allow moving groups of objects from one tablespace to another using the ALL IN TABLESPACE ... SET TABLESPACE form of ALTER TABLE, ALTER INDEX, or ALTER MATERIALIZED VIEW
- Allow changing foreign key constraint deferrability via ALTER TABLE ... ALTER CONSTRAINT
- Reduce lock strength for some ALTER TABLE commands
- Specifically, VALIDATE CONSTRAINT, CLUSTER ON, SET WITHOUT CLUSTER, ALTER COLUMN SET STATISTICS, ALTER COLUMN SET (attribute_option), ALTER COLUMN RESET (attribute_option) no longer require ACCESS EXCLUSIVE locks.
- Allow tablespace options to be set in CREATE TABLESPACE (Vik Fearing)
- Formerly these options could only be set via ALTER TABLESPACE.
- Allow CREATE AGGREGATE to define the estimated size of the aggregate's transition state data
- Proper use of this feature allows the planner to better estimate how much memory will be used by aggregates.
- Fix DROP IF EXISTS to avoid errors for non-existent objects in more cases
- Improve how system relations are identified
- Previously, relations once moved into the pg_catalog schema could no longer be modified or dropped.
Data Types:
- Fully implement the line data type
- The line segment data type (lseg) has always been fully supported. The previous line data type (which was enabled only via a compile-time option) is not binary or dump-compatible with the new implementation.
- Add pg_lsn data type to represent a WAL log sequence number (LSN)
- Allow single-point polygons to be converted to circles (Bruce Momjian)
- Support time zone abbreviations that change UTC offset from time to time
- Previously, PostgreSQL assumed that the UTC offset associated with a time zone abbreviation (such as EST) never changes in the usage of any particular locale. However this assumption fails in the real world, so introduce the ability for a zone abbreviation to represent a UTC offset that sometimes changes. Update the zone abbreviation definition files to make use of this feature in timezone locales that have changed the UTC offset of their abbreviations since 1970 (according to the IANA timezone database). In such timezones, PostgreSQL will now associate the correct UTC offset with the abbreviation depending on the given date.
- Allow 5+ digit years for non-ISO timestamp and date strings, where appropriate
JSON:
- Add jsonb, a more capable and efficient data type for storing JSON data
- This new type allows faster access to values within a JSON document, and faster and more useful indexing of JSON columns. Scalar values in jsonb documents are stored as appropriate scalar SQL types, and the JSON document structure is pre-parsed rather than being stored as text as in the original json data type.
- Add new JSON functions to allow for the construction of arbitrarily complex JSON trees
- New functions include json_array_elements_text(), json_build_array(), json_object(), json_object_agg(), json_to_record(), and json_to_recordset().
- Add json_typeof() to return the data type of a json value (Andrew Tipton)
- Add checks for overflow/underflow of interval values
Functions:
- Add pg_sleep_for(interval) and pg_sleep_until(timestamp) to specify delays more flexibly
- The existing pg_sleep() function only supports delays specified in seconds.
- Add cardinality() function for arrays
- This returns the total number of elements in the array, or zero for an array with no elements.
- Add SQL functions to allow large object reads/writes at arbitrary offsets
- Allow unnest() to take multiple arguments, which are individually unnested then horizontally concatenated
- Add functions to construct times, dates, timestamps, timestamptzs, and intervals from individual values, rather than strings
- These functions' names are prefixed with make_, e.g. make_date().
- Make to_char()'s TZ format specifier return a useful value for simple numeric time zone offsets
- Previously, to_char(CURRENT_TIMESTAMP, 'TZ') returned an empty string if the timezone was set to a constant like -4.
- Add timezone offset format specifier OF to to_char()
- Improve the random seed used for random()
- Tighten validity checking for Unicode code points in chr(int)
- This function now only accepts values that are valid UTF8 characters according to RFC 3629.
System Information Functions:
- Add functions for looking up objects in pg_class, pg_proc, pg_type, and pg_operator that do not generate errors for non-existent objects
- For example, to_regclass() does a lookup in pg_class similarly to the regclass input function, but it returns NULL for a non-existent object instead of failing.
- Add function pg_filenode_relation() to allow for more efficient lookup of relation names from filenodes
- Add parameter_default column to information_schema.parameters view
- Make information_schema.schemata show all accessible schemas
- Previously it only showed schemas owned by the current user.
Aggregates:
- Add control over which rows are passed into aggregate functions via the FILTER clause
- Support ordered-set (WITHIN GROUP) aggregates
- Add standard ordered-set aggregates percentile_cont(), percentile_disc(), mode(), rank(), dense_rank(), percent_rank(), and cume_dist()
- Support VARIADIC aggregate functions
- Allow polymorphic aggregates to have non-polymorphic state data types
- This allows proper declaration in SQL of aggregates like the built-in aggregate array_agg().
Server-Side Languages:
- Add event trigger support to PL/Perl and PL/Tcl
- Convert numeric values to decimal in PL/Python
- Previously such values were converted to Python float values, risking loss of precision.
PL/pgSQL Server-Side Language:
- Add ability to retrieve the current PL/PgSQL call stack using GET DIAGNOSTICS
- Add option print_strict_params to display the parameters passed to a query that violated a STRICT constraint
- Add variables plpgsql.extra_warnings and plpgsql.extra_errors to enable additional PL/pgSQL warnings and errors
- Currently only warnings/errors about shadowed variables are available.
libpq:
- Make libpq's PQconndefaults() function ignore invalid service files
- Previously it returned NULL if an incorrect service file was encountered.
- Accept TLS protocol versions beyond TLSv1 in libpq
Client Applications:
- Add createuser option -g to specify role membership (Chistopher Browne)
- Add vacuumdb option --analyze-in-stages to analyze in stages of increasing granularity
- This allows minimal statistics to be created quickly.
- Make pg_resetxlog with option -n output current and potentially changed values
- Make initdb throw error for incorrect locale settings, rather than silently falling back to a default choice
- Make pg_ctl return exit code 4 for an inaccessible data directory
- This behavior more closely matches the Linux Standard Base (LSB) Core Specification.
- On Windows, ensure that a non-absolute -D path specification is interpreted relative to pg_ctl's current directory
- Previously it would be interpreted relative to whichever directory the underlying Windows service was started in.
- Allow sizeof() in ECPG C array definitions
- Make ECPG properly handle nesting of C-style comments in both C and SQL text
psql:
- Suppress "No rows" output in psql expanded mode when the footer is disabled
- Allow Control-C to abort psql when it's hung at connection startup
Backslash Commands:
- Make psql's db+ show tablespace options
- Make do+ display the functions that implement the operators
- Make d+ output an OID line only if an oid column exists in the table
- Previously, the presence or absence of an oid column was always reported.
- Make d show disabled system triggers
- Previously, if you disabled all triggers, only user triggers would show as disabled.
- Fix copy to no longer require a space between stdin and a semicolon
- Output the row count at the end of copy, just like COPY already did
- Fix conninfo to display the server's IP address for connections using hostaddr
- Previously conninfo could not display the server's IP address in such cases.
- Show the SSL protocol version in conninfo
- Add tab completion for pset
- Allow pset with no arguments to show all settings
- Make s display the name of the history file it wrote without converting it to an absolute path
- The code previously attempted to convert a relative file name to an absolute path for display, but frequently got it wrong.
pg_dump:
- Allow pg_restore options -I, -P, -T and -n to be specified multiple times
- This allows multiple objects to be restored in one operation
- Optionally add IF EXISTS clauses to the DROP commands emitted when removing old objects during a restore
- This change prevents unnecessary errors when removing old objects. The new --if-exists option for pg_dump, pg_dumpall, and pg_restore is only available when --clean is also specified
pg_basebackup:
- Add pg_basebackup option --xlogdir to specify the pg_xlog directory location
- Allow pg_basebackup to relocate tablespaces in the backup copy
- This is particularly useful for using pg_basebackup on the same machine as the primary.
- Allow network-stream base backups to be throttled
- This can be controlled with the pg_basebackup --max-rate parameter.
Source Code:
- Improve the way tuples are frozen to preserve forensic information
- This change removes the main objection to freezing tuples as soon as possible. Code that inspects tuple flag bits will need to be modified.
- No longer require function prototypes for functions marked with the PG_FUNCTION_INFO_V1 macro
- This change eliminates the need to write boilerplate prototypes. Note that the PG_FUNCTION_INFO_V1 macro must appear before the corresponding function definition to avoid compiler warnings.
- Remove SnapshotNow and HeapTupleSatisfiesNow()
- All existing uses have been switched to more appropriate snapshot types. Catalog scans now use MVCC snapshots.
- Add an API to allow memory allocations over one gigabyte
- Add psprintf() to simplify memory allocation during string composition
- Support printf() size modifier z to print size_t values
- Change API of appendStringInfoVA() to better use vsnprintf()
- Allow new types of external toast datums to be created
- Add single-reader, single-writer, lightweight shared message queue
- Improve spinlock speed on x86_64 CPUs
- Remove spinlock support for unsupported platforms SINIX, Sun3, and NS32K
- Remove IRIX port
- Reduce the number of semaphores required by --disable-spinlocks builds
- Rewrite duplicate_oids Unix shell script in Perl
- Add Test Anything Protocol (TAP) tests for client programs
- Currently, these tests are run by make check-world only if the --enable-tap-tests option was given to configure. This might become the default behavior in some future release.
- Add make targets check-tests and installcheck-tests, which allow selection of individual tests to be run
- Remove maintainer-check makefile rule
- The default build rules now include all the formerly-optional tests.
- Improve support for VPATH builds of PGXS modules
- Upgrade to Autoconf 2.69
- Add a configure flag that appends custom text to the PG_VERSION string
- This is useful for packagers building custom binaries.
- Improve DocBook XML validity
- Fix various minor security and sanity issues reported by the Coverity scanner
- Improve detection of invalid memory usage when testing PostgreSQL with Valgrind
- Improve sample Emacs configuration file emacs.samples
- Also add .dir-locals.el to the top of the source tree.
- Allow pgindent to accept a command-line list of typedefs
- Make pgindent smarter about blank lines around preprocessor conditionals
- Avoid most uses of dlltool in Cygwin and Mingw builds
- Support client-only installs in MSVC (Windows) builds
Additional Modules:
- Add pg_prewarm extension to preload relation data into the shared buffer cache at server start
- This allows reaching full operating performance more quickly.
- Add UUID random number generator gen_random_uuid() to pgcrypto
- This allows creation of version 4 UUIDs without requiring installation of uuid-ossp.
- Allow uuid-ossp to work with the BSD or e2fsprogs UUID libraries, not only the OSSP UUID library
- This improves the uuid-ossp module's portability since it no longer has to have the increasingly-obsolete OSSP library. The module's name is now rather a misnomer, but we won't change it.
- Add option to auto_explain to include trigger execution time
- Fix pgstattuple to not report rows from uncommitted transactions as dead
- Make pgstattuple functions use regclass-type arguments
- While text-type arguments are still supported, they may be removed in a future major release.
- Improve consistency of pgrowlocks output to honor snapshot rules more consistently
- Improve pg_trgm's choice of trigrams for indexed regular expression searches
- This change discourages use of trigrams containing whitespace, which are usually less selective.
- Allow pg_xlogdump to report a live log stream with --follow
- Store cube data more compactly
- Existing data must be dumped/restored to use the new format. The old format can still be read.
- Reduce vacuumlo client-side memory usage by using a cursor
- Dramatically reduce memory consumption in pg_upgrade
- Pass pg_upgrade's user name (-U) option to generated analyze scripts
pgbench:
- Remove line length limit for pgbench scripts
- The previous line limit was BUFSIZ
- Add long option names to pgbench
- Add pgbench option --rate to control the transaction rate
- Add pgbench option --progress to print periodic progress reports
pg_stat_statements:
- Make pg_stat_statements use a file, rather than shared memory, for query text storage
- This removes the previous limitation on query text length, and allows a higher number of unique statements to be tracked by default
- Allow reporting of pg_stat_statements's internal query hash identifier
- Add the ability to retrieve all pg_stat_statements information except the query text
- This allows monitoring tools to fetch query text only for just-created entries, improving performance during repeated querying of the statistics
- Make pg_stat_statements ignore DEALLOCATE commands
- It already ignored PREPARE, as well as planning time in general, so this seems more consistent
- Save the statistics file into $PGDATA/pg_stat at server shutdown, rather than $PGDATA/global
更新時間：2014-07-27
更新細節：
What's new in this version:
- Correctly initialize padding bytes in contrib/btree_gist indexes on bit columns (Heikki Linnakangas)
- This error could result in incorrect query results due to values that should compare equal not being seen as equal. Users with GiST indexes on bit or bit varying columns should REINDEX those indexes after installing this update.
- Protect against torn pages when deleting GIN list pages (Heikki Linnakangas)
- This fix prevents possible index corruption if a system crash occurs while the page update is being written to disk.
- Fix possibly-incorrect cache invalidation during nested calls to ReceiveSharedInvalidMessages (Andres Freund)
- Don't assume a subquery's output is unique if there's a set-returning function in its targetlist (David Rowley)
- This oversight could lead to misoptimization of constructs like WHERE x IN (SELECT y, generate_series(1,10) FROM t GROUP BY y).
- Fix failure to detoast fields in composite elements of structured types (Tom Lane)
- This corrects cases where TOAST pointers could be copied into other tables without being dereferenced. If the original data is later deleted, it would lead to errors like "missing chunk number 0 for toast value ..." when the now-dangling pointer is used.
- Fix "record type has not been registered" failures with whole-row references to the output of Append plan nodes (Tom Lane)
- Fix possible crash when invoking a user-defined function while rewinding a cursor (Tom Lane)
- Fix query-lifespan memory leak while evaluating the arguments for a function in FROM (Tom Lane)
- Fix session-lifespan memory leaks in regular-expression processing (Tom Lane, Arthur O'Dwyer, Greg Stark)
- Fix data encoding error in hungarian.stop (Tom Lane)
- Fix liveness checks for rows that were inserted in the current transaction and then deleted by a now-rolled-back subtransaction (Andres Freund)
- This could cause problems (at least spurious warnings, and at worst an infinite loop) if CREATE INDEX or CLUSTER were done later in the same transaction.
- Clear pg_stat_activity.xact_start during PREPARE TRANSACTION (Andres Freund)
- After the PREPARE, the originating session is no longer in a transaction, so it should not continue to display a transaction start time.
- Fix REASSIGN OWNED to not fail for text search objects (Álvaro Herrera)
- Block signals during postmaster startup (Tom Lane)
- This ensures that the postmaster will properly clean up after itself if, for example, it receives SIGINT while still starting up.
- Secure Unix-domain sockets of temporary postmasters started during make check (Noah Misch)
- Any local user able to access the socket file could connect as the server's bootstrap superuser, then proceed to execute arbitrary code as the operating-system user running the test, as we previously noted in CVE-2014-0067. This change defends against that risk by placing the server's socket in a temporary, mode 0700 subdirectory of /tmp. The hazard remains however on platforms where Unix sockets are not supported, notably Windows, because then the temporary postmaster must accept local TCP connections.
- A useful side effect of this change is to simplify make check testing in builds that override DEFAULT_PGSOCKET_DIR. Popular non-default values like /var/run/postgresql are often not writable by the build user, requiring workarounds that will no longer be necessary.
- On Windows, allow new sessions to absorb values of PGC_BACKEND parameters (such as log_connections) from the configuration file (Amit Kapila)
- Previously, if such a parameter were changed in the file post-startup, the change would have no effect.
- Properly quote executable path names on Windows (Nikhil Deshpande)
- This oversight could cause initdb and pg_upgrade to fail on Windows, if the installation path contained both spaces and @ signs.
- Fix linking of libpython on OS X (Tom Lane)
- The method we previously used can fail with the Python library supplied by Xcode 5.0 and later.
- Avoid buffer bloat in libpq when the server consistently sends data faster than the client can absorb it (Shin-ichi Morita, Tom Lane)
- libpq could be coerced into enlarging its input buffer until it runs out of memory (which would be reported misleadingly as "lost synchronization with server"). Under ordinary circumstances it's quite far-fetched that data could be continuously transmitted more quickly than the recv() loop can absorb it, but this has been observed when the client is artificially slowed by scheduler constraints.
- Ensure that LDAP lookup attempts in libpq time out as intended (Laurenz Albe)
- Fix pg_restore's processing of old-style large object comments (Tom Lane)
- A direct-to-database restore from an archive file generated by a pre-9.0 version of pg_dump would usually fail if the archive contained more than a few comments for large objects.
- In contrib/pgcrypto functions, ensure sensitive information is cleared from stack variables before returning (Marko Kreen)
- In contrib/uuid-ossp, cache the state of the OSSP UUID library across calls (Tom Lane)
- This improves the efficiency of UUID generation and reduces the amount of entropy drawn from /dev/urandom, on platforms that have that.
- Update time zone data files to tzdata release 2014e for DST law changes in Crimea, Egypt, and Morocco.
更新時間：2014-07-27
更新細節：
What's new in this version:
- Correctly initialize padding bytes in contrib/btree_gist indexes on bit columns (Heikki Linnakangas)
- This error could result in incorrect query results due to values that should compare equal not being seen as equal. Users with GiST indexes on bit or bit varying columns should REINDEX those indexes after installing this update.
- Protect against torn pages when deleting GIN list pages (Heikki Linnakangas)
- This fix prevents possible index corruption if a system crash occurs while the page update is being written to disk.
- Fix possibly-incorrect cache invalidation during nested calls to ReceiveSharedInvalidMessages (Andres Freund)
- Don't assume a subquery's output is unique if there's a set-returning function in its targetlist (David Rowley)
- This oversight could lead to misoptimization of constructs like WHERE x IN (SELECT y, generate_series(1,10) FROM t GROUP BY y).
- Fix failure to detoast fields in composite elements of structured types (Tom Lane)
- This corrects cases where TOAST pointers could be copied into other tables without being dereferenced. If the original data is later deleted, it would lead to errors like "missing chunk number 0 for toast value ..." when the now-dangling pointer is used.
- Fix "record type has not been registered" failures with whole-row references to the output of Append plan nodes (Tom Lane)
- Fix possible crash when invoking a user-defined function while rewinding a cursor (Tom Lane)
- Fix query-lifespan memory leak while evaluating the arguments for a function in FROM (Tom Lane)
- Fix session-lifespan memory leaks in regular-expression processing (Tom Lane, Arthur O'Dwyer, Greg Stark)
- Fix data encoding error in hungarian.stop (Tom Lane)
- Fix liveness checks for rows that were inserted in the current transaction and then deleted by a now-rolled-back subtransaction (Andres Freund)
- This could cause problems (at least spurious warnings, and at worst an infinite loop) if CREATE INDEX or CLUSTER were done later in the same transaction.
- Clear pg_stat_activity.xact_start during PREPARE TRANSACTION (Andres Freund)
- After the PREPARE, the originating session is no longer in a transaction, so it should not continue to display a transaction start time.
- Fix REASSIGN OWNED to not fail for text search objects (Álvaro Herrera)
- Block signals during postmaster startup (Tom Lane)
- This ensures that the postmaster will properly clean up after itself if, for example, it receives SIGINT while still starting up.
- Secure Unix-domain sockets of temporary postmasters started during make check (Noah Misch)
- Any local user able to access the socket file could connect as the server's bootstrap superuser, then proceed to execute arbitrary code as the operating-system user running the test, as we previously noted in CVE-2014-0067. This change defends against that risk by placing the server's socket in a temporary, mode 0700 subdirectory of /tmp. The hazard remains however on platforms where Unix sockets are not supported, notably Windows, because then the temporary postmaster must accept local TCP connections.
- A useful side effect of this change is to simplify make check testing in builds that override DEFAULT_PGSOCKET_DIR. Popular non-default values like /var/run/postgresql are often not writable by the build user, requiring workarounds that will no longer be necessary.
- On Windows, allow new sessions to absorb values of PGC_BACKEND parameters (such as log_connections) from the configuration file (Amit Kapila)
- Previously, if such a parameter were changed in the file post-startup, the change would have no effect.
- Properly quote executable path names on Windows (Nikhil Deshpande)
- This oversight could cause initdb and pg_upgrade to fail on Windows, if the installation path contained both spaces and @ signs.
- Fix linking of libpython on OS X (Tom Lane)
- The method we previously used can fail with the Python library supplied by Xcode 5.0 and later.
- Avoid buffer bloat in libpq when the server consistently sends data faster than the client can absorb it (Shin-ichi Morita, Tom Lane)
- libpq could be coerced into enlarging its input buffer until it runs out of memory (which would be reported misleadingly as "lost synchronization with server"). Under ordinary circumstances it's quite far-fetched that data could be continuously transmitted more quickly than the recv() loop can absorb it, but this has been observed when the client is artificially slowed by scheduler constraints.
- Ensure that LDAP lookup attempts in libpq time out as intended (Laurenz Albe)
- Fix pg_restore's processing of old-style large object comments (Tom Lane)
- A direct-to-database restore from an archive file generated by a pre-9.0 version of pg_dump would usually fail if the archive contained more than a few comments for large objects.
- In contrib/pgcrypto functions, ensure sensitive information is cleared from stack variables before returning (Marko Kreen)
- In contrib/uuid-ossp, cache the state of the OSSP UUID library across calls (Tom Lane)
- This improves the efficiency of UUID generation and reduces the amount of entropy drawn from /dev/urandom, on platforms that have that.
- Update time zone data files to tzdata release 2014e for DST law changes in Crimea, Egypt, and Morocco.
更新時間：2014-07-27
更新細節：
What's new in this version:
- Correctly initialize padding bytes in contrib/btree_gist indexes on bit columns (Heikki Linnakangas)
- This error could result in incorrect query results due to values that should compare equal not being seen as equal. Users with GiST indexes on bit or bit varying columns should REINDEX those indexes after installing this update.
- Protect against torn pages when deleting GIN list pages (Heikki Linnakangas)
- This fix prevents possible index corruption if a system crash occurs while the page update is being written to disk.
- Fix possibly-incorrect cache invalidation during nested calls to ReceiveSharedInvalidMessages (Andres Freund)
- Don't assume a subquery's output is unique if there's a set-returning function in its targetlist (David Rowley)
- This oversight could lead to misoptimization of constructs like WHERE x IN (SELECT y, generate_series(1,10) FROM t GROUP BY y).
- Fix failure to detoast fields in composite elements of structured types (Tom Lane)
- This corrects cases where TOAST pointers could be copied into other tables without being dereferenced. If the original data is later deleted, it would lead to errors like "missing chunk number 0 for toast value ..." when the now-dangling pointer is used.
- Fix "record type has not been registered" failures with whole-row references to the output of Append plan nodes (Tom Lane)
- Fix possible crash when invoking a user-defined function while rewinding a cursor (Tom Lane)
- Fix query-lifespan memory leak while evaluating the arguments for a function in FROM (Tom Lane)
- Fix session-lifespan memory leaks in regular-expression processing (Tom Lane, Arthur O'Dwyer, Greg Stark)
- Fix data encoding error in hungarian.stop (Tom Lane)
- Fix liveness checks for rows that were inserted in the current transaction and then deleted by a now-rolled-back subtransaction (Andres Freund)
- This could cause problems (at least spurious warnings, and at worst an infinite loop) if CREATE INDEX or CLUSTER were done later in the same transaction.
- Clear pg_stat_activity.xact_start during PREPARE TRANSACTION (Andres Freund)
- After the PREPARE, the originating session is no longer in a transaction, so it should not continue to display a transaction start time.
- Fix REASSIGN OWNED to not fail for text search objects (Álvaro Herrera)
- Block signals during postmaster startup (Tom Lane)
- This ensures that the postmaster will properly clean up after itself if, for example, it receives SIGINT while still starting up.
- Secure Unix-domain sockets of temporary postmasters started during make check (Noah Misch)
- Any local user able to access the socket file could connect as the server's bootstrap superuser, then proceed to execute arbitrary code as the operating-system user running the test, as we previously noted in CVE-2014-0067. This change defends against that risk by placing the server's socket in a temporary, mode 0700 subdirectory of /tmp. The hazard remains however on platforms where Unix sockets are not supported, notably Windows, because then the temporary postmaster must accept local TCP connections.
- A useful side effect of this change is to simplify make check testing in builds that override DEFAULT_PGSOCKET_DIR. Popular non-default values like /var/run/postgresql are often not writable by the build user, requiring workarounds that will no longer be necessary.
- On Windows, allow new sessions to absorb values of PGC_BACKEND parameters (such as log_connections) from the configuration file (Amit Kapila)
- Previously, if such a parameter were changed in the file post-startup, the change would have no effect.
- Properly quote executable path names on Windows (Nikhil Deshpande)
- This oversight could cause initdb and pg_upgrade to fail on Windows, if the installation path contained both spaces and @ signs.
- Fix linking of libpython on OS X (Tom Lane)
- The method we previously used can fail with the Python library supplied by Xcode 5.0 and later.
- Avoid buffer bloat in libpq when the server consistently sends data faster than the client can absorb it (Shin-ichi Morita, Tom Lane)
- libpq could be coerced into enlarging its input buffer until it runs out of memory (which would be reported misleadingly as "lost synchronization with server"). Under ordinary circumstances it's quite far-fetched that data could be continuously transmitted more quickly than the recv() loop can absorb it, but this has been observed when the client is artificially slowed by scheduler constraints.
- Ensure that LDAP lookup attempts in libpq time out as intended (Laurenz Albe)
- Fix pg_restore's processing of old-style large object comments (Tom Lane)
- A direct-to-database restore from an archive file generated by a pre-9.0 version of pg_dump would usually fail if the archive contained more than a few comments for large objects.
- In contrib/pgcrypto functions, ensure sensitive information is cleared from stack variables before returning (Marko Kreen)
- In contrib/uuid-ossp, cache the state of the OSSP UUID library across calls (Tom Lane)
- This improves the efficiency of UUID generation and reduces the amount of entropy drawn from /dev/urandom, on platforms that have that.
- Update time zone data files to tzdata release 2014e for DST law changes in Crimea, Egypt, and Morocco.
更新時間：2014-07-25
更新細節：
What's new in this version:
In pg_upgrade, remove pg_multixact files left behind by initdb:
- If you used a pre-9.3.5 version of pg_upgrade to upgrade a database cluster to 9.3, it might have left behind a file $PGDATA/pg_multixact/offsets/0000 that should not be there and will eventually cause problems in VACUUM. However, in common cases this file is actually valid and must not be removed. Correctly initialize padding bytes in contrib/btree_gist indexes on bit columns:
- This error could result in incorrect query results due to values that should compare equal not being seen as equal. Users with GiST indexes on bit or bit varying - columns should REINDEX those indexes after installing this update. Protect against torn pages when deleting GIN list pages:
- This fix prevents possible index corruption if a system crash occurs while the page update is being written to disk.
- Don't clear the right-link of a GiST index page while replaying updates from WAL:
- This error could lead to transiently wrong answers from GiST index scans performed in Hot Standby.
- Fix corner-case infinite loop during insertion into an SP-GiST text index
- Fix incorrect answers from SP-GiST index searches with -|- (range adjacency) operator
- Fix wraparound handling for pg_multixact/members Truncate pg_multixact during checkpoints, not during VACUUM:
- This change ensures that pg_multixact segments can't be removed if they'd still be needed during WAL replay after a crash.
- Fix possible inconsistency of all-visible flags after WAL recovery
- Fix possibly-incorrect cache invalidation during nested calls to ReceiveSharedInvalidMessages
- Fix race condition when updating a tuple concurrently locked by another process
- Fix "could not find pathkey item to sort" planner failures with UNION ALL over subqueries reading from tables with inheritance children Don't assume a subquery's output is unique if there's a set-returning function in its targetlist:
- This oversight could lead to misoptimization of constructs like WHERE x IN (SELECT y, generate_series(1,10) FROM t GROUP BY y). Improve planner to drop constant-NULL inputs of AND/OR when possible:
- This change fixes some cases where the more aggressive parameter substitution done by 9.2 and later can lead to a worse plan than older versions produced.
- Ensure that the planner sees equivalent VARIADIC and non-VARIADIC function calls as equivalent:
- This bug could for example result in failure to use expression indexes involving variadic functions. It might be necessary to re-create such indexes, and/or re-create views including variadic function calls that should match the indexes, for the fix to be effective for existing 9.3 installations. Fix handling of nested JSON objects in json_populate_recordset() and friends:
- A nested JSON object could result in previous fields of the parent object not being shown in the output. Fix identification of input type category in to_json() and friends:
- This is known to have led to inadequate quoting of money fields in the JSON result, and there may have been wrong results for other data types as well. Fix failure to detoast fields in composite elements of structured types:
- This corrects cases where TOAST pointers could be copied into other tables without being dereferenced. If the original data is later deleted, it would lead to errors like "missing chunk number 0 for toast value ..." when the now-dangling pointer is used.
- Fix "record type has not been registered" failures with whole-row references to the output of Append plan nodes
- Fix possible crash when invoking a user-defined function while rewinding a cursor
- Fix query-lifespan memory leak while evaluating the arguments for a function in FROM
- Fix session-lifespan memory leaks in regular-expression processing
- Fix data encoding error in hungarian.stop
- Prevent foreign tables from being created with OIDS when default_with_oids is true Fix liveness checks for rows that were inserted in the current transaction and then deleted by a now-rolled-back subtransaction:
- This could cause problems (at least spurious warnings, and at worst an infinite loop) if CREATE INDEX or CLUSTER were done later in the same transaction. Clear pg_stat_activity.xact_start during PREPARE TRANSACTION:
- After the PREPARE, the originating session is no longer in a transaction, so it should not continue to display a transaction start time.
- Fix REASSIGN OWNED to not fail for text search objects
- Prevent pg_class.relminmxid values from going backwards during VACUUM FULL Reduce indentation in rule/view dumps to improve readability and avoid excessive whitespace:
- This change reduces the amount of indentation applied to nested constructs, including some cases that the user probably doesn't think of as nested, such as UNION lists. Previously, deeply nested constructs were printed with an amount of whitespace growing as O(N^2), which created a performance problem and even risk of out-of-memory failures. Now the indentation is reduced modulo 40, which is initially odd to look at but seems to preserve readability better than simply limiting the indentation would do. Redundant parenthesization of UNION lists has been reduced as well.
- Fix dumping of rules/views when subsequent addition of a column has resulted in multiple input columns matching a USING specification
- Repair view printing for some cases involving functions in FROM that return a composite type containing dropped columns Block signals during postmaster startup:
- This ensures that the postmaster will properly clean up after itself if, for example, it receives SIGINT while still starting up. Fix client host name lookup when processing pg_hba.conf entries that specify host names instead of IP addresses:
- Ensure that reverse-DNS lookup failures are reported, instead of just silently not matching such entries. Also ensure that we make only one reverse-DNS lookup attempt per connection, not one per host name entry, which is what previously happened if the lookup attempts failed. Allow the root user to use postgres -C variable and postgres --describe-config:
- The prohibition on starting the server as root does not need to extend to these operations, and relaxing it prevents failure of pg_ctl in some scenarios. Secure Unix-domain sockets of temporary postmasters started during make check:
- Any local user able to access the socket file could connect as the server's bootstrap superuser, then proceed to execute arbitrary code as the operating-system user running the test, as we previously noted in CVE-2014-0067. This change defends against that risk by placing the server's socket in a temporary, mode 0700 subdirectory of /tmp. The hazard remains however on platforms where Unix sockets are not supported, notably Windows, because then the temporary postmaster must accept local TCP connections.
- A useful side effect of this change is to simplify make check testing in builds that override DEFAULT_PGSOCKET_DIR. Popular non-default values like /var/run/postgresql are often not writable by the build user, requiring workarounds that will no longer be necessary.
- Fix tablespace creation WAL replay to work on Windows
- Fix detection of socket creation failures on Windows On Windows, allow new sessions to absorb values of PGC_BACKEND parameters (such as log_connections) from the configuration file:
- Previously, if such a parameter were changed in the file post-startup, the change would have no effect. Properly quote executable path names on Windows:
- This oversight could cause initdb and pg_upgrade to fail on Windows, if the installation path contained both spaces and @ signs. Avoid buffer bloat in libpq when the server consistently sends data faster than the client can absorb it:
- libpq could be coerced into enlarging its input buffer until it runs out of memory (which would be reported misleadingly as "lost synchronization with server"). - Under ordinary circumstances it's quite far-fetched that data could be continuously transmitted more quickly than the recv() loop can absorb it, but this has been observed when the client is artificially slowed by scheduler constraints.
- Ensure that LDAP lookup attempts in libpq time out as intended
- Fix ecpg to do the right thing when an array of char * is the target for a FETCH statement returning more than one row, as well as some other array-handling fixes Fix pg_dump to cope with a materialized view that depends on a table's primary key:
- This occurs if the view's query relies on functional dependency to abbreviate a GROUP BY list. pg_dump got sufficiently confused that it dumped the materialized view as a regular view.
- Fix parsing of pg_dumpall's -i switch Fix pg_restore's processing of old-style large object comments:
- A direct-to-database restore from an archive file generated by a pre-9.0 version of pg_dump would usually fail if the archive contained more than a few comments for large objects. Fix pg_upgrade for cases where the new server creates a TOAST table but the old version did not:
- This rare situation would manifest as "relation OID mismatch" errors. In pg_upgrade, preserve pg_database.datminmxid and pg_class.relminmxid values from the old cluster, or insert reasonable values when upgrading from pre-9.3; also defend against unreasonable values in the core server:
- These changes prevent scenarios in which autovacuum might insist on scanning the entire cluster's contents immediately upon starting the new cluster, or in which tracking of unfrozen MXID values might be disabled completely. Prevent contrib/auto_explain from changing the output of a user's EXPLAIN:
- If auto_explain is active, it could cause an EXPLAIN (ANALYZE, TIMING OFF) command to nonetheless print timing information.
- Fix query-lifespan memory leak in contrib/dblink
- In contrib/pgcrypto functions, ensure sensitive information is cleared from stack variables before returning
- Prevent use of already-freed memory in contrib/pgstattuple's pgstat_heap() In contrib/uuid-ossp, cache the state of the OSSP UUID library across calls:
- This improves the efficiency of UUID generation and reduces the amount of entropy drawn from /dev/urandom, on platforms that have that.
- Update time zone data files to tzdata release 2014e for DST law changes in Crimea, Egypt, and Morocco.
更新時間：2014-03-21
更新細節：
What's new in this version:
Fix WAL replay of locking an already-updated tuple:
- This error caused updated rows to not be found by index scans, resulting in inconsistent query results depending on whether an index scan was used. Subsequent processing could result in constraint violations, since the previously updated row would not be found by later index searches, thus possibly allowing conflicting rows to be inserted. Since this error is in WAL replay, it would only manifest during crash recovery or on standby servers. The improperly-replayed case most commonly arises when a table row that is referenced by a foreign-key constraint is updated concurrently with creation of a referencing row. Restore GIN metapages unconditionally to avoid torn-page risk:
- Although this oversight could theoretically result in a corrupted index, it is unlikely to have caused any problems in practice, since the active part of a GIN metapage is smaller than a standard 512-byte disk sector. Avoid race condition in checking transaction commit status during receipt of a NOTIFY message:
- This prevents a scenario wherein a sufficiently fast client might respond to a notification before database updates made by the notifier have become visible to the recipient. Allow materialized views to be referenced in UPDATE and DELETE commands:
- Previously such queries failed with a complaint about not being able to lock rows in the materialized view. Allow regular-expression operators to be terminated early by query cancel requests:
- This prevents scenarios wherein a pathological regular expression could lock up a server process uninterruptably for a long time. Remove incorrect code that tried to allow OVERLAPS with single-element row arguments:
- This code never worked correctly, and since the case is neither specified by the SQL standard nor documented, it seemed better to remove it than fix it. Avoid getting more than AccessShareLock when de-parsing a rule or view:
- This oversight resulted in pg_dump unexpectedly acquiring RowExclusiveLock locks on tables mentioned as the targets of INSERT/UPDATE/DELETE commands in rules. While usually harmless, that could interfere with concurrent transactions that tried to acquire, for example, ShareLock on those tables. Improve performance of index endpoint probes during planning:
- This change fixes a significant performance problem that occurred when there were many not-yet-committed rows at the end of the index, which is a common situation for indexes on sequentially-assigned values such as timestamps or sequence-generated identifiers. Remove the correct per-database statistics file during DROP DATABASE:
- This fix prevents a permanent leak of statistics file space. Users who have done many DROP DATABASE commands since upgrading to PostgreSQL 9.3 may wish to check their statistics directory and delete statistics files that do not correspond to any existing database. Please note that db_0.stat should not be removed. Fix walsender ping logic to avoid inappropriate disconnects under continuous load:
- walsender failed to send ping messages to the client if it was constantly busy sending WAL data; but it expected to see ping responses despite that, and would therefore disconnect once wal_sender_timeout elapsed. Add read-only data_checksums parameter to display whether page checksums are enabled:
- Without this parameter, determining the state of checksum processing was difficult. Prevent interrupts while reporting non-ERROR messages:
- This guards against rare server-process freezeups due to recursive entry to syslog(), and perhaps other related problems. Fix tracking of psql script line numbers during copy from out-of-line data:
- copy ... from incremented the script file line number for each data line, even if the data was not coming from the script file. This mistake resulted in wrong line numbers being reported for any errors occurring later in the same script file. Fix contrib/postgres_fdw to handle multiple join conditions properly:
- This oversight could result in sending WHERE clauses to the remote server for execution even though the clauses are not known to have the same semantics on the remote server (for example, clauses that use non-built-in operators). The query might succeed anyway, but it could also fail with errors from the remote server, or worse give silently wrong answers.
- Use non-default selectivity estimates for value IN (list) and value operator ANY (array) expressions when the righthand side is a stable expression
- Fix walsender's failure to shut down cleanly when client is pg_receivexlog
- Check WAL level and hot standby parameters correctly when doing crash recovery that will be followed by archive recovery
- Fix test to see if hot standby connections can be allowed immediately after a crash
- Fix memory leak in PL/Perl when returning a composite result, including multiple-OUT-parameter cases
- Prevent intermittent "could not reserve shared memory region" failures on recent Windows versions
- Update time zone data files to tzdata release 2014a for DST law changes in Fiji and Turkey, plus historical changes in Israel and Ukraine.
更新時間：2014-02-21
更新細節：
What's new in this version:
Shore up GRANT ... WITH ADMIN OPTION restrictions:
- Granting a role without ADMIN OPTION is supposed to prevent the grantee from adding or removing members from the granted role, but this restriction was easily bypassed by doing SET ROLE first. The security impact is mostly that a role member can revoke the access of others, contrary to the wishes of his grantor. Unapproved role member additions are a lesser concern, since an uncooperative role member could provide most of his rights to others anyway by creating views or SECURITY DEFINER functions. (CVE-2014-0060) Prevent privilege escalation via manual calls to PL validator functions (Andres Freund):
- The primary role of PL validator functions is to be called implicitly during CREATE FUNCTION, but they are also normal SQL functions that a user can call explicitly. Calling a validator on a function actually written in some other language was not checked for and could be exploited for privilege-escalation purposes. The fix involves adding a call to a privilege-checking function in each validator function. Non-core procedural languages will also need to make this change to their own validator functions, if any. (CVE-2014-0061) Avoid multiple name lookups during table and index DDL (Robert Haas, Andres Freund):
- If the name lookups come to different conclusions due to concurrent activity, we might perform some parts of the DDL on a different table than other parts. At least in the case of CREATE INDEX, this can be used to cause the permissions checks to be performed against a different table than the index creation, allowing for a privilege escalation attack. (CVE-2014-0062) Prevent buffer overrun with long datetime strings (Noah Misch):
- The MAXDATELEN constant was too small for the longest possible value of type interval, allowing a buffer overrun in interval_out(). Although the datetime input functions were more careful about avoiding buffer overrun, the limit was short enough to cause them to reject some valid inputs, such as input containing a very long timezone name. The ecpg library contained these vulnerabilities along with some of its own. (CVE-2014-0063) Prevent buffer overrun due to integer overflow in size calculations (Noah Misch, Heikki Linnakangas):
- Several functions, mostly type input functions, calculated an allocation size without checking for overflow. If overflow did occur, a too-small buffer would be allocated and then written past. (CVE-2014-0064) Prevent overruns of fixed-size buffers (Peter Eisentraut, Jozef Mlich):
- Use strlcpy() and related functions to provide a clear guarantee that fixed-size buffers are not overrun. Unlike the preceding items, it is unclear whether these cases really represent live issues, since in most cases there appear to be previous constraints on the size of the input string. Nonetheless it seems prudent to silence all Coverity warnings of this type. (CVE-2014-0065) Avoid crashing if crypt() returns NULL (Honza Horak, Bruce Momjian):
- There are relatively few scenarios in which crypt() could return NULL, but contrib/chkpass would crash if it did. One practical case in which this could be an issue is if libc is configured to refuse to execute unapproved hashing algorithms (e.g., "FIPS mode"). (CVE-2014-0066) Document risks of make check in the regression testing instructions (Noah Misch, Tom Lane):
- Since the temporary server started by make check uses "trust" authentication, another user on the same machine could connect to it as database superuser, and then potentially exploit the privileges of the operating-system user who started the tests. A future release will probably incorporate changes in the testing procedure to prevent this risk, but some public discussion is needed first. So for the moment, just warn people against using make check when there are untrusted users on the same machine. (CVE-2014-0067) Rework tuple freezing protocol (Álvaro Herrera, Andres Freund):
- The logic for tuple freezing was unable to handle some cases involving freezing of multixact IDs, with the practical effect that shared row-level locks might be forgotten once old enough.
- Fixing this required changing the WAL record format for tuple freezing. While this is no issue for standalone servers, when using replication it means that standby servers must be upgraded to 9.3.3 or later before their masters are. An older standby will be unable to interpret freeze records generated by a newer master, and will fail with a PANIC message. (In such a case, upgrading the standby should be sufficient to let it resume execution.) Create separate GUC parameters to control multixact freezing (Álvaro Herrera):
- 9.3 requires multixact tuple labels to be frozen before they grow too old, in the same fashion as plain transaction ID labels have been frozen for some time. Previously, the transaction ID freezing parameters were used for multixact IDs too; but since the consumption rates of transaction IDs and multixact IDs can be quite different, this did not work very well. Introduce new settings vacuum_multixact_freeze_min_age, vacuum_multixact_freeze_table_age, and autovacuum_multixact_freeze_max_age to control when to freeze multixacts. Account for remote row locks propagated by local updates (Álvaro Herrera):
- If a row was locked by transaction A, and transaction B updated it, the new version of the row created by B would be locked by A, yet visible only to B. If transaction B then again updated the row, A's lock wouldn't get checked, thus possibly allowing B to complete when it shouldn't. This case is new in 9.3 since prior versions did not have any types of row locking that would permit another transaction to update the row at all.
- This oversight could allow referential integrity checks to give false positives (for instance, allow deletes that should have been rejected). Applications using the new commands SELECT FOR KEY SHARE and SELECT FOR NO KEY UPDATE might also have suffered locking failures of this kind. Prevent "forgetting" valid row locks when one of several holders of a row lock aborts (Álvaro Herrera):
- This was yet another mechanism by which a shared row lock could be lost, thus possibly allowing updates that should have been prevented by foreign-key constraints. Fix incorrect logic during update chain locking (Álvaro Herrera):
- This mistake could result in spurious "could not serialize access due to concurrent update" errors in REPEATABLE READ and SERIALIZABLE transaction isolation modes.
- Handle wraparound correctly during extension or truncation of pg_multixact/members (Andres Freund, Álvaro Herrera) Fix handling of 5-digit filenames in pg_multixact/members (Álvaro Herrera):
- As of 9.3, these names can be more than 4 digits, but the directory cleanup code ignored such files.
- Improve performance of multixact cache code (Álvaro Herrera)
- Optimize updating a row that's already locked by the same transaction (Andres Freund, Álvaro Herrera)
- This fixes a performance regression from pre-9.3 versions when doing SELECT FOR UPDATE followed by UPDATE/DELETE.
- During archive recovery, prefer highest timeline number when WAL segments with the same ID are present in both the archive and pg_xlog/ (Kyotaro Horiguchi)
- Previously, not-yet-archived segments could get ignored during recovery. This reverts an undesirable behavioral change in 9.3.0 back to the way things worked pre-9.3. Fix possible mis-replay of WAL records when some segments of a relation aren't full size (Greg Stark, Tom Lane):
- The WAL update could be applied to the wrong page, potentially many pages past where it should have been. Aside from corrupting data, this error has been observed to result in significant "bloat" of standby servers compared to their masters, due to updates being applied far beyond where the end-of-file should have been. This failure mode does not appear to be a significant risk during crash recovery, only when initially synchronizing a standby created from a base backup taken from a quickly-changing master. Fix bug in determining when recovery has reached consistency (Tomonari Katsumata, Heikki Linnakangas):
- In some cases WAL replay would mistakenly conclude that the database was already consistent at the start of replay, thus possibly allowing hot-standby queries before the database was really consistent. Other symptoms such as "PANIC: WAL contains references to invalid pages" were also possible. Fix WAL logging of visibility map changes (Heikki Linnakangas):
- Fix improper locking of btree index pages while replaying a VACUUM operation in hot-standby mode (Andres Freund, Heikki Linnakangas, Tom Lane)
- This error could result in "PANIC: WAL contains references to invalid pages" failures. Ensure that insertions into non-leaf GIN index pages write a full-page WAL record when appropriate (Heikki Linnakangas):
- The previous coding risked index corruption in the event of a partial-page write during a system crash.
- When pause_at_recovery_target and recovery_target_inclusive are both set, ensure the target record is applied before pausing, not after (Heikki Linnakangas)
- Ensure walreceiver sends hot-standby feedback messages on time even when there is a continuous stream of data (Andres Freund, Amit Kapila)
- Prevent timeout interrupts from taking control away from mainline code unless ImmediateInterruptOK is set (Andres Freund, Tom Lane)
- This is a serious issue for any application making use of statement timeouts, as it could cause all manner of strange failures after a timeout occurred. We have seen reports of "stuck" spinlocks, ERRORs being unexpectedly promoted to PANICs, unkillable backends, and other misbehaviors.
- Fix race conditions during server process exit (Robert Haas)
- Ensure that signal handlers don't attempt to use the process's MyProc pointer after it's no longer valid.
- Fix race conditions in walsender shutdown logic and walreceiver SIGHUP signal handler (Tom Lane)
- Fix unsafe references to errno within error reporting logic (Christian Kruse)
- This would typically lead to odd behaviors such as missing or inappropriate HINT fields.
- Fix possible crashes from using ereport() too early during server startup (Tom Lane)
- The principal case we've seen in the field is a crash if the server is started in a directory it doesn't have permission to read.
- Clear retry flags properly in OpenSSL socket write function (Alexander Kukushkin)
- This omission could result in a server lockup after unexpected loss of an SSL-encrypted connection.
- Fix length checking for Unicode identifiers (U&"..." syntax) containing escapes (Tom Lane)
- A spurious truncation warning would be printed for such identifiers if the escaped form of the identifier was too long, but the identifier actually didn't need truncation after de-escaping.
- Fix parsing of Unicode literals and identifiers just before the end of a command string or function body (Tom Lane)
- Allow keywords that are type names to be used in lists of roles (Stephen Frost)
- A previous patch allowed such keywords to be used without quoting in places such as role identifiers; but it missed cases where a list of role identifiers was permitted, such as DROP ROLE.
- Fix parser crash for EXISTS(SELECT * FROM zero_column_table) (Tom Lane)
- Fix possible crash due to invalid plan for nested sub-selects, such as WHERE (... x IN (SELECT ...) ...) IN (SELECT ...) (Tom Lane)
- Fix mishandling of WHERE conditions pulled up from a LATERAL subquery (Tom Lane)
- The typical symptom of this bug was a "JOIN qualification cannot refer to other relations" error, though subtle logic errors in created plans seem possible as well.
- Disallow LATERAL references to the target table of an UPDATE/DELETE (Tom Lane)
- While this might be allowed in some future release, it was unintentional in 9.3, and didn't work quite right anyway.
- Fix UPDATE/DELETE of an inherited target table that has UNION ALL subqueries (Tom Lane)
- Without this fix, UNION ALL subqueries aren't correctly inserted into the update plans for inheritance child tables after the first one, typically resulting in no update happening for those child table(s).
- Fix ANALYZE to not fail on a column that's a domain over a range type (Tom Lane)
- Ensure that ANALYZE creates statistics for a table column even when all the values in it are "too wide" (Tom Lane)
- ANALYZE intentionally omits very wide values from its histogram and most-common-values calculations, but it neglected to do something sane in the case that all the sampled entries are too wide.
- In ALTER TABLE ... SET TABLESPACE, allow the database's default tablespace to be used without a permissions check (Stephen Frost)
- CREATE TABLE has always allowed such usage, but ALTER TABLE didn't get the memo.
- Fix support for extensions containing event triggers (Tom Lane)
- Fix "cannot accept a set" error when some arms of a CASE return a set and others don't (Tom Lane)
- Fix memory leakage in JSON functions (Craig Ringer)
- Properly distinguish numbers from non-numbers when generating JSON output (Andrew Dunstan)
- Fix possible misclassification of multibyte characters by the text search parser (Tom Lane)
- Non-ASCII characters could be misclassified when using C locale with a multibyte encoding. On Cygwin, non-C locales could fail as well.
- Fix possible misbehavior in plainto_tsquery() (Heikki Linnakangas)
- Use memmove() not memcpy() for copying overlapping memory regions. There have been no field reports of this actually causing trouble, but it's certainly risky.
- Fix placement of permissions checks in pg_start_backup() and pg_stop_backup() (Andres Freund, Magnus Hagander)
- The previous coding might attempt to do catalog access when it shouldn't.
- Accept SHIFT_JIS as an encoding name for locale checking purposes (Tatsuo Ishii)
- Fix *-qualification of named parameters in SQL-language functions (Tom Lane)
- Given a composite-type parameter named foo, $1.* worked fine, but foo.* not so much.
- Fix misbehavior of PQhost() on Windows (Fujii Masao)
- It should return localhost if no host has been specified.
- Improve error handling in libpq and psql for failures during COPY TO STDOUT/FROM STDIN (Tom Lane)
- In particular this fixes an infinite loop that could occur in 9.2 and up if the server connection was lost during COPY FROM STDIN. Variants of that scenario might be possible in older versions, or with other client applications.
- Fix incorrect translation handling in some psql d commands (Peter Eisentraut, Tom Lane)
- Ensure pg_basebackup's background process is killed when exiting its foreground process (Magnus Hagander)
- Fix possible incorrect printing of filenames in pg_basebackup's verbose mode (Magnus Hagander)
- Avoid including tablespaces inside PGDATA twice in base backups (Dimitri Fontaine, Magnus Hagander)
- Fix misaligned descriptors in ecpg (MauMau)
- In ecpg, handle lack of a hostname in the connection parameters properly (Michael Meskes)
- Fix performance regression in contrib/dblink connection startup (Joe Conway)
- Avoid an unnecessary round trip when client and server encodings match.
- In contrib/isn, fix incorrect calculation of the check digit for ISMN values (Fabien Coelho)
- Fix contrib/pgbench's progress logging to avoid overflow when the scale factor is large (Tatsuo Ishii)
- Fix contrib/pg_stat_statement's handling of CURRENT_DATE and related constructs (Kyotaro Horiguchi)
- Improve lost-connection error handling in contrib/postgres_fdw (Tom Lane)
- Ensure client-code-only installation procedure works as documented (Peter Eisentraut)
- In Mingw and Cygwin builds, install the libpq DLL in the bin directory (Andrew Dunstan)
- This duplicates what the MSVC build has long done. It should fix problems with programs like psql failing to start because they can't find the DLL.
- Avoid using the deprecated dllwrap tool in Cygwin builds (Marco Atzeri)
- Enable building with Visual Studio 2013 (Brar Piening)
- Don't generate plain-text HISTORY and src/test/regress/README files anymore (Tom Lane)
- These text files duplicated the main HTML and PDF documentation formats. The trouble involved in maintaining them greatly outweighs the likely audience for plain-text format. Distribution tarballs will still contain files by these names, but they'll just be stubs directing the reader to consult the main documentation. The plain-text INSTALL file will still be maintained, as there is arguably a use-case for that.
- Update time zone data files to tzdata release 2013i for DST law changes in Jordan and historical changes in Cuba.
- In addition, the zones Asia/Riyadh87, Asia/Riyadh88, and Asia/Riyadh89 have been removed, as they are no longer maintained by IANA, and never represented actual civil timekeeping practice.
更新時間：2013-12-06
更新細節：
What's new in this version:
- Fix VACUUM's tests to see whether it can update relfrozenxid
- Fix multiple bugs in MultiXactId freezing
- Fix initialization of pg_clog and pg_subtrans during hot standby startup
- Fix multiple bugs in update chain traversal
- Fix dangling-pointer problem in fast-path locking
- Fix assorted race conditions in timeout management
- Truncate pg_multixact contents during WAL replay
- Ensure an anti-wraparound VACUUM counts a page as scanned when it's only verified that no tuples need freezing
- Fix full-table-vacuum request mechanism for MultiXactIds
- Fix race condition in GIN index posting tree page deletion
- Fix "unexpected spgdoinsert() failure" error during SP-GiST index creation
- Fix assorted bugs in materialized views
- Re-allow duplicate table aliases if they're within aliased JOINs
- Avoid flattening a subquery whose SELECT list contains a volatile function wrapped inside a sub-SELECT
- Fix planner's processing of non-simple-variable subquery outputs nested within outer joins
- Fix incorrect planning in cases where the same non-strict expression appears in multiple WHERE and outer JOIN equality clauses
- Fix planner crash with whole-row reference to a subquery
- Fix incorrect generation of optimized MIN()/MAX() plans for inheritance trees
- Fix premature deletion of temporary files
- Prevent intra-transaction memory leak when printing range values
- Fix memory leaks when reloading configuration files
- Prevent incorrect display of dropped columns in NOT NULL and CHECK constraint violation messages
- Allow default arguments and named-argument notation for window functions
- Suppress trailing whitespace on each line when pretty-printing rules and views
- Fix possible read past end of memory in rule printing
- Fix array slicing of int2vector and oidvector values
- Return a valid JSON value when converting an empty hstore value to json
- Fix incorrect behaviors when using a SQL-standard, simple GMT offset timezone
- Prevent possible misbehavior when logging translations of Windows error codes
- Properly quote generated command lines in pg_ctl
- Fix pg_dumpall to work when a source database sets default_transaction_read_only via ALTER DATABASE SET
- Fix pg_isready to handle its -d option properly
- Fix parsing of WAL file names in pg_receivexlog
- Report out-of-disk-space failures properly in pg_upgrade
- Make ecpg search for quoted cursor names case-sensitively
- Fix ecpg's processing of lists of variables declared varchar
- Make contrib/lo defend against incorrect trigger definitions
- Update time zone data files to tzdata release 2013h for DST law changes in Argentina, Brazil, Jordan, Libya, Liechtenstein, Morocco, and Palestine. Also, new timezone abbreviations WIB, WIT, WITA for Indonesia
更新時間：2013-10-11
更新細節：
What's new in this version:
Changes:
- Update hstore extension with JSON functionality (Andrew Dunstan)
Users who installed hstore prior to 9.3.1 must execute:
- ALTER EXTENSION hstore UPDATE;
- to add two new JSON functions and a cast.
- Fix memory leak when creating range indexes (Heikki Linnakangas)
- Serializable snapshot fixes (Kevin Grittner, Heikki Linnakangas)
- Fix libpq SSL deadlock bug (Stephen Frost)
- Fix timeline handling bugs in pg_receivexlog (Heikki Linnakangas, Andrew Gierth)
- Prevent CREATE FUNCTION from checking SET variables unless function body checking is enabled (Tom Lane)
- Remove rare inaccurate warning during vacuum of index-less tables (Heikki Linnakangas)
更新時間：2013-09-09
更新細節：
What's new in this version:
Major enhancements in PostgreSQL 9.3 include:
- Add materialized views
- Make simple views auto-updatable
- Add many features for the JSON data type, including operators and functions to extract elements from JSON values
- Implement SQL-standard LATERAL option for FROM-clause subqueries and function calls
- Allow foreign data wrappers to support writes (inserts/updates/deletes) on foreign tables
- Add a Postgres foreign data wrapper to allow access to other Postgres servers
- Add support for event triggers
- Add optional ability to checksum data pages and report corruption
- Prevent non-key-field row updates from blocking foreign key checks
- Greatly reduce System V shared memory requirements