2017-03-18 20:40 GMT+01:00 Adrian Klaver <adrian.kla...@aklaver.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.kla...@aklaver.com > Thanks, Sylvain
