On 10/03/2014 11:02 AM, Dimitri Fontaine wrote:
Jim Nasby <jim.na...@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.
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
