Re: ALTER RENAME and indexes

On 05 Oct 2001 at 09:46 (-0400), Tom Lane wrote:
| Brent Verner <brent@rcfile.org> writes:
| > 'ALTER TABLE tbl RENAME col1 TO col2' does not update any indices that
| > reference the old column name.
| 
| It doesn't need to; the indexes link to column numbers, not column
| names.

Forgive my incorrect description of the problem... By example...


brent=# select version();                           version                            
---------------------------------------------------------------PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC
2.95.4
(1 row)

brent=# create table test ( id serial, col1 varchar(64) NOT NULL);
NOTICE:  CREATE TABLE will create implicit sequence 'test_id_seq' for SERIAL column 'test.id'
NOTICE:  CREATE TABLE/UNIQUE will create implicit index 'test_id_key' for table 'test'
CREATE
brent=# create index idx_test_col1 on test(col1);
CREATE
brent=# \d idx_test_col1      Index "idx_test_col1"Attribute |         Type          
-----------+-----------------------col1      | character varying(64)
btree

brent=# alter table test rename col1 to col2;
ALTER
brent=# \d idx_test_col1      Index "idx_test_col1"Attribute |         Type          
-----------+-----------------------col1      | character varying(64)
btree

brent=# \d test                                   Table "test"Attribute |         Type          |
Modifier                    
 
-----------+-----------------------+-------------------------------------------------id        | integer
|not null default nextval('"test_id_seq"'::text)col2      | character varying(64) | not null
 
Indices: idx_test_col1,        test_id_key


 I hit this problem using the jdbc driver, and originally thought 
it was the jdbc code, but as the above shows, the problem seems to
be a failure to update one (or more) of the system catalogs.
 Again, any pointers beyond look in src/backend/commands/rename.c
would be appreciated.  My big question is how is the content of
the system tables used/affected from within PG -- I originally 
thought it would be simple enough to issue some SQL to properly
update the system tables, but apparently that idea was /very/ naive.
Is there any way to list all $things that reference the altered
entity? find_all_inheritors() does not /appear/ to be getting 
everything that needs to be updated.

Also, a lot of terminology within the code is making my head spin (not
a difficult task ;-)), but then I've only spent about two hours 
digging around this code.  Is there a 'understanding internal PostgreSQL
terminology' document that I've missed?

thanks. Brent

-- 
"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