Thread: ...

...

From
Laurette Cisneros
Date:
I am trying move my development database to 7.3b1.

However, when I try to restore from a 7.2.2 dump to the 7.3.b1 server I get
the following error:

pg_restore -U nbadmin -h lnc -p 5432 -d stats -Fc /tmp/stats.pgdmp

pg_restore: [archiver (db)] could not execute query: ERROR:  function
plpgsql_call_handler() does not return type language_handler

Any ideas?

Thanks,

-- 
Laurette Cisneros
The Database Group
(510) 420-3137
NextBus Information Systems, Inc.
www.nextbus.com
----------------------------------
A wiki we will go...



Re:

From
"scott.marlowe"
Date:
On Mon, 9 Sep 2002, Laurette Cisneros wrote:

> 
> I am trying move my development database to 7.3b1.
> 
> However, when I try to restore from a 7.2.2 dump to the 7.3.b1 server I get
> the following error:
> 
> pg_restore -U nbadmin -h lnc -p 5432 -d stats -Fc /tmp/stats.pgdmp
> 
> pg_restore: [archiver (db)] could not execute query: ERROR:  function
> plpgsql_call_handler() does not return type language_handler

I sounds like there's a language installed on your 7.2.2 server that your 
7.3 server doesn't have installed.



Re:

From
Oliver Elphick
Date:
On Mon, 2002-09-09 at 21:34, Laurette Cisneros wrote:
> 
> I am trying move my development database to 7.3b1.
> 
> However, when I try to restore from a 7.2.2 dump to the 7.3.b1 server I get
> the following error:
> 
> pg_restore -U nbadmin -h lnc -p 5432 -d stats -Fc /tmp/stats.pgdmp
> 
> pg_restore: [archiver (db)] could not execute query: ERROR:  function
> plpgsql_call_handler() does not return type language_handler
> 
> Any ideas?

At the moment, you have to edit the dump.  Where the language handler
function is declared, change "RETURNS opaque" to "RETURNS
language_handler".


-- 
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK                            
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
========================================   "Submit yourselves therefore to God. Resist the devil,      and he will flee
fromyou."        James 4:7 
 



Re:

From
Laurette Cisneros
Date:
Thanks!

On 9 Sep 2002, Oliver Elphick wrote:

> On Mon, 2002-09-09 at 21:34, Laurette Cisneros wrote:
> > 
> > I am trying move my development database to 7.3b1.
> > 
> > However, when I try to restore from a 7.2.2 dump to the 7.3.b1 server I get
> > the following error:
> > 
> > pg_restore -U nbadmin -h lnc -p 5432 -d stats -Fc /tmp/stats.pgdmp
> > 
> > pg_restore: [archiver (db)] could not execute query: ERROR:  function
> > plpgsql_call_handler() does not return type language_handler
> > 
> > Any ideas?
> 
> At the moment, you have to edit the dump.  Where the language handler
> function is declared, change "RETURNS opaque" to "RETURNS
> language_handler".
> 
> 
> 

-- 
Laurette Cisneros
The Database Group
(510) 420-3137
NextBus Information Systems, Inc.
www.nextbus.com
----------------------------------
A wiki we will go...



Re:

From
Laurette Cisneros
Date:
Ok, am I missing somethig here?

In 7.3, the -Fp option has been removed which leaves the -Fc (which we use
in our 7.2 dumps) or -Ft. 

How does one edit a compressed or tar file?

Also, is this problem going to be fixed in a later beta or regular release
of 7.3?  This could pose a problem to restore full database dumps.

Thanks,

L.
On 9 Sep 2002, Oliver Elphick wrote:

