Thread: Reconsidering the behavior of ALTER COLUMN TYPE
A recent thread in pgsql-general shows yet another user who's befuddled by the need to add a USING clause to an ALTER TABLE ... ALTER COLUMN TYPE command: http://www.postgresql.org/message-id/flat/CAD25zGDiaqCG=eqXp=byvZcdgvcquBh7KBpJpJQseSPOwfvhiw@mail.gmail.com Specifically, it's not clear why you can change the type of a uuid[] column with alter table t alter u type text[]; but then you can't change it back with alter table t alter u type uuid[]; The reason of course is that uuid-to-text is considered an assignment-grade coercion while text-to-uuid is not. I've lost count of the number of times we've had to tell someone to use a USING clause for this. Maybe it's time to be a little bit less rigid about this situation, and do what the user obviously wants rather than make him spell out a rather pointless USING. Specifically, after a bit of thought, I suggest that (1) If there's no USING, attempt to coerce the column value as though an *explicit* coercion were used. (2) If there is a USING, maintain the current behavior that the result has to be assignment-coercible to the new column type. We could use explicit-coercion semantics here too, but I think that might be throwing away a bit too much error checking, in a case where the odds of a typo are measurably higher than for the default situation. This could be documented as "if there is no USING, the default behavior is as if you'd written USING column::newtype". Thoughts? In any case, we oughta use two different error messages for the two cases, as per my comment in the above thread. That seems like a back-patchable bug fix, though of course any semantics change should only be in HEAD. regards, tom lane
On Thu, Jun 11, 2015 at 3:12 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > A recent thread in pgsql-general shows yet another user who's befuddled by > the need to add a USING clause to an ALTER TABLE ... ALTER COLUMN TYPE > command: > > http://www.postgresql.org/message-id/flat/CAD25zGDiaqCG=eqXp=byvZcdgvcquBh7KBpJpJQseSPOwfvhiw@mail.gmail.com > > Specifically, it's not clear why you can change the type of a uuid[] > column with > > alter table t alter u type text[]; > > but then you can't change it back with > > alter table t alter u type uuid[]; > > The reason of course is that uuid-to-text is considered an > assignment-grade coercion while text-to-uuid is not. > > I've lost count of the number of times we've had to tell someone to > use a USING clause for this. Maybe it's time to be a little bit less > rigid about this situation, and do what the user obviously wants rather > than make him spell out a rather pointless USING. > > Specifically, after a bit of thought, I suggest that > > (1) If there's no USING, attempt to coerce the column value as though > an *explicit* coercion were used. > > (2) If there is a USING, maintain the current behavior that the result > has to be assignment-coercible to the new column type. We could use > explicit-coercion semantics here too, but I think that might be throwing > away a bit too much error checking, in a case where the odds of a typo > are measurably higher than for the default situation. > > This could be documented as "if there is no USING, the default behavior > is as if you'd written USING column::newtype". > > Thoughts? > > In any case, we oughta use two different error messages for the two cases, > as per my comment in the above thread. That seems like a back-patchable > bug fix, though of course any semantics change should only be in HEAD. I have a slight preference to keep it to tightening up the wording on both the hint and the error (for example, "Perhaps you meant USING foo::type?") but leaving the behavior alone. In other components of the system, forcing explicit casts has added safety and there is no more dangerous footgun than 'ALTER TABLE'. IMSNHO, the issue is the somewhat arcane syntax, not the casting rules. merlin
On Thu, Jun 11, 2015 at 03:41:49PM -0500, Merlin Moncure wrote: > On Thu, Jun 11, 2015 at 3:12 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > Specifically, it's not clear why you can change the type of a uuid[] > > column with > > > > alter table t alter u type text[]; > > > > but then you can't change it back with > > > > alter table t alter u type uuid[]; > > > > The reason of course is that uuid-to-text is considered an > > assignment-grade coercion while text-to-uuid is not. > > Specifically, after a bit of thought, I suggest that > > > > (1) If there's no USING, attempt to coerce the column value as though > > an *explicit* coercion were used. > > This could be documented as "if there is no USING, the default behavior > > is as if you'd written USING column::newtype". This removes the training wheels from varchar(N) in particular, and potentially from other three-argument cast functions: [local] test=# create table t (c) as select 1234; SELECT 1 [local] test=# alter table t alter c type varchar(2); ERROR: value too long for type character varying(2) [local] test=# alter table t alter c type varchar(2) using c::varchar(2); ALTER TABLE [local] test=# table t;c ----12 (1 row) I suppose you could accept explicit-only casts yet pass "false" for the isExplicit argument, but that is a wart. > > In any case, we oughta use two different error messages for the two cases, > > as per my comment in the above thread. That seems like a back-patchable > > bug fix, though of course any semantics change should only be in HEAD. > > I have a slight preference to keep it to tightening up the wording on > both the hint and the error (for example, "Perhaps you meant USING > foo::type?") but leaving the behavior alone. +1. The HINT could certainly provide situation-specific help.
Noah Misch <noah@leadboat.com> writes:
> On Thu, Jun 11, 2015 at 03:41:49PM -0500, Merlin Moncure wrote:
>> On Thu, Jun 11, 2015 at 3:12 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> In any case, we oughta use two different error messages for the two cases,
>>> as per my comment in the above thread. That seems like a back-patchable
>>> bug fix, though of course any semantics change should only be in HEAD.
>> I have a slight preference to keep it to tightening up the wording on
>> both the hint and the error (for example, "Perhaps you meant USING
>> foo::type?") but leaving the behavior alone.
> +1. The HINT could certainly provide situation-specific help.
Fair enough, I'll go fix that but leave the semantics alone.
regards, tom lane