On 23/01/14 14:46, Bill Moran wrote: > > Some quickie background: I'm on a project to migrate a fairly large > database from MySQL to PostgreSQL (~2T). As a result of a number of > factors, I have to do it in one shot and I have a limited time window > in which things can be down while I switch it over. > > As one of many, many things I'm considering to make this work, I'm > looking at adding constraints after the data move using NOT VALID to > allow them to be applied quickly. This seems pretty straight forward, > but I'm trying to understand if there are any troublesome side-effects > to leaving the constraints unvalidated. > > Because of the uptime requirements, there are some very large tables > with may foreign keys that I will never be allowed to take a lock on > long enough to validate all the constraints. It was suggested that > leaving the constraints as NOT VALID might affect the planner, causing > it to use less optimal plans because it doesn't think it can trust > the constraint. Is this true? > > It has also been suggested that manually changing the status to valid > in the catalog without going through the validation process could cause > problems, although I haven't found an explanation of what those > problems might be. > > I understand that the best way is to go through and do all the steps, > but that may simply be impossible for me because of the lock it > requires and the time involved. Is there any negative effect to > leaving the constraint unvalidated? Is there any actual danger in > manually flipping the value in the catalog (The constraint can be > consider safe because it was previously enforced on the source > database system) I had a similar problem some time ago. The way I solved it is as follows. First, add the constraint as NOT VALID. That prevents further changes to violate it. Then make sure the constraint is met. Then update pg_constraint. UPDATE pg_constraint SET convalidated = true WHERE conrelid='schema.table'::regclass::oid AND conname='constraintname' Not sure if that way can be recommended but it worked for me. In my case it was a check constraint ensuring an interdependence between the columns in a row. Torsten -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
