Re: DB Restart

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.