Thread: typo in src/interfaces/ecpg/preproc/preproc.y

typo in src/interfaces/ecpg/preproc/preproc.y

From
Brent Verner
Date:
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


Re: ALTER RENAME and indexes

From
Tom Lane
Date:
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


Re: ALTER RENAME and indexes

From
Brent Verner
Date:
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


Re: ALTER RENAME and indexes

From
Brent Verner
Date:
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


Re: ALTER RENAME and indexes

From
Brent Verner
Date:
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


Re: ALTER RENAME and indexes

From
Stephan Szabo
Date:
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.




Re: ALTER RENAME and indexes

From
Brent Verner
Date:
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


Re: ALTER RENAME and indexes

From
"Rod Taylor"
Date:
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
>