Skip site navigation (1) Skip section navigation (2)

Re: ON DELETE CASCADE with multiple paths

From: Max Khon <mkhon(at)swsoft(dot)com>
To: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-bugs(at)postgresql(dot)org
Subject: Re: ON DELETE CASCADE with multiple paths
Date: 2007-05-21 08:55:04
Message-ID: 46515E68.9040806@swsoft.com (view raw or flat)
Thread:
Lists: pgsql-bugs
Stephan Szabo wrote:
> On Thu, 17 May 2007, Tom Lane wrote:
> 
>> Max Khon <mkhon(at)swsoft(dot)com> writes:
>>> "delete from foo" fails:
>>> ERROR: update or delete on table "bar" violates foreign key constraint
>>> "foobar_fk0" on table "foobar"
>>> SQL state: 23503
>>> Detail: Key (bar_id)=(1) is still referenced from table "foobar".
>>> Context: SQL statement "DELETE FROM ONLY "public"."bar" WHERE "foo_id" = $1"
>> I see no bug here.  There is no guarantee about the order in which
>> constraints are applied.
> 
> Except that SQL92 at least does seem to say in 11.8 that "All rows that
> are marked for deletion are effectively deleted at the end of the
> SQL-statement, prior to the checking of any integrity constraints." I
> think that likely makes our behavior wrong, but I'm not really sure how to
> get there from what we have now.

Is it sufficient to execute ON DELETE CASCADE and ON DELETE SET
NULL/DEFAULT triggers before other triggers?

Attached patch solves the problem for me.

-- 
Max Khon
PEM Platform Team Leader
SWsoft, Inc.
E-mail: mkhon(at)swsoft(dot)com
Web Site: http://swsoft.com/

Attachment: patch-src-backend-commands-trigger.c
Description: text/plain (1.1 KB)

In response to

Responses

pgsql-bugs by date

Next:From: Zdenek KotalaDate: 2007-05-21 11:46:41
Subject: Re: BUG #3291: Query tool not returning all results
Previous:From: Tom LaneDate: 2007-05-20 17:45:55
Subject: Re: Fwd: Bug#425125: postgresql-8.1: localhost does not work for unix sockets in ~/.pgpass

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group