Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 948909FB71B for ; Thu, 31 May 2007 19:42:30 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 86384-02 for ; Thu, 31 May 2007 19:42:23 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id 2D4529FB864 for ; Thu, 31 May 2007 19:42:27 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.3 Received: from rrmailscan3.safenetbox.biz (unknown [203.25.40.75]) by svr4.postgresql.org (Postfix) with ESMTP id 4A9FC5AF882 for ; Thu, 31 May 2007 22:42:25 +0000 (GMT) Received: from rrmailscan3.safenetbox.biz (rrmailscan3.safenetbox.biz [127.0.0.1]) by rrmailscan3.safenetbox.biz (Postfix) with ESMTP id AD57D1D48432; Fri, 1 Jun 2007 08:42:18 +1000 (EST) Received: from [127.0.0.1] (online4-new.lnk.telstra.net [139.130.169.227]) (Authenticated sender: remote) by rrmailscan3.safenetbox.biz (Postfix) with ESMTP id 0346B1D484A2; Fri, 1 Jun 2007 08:42:16 +1000 (EST) Message-ID: <465F4F3C.6060709@autoledgers.com.au> Date: Fri, 01 Jun 2007 06:42:04 +0800 From: Paul Lambert User-Agent: Thunderbird 1.5.0.10 (Windows/20070221) MIME-Version: 1.0 To: Chris Hoover Cc: Jim Nasby , "pgsql-admin@postgresql.org Admin" Subject: Re: Deletes hurt References: <734907A8-F30D-4924-8C97-30D2898CA43C@decibel.org> <1d219a6f0705311210r26e696afi24356ee428b73a33@mail.gmail.com> In-Reply-To: <1d219a6f0705311210r26e696afi24356ee428b73a33@mail.gmail.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200705/347 X-Sequence-Number: 25667 Chris Hoover wrote: > Just curious, what sort of field do they use to mark the rows? I'm > assuming a timestamp since it would half to be part of the primary key, > or am I way off? This has really gotten me thinking about how I might > implement this in my database. > > Thanks, > > Chris > I do this on a number of tables in my database, I use a byte to store a status, with each 'bit' meaning something, usually the 128 bit indicates deleted, so any value over 128 means the record is not current and that is tested for in any standard queries. This doesn't get around allowing multiple rows with the same data, with one being current and the rest deleted - but that's not an issue in my system. If it's deleted it's deleted, it doesn't get recreated, but can be restored, thus not using an actual delete fits my scenario quite well. -- Paul Lambert Database Administrator AutoLedgers