On Thu, Oct 10, 2013 at 3:44 AM, Torsten Förtsch <torsten.foertsch@xxxxxxx> 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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
