FAQ
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.
http://windowslive.com/online/skydrive?ocid=TXT_TAGLM_WL_SD_25GB_062009

Search Discussions

  • Andy Shellam at Jun 9, 2009 at 7:17 pm
    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>
  • Plugge, Joe R. at Jun 9, 2009 at 7:36 pm
    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 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
  • Tom Lane at Jun 9, 2009 at 7:57 pm

    Andy Shellam 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
  • Andy Shellam at Jun 9, 2009 at 8:29 pm
    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
  • Tom Lane at Jun 9, 2009 at 8:39 pm

    Andy Shellam 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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-admin @
categoriespostgresql
postedJun 9, '09 at 6:50p
activeJun 9, '09 at 8:39p
posts6
users4
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase