2017-03-19 17:55 GMT+01:00 Adrian Klaver <adrian.kla...@aklaver.com>:
> On 03/19/2017 01:54 AM, Sylvain Marechal wrote: > >> >> >> 2017-03-18 20:40 GMT+01:00 Adrian Klaver <adrian.kla...@aklaver.com >> <mailto:adrian.kla...@aklaver.com>>: >> >> On 03/18/2017 12:05 PM, Sylvain Marechal wrote: >> > > >> >> Why not CASCADE?: >> >> test=# ALTER TABLE test1 DROP CONSTRAINT test1_t1_key CASCADE; >> NOTICE: drop cascades to constraint test2_t1_fkey on table test2 >> ALTER TABLE >> >> >> >> >> It is the same end result as the first two steps of what you are >> doing below, just a different direction. >> >> >> No special reason at all: I began with CASCADE, and as things went >> wrong, I tried to split the process to better figure out the problem >> >> >> >> Is there a solution to" alter" the "test2_t1_fkey" constraint so >> that it >> uses the "primary key constraint", then to remove the >> unnecessary unique >> constraint on table test1 >> >> The following solution works but causes me deadlocks problems >> with BDR: >> >> >> Is the below wrapped in a transaction? >> >> >> Yes. >> The goal is to wrap this upgrade process inside a transaction to be able >> to abort it in case something was wrong. >> >> Problem is that some tables may be accessed during the upgrade process. >> May be a solution is to avoid it by only allowing the upgrade backend >> and bdr to access the tables, but I do not like the idea to make the >> database readonly (UPDATE pg_database SET datallowconn = false WHERE pid >> != upgrade_and_bdr ... ): >> > > So is the above a BDR specific enhancement to pg_database or is pid != > upgrade_and_bdr just a placeholder for something else? > Sorry, forget all about BDR. In fact, I need to arrange the tables not to be accessed during the upgrade phase, else this leads to deadlocks, and there is no possible magic to avoid it as I was initially dreaming. In other words, to solve my problem, I think I have 2 solutions : 1) do the necessary job so that only the upgrade process access the tables during constraints changes; other processes will be stopped during the upgrade 2) or in the upgrade process, terminate all processes except the one that does the upgrade, and the bdr workers. (the "upgrade_and_bdr" pseudo code was not clear, sorry for this) > in case the upgrade process fails, this would >> requiere require a manual intervention to solve it (upgrade is called if >> needed by the application). >> > > If I am following correctly then the changes to the tables are being done > on a as needed basis based on some external criteria. > > In any case for each table it should be a one time operation, right? > Also from a practical stand point the FK between test2 and test1 is not > actually changing. So why not just change them ahead of time in a process > you can monitor directly? > Yes, this is what I should do. Thank you, Sylvain
