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