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);
From:
Lucas Possamai Date: Subject:
Re: [GENERAL] the master has two sync slave, which slave can promoteto master and the other slave as the new master's slave?