> On Mon, 2002-09-09 at 21:34, Laurette Cisneros wrote:
> > 
> > I am trying move my development database to 7.3b1.
> > 
> > However, when I try to restore from a 7.2.2 dump to the 7.3.b1 server I get
> > the following error:
> > 
> > pg_restore -U nbadmin -h lnc -p 5432 -d stats -Fc /tmp/stats.pgdmp
> > 
> > pg_restore: [archiver (db)] could not execute query: ERROR:  function
> > plpgsql_call_handler() does not return type language_handler
> > 
> > Any ideas?
> 
> At the moment, you have to edit the dump.  Where the language handler
> function is declared, change "RETURNS opaque" to "RETURNS
> language_handler".
> 
> 
> 

-- 
Laurette Cisneros
The Database Group
(510) 420-3137
NextBus Information Systems, Inc.
www.nextbus.com
----------------------------------
A wiki we will go...



Re:

From
Laurette Cisneros
Date:
Ok, I made the changes in the compressed pg_dump file.  Now pg_restore crashes:

pg_restore: [archiver] out of memory

*sigh*

L.
On 9 Sep 2002, Oliver Elphick wrote:

> On Mon, 2002-09-09 at 21:34, Laurette Cisneros wrote:
> > 
> > I am trying move my development database to 7.3b1.
> > 
> > However, when I try to restore from a 7.2.2 dump to the 7.3.b1 server I get
> > the following error:
> > 
> > pg_restore -U nbadmin -h lnc -p 5432 -d stats -Fc /tmp/stats.pgdmp
> > 
> > pg_restore: [archiver (db)] could not execute query: ERROR:  function
> > plpgsql_call_handler() does not return type language_handler
> > 
> > Any ideas?
> 
> At the moment, you have to edit the dump.  Where the language handler
> function is declared, change "RETURNS opaque" to "RETURNS
> language_handler".
> 
> 
> 

-- 
Laurette Cisneros
The Database Group
(510) 420-3137
NextBus Information Systems, Inc.
www.nextbus.com
----------------------------------
A wiki we will go...



Re:

From
Philip Warner
Date:
At 03:54 PM 9/09/2002 -0700, Laurette Cisneros wrote:
>Ok, I made the changes in the compressed pg_dump file.

That's probably a very bad idea.

It's a little more long-winded, but try:

pg_restore -l dumpfile > dump1.lis

copy dump1.lis to dump2.lis

delete everything from dump1.lis at and after the definition that causes 
the problem.

delete everything from dump2.lis at and before the definition that causes 
the problem.

pg_restore -L dump1.lis

manually define the language

pg_restore -L dump2.lis


ALTERNATIVELY, define the language in template1, then just edit dump1.lis 
to remove the line for the language definition, and run pg_restore -L 
dump1.lis.



----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/



Re:

From
Oliver Elphick
Date:
On Tue, 2002-09-10 at 00:50, Philip Warner wrote:

> ALTERNATIVELY, define the language in template1, then just edit dump1.lis 
> to remove the line for the language definition, and run pg_restore -L 
> dump1.lis.

That doesn't work for a dump and reload, because 7.3's pg_dumpall writes
a script to create the databases from template0 rather than template1.

The 7.3 documentation for pg_dump says:
   Notes      If your installation has any local additions to the template1   database, be careful to restore the
outputof pg_dump into a truly   empty database; otherwise you are likely to get errors due to   duplicate definitions
ofthe added objects. To make an empty   database without any local additions, copy from template0 not   template1, for
example:         CREATE DATABASE foo WITH TEMPLATE = template0;   
 
but this seems to be out of date.  pg_dumpall actually uses template0
itself.

-- 
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK                            
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
========================================   "Draw near to God and he will draw near to you.       Cleanse your hands,
yousinners; and purify your       hearts, you double minded."       James 4:8 
 



Re:

From
Bruce Momjian
Date:
I am confused.  This wording seems fine to me.

---------------------------------------------------------------------------

