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.