Обсуждение: reindexdb -t schema.table name

Hi all,

I'm having trouble reindexing a table in a schema other than public, called history.

This syntax isn't working.

reindexdb -h fido -p 5432 -d abc -t history._name_history

reindexdb: reindexing of table "history._name_history" in database "abc" failed: ERROR:  relation"h                                                                                                                            istory._name_history" does not exist

I also tried it with double quotes.  -t "history._name_history.  The table is actually _name_history.

Thanks in advance,
~DjK                                                                                      

---

Windows Live™ SkyDrive™: Get 25 GB of free online storage. Get it on your BlackBerry or iPhone.

Hi,

I've also replicated DjK's issue using a table called "supplier" in my
"system" schema.

# andyshel bin (networkmail): ./reindexdb -h localhost -d mydb -t
"system.supplier"
reindexdb: reindexing of table "system.supplier" in database "mydb "
failed: ERROR:  relation "system.supplier" does not exist
reindexdb: reindexing of database "mydb " failed: ERROR:  relation
"system.supplier" does not exist

After passing the "-e" argument it appears reindexdb is running the
following SQL: REINDEX TABLE "system.supplier";

According to the documentation for 8.3.7 (the version I'm running) this
syntax should work:

Name
REINDEX— rebuild indexes
Synopsis
REINDEX { INDEX | TABLE | DATABASE | SYSTEM } name [ FORCE ]

.....

name
... Index and table names can be schema-qualified ...

Any ideas?

Thanks,
Andy

dx k9 wrote:
> Hi all,
>
> I'm having trouble reindexing a table in a schema other than public,
> called history.
>
> This syntax isn't working.
>
> reindexdb -h fido -p 5432 -d abc -t history._name_history
>
> reindexdb: reindexing of table "history._name_history" in database
> "abc" failed: ERROR:
> relation"h
                
> istory._name_history" does not exist
>
> I also tried it with double quotes.  -t "history._name_history.  The
> table is actually _name_history.
>
> Thanks in advance,
> ~DjK
>
>
>
>
> ------------------------------------------------------------------------
> Windows Live™ SkyDrive™: Get 25 GB of free online storage. Get it on
> your BlackBerry or iPhone.
> <http://windowslive.com/online/skydrive?ocid=TXT_TAGLM_WL_SD_25GB_062009>

I woinder if you have to modify the search path:

SHOW search_path;
  search_path
----------------
 "$user",public
(1 row)

Issue the statement

ALTER USER myuser SET search_path TO public,system;

Then run your reindex command and then alter it back, or leave it ...


-----Original Message-----
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Andy Shellam
Sent: Tuesday, June 09, 2009 2:18 PM
To: dx k9
Cc: posgres support
Subject: Re: [ADMIN] reindexdb -t schema.table name

Hi,

I've also replicated DjK's issue using a table called "supplier" in my
"system" schema.

# andyshel bin (networkmail): ./reindexdb -h localhost -d mydb -t
"system.supplier"
reindexdb: reindexing of table "system.supplier" in database "mydb "
failed: ERROR:  relation "system.supplier" does not exist
reindexdb: reindexing of database "mydb " failed: ERROR:  relation
"system.supplier" does not exist

After passing the "-e" argument it appears reindexdb is running the
following SQL: REINDEX TABLE "system.supplier";

According to the documentation for 8.3.7 (the version I'm running) this
syntax should work:

Name
REINDEX- rebuild indexes
Synopsis
REINDEX { INDEX | TABLE | DATABASE | SYSTEM } name [ FORCE ]

.....

name
... Index and table names can be schema-qualified ...

Any ideas?

Thanks,
Andy

dx k9 wrote:
> Hi all,
>
> I'm having trouble reindexing a table in a schema other than public,
> called history.
>
> This syntax isn't working.
>
> reindexdb -h fido -p 5432 -d abc -t history._name_history
>
> reindexdb: reindexing of table "history._name_history" in database
> "abc" failed: ERROR:
> relation"h
                
> istory._name_history" does not exist
>
> I also tried it with double quotes.  -t "history._name_history.  The
> table is actually _name_history.
>
> Thanks in advance,
> ~DjK
>
>
>
>
> ------------------------------------------------------------------------
> Windows Live(tm) SkyDrive(tm): Get 25 GB of free online storage. Get it on
> your BlackBerry or iPhone.
> <http://windowslive.com/online/skydrive?ocid=TXT_TAGLM_WL_SD_25GB_062009>

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Andy Shellam <andy-lists@networkmail.eu> writes:
> After passing the "-e" argument it appears reindexdb is running the
> following SQL: REINDEX TABLE "system.supplier";
> According to the documentation for 8.3.7 (the version I'm running) this
> syntax should work:

No.  What would work is REINDEX TABLE "system"."supplier";

reindexdb isn't designed to support schema-qualified names.  You'd
need another switch to pass the schema name.

A possible workaround if you really need the schema qualification is

PGOPTIONS="--search_path=system" reindexdb ... -t supplier

            regards, tom lane

Hi Tom

> No.  What would work is REINDEX TABLE "system"."supplier";
>

Ah, thanks for pointing that out, I never clicked on the quoting of the
table name.
> reindexdb isn't designed to support schema-qualified names.  You'd
> need another switch to pass the schema name.
>

Could it be an option that if the table name is already qualified and
quoted, reindexdb doesn't touch it?

e.g.

# ./reindexdb -h localhost -d mydb -t "system"."supplier" -e
REINDEX TABLE "system.supplier";

would become:

# ./reindexdb -h localhost -d mydb -t "system"."supplier" -e
REINDEX TABLE "system"."supplier";

but:

# ./reindexdb -h localhost -d mydb -t system.supplier -e
REINDEX TABLE "system.supplier";

would still remain the same because it wasn't correctly quoted.

Note: this doesn't actually affect me, and there are other options as
you pointed out, I'm just asking the question :-)

Thanks,
Andy

Andy Shellam <andy-lists@networkmail.eu> writes:
> Could it be an option that if the table name is already qualified and
> quoted, reindexdb doesn't touch it?

One small problem with the examples you give is that the shell would
immediately strip all the double-quotes.  By the time you've made it
shell-safe it's pretty ugly.

See also the pg_dump switches for selecting tables, which are a lot
more sophisticated than reindexdb or the other clients have got.
If anything at all is done in this area, it should be compatible
with what pg_dump does.

            regards, tom lane