Oliver Elphick wrote:
> On Tue, 2002-09-10 at 00:50, Philip Warner wrote:
> 
> > ALTERNATIVELY, define the language in template1, then just edit dump1.lis 
> > to remove the line for the language definition, and run pg_restore -L 
> > dump1.lis.
> 
> That doesn't work for a dump and reload, because 7.3's pg_dumpall writes
> a script to create the databases from template0 rather than template1.
> 
> The 7.3 documentation for pg_dump says:
> 
>     Notes
>     
>     If your installation has any local additions to the template1
>     database, be careful to restore the output of pg_dump into a truly
>     empty database; otherwise you are likely to get errors due to
>     duplicate definitions of the added objects. To make an empty
>     database without any local additions, copy from template0 not
>     template1, for example:
>     
>         CREATE DATABASE foo WITH TEMPLATE = template0;
>     
> but this seems to be out of date.  pg_dumpall actually uses template0
> itself.
> 
> -- 
> Oliver Elphick                                Oliver.Elphick@lfix.co.uk
> Isle of Wight, UK                            
> http://www.lfix.co.uk/oliver
> GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
>                  ========================================
>      "Draw near to God and he will draw near to you.  
>       Cleanse your hands, you sinners; and purify your  
>       hearts, you double minded."       James 4:8 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> 

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 


Re:

From
Laurette Cisneros
Date:
I do this to begin with (createdb -T template0 db).

FYI:  Here's what I've determined is the best thing to do:

1.  create the database from template0
2.  create the needed languages (plpgsql, plperl, plpython) in the database
3.  create the needed tables, functions, types, etc. from script files.
4.  restore only the data from the dump.

Seems to be the "easiest" and safest way to convert the database(s) to
7.3b1 (we have a mirad of databases for different needs each having their
own set of types, functions and languages that they use).  I'll let you
know if I run into problems with this - as this, in my opinion, should not!

Thanks to all for the help,

L.
On Tue, 10 Sep 2002, Bruce Momjian wrote:

> 
> I am confused.  This wording seems fine to me.
> 
> ---------------------------------------------------------------------------
> 
> Oliver Elphick wrote:
> > On Tue, 2002-09-10 at 00:50, Philip Warner wrote:
> > 
> > > ALTERNATIVELY, define the language in template1, then just edit dump1.lis 
> > > to remove the line for the language definition, and run pg_restore -L 
> > > dump1.lis.
> > 
> > That doesn't work for a dump and reload, because 7.3's pg_dumpall writes
> > a script to create the databases from template0 rather than template1.
> > 
> > The 7.3 documentation for pg_dump says:
> > 
> >     Notes
> >     
> >     If your installation has any local additions to the template1
> >     database, be careful to restore the output of pg_dump into a truly
> >     empty database; otherwise you are likely to get errors due to
> >     duplicate definitions of the added objects. To make an empty
> >     database without any local additions, copy from template0 not
> >     template1, for example:
> >     
> >         CREATE DATABASE foo WITH TEMPLATE = template0;
> >     
> > but this seems to be out of date.  pg_dumpall actually uses template0
> > itself.
> > 
> > -- 
> > Oliver Elphick                                Oliver.Elphick@lfix.co.uk
> > Isle of Wight, UK                            
> > http://www.lfix.co.uk/oliver
> > GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
> >                  ========================================
> >      "Draw near to God and he will draw near to you.  
> >       Cleanse your hands, you sinners; and purify your  
> >       hearts, you double minded."       James 4:8 
> > 
> > 
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> >     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> > 
> 
> 

-- 
Laurette Cisneros
The Database Group
(510) 420-3137
NextBus Information Systems, Inc.
www.nextbus.com
----------------------------------
A wiki we will go...



Re:

From
Oliver Elphick
Date:
On Tue, 2002-09-10 at 18:38, Bruce Momjian wrote:
> 
> I am confused.  This wording seems fine to me.

The confusion was mine.  Of course, pg_dump doesn't create the
database.  I was mixing it up with pg_dumpall.

However, there is a problem in that recent changes have made it quite
likely that an upgrade will fail and will requre the dump script to be
edited.  There are some issues in pg_dump / pg_dumpall that need
addressing before final release.

-- 
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK                            
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
========================================   "Draw near to God and he will draw near to you.       Cleanse your hands,
yousinners; and purify your       hearts, you double minded."       James 4:8 
 



