Postgres Pro
Downloads
Home   >   mailing lists

Thread: BUG #8394: SQL command REINDEX doesn't work

BUG #8394: SQL command REINDEX doesn't work

From
yzhang@trinityusa.com
Date:
The following bug has been logged on the website:

Bug reference:      8394
Logged by:          Yz Primus
Email address:      yzhang@trinityusa.com
PostgreSQL version: 9.1.3
Operating system:   Windows 7 64-bit
Description:

I always received error when trying to run SQL command REINDEX either from
pgAdmin III or psql. If I ran REINDEX DATABASE it threw error:
ERROR:  syntax error at end of input
LINE 1: REINDEX DATABASE


If I ran REINDEX DATABASE mydb it threw error:
ERROR:  can only reindex the currently open database

Re: BUG #8394: SQL command REINDEX doesn't work

From
Thomas Kellerer
Date:
yzhang@trinityusa.com wrote on 23.08.2013 18:00:
> The following bug has been logged on the website:
>
> Bug reference:      8394
> Logged by:          Yz Primus
> Email address:      yzhang@trinityusa.com
> PostgreSQL version: 9.1.3
> Operating system:   Windows 7 64-bit
> Description:
>
> I always received error when trying to run SQL command REINDEX either from
> pgAdmin III or psql. If I ran REINDEX DATABASE it threw error:
> ERROR:  syntax error at end of input
> LINE 1: REINDEX DATABASE
>
>
> If I ran REINDEX DATABASE mydb it threw error:
> ERROR:  can only reindex the currently open database
>
Quote from the manual:

   Presently, REINDEX DATABASE and REINDEX SYSTEM can only reindex the current database,
   so their parameter must match the current database's name.

So you need to specify the name of the current database even though it's not really needed.

Re: BUG #8394: SQL command REINDEX doesn't work

