BUG #4777: pg_restore is done in alphabetical order by schema

The following bug has been logged online:

Bug reference: 4777
Logged by: Kasia Tuszynska
Email address: ktuszynska(at)esri(dot)com
PostgreSQL version: 8.3.7
Operating system: Windows 2003 server
Description: pg_restore is done in alphabetical order by schema
Details:

Summary:Postgres utility pg_restore.exe restores in alphabetical order by
schema name, thus if a user table has a dependency on data yet to be
restored (because it resides in a schema that will be restored further down
in the restore) it will be blank.

The Environment:Postgresql 8.3.0 (but the issue was retested in 8.3.7)is
used as a database to store spatial data maintained by an application called
ArcSDE (Spatial Database Engine). The application maintains 87 system
tables, one of which resides in the "public" schema, the rest reside in a
schema called "sde". The data it's self is stored in a user defined data
type.
All user data has a dependancy on a table in the public schema, the
dependency is not maintained with constraints.
If user data resides in schema called avtest and a schema called vtest,
doing a restore will result in the data in the vtest schema to restore
correctly ( meaning that the table was populated with data), and the data
stored in the avtest schema will have no records in the table.
example of unsuccessfull backup and restore:
C:\Program Files\PostgreSQL\8.3\bin\pg_dump.exe -h aisak -p 5432 -U postgres
-F c -b -v -f "C:\aisak.dump.backup" postgis

C:\Program Files\PostgreSQL\8.3\bin\pg_restore.exe -h localhost -p 5432 -U
postgres -d postgis -v "C:\aisak.dump.backup"

If however I were to restore the public schema first, and than the
remaineder of the database the data in both schemas restores correctly,
because the public.sde_spatial_reference table is present for the data
restore in both avtest and vtest schema.
Example of a successful restore:

C:\Program Files\PostgreSQL\8.3\bin\pg_dump.exe -h aisak -p 5432 -U postgres
-F c -b -v -f "C:\testbackup.dump.backup" testbackup

C:\Program Files\PostgreSQL\8.3\bin>pg_restore.exe -n public -p 5432 -U
postgres
-d testbackup -v "C:\testbackup.dump.backup"

C:\Program Files\PostgreSQL\8.3\bin>pg_restore.exe -p 5432 -U postgres -d
testbackup
ckup -v "C:\testbackup.dump.backup"

Identifying the problem:The best way to see the issue without setting up the
whole environment of ArcSDE is to look at the text backup file:
C:\Program Files\PostgreSQL\8.3\bin\pg_dump.exe -h aisak -p 5432 -U postgres
-F p -v -f "C:\testbackup_text.dump.backup" testbackup
reading the file illustrates that, the order in which the restore is
structured is the following:
create all schemas (in alphabetical order)
create all tables, object ( in alphabetical order)
execute copy command to populate tables ( in alphabetical schema order), the
data I was testing with was restored in the following order:
...
-- TOC entry 2891 (class 0 OID 889207)
-- Dependencies: 2239
-- Data for Name: state; Type: TABLE DATA; Schema: avtest; Owner: avtest
COPY state (objectid, state_name, state_fips...
...
-- TOC entry 2806 (class 0 OID 888299)
-- Dependencies: 2141
-- Data for Name: sde_spatial_references; Type: TABLE DATA; Schema: public;
Owner: sde
COPY sde_spatial_references (srid, sr_name, ...
...
-- TOC entry 2893 (class 0 OID 889278)
-- Dependencies: 2241
-- Data for Name: poles; Type: TABLE DATA; Schema: vtest; Owner: vtest
COPY poles (objectid, dscktlabel,...

Please notice that the sde_spatial_references table is restored as the
second table in the list. It should be restored as the first table since all
of the user data (states and poles) in this case relies on it.
Please let me know if there is any other information I can provide.
Sincerely,
Kasia Tuszynska
ktuszynska(at)esri(dot)com