Re:

From
Bruce Momjian
Date:
Oliver Elphick wrote:
> On Tue, 2002-09-10 at 18:38, Bruce Momjian wrote:
> > 
> > I am confused.  This wording seems fine to me.
> 
> The confusion was mine.  Of course, pg_dump doesn't create the
> database.  I was mixing it up with pg_dumpall.
> 
> However, there is a problem in that recent changes have made it quite
> likely that an upgrade will fail and will requre the dump script to be
> edited.  There are some issues in pg_dump / pg_dumpall that need
> addressing before final release.

OK, can you specifically list them?

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 


Re:

From
Oliver Elphick
Date:
On Tue, 2002-09-10 at 23:09, Bruce Momjian wrote:
> Oliver Elphick wrote:
> > edited.  There are some issues in pg_dump / pg_dumpall that need
> > addressing before final release.
> 
> OK, can you specifically list them?

Message yesterday to pgsql-hackers      Subject: [HACKERS] pg_dump problems in upgrading   Date: 09 Sep 2002 12:31:39
+0100  Message-Id: <1031571099.24419.199.camel@linda>
 

-- 
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK                            
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
========================================   "Draw near to God and he will draw near to you.       Cleanse your hands,
yousinners; and purify your       hearts, you double minded."       James 4:8 
 



Re:

From
Tom Lane
Date:
Oliver Elphick <olly@lfix.co.uk> writes:
> However, there is a problem in that recent changes have made it quite
> likely that an upgrade will fail and will requre the dump script to be
> edited.  There are some issues in pg_dump / pg_dumpall that need
> addressing before final release.

AFAIK, we did what we could on that front in 7.2.1.  If you have ideas
on how we can retroactively make things better, I'm all ears ...
        regards, tom lane


Re:

From
Lamar Owen
Date:
On Tuesday 10 September 2002 11:43 pm, Tom Lane wrote:
> Oliver Elphick <olly@lfix.co.uk> writes:
> > However, there is a problem in that recent changes have made it quite
> > likely that an upgrade will fail and will requre the dump script to be
> > edited.  There are some issues in pg_dump / pg_dumpall that need
> > addressing before final release.

> AFAIK, we did what we could on that front in 7.2.1.  If you have ideas
> on how we can retroactively make things better, I'm all ears ...

So this release is going to be the royal pain release to upgrade to?  Not 
good.  People may just not upgrade at all in that case.

My datasets aren't complicated enough to trigger some of these problems; 
people who have complex datasets need to report all failures so that we can 
at least write a sed/perl/awk script to massage the things that need 
massaging, if it can be done that easily.
-- 
Lamar Owen
WGCR Internet Radio
1 Peter 4:11


Re:

From
Tom Lane
Date:
Lamar Owen <lamar.owen@wgcr.org> writes:
> On Tuesday 10 September 2002 11:43 pm, Tom Lane wrote:
>> AFAIK, we did what we could on that front in 7.2.1.  If you have ideas
>> on how we can retroactively make things better, I'm all ears ...

> So this release is going to be the royal pain release to upgrade to?

pg_dumpall from a 7.2 db, and reload into 7.2, is broken if you have
mixed-case DB names.  AFAIK it's okay if you use a later-than-7.2
pg_dumpall, or reload with a later-than-7.2 psql.  If Oliver's got
info to the contrary then he'd better be more specific about what
he thinks should be fixed for 7.3.  Griping about the fact that 7.2.0
is broken is spectacularly unproductive at this point.
        regards, tom lane


Re:

