pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedrename idx's with table; avoid confusing idx names?
2+ messages / 2 participants
[nested] [flat]
* rename idx's with table; avoid confusing idx names?
@ 2005-12-02 18:00 george young <gry@ll.mit.edu>
0 siblings, 1 reply; 2+ messages in thread
From: george young @ 2005-12-02 18:00 UTC (permalink / raw)
To: pgsql-sql
[PostgreSQL 8.1.0 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.0.1]
After tearing out some hair over the following sequence of events:
[a few weeks ago]
alter table foo rename to old_foo;
create table foo(<somewhat different schema>);
insert into foo select blahblahblah from old_foo;
[today]
cluster foo_pkey on foo;
ERROR: "foo_pkey" is not an index for table "foo"
What????? Why does \d say the primary key idx is foo_pkey1 ????
[light dawns]
Aha! "alter table rename to" did not rename the table's indexes!
I put together a plpgsql function to rename a table and it's indexes
correspondingly[see below]. I would like to know:
Is there a more robust/portable/clear way to do this?
Is this a bad idea for some subtle reason?
Is there any way to get a less cumbersome interface than "select rename_table_and_indexes('foo','old_foo')?
Does this look useful enough for me to package more formally?
-- George Young
vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv
CREATE or REPLACE FUNCTION rename_table_and_indexes(old_name text, new_name text) returns void AS $$
declare
prefix_len integer;
r record;
begin
prefix_len = length(old_name);
for r in select indexrelname from pg_stat_user_indexes where relname=old_name loop
execute 'alter index ' || r.indexrelname || ' rename to ' || quote_ident(new_name) || substr(r.indexrelname, prefix_len + 1);
raise NOTICE 'renamed index % to %', r.indexrelname, new_name || substr(r.indexrelname, prefix_len + 1);
end loop;
execute 'alter table ' || quote_ident(old_name) || ' rename to ' || quote_ident(new_name);
raise NOTICE 'alter table % rename to %', old_name, new_name;
end;
$$
LANGUAGE plpgsql;
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
--
"Are the gods not just?" "Oh no, child.
What would become of us if they were?" (CSL)
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: rename idx's with table; avoid confusing idx names?
@ 2005-12-02 22:33 Tom Lane <tgl@sss.pgh.pa.us>
parent: george young <gry@ll.mit.edu>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2005-12-02 22:33 UTC (permalink / raw)
To: gry@ll.mit.edu; +Cc: pgsql-sql
george young <gry@ll.mit.edu> writes:
> I put together a plpgsql function to rename a table and it's indexes
> correspondingly[see below]. I would like to know:
> Is there a more robust/portable/clear way to do this?
> Is this a bad idea for some subtle reason?
It won't work if the table and column names are so long as to require
truncation to form an index name. Also there are some corner cases
in which you'll collide with existing index names. (The underlying
backend index-name creation logic goes to some effort to generate
nonconflicting index names, but this code isn't doing any such thing.)
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2005-12-02 22:33 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-12-02 18:00 rename idx's with table; avoid confusing idx names? george young <gry@ll.mit.edu>
2005-12-02 22:33 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox