Re: Support for REINDEX CONCURRENTLY

On 10 December 2012 06:03, Michael Paquier <michael(dot)paquier(at)gmail(dot)com> wrote:
>> On 2012-12-08 09:40:43 -0500, Tom Lane wrote:
>> > Andres Freund <andres(at)2ndquadrant(dot)com> writes:
>> > I'm tempted to propose that REINDEX CONCURRENTLY simply not try to
>> > preserve the index name exactly. Something like adding or removing
>> > trailing underscores would probably serve to generate a nonconflicting
>> > name that's not too unsightly. Or just generate a new name using the
>> > same rules that CREATE INDEX would when no name is specified. Yeah,
>> > it's a hack, but what about the CONCURRENTLY commands isn't a hack?
>>
>> I have no problem with ending up with a new name or something like
>> that. If that is what it takes: fine, no problem.
>
> For the indexes that are created internally by the system like toast or
> internal primary keys this is acceptable. However in the case of indexes
> that have been created externally I do not think it is acceptable as this
> impacts the user that created those indexes with a specific name.

If I have to choose between (1) keeping the same name OR (2) avoiding
an AccessExclusiveLock then I would choose (2). Most other people
would also, especially when all we would do is add/remove an
underscore. Even if that is user visible. And if it is we can support
a LOCK option that does (1) instead.

If we make it an additional constraint on naming, it won't be a
problem... namely that you can't create an index with/without an
underscore at the end, if a similar index already exists that has an
identical name apart from the suffix.

There are few, if any, commands that need the index name to remain the
same. For those, I think we can bend them to accept the index name and
then add/remove the underscore to get that to work.

That's all a little bit crappy, but this is too small a problem with
an important feature to allow us to skip.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services