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.