I am trying to populate a 7.3 database from a 7.2 dump.  I used 7.3's
pg_dumpall, but this did not handle all the issues:

1. The language dumping needs to be improved:

    CREATE FUNCTION plperl_call_handler () RETURNS opaque
                                           ^^^^^^^^^^^^^^
        AS '/usr/local/pgsql/lib/plperl.so', 'plperl_call_handler'
        LANGUAGE "C";
    CREATE FUNCTION
    GRANT ALL ON FUNCTION plperl_call_handler () TO PUBLIC;
    GRANT
    REVOKE ALL ON FUNCTION plperl_call_handler () FROM postgres;
    REVOKE
    CREATE TRUSTED PROCEDURAL LANGUAGE plperl HANDLER plperl_call_handler;
    ERROR:  function plperl_call_handler() does not return type language_handler
    
    
2.  Either casts or extra default conversions may be needed:

    CREATE TABLE cust_alloc_history (
        customer character varying(8) NOT NULL,
        product character varying(10) NOT NULL,
        "year" integer DEFAULT date_part('year'::text, ('now'::text)::timestamp(6) with time zone) NOT NULL,
        jan integer DEFAULT 0 NOT NULL,
        feb integer DEFAULT 0 NOT NULL,
        mar integer DEFAULT 0 NOT NULL,
        apr integer DEFAULT 0 NOT NULL,
        may integer DEFAULT 0 NOT NULL,
        jun integer DEFAULT 0 NOT NULL,
        jul integer DEFAULT 0 NOT NULL,
        aug integer DEFAULT 0 NOT NULL,
        sep integer DEFAULT 0 NOT NULL,
        oct integer DEFAULT 0 NOT NULL,
        nov integer DEFAULT 0 NOT NULL,
        dbr integer DEFAULT 0 NOT NULL,
        CONSTRAINT c_a_h_year CHECK (((float8("year") <= date_part('year'::text, ('now'::text)::timestamp(6) with time zone)) AND ("year" > 1997)))
    );
    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 is being created before one of the tables it refers to. 
Should not views be created only at the very end?

-- 
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 12:31:39 +0100
Oliver Elphick <olly@lfix.co.uk>
pg_dump problems in upgrading
At 12:31 PM 9/09/2002 +0100, Oliver Elphick wrote:
>3. A view is being created before one of the tables it refers to.
>Should not views be created only at the very end?

This would be trivial (and we already put several items at the end), but I 
am not sure it would fix the problem since views can also be on other 
views. I presume the bad ordering happened as a result of a drop/create on 
a table? Or is there some other cause?



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


Thu, 12 Sep 2002 09:52:40 +1000
Philip Warner <pjw@rhyme.com.au>
Re: pg_dump problems in upgrading
At 12:31 PM 9/09/2002 +0100, Oliver Elphick wrote:

>     CREATE FUNCTION plperl_call_handler () RETURNS opaque
>                                            ^^^^^^^^^^^^^^
>         AS '/usr/local/pgsql/lib/plperl.so', 'plperl_call_handler'
>         LANGUAGE "C";
...
>     CREATE TRUSTED PROCEDURAL LANGUAGE plperl HANDLER plperl_call_handler;
>     ERROR:  function plperl_call_handler() does not return type 
> language_handler

This is reminiscent of the mess with language definitions in the last 
version, prior to the more sensible function manager definition system.

A similar solution could be adopted here: extend the function manager 
definition macros to also (optionally) capture the return type; then when 
the function is defined, the function manager could check the real return 
type, issue a warning, and define it properly. This could be extended to 
args as well, if we felt so inclined. This solution obviously only works 
for languages since (I assume) they will be the only ones modified to use 
the improved macros; but it will fix 90% of problems.



>     ERROR:  Column "year" is of type integer but default expression is of 
> type double precision
>             You will need to rewrite or cast the expression

This does seem like a problem to me - has anything been done about this? 
There does not seem to be much traffic in this thread.


>3. A view is being created before one of the tables it refers to.
>Should not views be created only at the very end?

Unless this is a 7.3-specific problem, I'd put this at a lower priority; as 
I suggested in an earlier post, moving the views to the end won't 
necessarily fix the problem; and pre-7.3 databases don't know about 
dependencies, so we can't use the rudimentary support for dependencies in 
pg_dump.




