Re: Best way to alter a foreign constraint

2017-03-19 17:55 GMT+01:00 Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>:

> On 03/19/2017 01:54 AM, Sylvain Marechal wrote:
>
>>
>>
>> 2017-03-18 20:40 GMT+01:00 Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com
>> <mailto:adrian(dot)klaver(at)aklaver(dot)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