Received: from femail1.sdc1.sfba.home.com (femail1.sdc1.sfba.home.com [24.0.95.81]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f0OL7eq72972 for ; Wed, 24 Jan 2001 16:07:40 -0500 (EST) (envelope-from fspam@home.com) Received: from tajar ([24.1.6.224]) by femail1.sdc1.sfba.home.com (InterMail vM.4.01.03.00 201-229-121) with SMTP id <20010124210705.WRXN2567.femail1.sdc1.sfba.home.com@tajar>; Wed, 24 Jan 2001 13:07:05 -0800 From: Forest Wilkinson To: Michael Fork Cc: Jan Wieck , pgsql-sql@postgresql.org Subject: Re: unreferenced primary keys: garbage collection Date: Wed, 24 Jan 2001 13:09:08 -0800 Message-ID: <3rgu6ts8hvj5bonhuvusqdajogv4uc4f17@4ax.com> References: In-Reply-To: X-Mailer: Forte Agent 1.8/32.548 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200101/340 X-Sequence-Number: 1013 On Wed, 24 Jan 2001 00:26:58 -0500 (EST), Michael Fork wrote: >One other method is to setup up the foreign keys as ON DELETE RESTRICT, >then outside of your transaction block issue a DELETE FROM address WHERE >add_id = 1; If there are still records in the other tables referencing >this record, it will error out and nothing will happen, however if no >related records are left, the delete will succeed (you have to do it >outside of transaction, otherwise I belive it will rollback on the >error if other rows are found to be referencing the primary key).... Yes, that's the approach I originally posted. The rollback issue is the thing I'm complaining about. The code in question gets called from within a parent function, which uses a single transaction block for all of its operations. This means that executing a query outside a transaction block (or within a separate one) is not an option. I want to be able to tell postgres not to rollback the whole transaction just because my delete attempt fails. I can think of 3 ways to do this: 1. Allow the delete to fail without throwing a fatal error. (Perhaps a warning would suffice.) 2. Allow the client to tell postgres not to roll back if a specified query produces an error. 3. Implement nested transactions. Forest