hi.

I just discovered that doing an alter table ... alter
column (to rename a column) does not do a complete
rename throughout the database.

for example, say you have table a, with columns b and
c.  b is your primary key.

now rename b to new_b.  if you do a dump of the schema
after you rename, you'll find that you can't reload
that schema because at the bottom of the definition of
table a you have PRIMARY KEY ("b").

shouldn't rename update any index and key definitions?

also, and this may actually the source of the problem,
while scanning my full (schema and data) dump, I
noticed that the contents of table pga_layout also had
the old values of columns that I have renamed.

I'm very frightened right now, because I'm rather
dependent upon my database right now.  I don't like
the thought that my database is corrupt at the schema
level.

michael

__________________________________________________
Do You Yahoo!?
Yahoo! Messenger - Talk while you surf!  It's FREE.
http://im.yahoo.com/

Thu, 26 Oct 2000 22:01:41 -0700 (PDT)
Michael Teter <michael_teter@yahoo.com>
renaming columns... danger?

Just tested this on latest devel. version, and there does seem to be a
problem.

[]$ psql test
Welcome to psql, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

test=# select version();
                                version
------------------------------------------------------------------------

 PostgreSQL 7.1devel on i686-pc-linux-gnu, compiled by GCC egcs-2.91.66
(1 row)

test=# create table a ( aa serial primary key );
NOTICE:  CREATE TABLE will create implicit sequence 'a_aa_seq' for
SERIAL column 'a.aa'
NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'a_pkey'
for table 'a'
CREATE
test=# alter TABLE a RENAME aa to new_aa;
ALTER

[]$ pg_dump test
--
-- Selected TOC Entries:
--
\connect - gaf
--
-- TOC Entry ID 2 (OID 20352)
--
-- Name: "a_aa_seq" Type: SEQUENCE Owner: gaf
--

CREATE SEQUENCE "a_aa_seq" start 1 increment 1 maxvalue 2147483647
minvalue 1  cache 1 ;

--
-- TOC Entry ID 4 (OID 20370)
--
-- Name: a Type: TABLE Owner: gaf
--

CREATE TABLE "a" (
 "new_aa" integer DEFAULT nextval('"a_aa_seq"'::text) NOT NULL,
 PRIMARY KEY ("aa")
);

--
-- Data for TOC Entry ID 5 (OID 20370) TABLE DATA a
--

-- Disable triggers
UPDATE "pg_class" SET "reltriggers" = 0 WHERE "relname" ~* 'a';
COPY "a"  FROM stdin;
\.
-- Enable triggers
BEGIN TRANSACTION;
CREATE TEMP TABLE "tr" ("tmp_relname" name, "tmp_reltriggers" smallint);

INSERT INTO "tr" SELECT C."relname", count(T."oid") FROM "pg_class" C,
"pg_trigger" T WHERE C."oid" = T."tgrelid" AND C."relname" ~* 'a'  GROUP
BY 1;
UPDATE "pg_class" SET "reltriggers" = TMP."tmp_reltriggers" FROM "tr"
TMP WHERE "pg_class"."relname" = TMP."tmp_relname";
DROP TABLE "tr";
COMMIT TRANSACTION;

--
-- TOC Entry ID 3 (OID 20352)
--
-- Name: "a_aa_seq" Type: SEQUENCE SET Owner:
--

SELECT setval ('"a_aa_seq"', 1, 'f');



Michael Teter wrote:

> hi.
>
> I just discovered that doing an alter table ... alter
> column (to rename a column) does not do a complete
> rename throughout the database.
>
> for example, say you have table a, with columns b and
> c.  b is your primary key.
>
> now rename b to new_b.  if you do a dump of the schema
> after you rename, you'll find that you can't reload
> that schema because at the bottom of the definition of
> table a you have PRIMARY KEY ("b").
>
> shouldn't rename update any index and key definitions?
>
> also, and this may actually the source of the problem,
> while scanning my full (schema and data) dump, I
> noticed that the contents of table pga_layout also had
> the old values of columns that I have renamed.
>
> I'm very frightened right now, because I'm rather
> dependent upon my database right now.  I don't like
> the thought that my database is corrupt at the schema
> level.
>
> michael
>
> __________________________________________________
> Do You Yahoo!?
> Yahoo! Messenger - Talk while you surf!  It's FREE.
> http://im.yahoo.com/

--
> Poorly planned software requires a genius to write it
> and a hero to use it.

