Jim Nasby <Jim.Nasby@BlueTreble.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.
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.
> 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.
Regards,
--
Dimitri Fontaine 06 63 07 10 78
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support