Re: Reconsidering the behavior of ALTER COLUMN TYPE

On Thu, Jun 11, 2015 at 3:12 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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(at)mail(dot)gmail(dot)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