Обсуждение: [GENERAL] Best way to alter a foreign constraint

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.

>
> 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?

> <<<
> 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@aklaver.com

On 03/19/2017 01:54 AM, Sylvain Marechal wrote:
>
>
> 2017-03-18 20:40 GMT+01:00 Adrian Klaver <adrian.klaver@aklaver.com
> <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?

>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@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
>
>
> Thanks,
> Sylvain


--
Adrian Klaver
adrian.klaver@aklaver.com

2017-03-18 20:40 GMT+01:00 Adrian Klaver <adrian.klaver@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?: