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
'
declare
 a integer;
begin
 a := 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
'
declare
 a integer;
begin
 a := 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?


Tue, 7 May 2002 17:57:05 +0200 (CEST)
Kovacs Zoltan <kovacsz@pc10.radnoti-szeged.sulinet.hu>
7.1.3: pg_dump hierarchy problem