pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Grant Finnemore <gaf@ucs.co.za>
To: pgsql@orbits.com
Cc: PostgreSQL Development <pgsql-hackers@postgresql.org>
Subject: Re: [SQL] renaming columns... danger?
Date: Fri, 27 Oct 2000 10:30:19 +0200
Message-ID: <E13pPtX-00075O-00@orbits.com> (raw)
References: <20001027050141.76502.qmail@web10004.mail.yahoo.com>


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





view thread (6+ messages)  latest in thread

Message-ID: <E13pPtX-00075O-00@orbits.com>
Permalink:  ../E13pPtX-00075O-00@orbits.com/
Also on:    postgresql.org/message-id/E13pPtX-00075O-00@orbits.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: gaf@ucs.co.za, pgsql@orbits.com, pgsql-hackers@postgresql.org
  Subject: Re: [SQL] renaming columns... danger?
  In-Reply-To: <E13pPtX-00075O-00@orbits.com>
  References: <20001027050141.76502.qmail@web10004.mail.yahoo.com> <E13pPtX-00075O-00@orbits.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox