Skip site navigation (1) Skip section navigation (2)

Re: [SQL] renaming columns... danger?

From: Grant Finnemore <gaf(at)ucs(dot)co(dot)za>
To: Michael Teter <michael_teter(at)yahoo(dot)com>, pgsql-sql(at)postgresql(dot)org
Cc: PostgreSQL Development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: [SQL] renaming columns... danger?
Date: 2000-10-27 08:30:19
Message-ID: 39F93D1B.9EFE7B02@ucs.co.za (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-sql
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(at)ucs(dot)co(dot)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



In response to

pgsql-hackers by date

Next:From: Emils KlotinsDate: 2000-10-27 09:00:27
Subject: Re: renaming columns... danger?
Previous:From: Ian Lance TaylorDate: 2000-10-27 07:50:28
Subject: Re: Re: [GENERAL] A rare error

pgsql-sql by date

Next:From: Emils KlotinsDate: 2000-10-27 09:00:27
Subject: Re: renaming columns... danger?
Previous:From: Daniel KalchevDate: 2000-10-27 06:02:43
Subject: except on nulls?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group