Jacques Caron jc at oxado.com
Fri Oct 26 08:15:58 PDT 2007
At 15:54 26/10/2007, Andrew Sullivan wrote:
>On Fri, Oct 26, 2007 at 02:46:47AM +0200, hubert depesz lubaczewski wrote:
> >
> > i run it wehn the load on machine is minimal (it's next to impossible to
> > get lower load, as i'm doing it at 3 a.m.!), but all i get is:
> >
> > #v+
> > <stdin>:6: PGRES_FATAL_ERROR select "_QQQ".ddlScript_prepare(1, 
> -1);  - ERROR:  deadlock detected
> > DETAIL:  Process 7628 waits for AccessExclusiveLock on relation 
> 56719 of database 55909; blocked by process 6705.
> > Process 6705 waits for AccessShareLock on relation 56624 of 
> database 55909; blocked by process 7628.
>
>Without knowing what those other transactions are trying to do, it's
>sort of impossible to tell you.  But it sounds like something else
>has a lock on that table.

Not quite... It means something else has locks (even a very light 
read lock) on two random tables in the set, and got the locks in a 
different order than Slony tries to grab the locks before dropping 
the triggers, applying the script and restoring them. And since that 
order is currently random, it's nearly impossible to avoid :-(

>If you have a "better way of handling schema modifications" that will
>actually work, please propose it.

For the "add a column" case, *without* a default value, constraint or 
anything else like that:

1. add the column on all destination databases first (Slony won't 
mind and just ignore them)

2. change the trigger on the table on the origin database to add a 
"v" to the last argument:
UPDATE pg_trigger SET tgargs=substring(tgargs for 
octet_length(tgargs)-1)||E'v\\000' where tgname = '<name of trigger here>';

This additional "v" is ignored by Slony until the column is added

3. add the column on the origin database

Et voilà :-)

Notes:
- if you have any additional qualifiers on the column (a default, a 
contraint...), *really* think about it a lot before you try to use 
this. It really has a very good chance of breaking the replication or 
producing inconsistent results between origin and destination databases.

- a quick patch of the logtrigger C code (to ignore any columns after 
the end of the "kkvv" string) would avoid the need for the second step above

- the same thing would mean we could remove all trailing "v"s and 
save a few cycles on each update (the code only cares about the "k"s 
and any "v"s that are before or between "k"s)

- obviously a slonik EXECUTE ADD COLUMN instruction doing the ALTER 
TABLE ADD COLUMNs all over the place in the right order would be great

- alternatively, some way to tell EXECUTE SCRIPT to only lock (and 
"un-trigger" and "re-trigger") a defined set of tables (those 
actually affected by the DDL script) and/or to lock them in a 
particular order could be quite useful

- in the worst case, having EXECUTE SCRIPT lock tables in the order 
of their tab_id (ditto sequences) would be useful. Better: adding a 
"tab_lock_order" and sort on that one (as changing tab_ids after the 
fact seems a bit dangerous, eh).

Hope that helps, comments welcome.

Jacques.



More information about the Slony1-general mailing list