Обсуждение: 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.
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