Thread: rename index?

I am writing an analysis package that needs to create a table and index on a
live system.

The problem:

I have a program which does data analysis which, when completed, copys the
results back to PostgreSQL. This has to be done on a live system, therefore,
the summary table must be indexed prior to use. Here are the steps currently
needed:

create table fubar_tmp (...);
copy fubar_temp from stdin ;
create index fubar_tmp_id on fubar_tmp (id);
alter table fubar rename to fubar_old;
alter table fubar_tmp rename to fubar;
drop table fubar_old;
create index fubar_id on fubar(id);
drop index fubar_tmp_id;


It would be usefull to be able to do it this way:

create table fubar_tmp (...);
copy fubar_temp from stdin ;
alter index fubar_id rename fubar_id_old;
create index fubar_id on fubar_tmp (id);
alter table fubar rename to fubar_old;
alter table fubar_tmp rename to fubar;
drop table fubar_old;

The ability to rename an index so that it follows the table for which it was
created would be very helpfull. Otherwise one has to create a second index
prior summary tables being swapped, or come up with some way to track the index
name.

ALTER TABLE RENAME works on indexes (at least in recent releases).
        regards, tom lane

Tom Lane wrote:
> 
> ALTER TABLE RENAME works on indexes (at least in recent releases).
> 
>                         regards, tom lane

Hmm, how does that work? Is there a naming convention which I must follow for
this to work? (I am using 7.2B2 to devlope this system).

On 12 Nov 2001 at 13:35 (-0500), mlw wrote:
| Tom Lane wrote:
| > 
| > ALTER TABLE RENAME works on indexes (at least in recent releases).
| > 
| >                         regards, tom lane
| 
| Hmm, how does that work? Is there a naming convention which I must follow for
| this to work? (I am using 7.2B2 to devlope this system).

ALTER TABLE /will/ keep the index on the table, but the index will
retain its original name, i.e., idx_fubar_tmp_id, even after the 
table is renamed, so doing your create/copy/rename/drop sequence will 
not work the second time, since the temp index already exists (by name).

This seems like a useful feature to have.  Is there anything like 
this in SQL99?

cheers. brent-'who doesn''t have SQL99 docs'

-- 
"Develop your talent, man, and leave the world something. Records are 
really gifts from people. To think that an artist would love you enough
to share his music with anyone is a beautiful thing."  -- Duane Allman

Tom Lane wrote:
> 
> ALTER TABLE RENAME works on indexes (at least in recent releases).
> 
>                         regards, tom lane

OH, stupid me, I didn't get what you meant. Treat the index name as the table
name, i.e.

alter table fubar_idx rename to fubar_idx_old;

Yes, that works, but I would never have guessed that. Is that what Postgres
should be doing?

Might not it be useful to have an "alter Object ..." which will work on
Postgres objects, like sequences, functions, etc. to make general changes.
Using alter table to rename an index seems a bit arcane.

> Tom Lane wrote:
> > 
> > ALTER TABLE RENAME works on indexes (at least in recent releases).
> > 
> >                         regards, tom lane
> 
> OH, stupid me, I didn't get what you meant. Treat the index name as the table
> name, i.e.
> 
> alter table fubar_idx rename to fubar_idx_old;
> 
> Yes, that works, but I would never have guessed that. Is that what Postgres
> should be doing?
> 
> Might not it be useful to have an "alter Object ..." which will work on
> Postgres objects, like sequences, functions, etc. to make general changes.
> Using alter table to rename an index seems a bit arcane.

We have already forced DROP object to honor the object type, so ALTER
TABLE should do the same, right?  Do we need to add an ALTER INDEX
command, and an ALTER SEQUENCE command too?  Maybe ALTER NONTABLE?  :-)

Added to TODO:
       o Prevent ALTER TABLE RENAME from renaming indexes and sequences (?)

We can figure out figure out later how we want to address this.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 

> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Added to TODO:
> >         o Prevent ALTER TABLE RENAME from renaming indexes and sequences (?)
> 
> This would clearly be a step backwards, unless we provide alternate
> syntax.
> 
> While it's maybe a tad inconsistent to allow ALTER TABLE RENAME to work
> on the other relation types, I'm having a hard time getting excited about
> doing any work just to be more rigid about it.  There's a good reason
> for DROP to be extremely tight about what it will do: you can't always
> undo it.  So the more checking we can do to be sure you meant what you
> said, the better.  OTOH a mistaken RENAME is easy enough to undo, so I'm
> not so concerned about having very tight consistency checking on it.

Good point!  Item removed from TODO.  I will add documentation about
this capability.  Thanks.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Added to TODO:
>         o Prevent ALTER TABLE RENAME from renaming indexes and sequences (?)

This would clearly be a step backwards, unless we provide alternate
syntax.

While it's maybe a tad inconsistent to allow ALTER TABLE RENAME to work
on the other relation types, I'm having a hard time getting excited about
doing any work just to be more rigid about it.  There's a good reason
for DROP to be extremely tight about what it will do: you can't always
undo it.  So the more checking we can do to be sure you meant what you
said, the better.  OTOH a mistaken RENAME is easy enough to undo, so I'm
not so concerned about having very tight consistency checking on it.
        regards, tom lane