Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1akncG-0002A1-8u for pgsql-hackers@arkaria.postgresql.org; Tue, 29 Mar 2016 06:59:04 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1akncF-0003gK-9e for pgsql-hackers@arkaria.postgresql.org; Tue, 29 Mar 2016 06:59:03 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1aknan-00026l-Mq for pgsql-hackers@postgresql.org; Tue, 29 Mar 2016 06:57:33 +0000 Received: from mail-eopbgr650074.outbound.protection.outlook.com ([40.107.65.74] helo=na01-by2-obe.outbound.protection.outlook.com) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1aknaj-0002sp-Je for pgsql-hackers@postgresql.org; Tue, 29 Mar 2016 06:57:31 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=BlueTreble.onmicrosoft.com; s=selector1-bluetreble-com; h=From:To:Date:Subject:Message-ID:Content-Type:MIME-Version; bh=HlaD22/bjYC5LV4+D+kAlZ1utNQDNNJh7Vl9fHv0Yq0=; b=ZXSV+otmlq3HCK3jDHgfs1EYxMTnZEFT1qj/2DLVm9yd2d1zOerj3ScyuVOiAdeVsd70aOdvED0shVgsFh3IgqCNwyANg96agqENSGoHzJPbG4RTIDE2bfVYBGp8O1rqnozm3GaVfBVu0O//6WTRvGsFQhX2vlV/zICYqKiM/wU= Authentication-Results: postgresql.org; dkim=none (message not signed) header.d=none;postgresql.org; dmarc=none action=none header.from=BlueTreble.com; Received: from decina.attlocal.net (2602:306:ce8b:3460:39aa:fcd3:e7be:2893) by SN1PR11MB0688.namprd11.prod.outlook.com (10.163.207.146) with Microsoft SMTP Server (TLS) id 15.1.447.15; Tue, 29 Mar 2016 06:57:25 +0000 Subject: Re: Alter or rename enum value To: , Tom Lane References: <25605.1457539654@sss.pgh.pa.us> <56E05635.70908@dunslane.net> <28167.1457543612@sss.pgh.pa.us> <56F49CF8.8050007@BlueTreble.com> <27476.1458876456@sss.pgh.pa.us> <56F58876.6020508@dunslane.net> <56F6827F.9040104@dunslane.net> <3202.1459002341@sss.pgh.pa.us> <56F69F5C.1060408@dunslane.net> <56F7D8BF.5000000@dunslane.net> <4075.1459088427@sss.pgh.pa.us> CC: Andrew Dunstan , Christophe Pettus , "David G. Johnston" , Matthias Kurz , "pgsql-hackers@postgresql.org" From: Jim Nasby Message-ID: <56FA274D.4020108@BlueTreble.com> Date: Tue, 29 Mar 2016 01:57:17 -0500 User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.11; rv:38.0) Gecko/20100101 Thunderbird/38.7.1 MIME-Version: 1.0 In-Reply-To: Content-Type: text/plain; charset="utf-8"; format=flowed Content-Transfer-Encoding: 7bit X-Originating-IP: [2602:306:ce8b:3460:39aa:fcd3:e7be:2893] X-ClientProxiedBy: CY1PR18CA0042.namprd18.prod.outlook.com (10.163.31.52) To SN1PR11MB0688.namprd11.prod.outlook.com (10.163.207.146) X-MS-Office365-Filtering-Correlation-Id: 37a9c4c3-558f-43b8-2c9d-08d3579f6368 X-Microsoft-Exchange-Diagnostics: 1;SN1PR11MB0688;2:FJt3WKESuTZVh+RykycQvZr2lDq8hENnWmoEz4m2VZNP/814J/Fp+7YZgB9he1LqENkQJT/3gDNFe1oeupjfq2E7FX2raNP/Qng9WPP26HmrDSSBPMwGSlp2t3I3pChwk03a56B74k1oSRDAT7vBb4AC/iD93nXouFOSAB5EsVhlPkavbz7Pp2Pn3erSwGpl;3:IdRJioGMmB/5JjPhXjyABaqtx7Op7mOeLcoBvCkMkykr74+DAxFjckUQaO+bJnpm0LvWhDh2C7M68Dh7b5d/WAcnPbl3Y3VhisJGCyLkSdNfsKJof8i7CYXt71WlmEdG X-Microsoft-Antispam: UriScan:;BCL:0;PCL:0;RULEID:;SRVR:SN1PR11MB0688; X-Microsoft-Exchange-Diagnostics: 1;SN1PR11MB0688;25:jW160jrHzOhbNMra9LMh4Jma8ozTuB4BGqoEnQV/993aG2SCqZK0kCuUa0AQzY/DYlqcFumkTNVBHO+caL/CLBvmizFcSyyHuhp2kerXy0SI8/scRDoh0OarN2HNo/b7Et/LRDX8FFnFOsI6qyilVPGiYgggvy40C8qbliYENRV+pDSDBhwXMsm2R6aqQqJJqvNr9HH5QYZVsmVXtSXN7CEx6cbfwqeJG45UNCYca/RYj3w50PI2x4vE5r+LjKeRKUMzOaJA1ALByhF+L+6X5RZEWcS9ktgmWYs5UOwWJA/EJI05i99pBTYFvCxwq2Pi6VHOUOv3yyhF7Am3uC2/+urVDApqU1u92e9TOzVI3tZDwVGLcOLjzUV+0KpStbkwmg0oGTrN8z4LpxB3ct6bIVo0hhMPasejaE1aZKSRhsSvSdkh0vh0C9uX7qGPXlHOEiIkfgPmaFjUM4bkgRQ8xn6o87d9ww4noY0YRtFQ+rO4AyYDUhbIm0aborPEcFZpOotug8ZvNCS6LlhDjc51UbmugJVBxHbC2/BCzFj0HgBAE51Zv9Kw+JS/p7XxcL+5zZXou65QvA7T/aWRgB2r7ZccLjwhyC5Q6AK3rhMpGN2+UxBbPYeXK0lmMn3skObEwUAK98FVOnRgqM9O7g2Y+RpGNQzrTsUWPRtA25ee1VO3AZk+FfqyHENAhgbNjMCS X-Microsoft-Antispam-PRVS: X-Exchange-Antispam-Report-Test: UriScan:; X-Exchange-Antispam-Report-CFA-Test: BCL:0;PCL:0;RULEID:(6040046)(601004)(2401047)(8121501046)(5005006)(10201501046)(3002001)(6041046)(6043046);SRVR:SN1PR11MB0688;BCL:0;PCL:0;RULEID:;SRVR:SN1PR11MB0688; X-Microsoft-Exchange-Diagnostics: 1;SN1PR11MB0688;4:SqS9wyhQR3Bmrz3IMavVRTC5mQHRDs/sCsyyiX9/dTD7ABVASzDWPimkzwdan4IRG16kualth3nNdwXDft6wiwZ4TOjSoeb6T8WChd9PYkrej28+P4DejDuhea8xKEL5aNyoKbf6sbRYyenbg9YzbmweU9LopyMm/C0FkZ5ncSge7oAQC/GBoatzyLo3gFUHerM8srErZ9xZJzOIB2oq9h8ukkgqn/PabeCPXVCflCtxS0fGSNANAAUp+aQ6Yas0N+IH/er+faYWwh1NBmALiKId491YkdSBmpHbLbKzZ/ID/9HkX3COdXWry4nROjt4Sd0lKp0/QLnxEhOubnIgPbpRMMvlyHapYACcYnw5wdPxmTLEDYdLTYurkf0nAX139UXv+a9nSXwISyQ58arFeTDr0InvEEdEon+T7/FKLQA= X-Forefront-PRVS: 0896BFCE6C X-Forefront-Antispam-Report: SFV:NSPM;SFS:(10009020)(4630300001)(6009001)(24454002)(377454003)(1096002)(4326007)(2906002)(6116002)(230700001)(65816999)(87266999)(50986999)(54356999)(586003)(76176999)(77096005)(15975445007)(47776003)(23676002)(2950100001)(4001350100001)(50466002)(93886004)(5001770100001)(92566002)(81166005)(42186005)(53416004)(189998001)(64126003)(33656002)(86362001)(65806001)(65956001)(36756003)(19580395003)(5008740100001)(59896002)(83506001)(5004730100002)(3826002)(85282002)(18886065003);DIR:OUT;SFP:1101;SCL:1;SRVR:SN1PR11MB0688;H:decina.attlocal.net;FPR:;SPF:None;MLV:sfv;LANG:en; X-Microsoft-Exchange-Diagnostics: =?utf-8?B?MTtTTjFQUjExTUIwNjg4OzIzOktZb3RHS1EyN3BmTUtxdlRuckxhVjQvUEdU?= =?utf-8?B?SHhtcDM0OWk0NHJmV1h4cVg0V0szbUZMZzJPZUpUUldFYUF0TVl0SEpicDRv?= =?utf-8?B?Y1dWY0dhZzhoK0tYcEg1d2VNM2F5ZjFOYSthUndGdWNVRUJIcndUYzlGM2xZ?= =?utf-8?B?cEkyOU54RzR6TFhOeVdRSkJYV1hsMEdXQng1R1lZOGFhQmFlYVJmVjdjejY3?= =?utf-8?B?RjhFR1VoWW5XamhiNWRiSm1uWHhMaTJJQlNyRGRPb2lyd25ETmllcmRMdmdU?= =?utf-8?B?NDN4MDRtYjVJSUxGTzZDUVhseDR0WldleG42enpaclV6UDdmZ0FyeDcydEQ2?= =?utf-8?B?RGZJQUt2SE41WlVBSTR2TCtXWjFNZ2laZUluWU1CemxlZHJndDM2cjI3WHdK?= =?utf-8?B?b3p5elVKQXhiZG1BNjJZZVdhRGhQZy8yQk1qV0l4am0yR3BVZncrMXQvMnc0?= =?utf-8?B?eHBoUk1XdHpMdTRnOURCNGUzZm5NRHFPSEY4UlI0V0p1SFdmOU9TM2FCYjgy?= =?utf-8?B?ejM4QnJUbllyV2hYOGlRaTlqQmFOR2lsNTAvb1N4d1BGbXAxTkxod1orbVJM?= =?utf-8?B?SkRJN21BbE4yS2dQTVlPQlFqUS9HN0lZMi9XZjJwQ2RQQ0wrcXdDanhQa1py?= =?utf-8?B?L3htSCtBUjdKUzM4Q1pQaWN5UWZONnk3SkFJMGdlcm1GQklwNHFEOWhKZGt4?= =?utf-8?B?ditacWZRRGtRRVZsMHBrNEVRYXlhbi9rRG1OS002UTRoeXpuK2c4anZoNzZa?= =?utf-8?B?NlI4L1BpaExRTVlNUmJSY1lHUCswZnNLT3V1Yy9HME1LK1hQYVlFVEhhTmxQ?= =?utf-8?B?VmZSWDJWWDNERjRWMDZhYXV4RzU0NzRXdzhLT09NUlJwbWxERW9yOGRzcEU5?= =?utf-8?B?a3BxRngwb0RjbGYyMTAwSU4wYVJRallkNjIvcTAxcUpDeFZkMnZlSWZGUXJI?= =?utf-8?B?OEhkNFlYVkVqZUhCMWtDYWl4b3llUWU3c3dOSWVKMmV2NStNVGFXYisxZWp4?= =?utf-8?B?Z3N6T3dNTmZlMjZDSjVBai9qc1ZVcTRLSzRuU2dpb1cyeWpUaHhIaUloMS9O?= =?utf-8?B?WmxYcERCMC9OeDFDYytudXdPaEJXNHdDYVlCT1IyY0EzUS9CSnZFSkZrTzhs?= =?utf-8?B?V2w2YWVjSDBTcDFLK0UxTXkzZmRvUjFaMjBFNml6Y3dwd0ZJOVNEMFVaaTFW?= =?utf-8?B?aGRFTVpLeFg2TGI0L2RMVnFrZDkvZURWZjI3bXRPbUVuV0p6VEFjaTFJd1ZW?= =?utf-8?B?T0Ribit0R0QxdVRyMU9JUFF5U0lVL2JlREFZK1U3ZE9vSjl0cStHQVd1S0tq?= =?utf-8?B?dVJCSFltb25GemxKanVob2Z5b2RRM1ZoUTV2eWpHRW1IbVB1dncxYkxsOUJq?= =?utf-8?B?NERBMHp0ekFVYmxJc0FpWjlqTVlXbVR5SlJGMi9PQU1DeTdjODQ3enBOdWxI?= =?utf-8?B?TnZvYm5wZHV1MzREbTF5U2thTjFGM21hTjJ1TzU5Wkc5M3VDbklCNlg5ZzY3?= =?utf-8?Q?sDk98qDrml7+qOdCX01Vs9R+Oc5MlsxT/nT0X6ZnrL+gnf?= X-Microsoft-Exchange-Diagnostics: 1;SN1PR11MB0688;5:sF1y5yA7nL5BG9wmhXd6P9eB1WQQsbwoyNUTR5cWyT0gp93eAL0WAk3bDuV3q1Y3jw1WTyQBP1D22PCRAsuPIFCB89Fvp12l5zaMCeYsMblHBVHKyjYcta4lPD3P0q2dV8M87ylakuBrkKnxpOv2fQ==;24:dc6gYAkLk4imX1dUZZaiZot4RCWAXNEXUSoLK7K1ORV6hNNj52Mg9EnsZm4YYQfQmVryiuHxSJifu4lakdBmCAZOtmrq6hLQIfS3ITNPEYM= SpamDiagnosticOutput: 1:23 SpamDiagnosticMetadata: NSPM X-OriginatorOrg: bluetreble.com X-MS-Exchange-CrossTenant-OriginalArrivalTime: 29 Mar 2016 06:57:25.5902 (UTC) X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted X-MS-Exchange-Transport-CrossTenantHeadersStamped: SN1PR11MB0688 X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-hackers Precedence: bulk Sender: pgsql-hackers-owner@postgresql.org On 3/28/16 4:42 AM, Emre Hasegeli wrote: > Now, we are using a > function to replace an enum type on all tables with another one, but > we are not at all happy with this solution. It requires all objects > which were using the enum to be dropped and recreated, and it rewrites > the tables, so it greatly increases the migration time and effort. FWIW, there are ways to avoid some of that pain by having a trigger maintain the new column on INSERT/UPDATE and then slowly touching all the old rows where the new column is NULL. Obviously would be much better if we could just do this with ENUMs... -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers