X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 26B739DCA60 for ; Wed, 15 Feb 2006 17:20:52 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 22395-06 for ; Wed, 15 Feb 2006 17:20:53 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from smtp.nildram.co.uk (smtp.nildram.co.uk [195.112.4.54]) by postgresql.org (Postfix) with ESMTP id B19FA9DCA57 for ; Wed, 15 Feb 2006 17:20:49 -0400 (AST) Received: from [192.168.0.5] (unknown [84.12.26.47]) by smtp.nildram.co.uk (Postfix) with ESMTP id 9BAF4254CE9; Wed, 15 Feb 2006 21:20:20 +0000 (GMT) Subject: Re: constraint_exclusion on OLTP tables From: Simon Riggs To: Chris Hoover Cc: "pgsql-admin@postgresql.org" In-Reply-To: <1d219a6f0602130650l690a255amf4bb68ceab914e3b@mail.gmail.com> References: <1d219a6f0602130650l690a255amf4bb68ceab914e3b@mail.gmail.com> Content-Type: text/plain Date: Wed, 15 Feb 2006 21:20:15 +0000 Message-Id: <1140038415.12131.147.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Evolution 2.2.3 (2.2.3-2.fc4) Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.085 required=5 tests=[AWL=0.085] X-Spam-Score: 0.085 X-Spam-Level: X-Archive-Number: 200602/191 X-Sequence-Number: 20612 On Mon, 2006-02-13 at 09:50 -0500, Chris Hoover wrote: > The problem I am having is how can you safely move records between > main_table_live and one of the other partition tables? Obviously, > this will have to happen as our users work the data and the records > enter into on of the 2 closed statuses ('C' or 'D'). When the status > is changed to C or D, I need to first move the record and run an > update against the moved record to make sure all fields are updated > and our auditing triggers are fired. My not just delete from the live table and insert into the partitioned history table? That can be done atomically and with triggers. Alternatively, keep all versions of the record as it develops over time so the key becomes PK+timestamp and all writes are inserts. That way you don't need an audit table, since you never change data, so probably overall a smaller system and one less prone to bugs in the data update processes. Best Regards, Simon Riggs