Re: [SQL] renaming columns... danger? - Mailing list pgsql-hackers

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