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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

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

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:

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

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.