From
Oliver Elphick
Date:
On Wed, 2002-09-11 at 05:20, Tom Lane wrote:
> Lamar Owen <lamar.owen@wgcr.org> writes:
> > On Tuesday 10 September 2002 11:43 pm, Tom Lane wrote:
> >> AFAIK, we did what we could on that front in 7.2.1.  If you have ideas
> >> on how we can retroactively make things better, I'm all ears ...
> 
> > So this release is going to be the royal pain release to upgrade to?
> 
> pg_dumpall from a 7.2 db, and reload into 7.2, is broken if you have
> mixed-case DB names.  AFAIK it's okay if you use a later-than-7.2
> pg_dumpall, or reload with a later-than-7.2 psql.  If Oliver's got
> info to the contrary then he'd better be more specific about what
> he thinks should be fixed for 7.3.  Griping about the fact that 7.2.0
> is broken is spectacularly unproductive at this point.

I ran pg_dumpall from 7.3 on the 7.2 database.  So I am talking about
the pg_dump that is now being beta-tested.  Because of the major changes
in 7.3, the 7.2 dump is not very useful.  I am *not* complaining about
7.2's pg_dump!

Let me reiterate.  I got these problems dumping 7.2 data with 7.3's
pg_dumpall:

1.  The language handlers were dumped as opaque; that needs to be
changed to language_handler.

