ivan-slony1 at 420.am writes:
> I'm a new Slony user - great to see a clear, active direction for Pg
> replication, incidentally - and I have some questions about the IDs 
> referenced in "SET ADD TABLE", "LOCK SET" and deadlocks.
>
> I'm working on deploying my application in a "warm standby" replicated
> environment using Slony.  I'm aware of the limitations referred to in
> the documentation and am prepared to script the hearbeat and failover
> triggering myself.
>
> For background, my application uses transactions, "SELECT FOR UPDATE"
> and, in one place, "LOCK TABLE" on a single table in "SHARE ROW
> EXCLUSIVE MODE".
>
> On to the part I could use some guidance/advice on:
>
> "SET ADD TABLE" says about its ID value: "The numeric value of this ID
> also determines the order in which the tables are locked in a LOCK SET
> command for example.  So these numbers should represent any applicable
> table hierarchy to make sure the slonik command scripts do not deadlock
> at any critical moment."
>
> Can you tell me more about what events may trigger a "LOCK SET" command? 
> Is this something that happens during the operation of normal
> replication, or only when I trigger particular events such as adding a
> node, moving a set between nodes, etc.?

Two main events will lock a set:

 1.  You need to request LOCK SET before moving the origin to another
     node.

 2.  When you invoke the DDL_SCRIPT event

> What sort of table lock does "LOCK SET" acquire?

It needs an exclusive lock on each table.

> Am I correct in understanding "represent any applicable table
> hierarchy" to mean the order in which I update (and read???) tables
> during transactions?  Are read operations relevant?

It's presumably possible for some sequencing of read locks to
adversely affect things...

> Looking at my code I don't always touch tables in the same order,
> which I know should be corrected to avoid deadlocks, irregardless of
> Slony. Will replication exacerbate the deadlock problem here -
> should fix this before using Slony?  Or if my application is running
> basically okay despite my sloppy ordering of operations within
> tranactions, I shouldn't worry about adding Slony to the mix?

There are two times when you'll have "issues":

 1.  At the time that you want to move a set origin from one node to
     another;

 2.  At the time when you want to use EXECUTE SCRIPT to do maintenance
     on tables.

At those times, you probably want to try to get the application
near-quiescent; take an outage, if possible.  If you can disconnect
the application for even just a few seconds, that ought to be enough
to eliminate the risk of locking problems.

As for times of "normal operations," those are times when LOCK SET
isn't being invoked and so is not an issue...
-- 
"cbbrowne","@","ca.afilias.info"
<http://dev6.int.libertyrms.com/>
Christopher Browne
(416) 673-4124 (land)