DDL Damage Assessment

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.

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support