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