On 05 Oct 2001 at 09:46 (-0400), Tom Lane wrote:
| Brent Verner <[EMAIL PROTECTED]> 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
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
