Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id DAFF79FB1F2 for ; Mon, 13 Nov 2006 12:53:28 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 62231-06 for ; Mon, 13 Nov 2006 12:53:22 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from noel.decibel.org (noel.decibel.org [67.100.216.10]) by postgresql.org (Postfix) with ESMTP id 7B8089FB24D for ; Mon, 13 Nov 2006 12:53:22 -0400 (AST) Received: by noel.decibel.org (Postfix, from userid 1001) id 6A73856435; Mon, 13 Nov 2006 10:53:21 -0600 (CST) Received: (hashcash-sendmail, from uid 1001); Mon, 13 Nov 2006 10:53:19 -0600 Date: Mon, 13 Nov 2006 10:53:19 -0600 From: "Jim C. Nasby" To: Jerry Sievers Cc: pgsql-admin@postgresql.org Subject: Re: Manual sys catalog constraint setup to avoid downtime? Message-ID: <20061113165318.GS90133@nasby.net> References: <20061109071118.GF90133@nasby.net> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: X-Operating-System: FreeBSD 6.1-RELEASE-p10 amd64 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.13 (2006-08-11) X-Hashcash: 1:20:061113:jerry@jerrysievers.com::QrGmca7fsnyrQh49:000000000000000 0000000000000000000000005QCy X-Hashcash: 1:20:061113:pgsql-admin@postgresql.org::0E90q0ec5hDnlbCk:00000000000 0000000000000000000000001mG4 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200611/124 X-Sequence-Number: 23651 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@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)