Fri Oct 26 08:15:58 PDT 2007
- Previous message: [Slony1-general] deadlock in "execute script"?
- Next message: [Slony1-general] deadlock in "execute script"?
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
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.
- Previous message: [Slony1-general] deadlock in "execute script"?
- Next message: [Slony1-general] deadlock in "execute script"?
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
More information about the Slony1-general mailing list