Обсуждение: Reindex + relation doesnt exist

I am trying to reindex some indexes through a batch script.  for
example

Database : hermes
Schema : sc1
Indexes : ind1

when i use reindexdb with the following switches

 ./reindexdb --index=ind1 hermes

i get the below error message
reindexdb: reindexing of index "ind1" in database "hermes" failed:
ERROR:  relation "ind1" does not exist

when i know that the index does exist, same thing happens if i set up
a batch script and call it through psql command using the reindex
index ind1;

I guess i'm missing something really obvious just need someone to
point it out to me !! Any help is much apprciated
Thanks
Fran

On Mon, Mar 10, 2008 at 8:49 AM,  <frant101@googlemail.com> wrote:
> I am trying to reindex some indexes through a batch script.  for
>  example
>
>  Database : hermes
>  Schema : sc1
>  Indexes : ind1
>
>  when i use reindexdb with the following switches
>
>   ./reindexdb --index=ind1 hermes
>
>  i get the below error message
>  reindexdb: reindexing of index "ind1" in database "hermes" failed:
>  ERROR:  relation "ind1" does not exist
>
>  when i know that the index does exist, same thing happens if i set up
>  a batch script and call it through psql command using the reindex
>  index ind1;

Is it perhaps in a different schema / search path than the one you're
accessing?  If you append the schema to the name does it work?

On Mar 10, 10:43 pm, scott.marl...@gmail.com ("Scott Marlowe") wrote:
> On Mon, Mar 10, 2008 at 8:49 AM,  <frant...@googlemail.com> wrote:
> > I am trying to reindex some indexes through a batch script.  for
> >  example
>
> >  Database : hermes
> >  Schema : sc1
> >  Indexes : ind1
>
> >  when i use reindexdb with the following switches
>
> >   ./reindexdb --index=ind1 hermes
>
> >  i get the below error message
> >  reindexdb: reindexing of index "ind1" in database "hermes" failed:
> >  ERROR:  relation "ind1" does not exist
>
> >  when i know that the index does exist, same thing happens if i set up
> >  a batch script and call it through psql command using the reindex
> >  index ind1;
>
> Is it perhaps in a different schema / search path than the one you're
> accessing?  If you append the schema to the name does it work?
>
> --
> Sent via pgsql-admin mailing list (pgsql-ad...@postgresql.org)
> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-admin- Hide quoted text -
>
> - Show quoted text -

I have tried to put the schema name in front of the index name put i
still get the same error.

./reindexdb -e --index sc1.ind1 hermes
reindexdb: reindexing of index "sc1.ind1" in database "hermes" failed:
ERROR:  relation "sc1.ind1" does not exist

If i use the command on an index in the public schema it works fine.
Which i guess relates to the search path that you mentioned in your
post...just dont understand why the above command doesnt work.

Thanks
Fran

frant101@googlemail.com writes:
> I have tried to put the schema name in front of the index name put i
> still get the same error.

> ./reindexdb -e --index sc1.ind1 hermes
> reindexdb: reindexing of index "sc1.ind1" in database "hermes" failed:
> ERROR:  relation "sc1.ind1" does not exist

AFAICS reindexdb won't allow that --- it quotes the name you supply,
so that's going to look like a table name that happens to contain a
dot, not a schema and table name.

You didn't say where you are inserting the non-default schema path
into your interactive sessions...

            regards, tom lane

On Mar 11, 3:07 pm, t...@sss.pgh.pa.us (Tom Lane) wrote:
> frant...@googlemail.com writes:
> > I have tried to put the schema name in front of the index name put i
> > still get the same error.
> > ./reindexdb -e --index sc1.ind1 hermes
> > reindexdb: reindexing of index "sc1.ind1" in database "hermes" failed:
> > ERROR:  relation "sc1.ind1" does not exist
>
> AFAICS reindexdb won't allow that --- it quotes the name you supply,
> so that's going to look like a table name that happens to contain a
> dot, not a schema and table name.
>
> You didn't say where you are inserting the non-default schema path
> into your interactive sessions...
>
>                         regards, tom lane
>
> --
> Sent via pgsql-admin mailing list (pgsql-ad...@postgresql.org)
> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-admin

I guess what i will need to do is put the following command in the
script :

SET search_path TO sc1;

and then use the standard REINDEX command and this should do the job
for me...

Just that i need to run the job out of hours as i cant afford the
table to be locked through work hours and the index seems to have
swelled to 3GB  even though the table is only 1GB

thanks again for the help
Fran