Received: from orbits.com (mail@sdsl-64-7-11-116.dsl.iad.megapath.net [64.7.11.116]) by hub.org (8.10.1/8.11.0) with ESMTP id e9S6ncZ14897 for ; Sat, 28 Oct 2000 02:49:38 -0400 (EDT) (envelope-from root@orbits.com) Received: from root by orbits.com with local (Exim 3.12 #1 (Debian)) id 13pPtX-00075O-00; Sat, 28 Oct 2000 02:55:11 -0400 To: pgsql@orbits.com Received: from hub.org.org (localhost [127.0.0.1]) by hub.org (8.10.1/8.11.0) with SMTP id e9R9pTq58048; Fri, 27 Oct 2000 05:51:29 -0400 (EDT) (envelope-from pgsql-sql-owner+M1924@hub.org) Received: from ucs.co.za (ucs.co.za [196.23.43.2]) by hub.org (8.10.1/8.11.0) with ESMTP id e9R8Seq14635; Fri, 27 Oct 2000 04:28:41 -0400 (EDT) (envelope-from gaf@ucs.co.za) Received: from ucspost.ucs.co.za (IDENT:root@mailgw1.ucs.co.za [196.23.43.254]) by ucs.co.za (8.9.3/8.9.3) with ESMTP id KAA28561; Fri, 27 Oct 2000 10:39:16 +0200 Received: from jhb.ucs.co.za (jhb.ucs.co.za [172.31.1.73]) by ucspost.ucs.co.za (8.9.3/8.7.3) with ESMTP id KAA28138; Fri, 27 Oct 2000 10:28:17 +0200 Received: from ucs.co.za (IDENT:gaf@pc141.ucs.co.za [172.31.1.141]) by jhb.ucs.co.za (8.9.3/8.8.7) with ESMTP id KAA06840; Fri, 27 Oct 2000 10:33:35 +0200 Date: Fri, 27 Oct 2000 10:30:19 +0200 From: Grant Finnemore X-Mailer: Mozilla 4.61 [en] (X11; I; Linux 2.2.12-20 i686) X-Accept-Language: en MIME-Version: 1.0 CC: PostgreSQL Development Subject: Re: [SQL] renaming columns... danger? References: <20001027050141.76502.qmail@web10004.mail.yahoo.com> Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from base64 to 8bit by hub.org id e9R8TS414871 X-Mailing-List: pgsql-sql@postgresql.org Precedence: bulk Message-Id: X-Archive-Number: 200010/1123 X-Sequence-Number: 8196 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