From: | Thom Brown <thom(at)linux(dot)com> |
---|---|
To: | pgadmin-hackers <pgadmin-hackers(at)postgresql(dot)org> |
Subject: | Re: Overhaul of type attributes modification |
Date: | 2011-07-08 15:01:02 |
Message-ID: | CAA-aLv4kEBDKtuX4HjX5U0Eucu0i3yTthsq6Uug_xqUhuNnudQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgadmin-hackers |
On 8 July 2011 15:38, Thom Brown <thom(at)linux(dot)com> wrote:
> On 8 July 2011 15:19, Thom Brown <thom(at)linux(dot)com> wrote:
>> Hi all,
>>
>> I noticed that if you add, delete, rename or change the type,
>> collation or precision of a composite type attribute, it deletes all
>> of them then adds them all back in. Obviously attribute additions,
>> deletions and modifications may only occur for types under PostgreSQL
>> 9.1, but I thought it a bit extreme to actually drop all types if
>> you're adding a new one in, or just removing one. Also, if you rename
>> a type, it drops all attributes again then re-adds them, just to have
>> an attribute with a different name.
>>
>> So I've attached a patch which "does the right thing". Take the
>> example of the following type:
>>
>> CREATE TYPE bark AS
>> (one text,
>> two text,
>> three text,
>> four text,
>> five text);
>>
>> Say we wanted to remove "two", change the type of three to uuid,
>> rename four to forty and add an extra text type of six.
>>
>> Normally we'd just get:
>>
>> ALTER TYPE bark DROP ATTRIBUTE one;
>> ALTER TYPE bark DROP ATTRIBUTE two;
>> ALTER TYPE bark DROP ATTRIBUTE three;
>> ALTER TYPE bark DROP ATTRIBUTE four;
>> ALTER TYPE bark DROP ATTRIBUTE five;
>> ALTER TYPE bark ADD ATTRIBUTE one text;
>> ALTER TYPE bark ADD ATTRIBUTE three text;
>> ALTER TYPE bark ADD ATTRIBUTE forty uuid;
>> ALTER TYPE bark ADD ATTRIBUTE five text;
>> ALTER TYPE bark ADD ATTRIBUTE six uuid;
>>
>> With these changes we'd now get:
>>
>> ALTER TYPE bark DROP ATTRIBUTE two;
>> ALTER TYPE bark RENAME ATTRIBUTE four TO forty;
>> ALTER TYPE bark ADD ATTRIBUTE six text;
>>
>> .. except now those are also nicely indented to be more readable for
>> types with long schemas/type names.
>>
>> e.g.
>> ALTER TYPE long_schema_name.quite_a_long_table_name
>> ADD ATTRIBUTE "suspiciously long attribute name"?
>>
>> It also fixes a bug whereby if you have a precision specified, the
>> word COLLATE mysteriously appears after the type whether or not you
>> have a collation assigned because the collation condition was based on
>> the precision being present for some reason. And if you actually
>> assigned a collation for a valid data type, it wouldn't appear at all,
>> so that's fixed too.
>
> Actually I noticed that the example I gave of what would occur before
> the patch highlights another bug. Notice that forty and six have the
> type of uuid, and three doesn't? Three should have been the one with
> uuid, and forty and sixty should have been text. That's not my typo
> as that's come from PgAdmin 1.14 beta 2. So it was actually assigning
> the wrong data types in those case too. So the patch fixes those
> problems too.
Also I noticed that my example of the new output doesn't show the
three datatype being changed to uuid. This was just me forgetting to
click the Change button when I changed the type, so it would actually
come out as:
ALTER TYPE bark DROP ATTRIBUTE two;
ALTER TYPE bark DROP ATTRIBUTE three;
ALTER TYPE bark ADD ATTRIBUTE three uuid;
ALTER TYPE bark RENAME ATTRIBUTE four TO forty;
ALTER TYPE bark ADD ATTRIBUTE six text;
--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
From | Date | Subject | |
---|---|---|---|
Next Message | Guillaume Lelarge | 2011-07-08 18:46:52 | Re: [FEATURE] Add schema option to all relevant objects |
Previous Message | Thom Brown | 2011-07-08 14:38:27 | Re: Overhaul of type attributes modification |