Thread: Problem resolved (tsearch2 inhibiting migration)
Got it fixed. As it happens, there's an "untsearch2.sql" script in the contrib directory. Removing the transaction block around it allowed it to kill all the "dregs" from the 7.4.x database entries (on 8.0.1) and a subsequent "tsearch2.sql" was then able to rebuild them. I had to reinsert the columns and indices, but that's not a big deal. All fixed... thanks to the pointer to the OID issue, that got me on the right track. -- -- Karl Denninger (karl@denninger.net) Internet Consultant & Kids Rights Activist http://www.denninger.net My home on the net - links to everything I do! http://scubaforum.org Your UNCENSORED place to talk about DIVING! http://www.spamcuda.net SPAM FREE mailboxes - FREE FOR A LIMITED TIME! http://genesis3.blogspot.com Musings Of A Sentient Mind
On Thu, Feb 03, 2005 at 06:44:55PM -0600, Karl Denninger wrote: > > As it happens, there's an "untsearch2.sql" script in the contrib directory. That reminds me: it would be useful if all contributed modules had an unmodule.sql file. That would simplify reloading the module if the definitions changed, as recently happened when STRICT was added to functions in chkpass and a few other modules; it would also make it easy to remove the module from a particular database if it were no longer needed. Or is there already a way of doing this that doesn't require you to figure out the DROP statements yourself? Running "gmake uninstall" in the module's source directory only removes the .so, .sql, and other installed files -- it doesn't DROP any objects that have been created in databases. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
On Thu, Feb 03, 2005 at 06:59:55PM -0700, Michael Fuhr wrote: > On Thu, Feb 03, 2005 at 06:44:55PM -0600, Karl Denninger wrote: > > > > As it happens, there's an "untsearch2.sql" script in the contrib directory. > > That reminds me: it would be useful if all contributed modules had > an unmodule.sql file. That would simplify reloading the module if > the definitions changed, as recently happened when STRICT was added > to functions in chkpass and a few other modules; it would also make > it easy to remove the module from a particular database if it were > no longer needed. Or is there already a way of doing this that > doesn't require you to figure out the DROP statements yourself? > Running "gmake uninstall" in the module's source directory only > removes the .so, .sql, and other installed files -- it doesn't DROP > any objects that have been created in databases. No, there is no good way to do that. What's worse, is that if it goes into the template directory, it "pollutes" every database on the system from that point forward, so you have to back it out of each in turn.... I agree with this - what would be even better would be a way to create 'subclasses' for things like this, which could then be 'included' easily. I don't know if the latter is possible - I haven't seen an obvious way to do that, but there may be a way..... -- -- Karl Denninger (karl@denninger.net) Internet Consultant & Kids Rights Activist http://www.denninger.net My home on the net - links to everything I do! http://scubaforum.org Your UNCENSORED place to talk about DIVING! http://www.spamcuda.net SPAM FREE mailboxes - FREE FOR A LIMITED TIME! http://genesis3.blogspot.com Musings Of A Sentient Mind
Karl Denninger <karl@denninger.net> writes:
> I agree with this - what would be even better would be a way to create
> 'subclasses' for things like this, which could then be 'included' easily.
We could decree that a contrib module's script should create a schema
and shove everything it makes into that schema. Then "DROP SCHEMA CASCADE"
is all you need to get rid of it. However, you'd probably end up having
to add this schema to your search path to use the module conveniently.
regards, tom lane
On Thu, Feb 03, 2005 at 10:20:47PM -0500, Tom Lane wrote: > Karl Denninger <karl@denninger.net> writes: > > I agree with this - what would be even better would be a way to create > > 'subclasses' for things like this, which could then be 'included' easily. > > We could decree that a contrib module's script should create a schema > and shove everything it makes into that schema. Then "DROP SCHEMA CASCADE" > is all you need to get rid of it. However, you'd probably end up having > to add this schema to your search path to use the module conveniently. > > regards, tom lane I would prefer that vastly over what I had to deal with this time. Until I discovered the "untsearch2.sql" script I was seriously considering the trouble of backing this out <BY HAND> either in a dump or in the online database. Either would have been a stupendous amount of work. Please consider this change in approach - someone else has to have been bit in the butt by this one other than me by now. -- -- Karl Denninger (karl@denninger.net) Internet Consultant & Kids Rights Activist http://www.denninger.net My home on the net - links to everything I do! http://scubaforum.org Your UNCENSORED place to talk about DIVING! http://www.spamcuda.net SPAM FREE mailboxes - FREE FOR A LIMITED TIME! http://genesis3.blogspot.com Musings Of A Sentient Mind
On Thu, 3 Feb 2005, Karl Denninger wrote:
> On Thu, Feb 03, 2005 at 10:20:47PM -0500, Tom Lane wrote:
>> Karl Denninger <karl@denninger.net> writes:
>>> I agree with this - what would be even better would be a way to create
>>> 'subclasses' for things like this, which could then be 'included' easily.
>>
>> We could decree that a contrib module's script should create a schema
>> and shove everything it makes into that schema. Then "DROP SCHEMA CASCADE"
>> is all you need to get rid of it. However, you'd probably end up having
>> to add this schema to your search path to use the module conveniently.
>>
>> regards, tom lane
>
> I would prefer that vastly over what I had to deal with this time. Until I
> discovered the "untsearch2.sql" script I was seriously considering the
> trouble of backing this out <BY HAND> either in a dump or in the online
> database.
As I already pointed you should apply regprocedure_update.sql to your
7.4.1 database before dumping to get OIDs free tsearch2 functions !
This is the way most people dump their db without any problem. Unfortunately,
this was not documented in 7.4 readme, but it does in current documentations,
web site and discussed in mailing list.
>
> Either would have been a stupendous amount of work.
>
> Please consider this change in approach - someone else has to have been bit
> in the butt by this one other than me by now.
>
> --
>
Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
> We could decree that a contrib module's script should create a schema > and shove everything it makes into that schema. Then "DROP SCHEMA CASCADE" > is all you need to get rid of it. However, you'd probably end up having > to add this schema to your search path to use the module conveniently. > > regards, tom lane I currently load tsearch2 into a separate schema. It's a convenient way to separate tsearch2 from the rest of the database for backup procedures and listing database objects. To make this work as transparently as possible, I update the search_path to include the new schema to avoid explicit references. The only problem is that the search_path is stored in the catalog and not outputted in pg_dump files. You have to remember to set the search_path after restoring the database. George Essig