On Wed, Jul 02, 2008 at 03:51:30PM +0200, Csaba Nagy wrote:
> 
> If there would be a slony script to add columns to just 1 table
> reliably, and do all the necessary things transparently to the user, I
> would argue that 90% of the use cases of EXECUTE SCRIPT would be
> covered. Is that a scenario which can be covered reliably with a script
> and only minimal locking involved ?

"Trasparently to the user?"  No.  The special case that I had was for doing
a bulk load, but it'd work for adding a column too.  The problem is that you
need to be _absolutely sure_ that nothing is touching that table in the
meantime.  One way to do it is to lock that table on all nodes.  It's a
complicated but not difficult bit of logic.  I think it's possible to write
a bespoke script for each occasion.  It's probably even possible (I haven't
tried, but I think I know how to do it) to write a small reusable bit of
code that would do this for all the nodes without the really heavy-duty lock
that Slony takes.

BUT (and that's in 21-point bold in red with the <blink> tag), it's not
safe as a general-purpose tool, for exactly the reason that the lock Slony
actually takes got escalated to every table.  If you have foreign keys or
something, and this script doesn't lock all of them at the same time, you
can easily deadlock and leave yourself in a serious mess.  

So I recommend against such a script.  Slony does it the way it does because
of safety: it's trying to protect you, and this is the only reliable way to
do it.  If you can be sure you have a case where you don't need that
protection, then by careful use of the bare metal functions (which is how
Slony does its work, after all) you can do this with less protection.

A