declare constraint as valid

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

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