Search Postgresql Archives

Re: Best way to alter a foreign constraint

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





2017-03-19 17:55 GMT+01:00 Adrian Klaver <adrian.klaver@xxxxxxxxxxx>:
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@aklaver.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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux