Re: ALTER TABLE ... NOREWRITE option

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.

> 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.

Thanks,
nm