Hi,

there is a problem with restore if oid of user defined function is used as
a reference ( for index creation, for example ). This is a pain especially
for novice. Built-in functions doesn't dumped, so there are no problem with
them. We regularly encounter this kind of problem with our contrib modules.
Are there any plans to resolve the problem for 7.3 ?

	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


Fri, 22 Feb 2002 12:55:11 +0300 (GMT)
Oleg Bartunov <oleg@sai.msu.su>
problem with restore of functions
Oleg Bartunov <oleg@sai.msu.su> writes:
> there is a problem with restore if oid of user defined function is used as
> a reference ( for index creation, for example ).

How so?  The OID isn't used in the pg_dump script, AFAIR.

			regards, tom lane

Fri, 22 Feb 2002 09:25:39 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: problem with restore of functions 
On Fri, 22 Feb 2002, Tom Lane wrote:

> Oleg Bartunov <oleg@sai.msu.su> writes:
> > there is a problem with restore if oid of user defined function is used as
> > a reference ( for index creation, for example ).
>
> How so?  The OID isn't used in the pg_dump script, AFAIR.
>

Sorry, I meant when we use contrib module, for example 'tsearch',
it creates functions, defines op_classes for 'gist_txtidx_ops',
which use OID of that functions.
pg_dump doesn't dumps op_clasess and it's impossible to use pg_restore.



> 			regards, tom lane
>

	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


Fri, 22 Feb 2002 18:20:14 +0300 (GMT)
Oleg Bartunov <oleg@sai.msu.su>
Re: problem with restore of functions 
Oleg Bartunov <oleg@sai.msu.su> writes:
> Sorry, I meant when we use contrib module, for example 'tsearch',
> it creates functions, defines op_classes for 'gist_txtidx_ops',
> which use OID of that functions.
> pg_dump doesn't dumps op_clasess and it's impossible to use pg_restore.

So the real gripe is that pg_dump doesn't dump user-defined opclasses.
That's a fair complaint.  Submit a patch ;-)

There was talk a little while back of developing an SQL command to
define opclasses, so you wouldn't have to poke the system catalogs
directly.  It'd probably be better to implement that and then fix
pg_dump to emit that command than to make pg_dump emit catalog-poking
commands ... but of course it'd be a lot more work.

			regards, tom lane

Fri, 22 Feb 2002 10:39:25 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: problem with restore of functions 
On Fri, 22 Feb 2002, Tom Lane wrote:

> Oleg Bartunov <oleg@sai.msu.su> writes:
> > Sorry, I meant when we use contrib module, for example 'tsearch',
> > it creates functions, defines op_classes for 'gist_txtidx_ops',
> > which use OID of that functions.
> > pg_dump doesn't dumps op_clasess and it's impossible to use pg_restore.
>
> So the real gripe is that pg_dump doesn't dump user-defined opclasses.
> That's a fair complaint.  Submit a patch ;-)

the only way i got around this was to:

pg_dump -f filename dbname

then either load all those user defined opclasses in template1, then
create the db where it will automatically create all those templates. or
create the dbname and load in those opclasses (tsearch.sql) etc.

then do restore (not with pg_restore) but with:

psql -f filename dbname  (remember to create the db and make sure the
user functions are loaded first)

there will be errors about function blah blah already existing, in
addition to the db already existing. but your db should be ok.

not the most elegant or fastest way, but that's the only way i found it to
work. i couldn't find away to use pg_dump -Fc... and pg_restore :(

--
Thomas T. Thai
Minnesota.com, Inc.




Sun, 3 Mar 2002 01:49:44 -0600 (CST)
"Thomas T. Thai" <tom@minnesota.com>
Re: problem with restore of functions 