Thread: pg_dump problems in upgrading
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
CREATETRUSTED PROCEDURAL LANGUAGE plperl HANDLER plperl_call_handler; ERROR: function plperl_call_handler() does not
returntype 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)
NOTNULL, "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
integerDEFAULT 0 NOT NULL, may integer DEFAULT 0 NOT NULL, jun integer DEFAULT 0 NOT NULL, jul
integerDEFAULT 0 NOT NULL, aug integer DEFAULT 0 NOT NULL, sep integer DEFAULT 0 NOT NULL, oct
integerDEFAULT 0 NOT NULL, nov integer DEFAULT 0 NOT NULL, dbr integer DEFAULT 0 NOT NULL, CONSTRAINT
c_a_h_yearCHECK (((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
willneed 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
fromyou." James 4:7
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 |/
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 |/
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; andlet him return unto the LORD, and He will have mercy upon him; and to our God, for he will abundantly pardon." Isaiah 55:7
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
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 nortrust 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 themselvesa good foundation against the time to come, that they may lay hold on eternal life." I Timothy6:17-19
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