Обсуждение: 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.
> > > 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? 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 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 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)