Re: renameatt() can rename attribute of index, sequence, ...

Hi,

KaiGai Kohei írta:
> (2010/03/03 14:26), Robert Haas wrote:
>
>> 2010/3/2 KaiGai Kohei<kaigai(at)ak(dot)jp(dot)nec(dot)com>:
>>
>>> Is it an expected behavior?
>>>
>>> postgres=> CREATE SEQUENCE s;
>>> CREATE SEQUENCE
>>> postgres=> ALTER TABLE s RENAME sequence_name TO abcd;
>>> ALTER TABLE
>>>
>>> postgres=> CREATE TABLE t (a int primary key, b text);
>>> NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t_pkey" for table "t"
>>> CREATE TABLE
>>> postgres=> ALTER TABLE t_pkey RENAME a TO xyz;
>>> ALTER TABLE
>>>
>>> The documentation says:
>>> http://developer.postgresql.org/pgdocs/postgres/sql-altertable.html
>>>
>>> :
>>> RENAME
>>> The RENAME forms change the name of a table (or an index, sequence, or view) or
>>> the name of an individual column in a table. There is no effect on the stored data.
>>>
>>> It seems to me the renameatt() should check relkind of the specified relation, and
>>> raise an error if relkind != RELKIND_RELATION.
>>>
>> Are we talking about renameatt() or RenameRelation()? Letting
>> RenameRelation() rename whatever seems fairly harmless; renameatt(),
>> on the other hand, should probably refuse to allow this:
>>
>> CREATE SEQUENCE foo;
>> ALTER TABLE foo RENAME COLUMN is_cycled TO bob;
>>
>> ...because that's just weird. Tables, indexes, and views make sense,
>> but the attributes of a sequence should be nailed down I think;
>> they're basically system properties.
>>
>
> I'm talking about renameatt(), not RenameRelation().
>
> If our perspective is these are a type of system properties, we should
> be able to reference these attributes with same name, so it is not harmless
> to allow renaming these attributes.
>

I just tried it on 8.3.7:

zozo=# create sequence seq2;
CREATE SEQUENCE

"is_called" is modified from false to true on the first call of nextval()
so I renamed it:

zozo=# alter table seq2 rename column is_called to bob;
ALTER TABLE

zozo=# create table seq2_tab (id integer primary key default
nextval('seq2'), t text);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"seq2_tab_pkey" for table "seq2_tab"
CREATE TABLE
zozo=# alter sequence seq2 owned by seq2_tab.id;
ALTER SEQUENCE

No error it seems:

zozo=# insert into seq2_tab (t) values ('a');
INSERT 0 1

zozo=# select * from seq2;
sequence_name | last_value | increment_by | max_value | min_value |
cache_value | log_cnt | is_cycled | bob
---------------+------------+--------------+---------------------+-----------+-------------+---------+-----------+-----
seq2 | 1 | 1 | 9223372036854775807 | 1 | 1 | 0 | f | t
(1 sor)

Let's try other fields:

zozo=# alter table seq2 rename column min_value to first;
ALTER TABLE
zozo=# alter table seq2 rename column max_value to last;
ALTER TABLE
zozo=# alter table seq2 rename column last_value to always;
ALTER TABLE

Still no error:

zozo=# insert into seq2_tab (t) values ('b');
INSERT 0 1

Let's try more fields:

zozo=# alter table seq2 rename column cache_value to keep;
ALTER TABLE
zozo=# alter table seq2 rename column increment_by to advance;
ALTER TABLE
zozo=# alter table seq2 rename column is_cycled to bobek;
ALTER TABLE

Still no error:

zozo=# insert into seq2_tab (t) values ('c');
INSERT 0 1
zozo=# select * from seq2;
sequence_name | always | advance | last | first | keep | log_cnt | bobek
| bob
---------------+--------+---------+---------------------+-------+------+---------+-------+-----
seq2 | 3 | 1 | 9223372036854775807 | 1 | 1 | 31 | f | t
(1 sor)

Still no error:

zozo=# alter table seq2 rename column log_cnt to pampalini;
ALTER TABLE
zozo=# insert into seq2_tab (t) values ('d');
INSERT 0 1
zozo=# select * from seq2;
sequence_name | always | advance | last | first | keep | pampalini |
bobek | bob
---------------+--------+---------+---------------------+-------+------+-----------+-------+-----
seq2 | 4 | 1 | 9223372036854775807 | 1 | 1 | 32 | f | t
(1 sor)

Change the last remaining field and still no error:

zozo=# alter table seq2 rename column sequence_name to pimpa;
ALTER TABLE
zozo=# insert into seq2_tab (t) values ('d');
INSERT 0 1
zozo=# select * from seq2;
pimpa | always | advance | last | first | keep | pampalini | bobek | bob
-------+--------+---------+---------------------+-------+------+-----------+-------+-----
seq2 | 5 | 1 | 9223372036854775807 | 1 | 1 | 31 | f | t
(1 sor)

zozo=# select * from seq2_tab;
id | t
----+---
1 | a
2 | b
3 | c
4 | d
5 | d
(5 rows)

Internally, the system refers these column by position instead of names.
But from the user perspective, the sequence fields are more
like system columns, renaming them leads to confusion.

> I also agree that it makes sense to allow renaming attributes of tables
> and views. But I don't know whether it makes sense to allow it on indexs,
> like sequence and toast relations.
>

Best regards,
Zoltán Böszörményi

--
Bible has answers for everything. Proof:
"But let your communication be, Yea, yea; Nay, nay: for whatsoever is more
than these cometh of evil." (Matthew 5:37) - basics of digital technology.
"May your kingdom come" - superficial description of plate tectonics

----------------------------------
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
http://www.postgresql.at/