On 03/19/2017 01:54 AM, Sylvain Marechal wrote:
2017-03-18 20:40 GMT+01:00 Adrian Klaver <adrian.klaver@xxxxxxxxxxx
<mailto:adrian.klaver@xxxxxxxxxxx>>:
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?
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?
<<<
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.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>
Thanks,
Sylvain
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
