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
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
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?
----------------------------------------------------------------
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; 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
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 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
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