Grant Finnemore BSc(Eng)  (mailto:gaf@ucs.co.za)
Software Engineer         Universal Computer Services
Tel  (+27)(11)712-1366    PO Box 31266 Braamfontein 2017, South Africa
Cell (+27)(82)604-5536    20th Floor, 209 Smit St., Braamfontein
Fax  (+27)(11)339-3421    Johannesburg, South Africa



Fri, 27 Oct 2000 10:30:19 +0200
Grant Finnemore <gaf@ucs.co.za>
Re: [SQL] renaming columns... danger?

Just tested this on latest devel. version, and there does seem to be a
problem.

[]$ psql test
Welcome to psql, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

test=# select version();
                                version
------------------------------------------------------------------------

 PostgreSQL 7.1devel on i686-pc-linux-gnu, compiled by GCC egcs-2.91.66
(1 row)

test=# create table a ( aa serial primary key );
NOTICE:  CREATE TABLE will create implicit sequence 'a_aa_seq' for
SERIAL column 'a.aa'
NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'a_pkey'
for table 'a'
CREATE
test=# alter TABLE a RENAME aa to new_aa;
ALTER

[]$ pg_dump test
--
-- Selected TOC Entries:
--
\connect - gaf
--
-- TOC Entry ID 2 (OID 20352)
--
-- Name: "a_aa_seq" Type: SEQUENCE Owner: gaf
--

CREATE SEQUENCE "a_aa_seq" start 1 increment 1 maxvalue 2147483647
minvalue 1  cache 1 ;

--
-- TOC Entry ID 4 (OID 20370)
--
-- Name: a Type: TABLE Owner: gaf
--

CREATE TABLE "a" (
 "new_aa" integer DEFAULT nextval('"a_aa_seq"'::text) NOT NULL,
 PRIMARY KEY ("aa")
);

--
-- Data for TOC Entry ID 5 (OID 20370) TABLE DATA a
--

-- Disable triggers
UPDATE "pg_class" SET "reltriggers" = 0 WHERE "relname" ~* 'a';
COPY "a"  FROM stdin;
\.
-- Enable triggers
BEGIN TRANSACTION;
CREATE TEMP TABLE "tr" ("tmp_relname" name, "tmp_reltriggers" smallint);

INSERT INTO "tr" SELECT C."relname", count(T."oid") FROM "pg_class" C,
"pg_trigger" T WHERE C."oid" = T."tgrelid" AND C."relname" ~* 'a'  GROUP
BY 1;
UPDATE "pg_class" SET "reltriggers" = TMP."tmp_reltriggers" FROM "tr"
TMP WHERE "pg_class"."relname" = TMP."tmp_relname";
DROP TABLE "tr";
COMMIT TRANSACTION;

--
-- TOC Entry ID 3 (OID 20352)
--
-- Name: "a_aa_seq" Type: SEQUENCE SET Owner:
--

SELECT setval ('"a_aa_seq"', 1, 'f');



Michael Teter wrote:

> hi.
>
> I just discovered that doing an alter table ... alter
> column (to rename a column) does not do a complete
> rename throughout the database.
>
> for example, say you have table a, with columns b and
> c.  b is your primary key.
>
> now rename b to new_b.  if you do a dump of the schema
> after you rename, you'll find that you can't reload
> that schema because at the bottom of the definition of
> table a you have PRIMARY KEY ("b").
>
> shouldn't rename update any index and key definitions?
>
> also, and this may actually the source of the problem,
> while scanning my full (schema and data) dump, I
> noticed that the contents of table pga_layout also had
> the old values of columns that I have renamed.
>
> I'm very frightened right now, because I'm rather
> dependent upon my database right now.  I don't like
> the thought that my database is corrupt at the schema
> level.
>
> michael
>
> __________________________________________________
> Do You Yahoo!?
> Yahoo! Messenger - Talk while you surf!  It's FREE.
> http://im.yahoo.com/

--
> Poorly planned software requires a genius to write it
> and a hero to use it.

Grant Finnemore BSc(Eng)  (mailto:gaf@ucs.co.za)
Software Engineer         Universal Computer Services
Tel  (+27)(11)712-1366    PO Box 31266 Braamfontein 2017, South Africa
Cell (+27)(82)604-5536    20th Floor, 209 Smit St., Braamfontein
Fax  (+27)(11)339-3421    Johannesburg, South Africa



Fri, 27 Oct 2000 10:30:19 +0200
Grant Finnemore <gaf@ucs.co.za>
Re: [SQL] renaming columns... danger?
Subject:        	[SQL] renaming columns... danger?

> I just discovered that doing an alter table ... alter
> column (to rename a column) does not do a complete
> rename throughout the database.

> shouldn't rename update any index and key definitions?

> I'm very frightened right now, because I'm rather
> dependent upon my database right now.  I don't like
> the thought that my database is corrupt at the schema
> level.
> 
Yes, I believe the same is true about trigger definitions and 
suchlike. 
In short - to do a rename on column I do a pg_dumpall and change 
all references of the name by hand :*(((

Btw, is there a way to see what triggers are defined for particular 
field? Or how to drop triggers, which (by default) are unnamed?



Fri, 27 Oct 2000 11:00:27 +0200
"Emils Klotins" <emils@grafton.lv>
Re: renaming columns... danger?
Subject:        	[SQL] renaming columns... danger?

> I just discovered that doing an alter table ... alter
> column (to rename a column) does not do a complete
> rename throughout the database.

> shouldn't rename update any index and key definitions?

> I'm very frightened right now, because I'm rather
> dependent upon my database right now.  I don't like
> the thought that my database is corrupt at the schema
> level.
> 
Yes, I believe the same is true about trigger definitions and 
suchlike. 
In short - to do a rename on column I do a pg_dumpall and change 
all references of the name by hand :*(((

Btw, is there a way to see what triggers are defined for particular 
field? Or how to drop triggers, which (by default) are unnamed?



Fri, 27 Oct 2000 11:00:27 +0200
"Emils Klotins" <emils@grafton.lv>
Re: renaming columns... danger?
As for the latest CVS source, it looks still we have problems
regarding alter table rename column and pg_dump as Grant has
mentioned.  Results of pg_dump is attached.

test=# create table a ( aa serial primary key );
NOTICE:  CREATE TABLE will create implicit sequence 'a_aa_seq' for SERIAL column 'a.aa'
NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'a_pkey' for table 'a'
CREATE
test=# alter TABLE a RENAME aa to new_aa;
ALTER
test=# \q
[t-ishii@srapc1474 current]$ pg_dump test > /tmp/aaa
[t-ishii@srapc1474 current]$ dropdb test
DROP DATABASE
[t-ishii@srapc1474 current]$ createdb test
CREATE DATABASE
[t-ishii@srapc1474 current]$ psql test < /tmp/aaa
Using pager is off.
You are now connected as new user t-ishii.
CREATE
ERROR:  CREATE TABLE: column "aa" named in key does not exist
UPDATE 53
ERROR:  Relation 'a' does not exist
invalid command \.
BEGIN
CREATE
INSERT 18819 1
UPDATE 1
DROP
COMMIT
 setval 
--------
      1
(1 row)

[t-ishii@srapc1474 current]$ psql test
Welcome to psql, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

Using pager is off.
test=# \dt
No relations found.
test=# 

------------------------------ attachments ------------------------------
      Multipart/Mixed                                           2/
     1  Text/Plain(guess)                                         CoverPage*
B    2  Application/Octet-Stream                                  aaa.gz
     3                                                            .
--------0-1-2-3-4-5-6-7-8-9----------------------------------------------

Sun, 07 Jan 2001 15:12:15 +0900
Tatsuo Ishii <t-ishii@sra.co.jp>
Re: Re: [SQL] renaming columns... danger?
> As for the latest CVS source, it looks still we have problems
> regarding alter table rename column and pg_dump as Grant has
> mentioned.  Results of pg_dump is attached.

Sorry, an attachmet was missing.
Sun, 07 Jan 2001 15:15:38 +0900
Tatsuo Ishii <t-ishii@sra.co.jp>
Re: Re: [SQL] renaming columns... danger?
At 15:15 7/01/01 +0900, Tatsuo Ishii wrote:
>> As for the latest CVS source, it looks still we have problems
>> regarding alter table rename column and pg_dump as Grant has
>> mentioned.  Results of pg_dump is attached.
>
>Sorry, an attachmet was missing.
>

I can reproduce this in 7.0.2 as well; it's because the PK attr name comes
from the index relation attr names, not the original relation. I'll look
into alternative queries but would welcome suggestions.





----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

Sun, 07 Jan 2001 18:55:44 +1100
Philip Warner <pjw@rhyme.com.au>
Re: Re: [SQL] renaming columns... danger?