Re: ALTER TABLE ... NOREWRITE option

On 1 December 2012 16:38, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
>> It's hard to know whether your tables will be locked for long periods
>> when implementing DDL changes.
>
>> The NOREWRITE option would cause an ERROR if the table would be
>> rewritten by the command.
>
>> This would allow testing to highlight long running statements before
>> code hits production.
>
> I'm not thrilled about inventing YA keyword for this. If you have a
> problem with that sort of scenario, why aren't you testing your DDL
> on a test server before you do it on production?

That's the point. You run it on a test server first, and you can
conclusively see that it will/will not run for a long time on
production server.

Greg Sabine Mullane wrote an interesting blog about a way of solving
the problem in userspace.

> Or even more to the point, you can always cancel the statement once
> you realize it's taking too long.

Which means you have to watch it, which is not always possible.

> Also, I don't really like the idea of exposing syntax knobs for
> what ought to be purely an internal optimization. If someday the
> optimization becomes unnecessary or radically different in behavior,
> you're stuck with dead syntax. Sometimes the knob is sufficiently
> important to take that risk, but it doesn't seem to be so here.

I think it was an interesting idea, but I agree with comments about
weird syntax.

We need something better and more general for impact assessment.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services