Thread: typo in src/interfaces/ecpg/preproc/preproc.y
Hi,
In trying to solve a bug in 'ALTER TABLE tbl RENAME col1 TO col2',I
noticed (what must be) a typo in src/interfaces/ecpg/preproc/preproc.y
patch attached, tho it might be easier if you just look for this
line in the file:
opt_column: COLUMN { $$ = make_str("colmunn"); }
Back to that original bug...
'ALTER TABLE tbl RENAME col1 TO col2' does not update any indices that
reference the old column name. Any suggestions to get this worked out
would be appreciated :-) I'll have some time this weekend to dig into
this, and would /really/ to tackle this myself.
cheers. 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
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.
regards, tom lane
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
On 05 Oct 2001 at 10:18 (-0400), Brent Verner wrote: | 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. ah, I think I see the problem... The pg_attribute.attname just needs updating, right? I suspect this after noticing that the pg_get_indexdef(Oid) function produced the correct(expected) results, while those using pg_attribute were wrong. If this is the _wrong_ answer for this, stop me before I make a big mess :-) working... b -- "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
On 06 Oct 2001 at 20:13 (-0400), Rod Taylor wrote: | Of course, in 7.1 foreign key constraints become rather confused when | you rename columns on them. | | create table parent (id serial); | create table child (id int4 references parent(id) on update cascade); | alter table parent rename column id to anotherid; | alter table child rename column id to junk; | insert into child values (1); | | -> ERROR: constraint <unnamed>: table child does now have an | attribute id ok, I see where this breaks. The args to the RI_ConstraintTrigger_%d are written into the pg_trigger tuple like so.. '<unnamed>\000child\000parent\000UNSPECIFIED\000id\000id\000' There are really two approaches, AFAICS. 1) modify this tgargs value to reflect the modified column name(s). 2) modify <whatever uses these args> to use the oid instead of the column names, and modify CreateTrigger to reflect thischange.. #2 seems to be the most bulletproof approach, so I'm looking into hacking this up right now. Any comments would be much appreciated about any (better) ways to fix this problem. cheers. 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
On Sat, 6 Oct 2001, Brent Verner wrote: > On 06 Oct 2001 at 20:13 (-0400), Rod Taylor wrote: > | Of course, in 7.1 foreign key constraints become rather confused when > | you rename columns on them. > | > | create table parent (id serial); > | create table child (id int4 references parent(id) on update cascade); > | alter table parent rename column id to anotherid; > | alter table child rename column id to junk; > | insert into child values (1); > | > | -> ERROR: constraint <unnamed>: table child does now have an > | attribute id > > ok, I see where this breaks. The args to the RI_ConstraintTrigger_%d > are written into the pg_trigger tuple like so.. > > '<unnamed>\000child\000parent\000UNSPECIFIED\000id\000id\000' > > There are really two approaches, AFAICS. > > 1) modify this tgargs value to reflect the modified column name(s). > 2) modify <whatever uses these args> to use the oid instead of > the column names, and modify CreateTrigger to reflect this change.. > > #2 seems to be the most bulletproof approach, so I'm looking > into hacking this up right now. Any comments would be much > appreciated about any (better) ways to fix this problem. #2 also requires changes to dump/restore stuff, since AFAIK it currently dumps create constraint trigger statements and the oids won't be known for the restore, but this is probably a good idea in general.
On 07 Oct 2001 at 04:03 (-0700), Stephan Szabo wrote: | | On Sat, 6 Oct 2001, Brent Verner wrote: | | > On 06 Oct 2001 at 20:13 (-0400), Rod Taylor wrote: | > | Of course, in 7.1 foreign key constraints become rather confused when | > | you rename columns on them. | > 1) modify this tgargs value to reflect the modified column name(s). | > 2) modify <whatever uses these args> to use the oid instead of | > the column names, and modify CreateTrigger to reflect this change.. | > | > #2 seems to be the most bulletproof approach, so I'm looking | > into hacking this up right now. Any comments would be much | > appreciated about any (better) ways to fix this problem. | | #2 also requires changes to dump/restore stuff, since AFAIK | it currently dumps create constraint trigger statements and the | oids won't be known for the restore, but this is probably a good | idea in general. After looking this over for a couple of hours and seeing how many places would have to be touched, combined with the pg_dump breakage makes #2 an unreasonable task for me to complete before real life stops my party. Plus, being this close to beta, this fix/hack might actually get into 7.2, since it will be a really minor addition to rename.c. Thanks for alerting me to pg_dump's dependency on this stuff :-). cheers. 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
Of course, in 7.1 foreign key constraints become rather confused when you rename columns on them. create table parent (id serial); create table child (id int4 references parent(id) on update cascade); alter table parent rename column id to anotherid; alter table child rename column id to junk; insert into child values (1); -> ERROR: constraint <unnamed>: table child does now have an attribute id -- Rod Taylor There are always four sides to every story: your side, their side, the truth, and what really happened. ----- Original Message ----- From: "Brent Verner" <brent@rcfile.org> To: "pgsql-hackers" <pgsql-hackers@postgresql.org> Sent: Saturday, October 06, 2001 7:49 PM Subject: Re: [HACKERS] ALTER RENAME and indexes > On 05 Oct 2001 at 10:18 (-0400), Brent Verner wrote: > | 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. > > ah, I think I see the problem... The pg_attribute.attname just needs > updating, right? I suspect this after noticing that the > pg_get_indexdef(Oid) function produced the correct(expected) results, > while those using pg_attribute were wrong. > > If this is the _wrong_ answer for this, stop me before I make a > big mess :-) > > working... > b > > -- > "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 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >