Thread: Problem resolved (tsearch2 inhibiting migration)

Problem resolved (tsearch2 inhibiting migration)

From
Karl Denninger
Date:
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



Re: Problem resolved (tsearch2 inhibiting migration)

From
Michael Fuhr
Date:
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/

Re: Problem resolved (tsearch2 inhibiting migration)

From
Karl Denninger
Date:
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



Re: Problem resolved (tsearch2 inhibiting migration)

From
Tom Lane
Date:
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

Re: Problem resolved (tsearch2 inhibiting migration)

From
Karl Denninger
Date:
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



Re: Problem resolved (tsearch2 inhibiting migration)

From
Oleg Bartunov
Date:
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

Re: Problem resolved (tsearch2 inhibiting migration)

From
George Essig
Date:
> 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