Hi, I experienced a problem using pg_dump with 7.1.3. I don't know whether
7.2 also has the same behaviour. AFAIK everything is dumped out using the
order of OIDs. But the following example shows that it works incorrectly
if a PLPGSQL function is recreated, assuming this PLPGSQL function was
used by an SQL function. Recreating the PLPGSQL function, the SQL function
can use it again, but their OID orders will be changed. This causes
pg_dump to dump the database into an incorrect hierarchy. A shell script
example can be seen below. Regards, Zoltan
#!/bin/sh
libdir=/usr/lib/pgsql
database=pg_dump_test
createdb $database
echo "
create function plpgsql_call_handler() returns opaque as
'$libdir/plpgsql.so' language 'c';
create trusted procedural language 'plpgsql' handler plpgsql_call_handler
lancompiler 'PL/pgSQL';
create function test_a (integer) returns integer as
'
declarea integer;
begina := count(*) from pg_shadow;return a;
end;
' language 'plpgsql';
create function test_b(integer) returns integer as
'select test_a (\$1);' language 'sql';" | psql -U postgres $database
pg_dump $database > dump1
echo "
drop function test_a(integer);
select test_b(5);" | psql -U postgres $database
echo "
create function test_a (integer) returns integer as
'
declarea integer;
begina := count(*) from pg_shadow;return a;
end;
' language 'plpgsql';
select test_b(5);" | psql -U postgres $database
pg_dump $database > dump2
dropdb $database
more dump?
