E.49.1. Migration to version 7.4.2

A dump/restore is not required for those running 7.4.X. However, it might be advisable as the easiest method of incorporating fixes for two errors that have been found in the initial contents of 7.4.X system catalogs. A dump/initdb/reload sequence using 7.4.2's initdb will automatically correct these problems.

The more severe of the two errors is that data type anyarray has the wrong alignment label; this is a problem because the pg_statistic system catalog uses anyarray columns. The mislabeling can cause planner misestimations and even crashes when planning queries that involve WHERE clauses on double-aligned columns (such as float8 and timestamp). It is strongly recommended that all installations repair this error, either by initdb or by following the manual repair procedure given below.

The lesser error is that the system view pg_settings ought to be marked as having public update access, to allow UPDATE pg_settings to be used as a substitute for SET. This can also be fixed either by initdb or manually, but it is not necessary to fix unless you want to use UPDATE pg_settings.

If you wish not to do an initdb, the following procedure will work for fixing pg_statistic. As the database superuser, do:

-- clear out old data in pg_statistic:
DELETE FROM pg_statistic;
VACUUM pg_statistic;
-- this should update 1 row:
UPDATE pg_type SET typalign = 'd' WHERE oid = 2277;
-- this should update 6 rows:
UPDATE pg_attribute SET attalign = 'd' WHERE atttypid = 2277;
--
-- At this point you MUST start a fresh backend to avoid a crash!
--
-- repopulate pg_statistic:
ANALYZE;

This can be done in a live database, but beware that all backends running in the altered database must be restarted before it is safe to repopulate pg_statistic.

To repair the pg_settings error, simply do:

GRANT SELECT, UPDATE ON pg_settings TO PUBLIC;

The above procedures must be carried out in each database of an installation, including template1, and ideally including template0 as well. If you do not fix the template databases then any subsequently created databases will contain the same errors. template1 can be fixed in the same way as any other database, but fixing template0 requires additional steps. First, from any database issue:

UPDATE pg_database SET datallowconn = true WHERE datname = 'template0';

Next connect to template0 and perform the above repair procedures. Finally, do:

-- re-freeze template0:
VACUUM FREEZE;
-- and protect it against future alterations:
UPDATE pg_database SET datallowconn = false WHERE datname = 'template0';

E.49.2. Changes

Release 7.4.2 incorporates all the fixes included in release 7.3.6, plus the following fixes:

• Fix pg_statistics alignment bug that could crash optimizer

  See above for details about this problem.

• Allow non-super users to update pg_settings

• Fix several optimizer bugs, most of which led to "variable not found in subplan target lists" errors

• Avoid out-of-memory failure during startup of large multiple index scan

• Fix multibyte problem that could lead to "out of memory" error during COPY IN

• Fix problems with SELECT INTO / CREATE TABLE AS from tables without OIDs

• Fix problems with alter_table regression test during parallel testing

• Fix problems with hitting open file limit, especially on OS X (Tom)

• Partial fix for Turkish-locale issues

  initdb will succeed now in Turkish locale, but there are still some inconveniences associated with the i/I problem.

• Make pg_dump set client encoding on restore

• Other minor pg_dump fixes

• Allow ecpg to again use C keywords as column names (Michael)

• Added ecpg WHENEVER NOT_FOUND to SELECT/INSERT/UPDATE/DELETE (Michael)

• Fix ecpg crash for queries calling set-returning functions (Michael)

• Various other ecpg fixes (Michael)

• Fixes for Borland compiler

• Thread build improvements (Bruce)

• Various other build fixes

• Various JDBC fixes