Csaba Nagy <ncslists at googlemail.com> writes:
> On Thu, 2010-08-19 at 17:51 -0400, Christopher Browne wrote:
>> Selena Deckelmann <selenamarie at gmail.com> writes:
>> > It would be lovely if there was a way to get a list of potential locks
>> > required by a script run, without actually taking the locks.
>> 
> [snip]
>> Fortunately, you may an excellent approximation (which should be good
>> enough for any but rather perverse situations) by doing a QA run (not in
>> production!) as follows:
> [snip]
>> If there are crucial differences between the data QA and production,
>> then the list *could* be wrong, due to the DDL behaving differently.
>> That's an argument for realistic test environments, not for not doing
>> this.
>
> What about running the locking test on an actual replica (and perhaps
> inside a transaction which is rolled back at the end) ? For any sane DDL
> which should replicate correctly that should take the same locks as on
> the master, and if you stop the slon daemon for that replica (or execute
> the test _via_ the slon daemon) it will also be sure not to deadlock.

Sure, that would do the trick, and will be "sufficiently realistic" for
any but the most weirdly pathological sorts of DDL changes.  (Stuff that
would pretty much look like self-modifying code, and if you head down
that road, you can expect big trouble!) 

The other scenario where this is troublesome is the case where the
script includes a massive amount of DML work.  In that case, two
somewhat bad things happen:

  - It will take a long time to apply the DDL change.

  - When the DDL change gets rolled back, it leaves an enormous amount
    of trash to be vacuumed up afterwards.

That's not an argument against either the DDL+DML combination, just that
if you choose to do that sort of thing, you have to accept that it won't
be cheap.
-- 
select 'cbbrowne' || '@' || 'ca.afilias.info';
Christopher Browne
"Bother,"  said Pooh,  "Eeyore, ready  two photon  torpedoes  and lock
phasers on the Heffalump, Piglet, meet me in transporter room three"