Обсуждение: [HACKERS] how to correctly invalidate a constraint?
Hi
I would to do import without RI check - so I disable RI triggers.
I would to invalidate constraint on Foreign Keys and then I would to use ALTER TABLE VALIDATE CONSTRAINT ...
I didn't find how to invalidate constraint without direct update pg_constraint.
Is there some clean way?
Regards
Pavel
Pavel Stehule wrote: > Hi > > I would to do import without RI check - so I disable RI triggers. > > I would to invalidate constraint on Foreign Keys and then I would to use > ALTER TABLE VALIDATE CONSTRAINT ... > > I didn't find how to invalidate constraint without direct update > pg_constraint. > > Is there some clean way? I think what you want is: - set the constraint as "not valid", so that the following is a valid operation - set the RI trigger not to fire, to improve performance of bulk loads - do the load - activate the trigger - validate the constraint We have SQL commands for everything except the first step. Now my question would be: do we want to support that operation as a stand-alone thing so that you can construct the above from pieces, or do we want some higher-level command so that the above is less cumbersome? The main issue I see is that a single constraint involves several triggers, and the triggers have internally-derived, very ugly names. So in my mind the right way to provide this functionality is to have a command that operates on the RI constraint and modifies the triggers status. ALTER TABLE .. ALTER CONSTRAINT [name / ALL] DEACTIVATE -- sets constraint as NOT VALID, also sets triggers inactive [user bulkload occurs here] ALTER TABLE .. ALTER CONSTRAINT [name / ALL] ACTIVATE -- activates triggers, validates constraint -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
2017-01-13 22:44 GMT+01:00 Alvaro Herrera <alvherre@2ndquadrant.com>:
Pavel Stehule wrote:
> Hi
>
> I would to do import without RI check - so I disable RI triggers.
>
> I would to invalidate constraint on Foreign Keys and then I would to use
> ALTER TABLE VALIDATE CONSTRAINT ...
>
> I didn't find how to invalidate constraint without direct update
> pg_constraint.
>
> Is there some clean way?
I think what you want is:
- set the constraint as "not valid", so that the following is a valid
operation
- set the RI trigger not to fire, to improve performance of bulk loads
- do the load
- activate the trigger
- validate the constraint
yes
We have SQL commands for everything except the first step. Now my
question would be: do we want to support that operation as a stand-alone
thing so that you can construct the above from pieces, or do we want
some higher-level command so that the above is less cumbersome? The
main issue I see is that a single constraint involves several triggers,
and the triggers have internally-derived, very ugly names. So in my
mind the right way to provide this functionality is to have a command
that operates on the RI constraint and modifies the triggers status.
ALTER TABLE .. ALTER CONSTRAINT [name / ALL] DEACTIVATE
-- sets constraint as NOT VALID, also sets triggers inactive
[user bulkload occurs here]
ALTER TABLE .. ALTER CONSTRAINT [name / ALL] ACTIVATE
-- activates triggers, validates constraint
In this case I prefer simple low level command
ALTER TABLE .. ALTER CONSTRAINT name NOT VALID
It should to set catalog to state after new NOT VALID constraint.
Regards
Pavel
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services