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;       ....   ->
truncateindex: 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   ->
truncaterelation 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