Re: Which SET TYPE don't actually require a rewrite

On Fri, Jul 17, 2020 at 5:40 AM Noah Misch <noah(at)leadboat(dot)com> wrote:

> On Wed, Jul 15, 2020 at 02:54:37PM +0200, Magnus Hagander wrote:
> > Our Fine Manual (TM) specifies:
> > "As an exception, when changing the type of an existing column, if the
> > USING clause does not change the column contents and the old type is
> either
> > binary coercible to the new type or an unconstrained domain over the new
> > type, a table rewrite is not needed; but any indexes on the affected
> > columns must still be rebuilt."
> >
> > First of all, how is a non-internals-expert even supposed to know what a
> > binary coercible type is?
>
> The manual defines it at <firstterm>binary coercible</firstterm>.
>

The only way to actually realize that this is a <firstterm> is to look at
the source code though, right? It's definitely not clear that one should go
look at the CREATE CAST documentation to find the definition -- certainly
not from the ALTER TABLE documentation, which I would argue is the place
where most people would go.

And while having the definition there is nice, it doesn't help an end user
in any way at all to determine if their ALTER TABLE statement is going to
be "safe from rewrites" or not. It (hopefully) helps someone who knows some
things about the database internals, which is of course a valuable thing as
well, but not the end user.

> We can also for example increase the precision of numeric without a
> rewrite
> > (but not scale). Or we can change between text and varchar. And we can
> > increase the length of a varchar but not decrease it.
> >
> > Surely we can do better than this when it comes to documenting it? Even
> if
> > it's a pluggable thing so it may or may not be true of external
> > datatypes installed later, we should be able to at least be more clear
> > about the builtin types, I think?
>
> I recall reasoning that ATColumnChangeRequiresRewrite() is a DDL analog of
> query optimizer logic. The manual brings up only a minority of planner
> optimizations, and comprehensive lists of optimization preconditions are
> even
> rarer. But I don't mind if $SUBJECT documentation departs from that norm.
>

I can see the argument being made for that, and certainly having been made
for it in the future. But I'd say given the very bad consequences of
getting it wrong, it's far from minor. And given the number of times I've
had to answer the question "can I make this change safely" (which usually
amounts to me trying it out to see what happens, if I hadn't done that
exact one many times before) indicates the need for a more detailed
documentation on it.

As Amit mentions it is also triggered by some store parameter changes. But
not all. So looking at it the other way, the part that the end user really
cares about it "which ALTER TABLE operations will rewrite the table and
which will not". Maybe what we need is a section specifically on this that
summarizes all the different ways that it can happen.

--
Magnus Hagander
Me: https://www.hagander.net/ <http://www.hagander.net/>
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>