2.  The dump produced:    CREATE TABLE cust_alloc_history (        ...       "year" integer DEFAULT
date_part('year'::text,               ('now'::text)::timestamp(6) with time zone) NOT NULL,        ...   ERROR:  Column
"year"is of type integer but default expression is
 
of type double precision           You will need to rewrite or cast the expression

3.  A view was created before one of the tables to which it referred.

-- 
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK                            
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
========================================   "I am crucified with Christ; nevertheless I live; yet      not I, but Christ
livethin me; and the life which I      now live in the flesh I live by the faith of the Son      of God, who loved me,
andgave himself for me."                                               Galatians 2:20 
 



Re:

From
Tom Lane
Date:
Oliver Elphick <olly@lfix.co.uk> writes:
> Let me reiterate.  I got these problems dumping 7.2 data with 7.3's
> pg_dumpall:

> 1.  The language handlers were dumped as opaque; that needs to be
> changed to language_handler.

Okay, we need to do something about that, though I'm not sure I see
a clean solution offhand.

> 2.  The dump produced:
>      CREATE TABLE cust_alloc_history (
>          ...
>         "year" integer DEFAULT date_part('year'::text,
>                  ('now'::text)::timestamp(6) with time zone) NOT NULL,
>          ...
>     ERROR:  Column "year" is of type integer but default expression is
> of type double precision
>             You will need to rewrite or cast the expression

Hmm ... what was the original coding of the default?

> 3.  A view was created before one of the tables to which it referred.

This has been a problem all along and will continue to be a problem
for awhile longer.  Sorry.
        regards, tom lane


Re:

From
Oliver Elphick
Date:
On Wed, 2002-09-11 at 14:59, Tom Lane wrote:
> Oliver Elphick <olly@lfix.co.uk> writes:
> > Let me reiterate.  I got these problems dumping 7.2 data with 7.3's
> > pg_dumpall:
> 
> > 1.  The language handlers were dumped as opaque; that needs to be
> > changed to language_handler.
> 
> Okay, we need to do something about that, though I'm not sure I see
> a clean solution offhand.

In 7.2, this will identify the functions that need to be dumped as
language handlers:

junk=# SELECT p.proname
junk-#   FROM pg_proc AS p, pg_language AS l
junk-#  WHERE l.lanplcallfoid = p.oid AND l.lanplcallfoid != 0;      proname        
----------------------plperl_call_handlerplpgsql_call_handlerpltcl_call_handler
(3 rows)


> > 2.  The dump produced:
> >      CREATE TABLE cust_alloc_history (
> >          ...
> >         "year" integer DEFAULT date_part('year'::text,
> >                  ('now'::text)::timestamp(6) with time zone) NOT NULL,
> >          ...
> >     ERROR:  Column "year" is of type integer but default expression is
> > of type double precision
> >             You will need to rewrite or cast the expression
> 
> Hmm ... what was the original coding of the default?
  year         INTEGER      DEFAULT date_part('year',CURRENT_TIMESTAMP)



> > 3.  A view was created before one of the tables to which it referred.
> 
> This has been a problem all along and will continue to be a problem
> for awhile longer.  Sorry.

Is it not enough to defer all views until the end?  Why would they be
needed any sooner?

-- 
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK                            
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
========================================   "I am crucified with Christ; nevertheless I live; yet      not I, but Christ
livethin me; and the life which I      now live in the flesh I live by the faith of the Son      of God, who loved me,
andgave himself for me."                                               Galatians 2:20 
 



Re:

From
Jeff Davis
Date:
> Is it not enough to defer all views until the end?  Why would they be
> needed any sooner?

I would think that views of views, or permissions on views, or prepared 
statements might need the right view to be declared first. There may be other 
examples as well.

Your solution might be better than the current situation, however.

Regards,Jeff


Re:

From
elein
Date:
yes, deferring views to the end will also break if you have
SQL functions defined that use views.  The dependencies
is (are?) a really hard problem.

elein

At 12:41 PM 9/11/02, Jeff Davis wrote:

> > Is it not enough to defer all views until the end?  Why would they be
> > needed any sooner?
>
>I would think that views of views, or permissions on views, or prepared
>statements might need the right view to be declared first. There may be other
>examples as well.
>
>Your solution might be better than the current situation, however.
>
>Regards,
>         Jeff
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: if posting/reading through Usenet, please send an appropriate
>subscribe-nomail command to majordomo@postgresql.org so that your
>message can get through to the mailing list cleanly

:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:    elein@norcov.com   (510)543-6079    "Taking a Trip. Not taking a
Trip."--anonymous
 
:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:



Re:

From
Tom Lane
Date:
Oliver Elphick <olly@lfix.co.uk> writes:
> ERROR:  Column "year" is of type integer but default expression is
> of type double precision
> You will need to rewrite or cast the expression
>> 
>> Hmm ... what was the original coding of the default?

>    year         INTEGER      DEFAULT date_part('year',CURRENT_TIMESTAMP)

Well, date_part has always yielded double, so what we are really looking
at here is a side-effect of the tightening of implicit casting in 7.3.
It wants you to cast down to integer explicitly.

There was some discussion of allowing "implicit explicit casting" of
INSERT and UPDATE values to the target column's datatype, ie, allow a
cast path to be used even if it is not marked as implicitly castable.
If we did that then it's be reasonable to do it for default values as
well, and that would allow this coding to keep working.  But we did not
have a consensus to do it AFAIR.

> 3.  A view was created before one of the tables to which it referred.
>> 
>> This has been a problem all along and will continue to be a problem
>> for awhile longer.  Sorry.

> Is it not enough to defer all views until the end?  Why would they be
> needed any sooner?

Well, one counterexample is where the view is being used as a substitute
for a standalone composite type: there might be a function somewhere
that uses the view's rowtype as an input or result datatype.  I recall
seeing exactly that coding technique in some of Joe Conway's contrib
stuff (though it's now been superseded by use of standalone types).
In any case, such a rule won't ensure getting cross-references between
views to work.

The only real solution to pg_dump's ordering woes is to examine the
database dependency graph and do a topological sort to determine a
safe dump order.  As of 7.3 we have the raw materials to do this (in
the form of the pg_depend system table), but making pg_dump actually
do it is a major rewrite that didn't get done, and IMHO shouldn't be
tackled during beta.  (I sure want to see it for 7.4 though.)

In the meantime, I think that we shouldn't mess with pg_dump's basically
OID-order-driven dump ordering.  It works in normal cases, and adding
arbitrary rules to it to fix one corner case is likely to accomplish
little except breaking other corner cases.
        regards, tom lane


Re: - pg_dump issues

From
Oliver Elphick
Date:
On Wed, 2002-09-11 at 21:19, Tom Lane wrote:
> In the meantime, I think that we shouldn't mess with pg_dump's basically
> OID-order-driven dump ordering.  It works in normal cases, and adding
> arbitrary rules to it to fix one corner case is likely to accomplish
> little except breaking other corner cases.

I can see that Lamar and I are going to have major problems dealing with
users who fall over these problems.  There are some things that simply
cannot be handled automatically, such as user-written functions that
return opaque.  Then there are issues of ordering; and finally the fact
that we need to use the new pg_dump with the old binaries to get a
useful dump.

It seems to me that I shall have to make the new package such that it
can exist alongside the old one for a time, or else possibly separate
7.3 pg_dump and pg_dumpall into a separate package.  It is going to be a
total pain!

-- 
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK                            
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
========================================   "I am crucified with Christ; nevertheless I live; yet      not I, but Christ
livethin me; and the life which I      now live in the flesh I live by the faith of the Son      of God, who loved me,
andgave himself for me."                                               Galatians 2:20 
 



Re: - pg_dump issues

From
Lamar Owen
Date:
On Wednesday 11 September 2002 05:40 pm, Oliver Elphick wrote:
> On Wed, 2002-09-11 at 21:19, Tom Lane wrote:
> > In the meantime, I think that we shouldn't mess with pg_dump's basically
> > OID-order-driven dump ordering.  It works in normal cases, and adding
> > arbitrary rules to it to fix one corner case is likely to accomplish
> > little except breaking other corner cases.

> I can see that Lamar and I are going to have major problems dealing with
> users who fall over these problems.

Yes, we are.  Thankfully, with RPM dependencies I can prevent blind upgrades. 
But that doe not help the data migration issue this release is going to be.  
Guys, migration that is this shabby is, well, shabby.  This _has_ to be fixed 
where a dump of 7.2.2 data (not 7.2.0, Tom) can be cleanly restored into 7.3.  
That is, after all, our only migration path.

I think this upgrade/migration nightmare scenario warrants upping the version 
to 8.0 to draw attention to the potential problems.

> It seems to me that I shall have to make the new package such that it
> can exist alongside the old one for a time, or else possibly separate
> 7.3 pg_dump and pg_dumpall into a separate package.  It is going to be a
> total pain!

I had planned on making just such a 'pg_dump' package -- but if the 7.3 
pg_dump isn't going to produce useful output, it seems like a waste of time.

However, the jury is still out -- what sort of percentages are involved?  That 
is, how likely are problems going to happen?

Bruce, I mentioned a sed/perl/awk script already to massage the dump into a 
7.3-friendly form -- but we need to gather the cases that are involved.  
Methinks every single OpenACS installation will hit this issue.

How big is the problem?  It's looking bigger with each passing day, ISTM.
-- 
Lamar Owen
WGCR Internet Radio
1 Peter 4:11


Re: - pg_dump issues

From
Bruce Momjian
Date:
Lamar Owen wrote:
> Bruce, I mentioned a sed/perl/awk script already to massage the dump into a 
> 7.3-friendly form -- but we need to gather the cases that are involved.  
> Methinks every single OpenACS installation will hit this issue.
> 
> How big is the problem?  It's looking bigger with each passing day, ISTM.

That is exactly what I want to know and document on the open items page.
I am having trouble understanding some of the failures because no one is
showing the failure messages/statements, just describing them.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 


Re: - pg_dump issues

From
Lamar Owen
Date:
On Wednesday 11 September 2002 09:44 pm, Bruce Momjian wrote:
> Lamar Owen wrote:
> > Bruce, I mentioned a sed/perl/awk script already to massage the dump into
> > a 7.3-friendly form -- but we need to gather the cases that are involved.
> > Methinks every single OpenACS installation will hit this issue.

> > How big is the problem?  It's looking bigger with each passing day, ISTM.

> That is exactly what I want to know and document on the open items page.
> I am having trouble understanding some of the failures because no one is
> showing the failure messages/statements, just describing them.

Well, I am going to _try_ to lay aside an hour or two tomorrow or Friday and 
try to import a 7.2.2 OpenACS dump into a 7.3 installation.  I'll try to get 
very verbose with the errors... :-)
-- 
Lamar Owen
WGCR Internet Radio
1 Peter 4:11