Thread: [GENERAL] Best way to alter a foreign constraint

[GENERAL] Best way to alter a foreign constraint

From

Sylvain Marechal

Date:

18 March 2017, 22:05:05

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

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

Re: [GENERAL] Best way to alter a foreign constraint

From

Adrian Klaver

Date:

18 March 2017, 22:40:35

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

Re: [GENERAL] Best way to alter a foreign constraint

From

Sylvain Marechal

Date:

19 March 2017, 11:54:50

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

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

Thanks,

Sylvain

Re: [GENERAL] Best way to alter a foreign constraint

From

Adrian Klaver

Date:

19 March 2017, 19:55:42

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

Re: [GENERAL] Best way to alter a foreign constraint

From

Sylvain Marechal

Date:

19 March 2017, 22:04:07

2017-03-19 17:55 GMT+01:00 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?

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

Re: [GENERAL] Best way to alter a foreign constraint

From

Sylvain MARECHAL

Date:

20 March 2017, 18:07:33

Le 19/03/2017 à 09:54, Sylvain Marechal a écrit :

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

In fact, CASCADE is not enough, because I don't know how the test2_t1_fkey is built : does it use the test1_pkey primary key or the test1_t1_key unique key?
I am sure this information can be found in system catalogs, but I find it safer to explicitely delete then recreate the foreign constraint.

Sylvain

Re: [GENERAL] Best way to alter a foreign constraint

From

Melvin Davidson

Date:

20 March 2017, 18:26:21

On Mon, Mar 20, 2017 at 11:07 AM, Sylvain MARECHAL <marechal.sylvain2@gmail.com> wrote:

Le 19/03/2017 à 09:54, Sylvain Marechal a écrit :
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?:
In fact, CASCADE is not enough, because I don't know how the test2_t1_fkey is built : does it use the test1_pkey primary key or the test1_t1_key unique key?
I am sure this information can be found in system catalogs, but I find it safer to explicitely delete then recreate the foreign constraint.

Sylvain

This query might help you determine which key is referenced.

The key information is confkey, which is reported as key position in the referenced table.

Refer to system catalogs description in documentaion for more info.

SELECT cn.conname,
       CASE WHEN cn.contype = 'c' THEN 'check'
            WHEN cn.contype = 'f' THEN 'foreign key'
            WHEN cn.contype = 'p' THEN 'primary key'
            WHEN cn.contype = 'u' THEN 'unique'
            WHEN cn.contype = 't' THEN 'trigger'
            WHEN cn.contype = 'x' THEN 'exclusion'
       END as type,
       cn.condeferrable,
       CASE WHEN cn.conrelid > 0
            THEN (SELECT nspname || '.' || relname
                   FROM pg_class c
                   JOIN pg_namespace n ON n.oid = c.relnamespace
                  WHERE c.oid = cn.conrelid)
            ELSE ''
       END as table,
       confkey,
       consrc
   FROM pg_constraint cn
ORDER BY 1;

--

Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.