Re: DDL Damage Assessment

On 10/03/2014 11:02 AM, Dimitri Fontaine wrote:
> Jim Nasby <Jim(dot)Nasby(at)BlueTreble(dot)com> writes:
>>> EXPLAIN
>>> ALTER TABLE ....
>> I'm thinking it would be better to have something you could set at a session
>> level, so you don't have to stick EXPLAIN in front of all your DDL.

We were considering the potential needs of "accidental DBAs" here at
first, if memory serves me well.
I definitively see the value of EXPLAIN [DDL STATEMENT]... even if
implemented as a regular "this is what should happen", without even
attempting to run a thing (not even "dry run transactions"), but
including the full catalog search / attribute resolution etc.
Providing insight on the locking that would happen sounds like a real
life-saver for many real life situations (i.e. would this ALTER
completely lock my application due to the constant logging-to-table?)
This is, obviously IMHO.
> Yeah I'm coming into that camp too, and I think the Event Trigger idea
> gets us halfway there. Here's a detailed sketched of how it would work:
>
> 1. preparatory steps: install the Event Trigger
>
> create extension norewrite;
>
> 2. test run:
>
> psql -1 -f ddl.sql
> ERROR: Table Rewrite has been cancelled.
>
> 3. Well actually we need to run that thing in production
>
> BEGIN;
> ALTER EVENT TRIGGER norewrite DISABLE;
> \i ddl.sql
> ALTER EVENT TRIGGER norewrite ENABLE;
> COMMIT;
>
> Then it's also possible to have another Event Trigger that would
> automatically issue a LOCK <table> NOWAIT; command before any DDL
> against a table is run, in another extension:
>
> create extension ddl_lock_nowait;
>
> The same applies, if your production rollout is blocked repeatedly and
> you want to force it through at some point, it's possible to disable the
> event trigger within the DDL script/transaction.

This serves a different purpose which is, at least, as worthwhile as the
former: provide a real "dry run" mechanism for advanced users.
Stephen's "delta" fork sounds like a promising approach ... even if a
bit too "Oracle-ish" (sounds an awful lot like UNDO logs!) for my liking.

>> As for the dry-run idea, I don't think that's really necessary. I've never
>> seen anyone serious that doesn't have a development environment, which is
>> where you would simply deploy the real DDL using "verbose" mode and see what
>> the underlying commands actually do.
> The major drawback of the Event Trigger idea is that the transaction is
> cancelled as soon as a Rewrite Event is fired when you have installed
> the protective trigger. It means that you won't see the next problem
> after the first one, so it's not a dry-run.
>
> But considering what you're saying here, it might well be enough.

It is a very convenient first step (minimally invasive, and good use of
existing infrastructure)... since it allows an easy testing phase in
order to iron out potential shortcomings and gather input on some other
applications.

Thanks,

/ Jose