Brian Hirt wrote:

>
> On Jan 27, 2005, at 10:11 AM, Steve Simms wrote:
>
>> Creating/dropping indexes, provided they're not primary keys or 
>> otherwise being used for replication, can be done on each server 
>> without going through the EXECUTE SCRIPT command.
>
>
> Thanks, I'll give this a try.   I was under the impression all DDL 
> changes should go through execute script.   Is there some sort of list 
> of safe commands that can be done outside of slony?   How about adding 
> foreign keys?
>
> http://cbbrowne.com/info/ddlchanges.html and 
> http://cbbrowne.com/info/stmtddlscript.html don't seem to get into this.
>
>> I can't help you with the overall problem, though -- I look forward 
>> to seeing the responses from more knowledgeable people, as this would 
>> have a definite impact in my environment as well.
>>
>
> Me too. Your advice will help with some of the easier tasks we have, 
> but i'll have to wait to hear what to do about the more complex DDL 
> changes.


It may be simpler to delineate what is Definitely Unsafe to do any way 
other than with EXECUTE SCRIPT.

--> If you need to change the table schema of a replicated table, any 
way other than EXECUTE SCRIPT is Definitely Unsafe.

--> A data conversion that follows schema change?  Probably Unsafe.

--> If you needed that conversion to set values on a soon-to-become NOT 
NULL new column, upgrade to Definitely Unsafe.

-> You add a REFERENCES constraint that leads to a referential integrity 
trigger?  Definitely Unsafe.

The notion of "Definitely Unsafe" may be read as "if you do that which 
is Definitely Unsafe, you run the risk of breaking replication and even 
downright corrupting data on subscriber nodes.

Indexes that aren't part of replication wouldn't forcibly need to get 
EXECUTE SCRIPT run on them.  And it may well be a good idea to keep this 
independent as a new index that only needs to be on one table will 
pretty happily lock whatever it needs to, eliminating issues of needing 
to lock anything on the origin node.

As for Brian's deadlocking scenario, in effect, the problem is that 
you're never getting a point where Slony-I can get in "edgewise" to get 
at all the tables.

I think you could improve things if you built a new set, subscribed it 
everywhere, used SET MOVE TABLE to put into that table just the few that 
are being affected by your SQL script.  You'd associate the EXECUTE 
SCRIPT with that set, which would just look for locks on a couple tables 
instaed of all of them.  Whether or not that's totally safe as far as 
your application is concerned is something I'd have to think more about.

I'd feel way more comfortable suggesting a brief, non-zero downtime to 
update the database schema..