Re: ALTER TABLE ... NOREWRITE option

On 12/02/2012 03:07 AM, Noah Misch wrote:
> On Sat, Dec 01, 2012 at 07:34:51PM +0100, Andres Freund wrote:
>> On 2012-12-01 18:27:08 +0000, Simon Riggs wrote:
>>> 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.
> Acquiring the lock could still take an unpredictable amount of time.
>
>>> Greg Sabine Mullane wrote an interesting blog about a way of solving
>>> the problem in userspace.
> I currently recommend using the DEBUG1 messages for this purpose:
>
> [local] test=# set client_min_messages = debug1;
> SET
> [local] test=# create table t (c int8 primary key, c1 text);
> DEBUG: building index "pg_toast_109381_index" on table "pg_toast_109381"
> DEBUG: CREATE TABLE / PRIMARY KEY will create implicit index "t_pkey" for table "t"
> DEBUG: building index "t_pkey" on table "t"
> CREATE TABLE
> [local] test=# alter table t alter c type int4;
> DEBUG: building index "pg_toast_109391_index" on table "pg_toast_109391"
> DEBUG: rewriting table "t"
> DEBUG: building index "t_pkey" on table "t"
> ALTER TABLE
> [local] test=# alter table t alter c type oid;
> DEBUG: building index "t_pkey" on table "t"
> ALTER TABLE
>
> Observe that some changes rewrite the table and all indexes, while others skip
> rewriting the table but rebuild one or more indexes. I've threatened to
> optimize type changes like (base type) -> (domain with CHECK constraint) by
> merely scanning the table for violations. If we do add syntax such as you
> have proposed, I recommend using a different name and defining it to reject
> any operation with complexity O(n) or worse relative to table size. That
> being said, I share Tom's doubts. The DEBUG1 messages are a sorry excuse for
> a UI, but I'm not seeing a clear improvement in NOREWRITE.
>
>>>> 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.
> There's statement_timeout.
I think the point was in catching the rewrite behaviour in testing.

for statement_timeout to kick in you may need to have both
production size and production load which is not always easy
to achieve in testing.

>> My first thought is to add more detailed EXPLAIN support for
>> DDL... Although that unfortunately broadens the scope of this a tiny
>> bit.
> That would be ideal.
>
It would also be nice to add a "dry run" support, which switches to EXPLAIN
for all SQL instead of executing.

SET explain_only TO TRUE;

----------------
Hannu