Re: DDL Damage Assessment

On 10/02/2014 01:15 PM, Joe Conway wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> On 10/02/2014 11:30 AM, Dimitri Fontaine wrote:
>> 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?
>
> +1
>
> I really like the idea and would find it useful/time-saving
>
>> 2. What do you think such a feature should look like?
>
> Elsewhere on this thread EXPLAIN was suggested. That makes a certain
> amount of sense.
>
> Maybe something like EXPLAIN IMPACT [...]
>
>> 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?
>
> Yes, I think it should cover all commands that can have an
> availability impact.

In principle I agree with the sentiment. However, that full coverage is
a nice goal, seldom achieved.

The real question is at what level of information, returned to the user,
does this feature become user friendly?

It is one thing to provide information of the kind of

TAKE ACCECSS EXCLUSIVE LOCK ON TABLE foo
TEST EVERY ROW IN TABLE foo FOR FK (a, b) IN bar (id1, id2)

That information is useful, but only to an experienced DBA who knows
their schema and data to a certain degree. The majority of users, I
fear, will not be able to even remotely guesstimate if that will need
seconds or hours.

There needs to be more detail information for those cases and I believe
that tackling them one at a time in depth will lead to more useful
results than trying to cover a lot but shallow.

My $.02
Jan

--
Jan Wieck
Senior Software Engineer
http://slony.info