From
"Yong Zhang"
Date:
VGhhbmtzIGZvciB0aGUgcHJvbXB0IHJlc3BvbnNlLiBCdXQgdGhhdCdzIHdoYXQgSSB0cmllZCBh
bmQgaXQgZGlkbid0IHdvcmsuIEkgdHJpZWQgZm9sbG93aW5nIGNvbW1hbmQgaW4gYm90aCBwZ0Fk
bWluIElJSSBhbmQgcHNxbDoNCg0KIA0KDQogICAgICBSRUlOREVYIERBVEFCQVNFIFByZW1pZXJJ
RVgNCg0KIA0KDQpUaGVuIEkgZ290IGVycm9yDQoNCiANCg0KICAgICAgRVJST1I6ICBjYW4gb25s
eSByZWluZGV4IHRoZSBjdXJyZW50bHkgb3BlbiBkYXRhYmFzZQ0KDQogDQoNClByZW1pZXJJRVgg
aXMgbXkgY3VycmVudCBkYXRhYmFzZS4gSW4gcGdBZG1pbiBJSUkgaXQgdGhyZXcgZXh0cmEgaW5m
bzoNCg0KIA0KDQogICAgICBTUUwgc3RhdGU6IDBBMDAwDQoNCiANCg0KVGhhbmtzLA0KDQogDQoN
CllvbmcNCg0KIA0KDQotLS0tLU9yaWdpbmFsIE1lc3NhZ2UtLS0tLQ0KRnJvbTogVGhvbWFzIEtl
bGxlcmVyIFttYWlsdG86c3BhbV9lYXRlckBnbXgubmV0XSANClNlbnQ6IEZyaWRheSwgQXVndXN0
IDIzLCAyMDEzIDM6MTMgUE0NClRvOiBZb25nIFpoYW5nDQpDYzogWW9uZyBaaGFuZw0KU3ViamVj
dDogUmU6IEJVRyAjODM5NDogU1FMIGNvbW1hbmQgUkVJTkRFWCBkb2Vzbid0IHdvcmsNCg0KIA0K
DQp5emhhbmdAdHJpbml0eXVzYS5jb20gPG1haWx0bzp5emhhbmdAdHJpbml0eXVzYS5jb20+ICB3
cm90ZSBvbiAyMy4wOC4yMDEzIDE4OjAwOg0KDQo+IFRoZSBmb2xsb3dpbmcgYnVnIGhhcyBiZWVu
IGxvZ2dlZCBvbiB0aGUgd2Vic2l0ZToNCg0KPiANCg0KPiBCdWcgcmVmZXJlbmNlOiAgICAgIDgz
OTQNCg0KPiBMb2dnZWQgYnk6ICAgICAgICAgIFl6IFByaW11cw0KDQo+IEVtYWlsIGFkZHJlc3M6
ICAgICAgeXpoYW5nQHRyaW5pdHl1c2EuY29tIDxtYWlsdG86eXpoYW5nQHRyaW5pdHl1c2EuY29t
PiANCg0KPiBQb3N0Z3JlU1FMIHZlcnNpb246IDkuMS4zDQoNCj4gT3BlcmF0aW5nIHN5c3RlbTog
ICBXaW5kb3dzIDcgNjQtYml0DQoNCj4gRGVzY3JpcHRpb246DQoNCj4gDQoNCj4gSSBhbHdheXMg
cmVjZWl2ZWQgZXJyb3Igd2hlbiB0cnlpbmcgdG8gcnVuIFNRTCBjb21tYW5kIFJFSU5ERVggZWl0
aGVyIA0KDQo+IGZyb20gcGdBZG1pbiBJSUkgb3IgcHNxbC4gSWYgSSByYW4gUkVJTkRFWCBEQVRB
QkFTRSBpdCB0aHJldyBlcnJvcjoNCg0KPiBFUlJPUjogIHN5bnRheCBlcnJvciBhdCBlbmQgb2Yg
aW5wdXQNCg0KPiBMSU5FIDE6IFJFSU5ERVggREFUQUJBU0UNCg0KPiANCg0KPiANCg0KPiBJZiBJ
IHJhbiBSRUlOREVYIERBVEFCQVNFIG15ZGIgaXQgdGhyZXcgZXJyb3I6DQoNCj4gRVJST1I6ICBj
YW4gb25seSByZWluZGV4IHRoZSBjdXJyZW50bHkgb3BlbiBkYXRhYmFzZQ0KDQo+IA0KDQpRdW90
ZSBmcm9tIHRoZSBtYW51YWw6DQoNCiANCg0KICAgUHJlc2VudGx5LCBSRUlOREVYIERBVEFCQVNF
IGFuZCBSRUlOREVYIFNZU1RFTSBjYW4gb25seSByZWluZGV4IHRoZSBjdXJyZW50IGRhdGFiYXNl
LA0KDQogICBzbyB0aGVpciBwYXJhbWV0ZXIgbXVzdCBtYXRjaCB0aGUgY3VycmVudCBkYXRhYmFz
ZSdzIG5hbWUuDQoNCiANCg0KU28geW91IG5lZWQgdG8gc3BlY2lmeSB0aGUgbmFtZSBvZiB0aGUg
Y3VycmVudCBkYXRhYmFzZSBldmVuIHRob3VnaCBpdCdzIG5vdCByZWFsbHkgbmVlZGVkLg0KDQog
DQoNCiANCg0KIA0KDQo=

Re: BUG #8394: SQL command REINDEX doesn't work

From
Jaime Casanova
Date:
On Fri, Aug 23, 2013 at 3:39 PM, Yong Zhang <yzhang@trinityusa.com> wrote:
> Thanks for the prompt response. But that's what I tried and it didn't wor=
k.
> I tried following command in both pgAdmin III and psql:
>
>
>
>       REINDEX DATABASE PremierIEX
>

If that is the name of the database, then it probably is because of
the uppercase letters. Try this way:

REINDEX DATABASE "PremierIEX"

--=20
Jaime Casanova         www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitaci=F3n
Phone: +593 4 5107566         Cell: +593 987171157

Re: BUG #8394: SQL command REINDEX doesn't work

From
Kevin Grittner
Date:
Jaime Casanova <jaime@2ndquadrant.com> wrote:=0A=0A> If that is the name of=
 the database, then it probably is because=0A> of the uppercase letters. Tr=
y this way:=0A>=0A> REINDEX DATABASE "PremierIEX"=0A=0ASome command shells =
will eat the quotes when done like that.=A0 It=0Amay be necessary to use th=
is style of quoting:=0A=0AREINDEX DATABASE '"PremierIEX"'=0A=0A--=0AKevin G=
rittner=0AEDB: http://www.enterprisedb.com=0AThe Enterprise PostgreSQL Comp=
any