Re: DDL Damage Assessment

On 10/02/2014 06:30 PM, Dimitri Fontaine wrote:
> Hi fellow hackers,
> [snip]
> 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?

Yes, please

> 2. What do you think such a feature should look like?
EXPLAIN [(verbose, format)] [DDL_COMMAND]

as in:
EXPLAIN (verbose on, format text, impact on)
ALTER TABLE emp
ADD COLUMN foo2 jsonb NOT NULL DEFAULT '{}';

where the output would include something like:

...
EXCLUSIVE LOCK ON TABLE emp; // due to "IMPACT ON"
REWRITE TABLE emp due to adding column foo2 (default='{}'::jsonb)
// due to "VERBOSE on"
...

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

For completeness sake, yes.
But, unless the "impact" and "verbose" modifiers are specified, most
would be quite self-explanatory:

EXPLAIN (verbose on, impact on) TRUNCATE TABLE emp;
Execution plan:
-> EXCLUSIVE LOCK ON TABLE emp;
....
-> truncate index: IIIIII (file=NNNNN) // NNNN
= relfilenode
-> truncate main fork: NNNNN (tablespace: TTTTT) // NNNN
= relfilenode
-> truncate visibility map
....
-> RELEASE LOCK ON TABLE emp;
....
Summary: ZZZZZ pages ( MMM MB ) would be freed

versus a simple:
EXPLAIN TRUNCATE TABLE emp;
Execution plan:
-> truncate index: emp_pkey
-> truncate index: emp_foo2_idx
-> truncate relation emp

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

Sounds very good, indeed.
Count on me as tester :)

--
José Luis Tallón