When upgrading between major releases (e.g. between 7.3.x and 7.4.x) it is necessary to dump and reload all data, as PostgreSQL cannot run using the data files from another version.

Users of semi-automated binary upgrade processes (e.g. RPM or apt in Linux) should be particularly aware of this issue as they may attempt to automatically upgrade data.

Affects: PostgreSQL <= 7.4

By definition the superuser postgres requires access to all databases in a PostgreSQL cluster. However, each database may have a variety of schemas (see Schemas in PostgreSQL [postgres pgsql psql] for an introduction on PostgreSQL schemas) which are not automatically in the postgres user's search path.

This is particularly problematic when the default public schema has been removed and one of the external utilities such as createlang is used. If the schema is not in the postgres user's search path, createlang fails like this:

user@pc:~> createlang -U postgres plpgsql test
createlang: language installation failed: ERROR:  no schema has been selected to
 create in

It is not currently possible to specifiy a schema to createlang. The obvious workaround would be to change postgres' search path with ALTER USER:

test=# ALTER USER postgres SET search_path TO testschema;
ALTER USER

However this may cause problems if other applications are expecting postgres to have a particular search path, especially one containing the default public schema. Unfortunately it is not possible to set a user's search path to include schemas not in the current database.

A safer workaround is to grab the SQL statements which createlang would execute (use the -e option), set the search path in a psql session and execute the relevant DDL (data definition language) statements there. The output with the -e option looks like this:

user@pc:~> createlang -e -U postgres plpgsql test
SELECT oid FROM pg_language WHERE lanname = 'plpgsql';
SELECT oid FROM pg_proc WHERE proname = 'plpgsql_call_handler' AND prorettype = 
(SELECT oid FROM pg_type WHERE typname = 'language_handler') AND pronargs = 0;
CREATE FUNCTION "plpgsql_call_handler" () RETURNS language_handler AS '$libdir/p
lpgsql' LANGUAGE C;
CREATE TRUSTED LANGUAGE "plpgsql" HANDLER "plpgsql_call_handler";

and the CREATE statements should be executed like this:

user@pc:~> psql -U postgres test
Welcome to psql 7.4.2, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit
eg=# SET search_path TO testschema;
SET
eg=# CREATE FUNCTION "plpgsql_call_handler" () RETURNS language_handler AS '$lib
dir/plpgsql' LANGUAGE C;
CREATE FUNCTION
eg=# CREATE TRUSTED LANGUAGE "plpgsql" HANDLER "plpgsql_call_handler";
CREATE LANGUAGE
eg=# \q
user@pc:~>

Affects: PostgreSQL <= 7.4

Prior to version 8.0 pg_dump was not always able to produce database dumps which could be restored without manual intervention. This was particularly the case with complex foreign-key relationships where dependencies cannot be satisfied during sequential data restoration.

Beginning with PostgreSQL 8.0 all known issues have been fixed.