Re: ALTER TABLE ... NOREWRITE option
От | Hannu Krosing |
---|---|
Тема | Re: ALTER TABLE ... NOREWRITE option |
Дата | |
Msg-id | 50BDA7DB.5080402@krosing.net обсуждение исходный текст |
Ответ на | Re: ALTER TABLE ... NOREWRITE option (Noah Misch <noah@leadboat.com>) |
Список | pgsql-hackers |
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@sss.pgh.pa.us> wrote: >>>> Simon Riggs <simon@2ndQuadrant.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
В списке pgsql-hackers по дате отправления: