Обсуждение: BUG #10823: Better REINDEX syntax.
The following bug has been logged on the website: Bug reference: 10823 Logged by: Daniel Migowski Email address: dmigowski@ikoffice.de PostgreSQL version: 9.1.13 Operating system: n/a Description: Hello. Compared to CLUSTER and VACUUM FULL we need to specify a database to the REINDEX command. Why? It would be logical to reindex the current database, exactly like CLUSTER does. So why isn't the DATABASE parameter optional? PS: Thanks for all your work on this great database!
On Tue, Jul 1, 2014 at 10:33:07AM +0000, dmigowski@ikoffice.de wrote:
> The following bug has been logged on the website:
>
> Bug reference: 10823
> Logged by: Daniel Migowski
> Email address: dmigowski@ikoffice.de
> PostgreSQL version: 9.1.13
> Operating system: n/a
> Description:
>
> Hello.
>
> Compared to CLUSTER and VACUUM FULL we need to specify a database to the
> REINDEX command. Why? It would be logical to reindex the current database,
> exactly like CLUSTER does. So why isn't the DATABASE parameter optional?
Wow, yeah, that is kind of odd, e.g.
REINDEX { INDEX | TABLE | DATABASE | SYSTEM } name [ FORCE ]
...
name
The name of the specific index, table, or database
to be reindexed. Index and table names can be
schema-qualified. Presently, REINDEX DATABASE and REINDEX SYSTEM
can only reindex the current database, so their parameter must
match the current database's name.
Let me look at improving that for 9.5.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ Everyone has their own god. +
Bruce Momjian <bruce@momjian.us> writes:
> On Tue, Jul 1, 2014 at 10:33:07AM +0000, dmigowski@ikoffice.de wrote:
>> Compared to CLUSTER and VACUUM FULL we need to specify a database to the
>> REINDEX command. Why? It would be logical to reindex the current database,
>> exactly like CLUSTER does. So why isn't the DATABASE parameter optional?
> Wow, yeah, that is kind of odd, e.g.
I don't find it all that odd. We should not be encouraging routine
database-wide reindexes.
regards, tom lane
On Wed, Jul 30, 2014 at 01:29:31PM -0400, Tom Lane wrote: > Bruce Momjian <bruce@momjian.us> writes: > > On Tue, Jul 1, 2014 at 10:33:07AM +0000, dmigowski@ikoffice.de wrote: > >> Compared to CLUSTER and VACUUM FULL we need to specify a database to the > >> REINDEX command. Why? It would be logical to reindex the current database, > >> exactly like CLUSTER does. So why isn't the DATABASE parameter optional? > > > Wow, yeah, that is kind of odd, e.g. > > I don't find it all that odd. We should not be encouraging routine > database-wide reindexes. Uh, do we encourage database-wide VACUUM FULL or CLUSTER, as we use them there with no parameter. Is there a reason REINDEX should be harder, and require a dummy argument to run? -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. +
Bruce Momjian <bruce@momjian.us> writes:
> On Wed, Jul 30, 2014 at 01:29:31PM -0400, Tom Lane wrote:
>> I don't find it all that odd. We should not be encouraging routine
>> database-wide reindexes.
> Uh, do we encourage database-wide VACUUM FULL or CLUSTER, as we use them
> there with no parameter. Is there a reason REINDEX should be harder,
> and require a dummy argument to run?
I believe that REINDEX on system catalogs carries a risk of deadlock
failures against other processes --- there was a recent example of that
in the mailing lists. VACUUM FULL has such risks too, but that's been
pretty well deprecated for many years. (I think CLUSTER is probably
relatively safe on this score because it's not going to think any system
catalogs are clustered.)
If there were a variant of REINDEX that only hit user tables, I'd be fine
with making that easy to invoke.
regards, tom lane
On 07/30/2014 07:35 PM, Bruce Momjian wrote: > On Wed, Jul 30, 2014 at 01:29:31PM -0400, Tom Lane wrote: >> Bruce Momjian <bruce@momjian.us> writes: >>> On Tue, Jul 1, 2014 at 10:33:07AM +0000, dmigowski@ikoffice.de wrote: >>>> Compared to CLUSTER and VACUUM FULL we need to specify a database to the >>>> REINDEX command. Why? It would be logical to reindex the current database, >>>> exactly like CLUSTER does. So why isn't the DATABASE parameter optional? >> >>> Wow, yeah, that is kind of odd, e.g. >> >> I don't find it all that odd. We should not be encouraging routine >> database-wide reindexes. > > Uh, do we encourage database-wide VACUUM FULL or CLUSTER, as we use them > there with no parameter. Is there a reason REINDEX should be harder, > and require a dummy argument to run? I agree. The request isn't for a naked REINDEX command, it's for a naked REINDEX DATABASE command. -- Vik
On Wed, Jul 30, 2014 at 07:48:39PM +0200, Vik Fearing wrote: > On 07/30/2014 07:35 PM, Bruce Momjian wrote: > > On Wed, Jul 30, 2014 at 01:29:31PM -0400, Tom Lane wrote: > >> Bruce Momjian <bruce@momjian.us> writes: > >>> On Tue, Jul 1, 2014 at 10:33:07AM +0000, dmigowski@ikoffice.de wrote: > >>>> Compared to CLUSTER and VACUUM FULL we need to specify a database to the > >>>> REINDEX command. Why? It would be logical to reindex the current database, > >>>> exactly like CLUSTER does. So why isn't the DATABASE parameter optional? > >> > >>> Wow, yeah, that is kind of odd, e.g. > >> > >> I don't find it all that odd. We should not be encouraging routine > >> database-wide reindexes. > > > > Uh, do we encourage database-wide VACUUM FULL or CLUSTER, as we use them > > there with no parameter. Is there a reason REINDEX should be harder, > > and require a dummy argument to run? > > I agree. The request isn't for a naked REINDEX command, it's for a > naked REINDEX DATABASE command. Yes, the question is should we support REINDEX DATABASE without a database name that matches the current database. REINDEX alone might be too risky. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. +