Name: Alexis Wilke
email: [EMAIL PROTECTED]
PostgreSQL version: 8.0.1
Operating System: Linux RedHat 9.0
Short Description: problems with renaming a column of type serial
and GRANT/REVOKE instructions in pg_dump
Details:
Hi guys,
I have been working on a database for a while and just today tried to
restore a dump of it. (I have an automatic dump created every night for
safe keeping).
This is when I noticed a problem with several columns of type serial which
I had renamed. The sequence functioning worked fine, but the sequence
references in the GRANT/REVOKE commands weren't renamed.
According to the release notes of version 8.1.0, Tom has fixed it, however
he mentions problems with DEFAULT values and not GRANT/REVOKE. So I'm
still posting this bug in case the fix was not enough for the entire
functionality of a database.
Please, see the attached .sql file which includes instructions on how
to run it
--
Alexis Wilke
President
Made to Order Software, Corporation
Made to Order Software, Limited
e-mail: [EMAIL PROTECTED]
cell-text: [EMAIL PROTECTED] (please, only small messages)
Web Page: http://www.m2osw.com
Company e-mail: [EMAIL PROTECTED]
Phone: +(1) 916 220 6482
Fax: +(1) 916 988 1450
Address 1: 9275 Blue Oak Drive
Orangevale, California 95662
United States of America
Address 2: 2nd Floor, Hammersmith Broadway
Hammersmith
London W6 7BB
United Kingdom
CONFIDENTIAL
This document contains non-public proprietary information
that is subject to restrictions on use and/or disclosure.
If you are not the intended recipient, any dissemination,
distribution or copying is strictly prohibited. If you
think that you have received this e-mail message in error,
please e-mail the sender and delete all copies. Thank you.
--
-- With Postgres version 8.0.1
--
-- Renaming a column which is attached to a sequence is bogus
-- when that sequence had some specific granted rights
--
-- Commands:
--
-- [ONCE:]
-- bash> psql template1
-- psql> CREATE DATABASE rename_bug;
-- psql> \q
--
-- [REPEAT AS REQUIRED:]
-- bash> psql -f rename-bug.sql
-- bash> pg_dump rename_bug
--
-- IMPORTANT NOTE:
-- The psql -f rename-bug.sql command will generate two errors
-- the first time since the my_table table and rename_bug group
-- do not exist yet.
--
-- The problem: the pg_dump will print out the sequence as
-- my_table_unique_id_seq in the list of REVOKE/GRANT, whereas
-- in the SELECT ... setvalue() ...; the renamed column appears
-- properly as my_table_serial_id_seq
-- We would either need both to be renamed or neither.
-- [or, as Tom pointed out in Release Notes 8.1.0 : we should use
-- an oid and not the actual name]
--
-- Note that the renamed column works properly in the original
-- database, only pg_dump gets it wrong... it seems.
--
-- IMPORTANT: it seems this is a problem with the index files too.
-- (i.e. with column setup as 'unique')
--
DROP TABLE my_table;
CREATE TABLE my_table (
unique_id serial,
name text
);
-- INSERT INTO my_table (name) VALUES ('Rename Bug');
DROP GROUP rename_bug;
CREATE GROUP rename_bug;
GRANT SELECT ON TABLE my_table TO GROUP rename_bug;
GRANT SELECT ON TABLE my_table_unique_id_seq TO GROUP rename_bug;
ALTER TABLE my_table RENAME COLUMN unique_id TO serial_id;
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
