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


Mon, 9 Sep 2002 13:34:33 -0700 (PDT)
Laurette Cisneros <laurette@nextbus.com>
None
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.


Mon, 9 Sep 2002 14:50:41 -0600 (MDT)
"scott.marlowe" <scott.marlowe@ihs.com>
Re: 
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 from you."        James 4:7 


09 Sep 2002 22:01:57 +0100
Oliver Elphick <olly@lfix.co.uk>
Re: 
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...


Mon, 9 Sep 2002 14:02:55 -0700 (PDT)
Laurette Cisneros <laurette@nextbus.com>
Re: 
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...


Mon, 9 Sep 2002 14:58:01 -0700 (PDT)
Laurette Cisneros <laurette@nextbus.com>
Re: 
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...


Mon, 9 Sep 2002 15:54:49 -0700 (PDT)
Laurette Cisneros <laurette@nextbus.com>
Re: 
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   |/


Tue, 10 Sep 2002 09:50:22 +1000
Philip Warner <pjw@rhyme.com.au>
Re: 
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 


10 Sep 2002 12:18:36 +0100
Oliver Elphick <olly@lfix.co.uk>
Re: 

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, Pennsylvania 19073

Tue, 10 Sep 2002 13:38:00 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: 
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...


Tue, 10 Sep 2002 10:45:43 -0700 (PDT)
Laurette Cisneros <laurette@nextbus.com>
Re: 
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, you sinners; and purify your  
      hearts, you double minded."       James 4:8 


10 Sep 2002 23:08:00 +0100
Oliver Elphick <olly@lfix.co.uk>
Re: 
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, Pennsylvania 19073

Tue, 10 Sep 2002 18:09:03 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: 
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, you sinners; and purify your  
      hearts, you double minded."       James 4:8 


10 Sep 2002 23:22:19 +0100
Oliver Elphick <olly@lfix.co.uk>
Re: 
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

Tue, 10 Sep 2002 23:43:11 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: 
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

Tue, 10 Sep 2002 23:52:48 -0400
Lamar Owen <lamar.owen@wgcr.org>
Re: 
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

Wed, 11 Sep 2002 00:20:30 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: 
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 liveth in me; and the life which I 
      now live in the flesh I live by the faith of the Son 
      of God, who loved me, and gave himself for me."       
                                         Galatians 2:20 


11 Sep 2002 07:29:12 +0100
Oliver Elphick <olly@lfix.co.uk>
Re: 
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

Wed, 11 Sep 2002 09:59:36 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: 
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_handler
 plpgsql_call_handler
 pltcl_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 liveth in me; and the life which I 
      now live in the flesh I live by the faith of the Son 
      of God, who loved me, and gave himself for me."       
                                         Galatians 2:20 


11 Sep 2002 15:29:09 +0100
Oliver Elphick <olly@lfix.co.uk>
Re: 

> 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

Wed, 11 Sep 2002 12:41:11 -0700
Jeff Davis <list-pgsql-hackers@empires.org>
Re: 

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
:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:~:


Wed, 11 Sep 2002 12:58:10 -0700
elein <elein@norcov.com>
Re: 
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

Wed, 11 Sep 2002 16:19:26 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: 
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 liveth in me; and the life which I 
      now live in the flesh I live by the faith of the Son 
      of God, who loved me, and gave himself for me."       
                                         Galatians 2:20 


11 Sep 2002 22:40:44 +0100
Oliver Elphick <olly@lfix.co.uk>
Re: - pg_dump issues
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

Wed, 11 Sep 2002 21:28:23 -0400
Lamar Owen <lamar.owen@wgcr.org>
Re: - pg_dump issues
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, Pennsylvania 19073

Wed, 11 Sep 2002 21:44:16 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: - pg_dump issues
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

Wed, 11 Sep 2002 22:11:46 -0400
Lamar Owen <lamar.owen@wgcr.org>
Re: - pg_dump issues