Hi,

I'm using the ltree module and located several problems, I think not all problems are really ltree related but might be a pg_dump/pg_restore problem.

Here are the problems I've encountered:
* pg_restore tries to create a table with ltree and ltree[] datatypes before the type itself is created, so it fails.
* several functions are already defined in template1, so "create database " will restore these functions. pg_restore will try to restore those functions as well
  and fails. Maybe "create or replace function" can be used here? I'm willing to do this if it's ok.
* I've gist indices on ltree[] columns, and when pg_restore tries to restore it it will report:
  pg_restore: [archiver (db)] could not execute query: ERROR:  data type ltree[] has no default operator class for access method "gist"
          You must specify an operator class for the index or define a
          default operator class for the data type

  It seems those extra functions for index support are not restored. Removing the commit/end from ltree.sql and running it again (will report a lot of errors, but a few
  inserts as well) will make this work again. However it requires a lot of DBA assistance to restore.

All other problems are solvable by using "pg_restore -L" and using a modified archiv index, but IMO this should work out of the box.

Best regards,
	Mario Weilguni

Fri, 16 Aug 2002 11:50:57 +0200
Mario Weilguni <mweilguni@sime.com>
pg_restore and user defined types, several other pg_restore problems
This is known problem with pg_dump.
Don't remember if it's solved in 7.3

	Regards,

		Oleg
On Fri, 16 Aug 2002, Mario Weilguni wrote:

> Hi,
>
> I'm using the ltree module and located several problems, I think not all problems are really ltree related but might be a pg_dump/pg_restore problem.
>
> Here are the problems I've encountered:
> * pg_restore tries to create a table with ltree and ltree[] datatypes before the type itself is created, so it fails.
> * several functions are already defined in template1, so "create database " will restore these functions. pg_restore will try to restore those functions as well
>   and fails. Maybe "create or replace function" can be used here? I'm willing to do this if it's ok.
> * I've gist indices on ltree[] columns, and when pg_restore tries to restore it it will report:
>   pg_restore: [archiver (db)] could not execute query: ERROR:  data type ltree[] has no default operator class for access method "gist"
>           You must specify an operator class for the index or define a
>           default operator class for the data type
>
>   It seems those extra functions for index support are not restored. Removing the commit/end from ltree.sql and running it again (will report a lot of errors, but a few
>   inserts as well) will make this work again. However it requires a lot of DBA assistance to restore.
>
> All other problems are solvable by using "pg_restore -L" and using a modified archiv index, but IMO this should work out of the box.
>
> Best regards,
> 	Mario Weilguni
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>

	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, 16 Aug 2002 13:58:45 +0300 (GMT)
Oleg Bartunov <oleg@sai.msu.su>
Re: pg_restore and user defined types, several other
Mario Weilguni <mweilguni@sime.com> writes:
> Here are the problems I've encountered:
> * pg_restore tries to create a table with ltree and ltree[] datatypes before the type itself is created, so it fails.

Odd; what are the OIDs of the table and the datatypes?

> * several functions are already defined in template1, so "create database " will restore these functions. pg_restore will try to restore those functions as well
>   and fails. Maybe "create or replace function" can be used here?

No.  Use pg_restore per the documentation: make an empty database for it
to restore into (by cloning template0 instead of template1).

>   pg_restore: [archiver (db)] could not execute query: ERROR:  data type ltree[] has no default operator class for access method "gist"
>           You must specify an operator class for the index or define a
>           default operator class for the data type

Are you using recent sources?  As of two weeks or so ago, pg_dump should
know how to dump operator classes.

			regards, tom lane

Fri, 16 Aug 2002 09:51:52 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: pg_restore and user defined types,
	several other pg_restore problems
Am Freitag, 16. August 2002 15:51 schrieben Sie:
> Mario Weilguni <mweilguni@sime.com> writes:
> > Here are the problems I've encountered:
> > * pg_restore tries to create a table with ltree and ltree[] datatypes
> > before the type itself is created, so it fails.
>
> Odd; what are the OIDs of the table and the datatypes?

The table has 20517267, and the datatype has 85286596. The type was introduced later on, and added with 
"alter table add". Maybe this is the problem?

>
> > * several functions are already defined in template1, so "create database
> > " will restore these functions. pg_restore will try to restore those
> > functions as well and fails. Maybe "create or replace function" can be
> > used here?
>
> No.  Use pg_restore per the documentation: make an empty database for it
> to restore into (by cloning template0 instead of template1).

Oops, I did not know this. What happens if I use the -C switch of pg_restore?
The man page says:
      -C

       --create
              Create the database before restoring into it.  (When this switch
              appears, the database named with -d is used only  to  issue  the
              initial  CREATE  DATABASE command. All data is restored into the
              database name that appears in the archive.)

But does pg_restore use template0 or template1?

>
> >   pg_restore: [archiver (db)] could not execute query: ERROR:  data type
> > ltree[] has no default operator class for access method "gist" You must
> > specify an operator class for the index or define a default operator
> > class for the data type
>
> Are you using recent sources?  As of two weeks or so ago, pg_dump should
> know how to dump operator classes.

No, I do not use 7.3cvs, this is version 7.2.1. But if this is fixed, it's not a problem for me,
now I know how to restore the database, and 7.3 should not be too far away :-)

Thanks!

Best regards,
	Mario Weilguni


Sat, 17 Aug 2002 08:51:35 +0200
Mario Weilguni <mweilguni@sime.com>
Re: pg_restore and user defined types,
	several other pg_restore problems