Received: from localhost (uranus.hub.org [200.46.204.60]) by postgresql.org (Postfix) with ESMTP id 34E7E9FA342 for ; Tue, 7 Nov 2006 12:05:51 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.60]) (amavisd-new, port 10024) with ESMTP id 67596-10 for ; Tue, 7 Nov 2006 12:05:48 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id 2585A9FA2EC for ; Tue, 7 Nov 2006 12:05:47 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.3 Received: from mama.jerrysievers.com (adsl-64-118-249-70.netrox.net [64.118.249.70]) by svr4.postgresql.org (Postfix) with ESMTP id F16A05AF03F for ; Tue, 7 Nov 2006 16:05:44 +0000 (GMT) Received: from homie.jerrysievers.com (homie.jerrysievers.com [192.168.1.7]) by mama.jerrysievers.com (8.13.7/8.13.7) with ESMTP id kA7G5cFM019354 for ; Tue, 7 Nov 2006 11:05:38 -0500 Received: from homie.jerrysievers.com (localhost.localdomain [127.0.0.1]) by homie.jerrysievers.com (8.13.7/8.13.7) with ESMTP id kA7G5cch028434 for ; Tue, 7 Nov 2006 11:05:38 -0500 Received: (from jerry@localhost) by homie.jerrysievers.com (8.13.7/8.13.7/Submit) id kA7G5cqP028426; Tue, 7 Nov 2006 11:05:38 -0500 To: pgsql-admin@postgresql.org Subject: Manual sys catalog constraint setup to avoid downtime? From: Jerry Sievers Date: 07 Nov 2006 11:05:38 -0500 Message-ID: Lines: 32 User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.4 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200611/49 X-Sequence-Number: 23576 Admins; this is (I assume) a not uncommon scenario). Suppose you are managing a BIG ugly DB that's in 24x7 production and desperately in need of constraints of all types. (Of course, you inherited this piece of crap and the irresponsible designers are long gone :-) You have verified that the application now honors the new constraints because on your R&D DBs, the constraints are in place and no constraint violations are occuring. You can't however get business to sign off on proper constraint addition via ALTER TABLE because the tables are huge and this locks out concurrent activity for several hours. My question; Have any of you setup the constraints by doing manual catalog updates and later (or maybe never), gone back and cleaned up the non-conforming data using small batch updates or deletes? Frobing of pg_constraint and pg_depend (at least) would be required. I'm wondering what non-obvious pitfalls exist here? Comments? -- ------------------------------------------------------------------------------- Jerry Sievers 305 854-3001 (home) Production Database Administrator 305 321-1144 (mobil WWW E-Commerce Consultant