Re: declare constraint as valid

On Thu, Oct 10, 2013 at 3:44 AM, Torsten Förtsch
<torsten(dot)foertsch(at)gmx(dot)net> wrote:
> Hi,
>
> assuming a constraint is added to a table as NOT VALID. Now I know it IS
> valid. Can I simply declare it as valid by
>
> update pg_constraint
> set convalidated='t'
> where conrelid=(select c.oid
> from pg_class c
> join pg_namespace n on (n.oid=c.relnamespace)
> where c.relname='tablename'
> and n.nspname='schemaname')
> and conname='constraintname';
>
> instead of
>
> alter table tablename validate constraint ...
>
> Or does the latter have other side effects?
>
> I am asking because I want to avoid the ACCESS EXCLUSIVE lock required
> by the ALTER TABLE. I am sure there are no rows violating the constraint.
You should avoid updating directly the catalogs except if you
absolutely have to. ALTER TABLE does a lot of internal checks and
manipulations of relations when used, so relying on that would be
better IMO. For example in the case of constraint validation there is
processing for inherited relations as far as I recall. Even if you
*know* that the constraint is valid, it is better to play safe
usually. So use the constraint validation when the server is less
busy.

Regards,
--
Michael