Re: [HACKERS] how to correctly invalidate a constraint?

2017-01-13 Thread Pavel Stehule
2017-01-13 22:44 GMT+01:00 Alvaro Herrera :

> 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 Herrerahttps://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>


Re: [HACKERS] how to correctly invalidate a constraint?

2017-01-13 Thread Alvaro Herrera
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 Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


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


[HACKERS] how to correctly invalidate a constraint?

2017-01-13 Thread Pavel Stehule
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