Re: [HACKERS] dump/reload

>
>
> they don't always work, in the case of a table with an attribute that
> calls a function for its default value.
>
> postgres=> create function foo() returns int4 as 'select 1' language 'sql';
> CREATE
> postgres=> create table a (b int4 default foo());
> CREATE
>
> % pg_dump postgres > tmpfile
> % cat tmpfile
> \connect - postgres
> CREATE TABLE a (b int4 DEFAULT foo ( ));
> \connect - postgres
> CREATE FUNCTION foo ( ) RETURNS int4 AS 'select 1' LANGUAGE 'SQL';
> COPY a FROM stdin;
> \.
> % destroydb
> % createdb
> % psql < tmpfile
>
> which of course doesn't work, because it tries to create the table before
> the function, which fails.
>
> then it spits out the help message because it can't understand \.
>
> this happens every time I dump/reload my db
>
> not a super easy fix, because sql functions can depend on tables to be
> created as well as table depending on functions.  are circular
> references possible?  doesn't seem like they would be.
>
> so pg_dump would have to figure out what order to put the
> table/function creation in.  perhaps having ppl manually re-ordering
> the dump output (and documenting this accordingly!) is the best way.
>
>

This is a good point, and something worth thinking about.  Maybe we
could scan through the defaults for a table, and call the dumpfunction
command for any functions.  Then when they are later attempted to be
created, they would fail, or we could somehow mark them as already
dumped.


--
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)