Re: DDL Damage Assessment

I think the main issue is when a table rewrite is triggered on a DDL
command on a large table, as this is what frequently leads to
unavailability. The idea of introducing a NOREWRITE keyword to DDL
commands then came up (credit: Peter Geoghegan). When the NOREWRITE
keyword is used and the DDL statement would rewrite the table, the
command errors and exits.

This would allow ORM and framework authors to include the NOREWRITE
option by default, only to be disabled on a per-statement basis by the
developer, once they have assessed that it may be safe or otherwise
they still want to proceed with this. The workflow for an app
developer then becomes:

* Write offending data migration (eg: add a column with a NOT NULL
constraint and default value)
* Test it locally, either by running automated test suite or running on staging
* See that it fails because of NOREWRITE option
* Assess situation. If it's a small table, or I still want to ignore,
override the option. Or rewrite migration to avoid rewrite.
* Repeat

I like this a lot just because it's simple, limited in scope, and can
be easily integrated into ORMs saving users hours of downtime and
frustration.

Thoughts?

On Thu, Oct 2, 2014 at 9:46 AM, Fabrízio de Royes Mello
<fabriziomello@gmail.com> wrote:
>
>
> On Thu, Oct 2, 2014 at 1:30 PM, Dimitri Fontaine <dimitri@2ndquadrant.fr>
> wrote:
>>
>> Hi fellow hackers,
>>
>> I would like to work on a new feature allowing our users to assess the
>> amount of trouble they will run into when running a DDL script on their
>> production setups, *before* actually getting their services down.
>>
>> The main practical example I can offer here is the ALTER TABLE command.
>> Recent releases are including very nice optimisations to it, so much so
>> that it's becoming increasingly hard to answer some very basic
>> questions:
>>
>>   - what kind of locks will be taken? (exclusive, shared)
>>   - on what objects? (foreign keys, indexes, sequences, etc)
>>   - will the table have to be rewritten? the indexes?
>>
>> Of course the docs are answering parts of those, but in particular the
>> table rewriting rules are complex enough that “accidental DBAs” will
>> fail to predict if the target data type is binary coercible to the
>> current one.
>>
>> Questions:
>>
>>  1. Do you agree that a systematic way to report what a DDL command (or
>>     script, or transaction) is going to do on your production database
>>     is a feature we should provide to our growing user base?
>>
>>  2. What do you think such a feature should look like?
>>
>>  3. Does it make sense to support the whole set of DDL commands from the
>>     get go (or ever) when most of them are only taking locks in their
>>     own pg_catalog entry anyway?
>>
>> Provided that we are able to converge towards a common enough answer to
>> those questions, I propose to hack my way around and send patches to
>> have it (the common answer) available in the next PostgreSQL release.
>>
>
> What you are proposing is some kind of "dry-run" with verbose output?
>
> Regards,
>
> --
> Fabrízio de Royes Mello
> Consultoria/Coaching PostgreSQL
>>> Timbira: http://www.timbira.com.br
>>> Blog: http://fabriziomello.github.io
>>> Linkedin: http://br.linkedin.com/in/fabriziomello
>>> Twitter: http://twitter.com/fabriziomello
>>> Github: http://github.com/fabriziomello