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) -- Bill Moran <wmoran@xxxxxxxxxxxxxxxxx> -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
