Re: [HACKERS] pg_dump problems in upgrading
Awhile back, Oliver Elphick [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] 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[EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] pg_dump problems in upgrading
Oliver Elphick [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] 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[EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] 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 |/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] 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 |/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] 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[EMAIL PROTECTED]
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
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