>---------------------------(end of broadcast)---------------------------
>TIP 6: Have you searched our list archives?
>
>http://archives.postgresql.org

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


Thu, 12 Sep 2002 11:50:21 +1000
Philip Warner <pjw@rhyme.com.au>
Re: pg_dump problems in upgrading
On Thu, 2002-09-12 at 00:52, Philip Warner wrote:
> At 12:31 PM 9/09/2002 +0100, Oliver Elphick wrote:
> >3. A view is being created before one of the tables it refers to.
> >Should not views be created only at the very end?
> 
> This would be trivial (and we already put several items at the end), but I 
> am not sure it would fix the problem since views can also be on other 
> views. I presume the bad ordering happened as a result of a drop/create on 
> a table? Or is there some other cause?

It could be, but I don't know for sure.  This is a development db which
quite often gets reloaded entirely and repopulated.

-- 
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
                 ========================================
     "Let the wicked forsake his way, and the unrighteous 
      man his thoughts; and let him return unto the LORD, 
      and He will have mercy upon him; and to our God, for 
      he will abundantly pardon."          Isaiah 55:7 


12 Sep 2002 12:35:17 +0100
Oliver Elphick <olly@lfix.co.uk>
Re: pg_dump problems in upgrading
Awhile back, Oliver Elphick <olly@lfix.co.uk> wrote:
> I am trying to populate a 7.3 database from a 7.2 dump.  I used 7.3's
> pg_dumpall, but this did not handle all the issues:

> 1. The language dumping needs to be improved:

This is now fixed.

> 2.  Either casts or extra default conversions may be needed:

This too --- at least in the example you give.

> 3. A view is being created before one of the tables it refers to. 

On thinking about it, I'm having a hard time seeing how that case could
arise, unless the source database was old enough to have wrapped around
its OID counter.  I'd be interested to see the details of your case.
While the only long-term solution is proper dependency tracking in
pg_dump, there might be some shorter-term hack that we should apply...

			regards, tom lane

Sat, 21 Sep 2002 14:49:20 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: pg_dump problems in upgrading 
On Sat, 2002-09-21 at 19:49, Tom Lane wrote:
> > 3. A view is being created before one of the tables it refers to. 
> 
> On thinking about it, I'm having a hard time seeing how that case could
> arise, unless the source database was old enough to have wrapped around
> its OID counter.  I'd be interested to see the details of your case.
> While the only long-term solution is proper dependency tracking in
> pg_dump, there might be some shorter-term hack that we should apply...

While I don't think that the oids have wrapped round, the oid of the
table in question is larger than the oid of the view.  It is quite
likely that the table was dropped and recreated after the view was
created.

In fact, the view no longer works:
  ERROR:  Relation "sales_forecast" with OID 26246751 no longer exists
so that must be what happened.
  
-- 
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
                 ========================================
     "Charge them that are rich in this world, that they not
      be highminded nor trust in uncertain riches, but in 
      the living God, who giveth us richly all things to 
      enjoy; That they do good, that they be rich in good 
      works, ready to distribute, willing to communicate; 
      Laying up in store for themselves a good foundation 
      against the time to come, that they may lay hold on 
      eternal life."          I Timothy 6:17-19 


21 Sep 2002 20:28:11 +0100
Oliver Elphick <olly@lfix.co.uk>
Re: pg_dump problems in upgrading
Oliver Elphick <olly@lfix.co.uk> writes:
>>> 3. A view is being created before one of the tables it refers to. 

> While I don't think that the oids have wrapped round, the oid of the
> table in question is larger than the oid of the view.  It is quite
> likely that the table was dropped and recreated after the view was
> created.

> In fact, the view no longer works:
>   ERROR:  Relation "sales_forecast" with OID 26246751 no longer exists
> so that must be what happened.

Ah ... so the view was broken already.  I'm surprised you didn't get a
failure while attempting to dump the view definition.

The new dependency stuff should help prevent this type of problem in
future ...

			regards, tom lane

Sat, 21 Sep 2002 15:35:54 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: pg_dump problems in upgrading 