Re: Best way to alter a foreign constraint

2017-03-18 20:40 GMT+01:00 Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>:

> On 03/18/2017 12:05 PM, Sylvain Marechal wrote:
>
>> Hello all,
>>
>> Some of my tables were badly designed and have 2 indexes, like the
>> following example (lots of tables have same problem):
>>
>> <<<
>> postgres=# \d test1
>> Table "public.test1"
>> Column | Type | Modifiers
>> --------+---------+-----------
>> t1 | integer | not null
>> Indexes:
>> "test1_pkey" PRIMARY KEY, btree (t1)
>> "test1_t1_key" UNIQUE CONSTRAINT, btree (t1)
>> Referenced by:
>> TABLE "test2" CONSTRAINT "test2_t1_fkey" FOREIGN KEY (t1) REFERENCES
>> test1(t1)
>>
>> postgres=# \d test2
>> Table "public.test2"
>> Column | Type | Modifiers
>> --------+---------+-----------
>> t2 | integer | not null
>> t1 | integer |
>> Indexes:
>> "test2_pkey" PRIMARY KEY, btree (t2)
>> Foreign-key constraints:
>> "test2_t1_fkey" FOREIGN KEY (t1) REFERENCES test1(t1)
>>
>>>
>>>>>
>> It is not possible to remove the "test1_t1_key" constraint because the
>> "test2_t1_fkey" internally references it:
>> <<<
>> postgres=# ALTER TABLE test1 DROP CONSTRAINT test1_t1_key;
>> ERROR: cannot drop constraint test1_t1_key on table test1 because other
>> objects depend on it
>> DETAIL: constraint test2_t1_fkey on table test2 depends on index
>> test1_t1_key
>> HINT: Use DROP ... CASCADE to drop the dependent objects too.
>>
>>>
>>>>>
> 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 ... ): in case the upgrade process fails, this would
requiere require a manual intervention to solve it (upgrade is called if
needed by the application).

>
>
> <<<
>> ALTER TABLE test2 DROP CONSTRAINT test2_t1_fkey;
>> ALTER TABLE test1 DROP CONSTRAINT test1_t1_key;
>> ALTER TABLE test2 ADD CONSTRAINT test2_t1_fkey FOREIGN KEY (t1)
>> REFERENCES test1(t1);
>>
>>>
>>>>>
>> Thanks and regards,
>> Sylvain
>>
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>

Thanks,
Sylvain