Horton

Thread

  1. dump/reload

    Brett McCormick <brett@work.chicken.org> — 1998-06-03T01:25:37Z

    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.

  2. Re: [HACKERS] dump/reload

    Bruce Momjian <maillist@candle.pha.pa.us> — 1998-06-03T02:19:01Z

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

  3. Re: [HACKERS] dump/reload

    Brett McCormick <brett@work.chicken.org> — 1998-06-03T03:12:37Z

    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?

  4. Re: [HACKERS] dump/reload

    Bruce Momjian <maillist@candle.pha.pa.us> — 1998-06-03T04:05:48Z

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

  5. Re: [HACKERS] dump/reload

    Bruce Momjian <maillist@candle.pha.pa.us> — 1998-06-03T04:09:07Z

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

  6. Re: [HACKERS] dump/reload

    jose' soares <sferac@bo.nettuno.it> — 1998-06-03T11:33:28Z

    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'

  7. Re: [HACKERS] dump/reload

    Bruce Momjian <maillist@candle.pha.pa.us> — 1998-06-03T13:00:11Z

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