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)

On Tue, 2 June 1998, at 22:19:01, Bruce Momjian wrote:

they don't always work, in the case of a table with an attribute that
calls a function for its default value.

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.

Would we look at the binary plan (aiee!) or just try and parse the
string value 'pg_attdef.adsrc` for a function call?

On Tue, 2 June 1998, at 22:19:01, Bruce Momjian wrote:

they don't always work, in the case of a table with an attribute that
calls a function for its default value.

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.

Would we look at the binary plan (aiee!) or just try and parse the
string value 'pg_attdef.adsrc` for a function call?

Just thought about it. With our new subselects we could:

select * from pg_proc where proid in (select deffunc from pg_class)
dump each func
dump tables
select * from pg_proc where proid not in (select deffunc from pg_class)
dump each func

This sounds like a winner. (I just made up the field names and stuff.)

Or are the oid's of the functions used in default values not immediately
available. Is that the binary plan you were talking about. That could
be very messy. Now I see pg_attrdef. This looks tough to grab function
names from.

Oh well.

-- 
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)

On Tue, 2 June 1998, at 22:19:01, Bruce Momjian wrote:

they don't always work, in the case of a table with an attribute that
calls a function for its default value.

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.

Would we look at the binary plan (aiee!) or just try and parse the
string value 'pg_attdef.adsrc` for a function call?

I see pg_attrdef.adsrc now. Wow, that looks tough. Could we grab any
identifier before an open paren?

There has to be an easy fix for this. Can't think of it though.

-- 
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)

On Tue, 2 Jun 1998, Bruce Momjian wrote:

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.

Apologies for intrusion,

I have also a problem with pg_dump, I already posted a bug-report but
nobody replays. If you already know this problem, forget this message.

postgres=> create table prova (var varchar, bp bpchar check (bp='zero'));
CREATE
postgres=> create view wprova as select var from prova;
CREATE

$ pg_dump hygea -s prova > file
$ cat file

\connect - postgres
CREATE TABLE prova (var varchar(-5), bp char(-5)) CONSTRAINT prova_bp CHECK bp = 'zero';
COPY prova FROM stdin;
\.
---------------
. pg_dump don't recreate VIEWs
. recreates varchar as varchar(-5)
. recreates bpchar as CHAR(-5)
. recreates CONSTRAINTs with wrong syntax
Jose'

% 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

Yes, I have this in my mailbox. Doesn't hurt to be reminded though.

-- 
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)