Re: Manual sys catalog constraint setup to avoid downtime?

On Thu, Nov 09, 2006 at 08:46:42AM -0500, Jerry Sievers wrote:
> 2. Create new NOT NULL, CHECK and/or FK constraints by inserting them
> into the catalogs directly. NOT NULL is the simplest and requires
> only an update to pg_attribute table. CHECK constraints a bit more
> tricky and requires figuring out what goes in the conbin field of
> pg_constraint table. FK the most difficult as it requires adding
> recs to all of pg_constraint, pg_trigger and pg_depend. The
> trigger I guess can be created using standard CREATE TRIGGER
> operation.
>
> Having done this, we are NOT absolutely guaranteed that the new
> constraints are expressed in the DB due to possible client activity
> that ran during or after our bulk delete or updates in step #1. We
> are however insured that new activity as of the constraint additions
> is conforming.
>
> 3. Let the DB age beyond any transactions that may have been open
> during our initial cleanup pass and repeat cleanup steps again.
> Now, we should be up to snuff and fine going forward.

You should check into whether other backends will pick those catalog
changes up automagically or not... you may have to restart all
connections for those changes to take effect.

BTW, it'd probably be worthwhile looking at the code that creates the
different constraints to see exactly what it's doing.

And you're right... this is not something the community supports. :)
--
Jim Nasby jim(at)nasby(dot)net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)