Обсуждение: declare constraint as valid

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.

Thanks,
Torsten

On Thu, Oct 10, 2013 at 3:44 AM, Torsten Förtsch
<torsten.foertsch@gmx.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