Re: DB Restart - Mailing list pgsql-general
From | John R Pierce |
---|---|
Subject | Re: DB Restart |
Date | |
Msg-id | 4AFC5230.6090007@hogranch.com Whole thread Raw |
In response to | Re: DB Restart (Sam Jas <samjas33@yahoo.com>) |
List | pgsql-general |
Sam Jas wrote: > ... > BTW we are using postgreSQL 8.3.2. > red flag. 8.3 is up to 8.3.8, lots of updates since 8.3.2 I attach the release notes for these incremental revisions. note there are gobs of fixes in many of these releases. you can upgrade 8.3.2 directly to 8.3.8, with only the caveat that you should reindex any GiST indexes due to a fix in 8.3.5 E.6. Release 8.3.3 *Release date: *2008-06-12 This release contains one serious and one minor bug fix over 8.3.2. For information about new features in the 8.3 major release, see Section E.9 <release-8-3.html>. E.6.1. Migration to Version 8.3.3 A dump/restore is not required for those running 8.3.X. However, if you are upgrading from a version earlier than 8.3.1, see the release notes for 8.3.1. E.6.2. Changes * Make |pg_get_ruledef()| parenthesize negative constants (Tom) Before this fix, a negative constant in a view or rule might be dumped as, say, -42::integer, which is subtly incorrect: it should be (-42)::integer due to operator precedence rules. Usually this would make little difference, but it could interact with another recent patch to cause PostgreSQL to reject what had been a valid SELECT DISTINCT view query. Since this could result inpg_dump output failing to reload, it is being treated as a high-priority fix. The only released versions in which dump output is actually incorrect are 8.3.1 and 8.2.7. * Make ALTER AGGREGATE ... OWNER TO update pg_shdepend (Tom) This oversight could lead to problems if the aggregate was later involved in a DROP OWNED or REASSIGN OWNED operation. E.5. Release 8.3.4 *Release date: *2008-09-22 This release contains a variety of fixes from 8.3.3. For information about new features in the 8.3 major release, see Section E.9 <release-8-3.html>. E.5.1. Migration to Version 8.3.4 A dump/restore is not required for those running 8.3.X. However, if you are upgrading from a version earlier than 8.3.1, see the release notes for 8.3.1. E.5.2. Changes * Fix bug in btree WAL recovery code (Heikki) Recovery failed if the WAL ended partway through a page split operation. * Fix potential use of wrong cutoff XID for HOT page pruning (Alvaro) This error created a risk of corruption in system catalogs that are consulted by VACUUM: dead tuple versions might be removed too soon. The impact of this on actual database operations would be minimal, since the system doesn't follow MVCC rules while examining catalogs, but it might result in transiently wrong output from pg_dump or other client programs. * Fix potential miscalculation of datfrozenxid (Alvaro) This error may explain some recent reports of failure to remove old pg_clog data. * Fix incorrect HOT updates after pg_class is reindexed (Tom) Corruption of pg_class could occur if REINDEX TABLE pg_class was followed in the same session by an ALTER TABLE RENAME orALTER TABLE SET SCHEMA command. * Fix missed "combo cid" case (Karl Schnaitter) This error made rows incorrectly invisible to a transaction in which they had been deleted by multiple subtransactions that all aborted. * Prevent autovacuum from crashing if the table it's currently checking is deleted at just the wrong time (Alvaro) * Widen local lock counters from 32 to 64 bits (Tom) This responds to reports that the counters could overflow in sufficiently long transactions, leading to unexpected "lock is already held" errors. * Fix possible duplicate output of tuples during a GiST index scan (Teodor) * Regenerate foreign key checking queries from scratch when either table is modified (Tom) Previously, 8.3 would attempt to replan the query, but would work from previously generated query text. This led to failures if a table or column was renamed. * Fix missed permissions checks when a view contains a simple UNION ALL construct (Heikki) Permissions for the referenced tables were checked properly, but not permissions for the view itself. * Add checks in executor startup to ensure that the tuples produced by an INSERT or UPDATE will match the target table's current rowtype (Tom) This situation is believed to be impossible in 8.3, but it can happen in prior releases, so a check seems prudent. * Fix possible repeated drops during DROP OWNED (Tom) This would typically result in strange errors such as "cache lookup failed for relation NNN". * Fix several memory leaks in XML operations (Kris Jurka, Tom) * Fix |xmlserialize()| to raise error properly for unacceptable target data type (Tom) * Fix a couple of places that mis-handled multibyte characters in text search configuration file parsing (Tom) Certain characters occurring in configuration files would always cause "invalid byte sequence for encoding" failures. * Provide file name and line number location for all errors reported in text search configuration files (Tom) * Fix AT TIME ZONE to first try to interpret its timezone argument as a timezone abbreviation, and only try it as a full timezone name if that fails, rather than the other way around as formerly (Tom) The timestamp input functions have always resolved ambiguous zone names in this order. Making AT TIME ZONE do so as well improves consistency, and fixes a compatibility bug introduced in 8.1: in ambiguous cases we now behave the same as 8.0 and before did, since in the older versions AT TIME ZONE accepted /only/ abbreviations. * Fix datetime input functions to correctly detect integer overflow when running on a 64-bit platform (Tom) * Prevent integer overflows during units conversion when displaying a configuration parameter that has units (Tom) * Improve performance of writing very long log messages to syslog (Tom) * Allow spaces in the suffix part of an LDAP URL in pg_hba.conf (Tom) * Fix bug in backwards scanning of a cursor on a SELECT DISTINCT ON query (Tom) * Fix planner bug that could improperly push down IS NULL tests below an outer join (Tom) This was triggered by occurrence of IS NULL tests for the same relation in all arms of an upper OR clause. * Fix planner bug with nested sub-select expressions (Tom) If the outer sub-select has no direct dependency on the parent query, but the inner one does, the outer value might not get recalculated for new parent query rows. * Fix planner to estimate that GROUP BY expressions yielding boolean results always result in two groups, regardless of the expressions' contents (Tom) This is very substantially more accurate than the regular GROUP BY estimate for certain boolean tests like /col/ IS NULL. * Fix PL/PgSQL to not fail when a FOR loop's target variable is a record containing composite-type fields (Tom) * Fix PL/Tcl to behave correctly with Tcl 8.5, and to be more careful about the encoding of data sent to or from Tcl (Tom) * Improve performance of |PQescapeBytea()| (Rudolf Leitgeb) * On Windows, work around a Microsoft bug by preventing libpq from trying to send more than 64kB per system call (Magnus) * Fix ecpg to handle variables properly in SET commands (Michael) * Improve pg_dump and pg_restore's error reporting after failure to send a SQL command (Tom) * Fix pg_ctl to properly preserve postmaster command-line arguments across a restart (Bruce) * Fix erroneous WAL file cutoff point calculation in pg_standby (Simon) * Update time zone data files to tzdata release 2008f (for DST law changes in Argentina, Bahamas, Brazil, Mauritius, Morocco, Pakistan, Palestine, and Paraguay) E.4. Release 8.3.5 *Release date: *2008-11-03 This release contains a variety of fixes from 8.3.4. For information about new features in the 8.3 major release, see Section E.9 <release-8-3.html>. E.4.1. Migration to Version 8.3.5 A dump/restore is not required for those running 8.3.X. However, if you are upgrading from a version earlier than 8.3.1, see the release notes for 8.3.1. Also, if you were running a previous 8.3.X release, it is recommended to REINDEX all GiST indexes after the upgrade. E.4.2. Changes * Fix GiST index corruption due to marking the wrong index entry "dead" after a deletion (Teodor) This would result in index searches failing to find rows they should have found. Corrupted indexes can be fixed with REINDEX. * Fix backend crash when the client encoding cannot represent a localized error message (Tom) We have addressed similar issues before, but it would still fail if the "character has no equivalent" message itself couldn't be converted. The fix is to disable localization and send the plain ASCII error message when we detect such a situation. * Fix possible crash in bytea-to-XML mapping (Michael McMaster) * Fix possible crash when deeply nested functions are invoked from a trigger (Tom) * Improve optimization of /expression/ IN (/expression-list/) queries (Tom, per an idea from Robert Haas) Cases in which there are query variables on the right-hand side had been handled less efficiently in 8.2.x and 8.3.x than in prior versions. The fix restores 8.1 behavior for such cases. * Fix mis-expansion of rule queries when a sub-SELECT appears in a function call in FROM, a multi-row VALUES list, or a RETURNINGlist (Tom) The usual symptom of this problem is an "unrecognized node type" error. * Fix Assert failure during rescan of an IS NULL search of a GiST index (Teodor) * Fix memory leak during rescan of a hashed aggregation plan (Neil) * Ensure an error is reported when a newly-defined PL/pgSQL trigger function is invoked as a normal function (Tom) * Force a checkpoint before CREATE DATABASE starts to copy files (Heikki) This prevents a possible failure if files had recently been deleted in the source database. * Prevent possible collision of relfilenode numbers when moving a table to another tablespace with ALTER SET TABLESPACE(Heikki) The command tried to re-use the existing filename, instead of picking one that is known unused in the destination directory. * Fix incorrect text search headline generation when single query item matches first word of text (Sushant Sinha) * Fix improper display of fractional seconds in interval values when using a non-ISO datestyle in an --enable-integer-datetimesbuild (Ron Mayer) * Make ILIKE compare characters case-insensitively even when they're escaped (Andrew) * Ensure DISCARD is handled properly by statement logging (Tom) * Fix incorrect logging of last-completed-transaction time during PITR recovery (Tom) * Ensure |SPI_getvalue| and |SPI_getbinval| behave correctly when the passed tuple and tuple descriptor have different numbers of columns (Tom) This situation is normal when a table has had columns added or removed, but these two functions didn't handle it properly. The only likely consequence is an incorrect error indication. * Mark SessionReplicationRole as PGDLLIMPORT so it can be used by Slony on Windows (Magnus) * Fix small memory leak when using libpq's gsslib parameter (Magnus) The space used by the parameter string was not freed at connection close. * Ensure libgssapi is linked into libpq if needed (Markus Schaaf) * Fix ecpg's parsing of CREATE ROLE (Michael) * Fix recent breakage of pg_ctl restart (Tom) * Ensure pg_control is opened in binary mode (Itagaki Takahiro) pg_controldata and pg_resetxlog did this incorrectly, and so could fail on Windows. * Update time zone data files to tzdata release 2008i (for DST law changes in Argentina, Brazil, Mauritius, Syria) E.3. Release 8.3.6 *Release date: *2009-02-02 This release contains a variety of fixes from 8.3.5. For information about new features in the 8.3 major release, see Section E.9 <release-8-3.html>. E.3.1. Migration to Version 8.3.6 A dump/restore is not required for those running 8.3.X. However, if you are upgrading from a version earlier than 8.3.5, see the release notes for 8.3.5. E.3.2. Changes * Make DISCARD ALL release advisory locks, in addition to everything it already did (Tom) This was decided to be the most appropriate behavior. This could affect existing applications, however. * Fix whole-index GiST scans to work correctly (Teodor) This error could cause rows to be lost if a table is clustered on a GiST index. * Fix crash of xmlconcat(NULL) (Peter) * Fix possible crash in ispell dictionary if high-bit-set characters are used as flags (Teodor) This is known to be done by one widely available Norwegian dictionary, and the same condition may exist in others. * Fix misordering of pg_dump output for composite types (Tom) The most likely problem was for user-defined operator classes to be dumped after indexes or views that needed them. * Improve handling of URLs in |headline()| function (Teodor) * Improve handling of overlength headlines in |headline()| function (Teodor) * Prevent possible Assert failure or misconversion if an encoding conversion is created with the wrong conversion function for the specified pair of encodings (Tom, Heikki) * Fix possible Assert failure if a statement executed in PL/pgSQL is rewritten into another kind of statement, for example if an INSERTis rewritten into an UPDATE (Heikki) * Ensure that a snapshot is available to datatype input functions (Tom) This primarily affects domains that are declared with CHECK constraints involving user-defined stable or immutable functions. Such functions typically fail if no snapshot has been set. * Make it safer for SPI-using functions to be used within datatype I/O; in particular, to be used in domain check constraints (Tom) * Avoid unnecessary locking of small tables in VACUUM (Heikki) * Fix a problem that sometimes kept ALTER TABLE ENABLE/DISABLE RULE from being recognized by active sessions (Tom) * Fix a problem that made UPDATE RETURNING tableoid return zero instead of the correct OID (Tom) * Allow functions declared as taking ANYARRAY to work on the pg_statistic columns of that type (Tom) This used to work, but was unintentionally broken in 8.3. * Fix planner misestimation of selectivity when transitive equality is applied to an outer-join clause (Tom) This could result in bad plans for queries like ... from a left join b on a.a1 = b.b1 where a.a1 = 42 ... * Improve optimizer's handling of long IN lists (Tom) This change avoids wasting large amounts of time on such lists when constraint exclusion is enabled. * Prevent synchronous scan during GIN index build (Tom) Because GIN is optimized for inserting tuples in increasing TID order, choosing to use a synchronous scan could slow the build by a factor of three or more. * Ensure that the contents of a holdable cursor don't depend on the contents of TOAST tables (Tom) Previously, large field values in a cursor result might be represented as TOAST pointers, which would fail if the referenced table got dropped before the cursor is read, or if the large value is deleted and then vacuumed away. This cannot happen with an ordinary cursor, but it could with a cursor that is held past its creating transaction. * Fix memory leak when a set-returning function is terminated without reading its whole result (Tom) * Fix encoding conversion problems in XML functions when the database encoding isn't UTF-8 (Tom) * Fix contrib/dblink's |dblink_get_result(text,bool)| function (Joe) * Fix possible garbage output from contrib/sslinfo functions (Tom) * Fix incorrect behavior of contrib/tsearch2 compatibility trigger when it's fired more than once in a command (Teodor) * Fix possible mis-signaling in autovacuum (Heikki) * Support running as a service on Windows 7 beta (Dave and Magnus) * Fix ecpg's handling of varchar structs (Michael) * Fix configure script to properly report failure when unable to obtain linkage information for PL/Perl (Andrew) * Make all documentation reference pgsql-bugs and/or pgsql-hackers as appropriate, instead of the now-decommissioned pgsql-ports and pgsql-patches mailing lists (Tom) * Update time zone data files to tzdata release 2009a (for Kathmandu and historical DST corrections in Switzerland, Cuba) E.2. Release 8.3.7 *Release date: *2009-03-16 This release contains a variety of fixes from 8.3.6. For information about new features in the 8.3 major release, see Section E.9 <release-8-3.html>. E.2.1. Migration to Version 8.3.7 A dump/restore is not required for those running 8.3.X. However, if you are upgrading from a version earlier than 8.3.5, see the release notes for 8.3.5. E.2.2. Changes * Prevent error recursion crashes when encoding conversion fails (Tom) This change extends fixes made in the last two minor releases for related failure scenarios. The previous fixes were narrowly tailored for the original problem reports, but we have now recognized that /any/ error thrown by an encoding conversion function could potentially lead to infinite recursion while trying to report the error. The solution therefore is to disable translation and encoding conversion and report the plain-ASCII form of any error message, if we find we have gotten into a recursive error reporting situation. (CVE-2009-0922) * Disallow CREATE CONVERSION with the wrong encodings for the specified conversion function (Heikki) This prevents one possible scenario for encoding conversion failure. The previous change is a backstop to guard against other kinds of failures in the same area. * Fix |xpath()| to not modify the path expression unless necessary, and to make a saner attempt at it when necessary (Andrew) The SQL standard suggests that |xpath| should work on data that is a document fragment, but libxml doesn't support that, and indeed it's not clear that this is sensible according to the XPath standard. |xpath| attempted to work around this mismatch by modifying both the data and the path expression, but the modification was buggy and could cause valid searches to fail. Now,|xpath| checks whether the data is in fact a well-formed document, and if so invokes libxml with no change to the data or path expression. Otherwise, a different modification method that is somewhat less likely to fail is used. *Note: *The new modification method is still not 100% satisfactory, and it seems likely that no real solution is possible. This patch should therefore be viewed as a band-aid to keep from breaking existing applications unnecessarily. It is likely that PostgreSQL 8.4 will simply reject use of |xpath| on data that is not a well-formed document. * Fix core dump when |to_char()| is given format codes that are inappropriate for the type of the data argument (Tom) * Fix possible failure in text search when C locale is used with a multi-byte encoding (Teodor) Crashes were possible on platforms where wchar_t is narrower than int; Windows in particular. * Fix extreme inefficiency in text search parser's handling of an email-like string containing multiple @ characters (Heikki) * Fix planner problem with sub-SELECT in the output list of a larger subquery (Tom) The known symptom of this bug is a "failed to locate grouping columns" error that is dependent on the datatype involved; but there could be other issues as well. * Fix decompilation of CASE WHEN with an implicit coercion (Tom) This mistake could lead to Assert failures in an Assert-enabled build, or an "unexpected CASE WHEN clause" error message in other cases, when trying to examine or dump a view. * Fix possible misassignment of the owner of a TOAST table's rowtype (Tom) If CLUSTER or a rewriting variant of ALTER TABLE were executed by someone other than the table owner, the pg_type entry for the table's TOAST table would end up marked as owned by that someone. This caused no immediate problems, since the permissions on the TOAST rowtype aren't examined by any ordinary database operation. However, it could lead to unexpected failures if one later tried to drop the role that issued the command (in 8.1 or 8.2), or "owner of data type appears to be invalid"warnings from pg_dump after having done so (in 8.3). * Change UNLISTEN to exit quickly if the current session has never executed any LISTEN command (Tom) Most of the time this is not a particularly useful optimization, but since DISCARD ALL invokes UNLISTEN, the previous coding caused a substantial performance problem for applications that made heavy use of DISCARD ALL. * Fix PL/pgSQL to not treat INTO after INSERT as an INTO-variables clause anywhere in the string, not only at the start; in particular, don't fail for INSERT INTO within CREATE RULE (Tom) * Clean up PL/pgSQL error status variables fully at block exit (Ashesh Vashi and Dave Page) This is not a problem for PL/pgSQL itself, but the omission could cause the PL/pgSQL Debugger to crash while examining the state of a function. * Retry failed calls to |CallNamedPipe()| on Windows (Steve Marshall, Magnus) It appears that this function can sometimes fail transiently; we previously treated any failure as a hard error, which could confuseLISTEN/NOTIFY as well as other operations. * Add MUST (Mauritius Island Summer Time) to the default list of known timezone abbreviations (Xavier Bugaud) E.1. Release 8.3.8 *Release date: *2009-09-09 This release contains a variety of fixes from 8.3.7. For information about new features in the 8.3 major release, see Section E.9 <release-8-3.html>. E.1.1. Migration to Version 8.3.8 A dump/restore is not required for those running 8.3.X. However, if you have any hash indexes on interval columns, you must REINDEXthem after updating to 8.3.8. Also, if you are upgrading from a version earlier than 8.3.5, see the release notes for 8.3.5. E.1.2. Changes * Fix Windows shared-memory allocation code (Tsutomu Yamada, Magnus) This bug led to the often-reported "could not reattach to shared memory" error message. * Force WAL segment switch during |pg_start_backup()| (Heikki) This avoids corner cases that could render a base backup unusable. * Disallow RESET ROLE and RESET SESSION AUTHORIZATION inside security-definer functions (Tom, Heikki) This covers a case that was missed in the previous patch that disallowed SET ROLE and SET SESSION AUTHORIZATION inside security-definer functions. (See CVE-2007-6600) * Make LOAD of an already-loaded loadable module into a no-op (Tom) Formerly, LOAD would attempt to unload and re-load the module, but this is unsafe and not all that useful. * Disallow empty passwords during LDAP authentication (Magnus) * Fix handling of sub-SELECTs appearing in the arguments of an outer-level aggregate function (Tom) * Fix bugs associated with fetching a whole-row value from the output of a Sort or Materialize plan node (Tom) * Prevent synchronize_seqscans from changing the results of scrollable and WITH HOLD cursors (Tom) * Revert planner change that disabled partial-index and constraint exclusion optimizations when there were more than 100 clauses in an AND or OR list (Tom) * Fix hash calculation for data type interval (Tom) This corrects wrong results for hash joins on interval values. It also changes the contents of hash indexes on interval columns. If you have any such indexes, you must REINDEX them after updating. * Treat |to_char(..., 'TH')| as an uppercase ordinal suffix with 'HH'/'HH12' (Heikki) It was previously handled as 'th' (lowercase). * Fix overflow for INTERVAL '/x/ ms' when /x/ is more than 2 million and integer datetimes are in use (Alex Hunsaker) * Fix calculation of distance between a point and a line segment (Tom) This led to incorrect results from a number of geometric operators. * Fix money data type to work in locales where currency amounts have no fractional digits, e.g. Japan (Itagaki Takahiro) * Fix LIKE for case where pattern contains %_ (Tom) * Properly round datetime input like 00:12:57.9999999999999999999999999999 (Tom) * Fix memory leaks in XML operations (Tom) * Fix poor choice of page split point in GiST R-tree operator classes (Teodor) * Ensure that a "fast shutdown" request will forcibly terminate open sessions, even if a "smart shutdown" was already in progress (Fujii Masao) * Avoid performance degradation in bulk inserts into GIN indexes when the input values are (nearly) in sorted order (Tom) * Correctly enforce NOT NULL domain constraints in some contexts in PL/pgSQL (Tom) * Fix portability issues in plperl initialization (Andrew Dunstan) * Fix pg_ctl to not go into an infinite loop if postgresql.conf is empty (Jeff Davis) * Improve pg_dump's efficiency when there are many large objects (Tamas Vincze) * Use SIGUSR1, not SIGQUIT, as the failover signal for pg_standby (Heikki) * Make pg_standby's maxretries option behave as documented (Fujii Masao) * Make contrib/hstore throw an error when a key or value is too long to fit in its data structure, rather than silently truncating it (Andrew Gierth) * Fix contrib/xml2's |xslt_process()| to properly handle the maximum number of parameters (twenty) (Tom) * Improve robustness of libpq's code to recover from errors during COPY FROM STDIN (Tom) * Avoid including conflicting readline and editline header files when both libraries are installed (Zdenek Kotala) * Update time zone data files to tzdata release 2009l for DST law changes in Bangladesh, Egypt, Jordan, Pakistan, Argentina/San_Luis, Cuba, Jordan (historical correction only), Mauritius, Morocco, Palestine, Syria, Tunisia.
pgsql-general by date: