X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id A9AF7D1B49B for ; Mon, 22 Mar 2004 16:34:50 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 83198-02 for ; Mon, 22 Mar 2004 12:34:54 -0400 (AST) Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210]) by svr1.postgresql.org (Postfix) with ESMTP id 355DCD1DB16 for ; Mon, 22 Mar 2004 12:34:44 -0400 (AST) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 238C535892; Mon, 22 Mar 2004 08:34:55 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 21FC33588C; Mon, 22 Mar 2004 08:34:55 -0800 (PST) Date: Mon, 22 Mar 2004 08:34:54 -0800 (PST) From: Stephan Szabo To: Erik Thiele Cc: achill@matrix.gatewaynet.com, pgsql-sql@postgresql.org Subject: Re: special integrity constraints In-Reply-To: <20040322172544.0a9b34d4.erik@thiele-hydraulik.de> Message-ID: <20040322083110.X58785@megazone.bigpanda.com> References: <20040322102031.6a6f0b77.erik@thiele-hydraulik.de> <20040322112659.4553df76.erik@thiele-hydraulik.de> <20040322061854.W56073@megazone.bigpanda.com> <20040322172544.0a9b34d4.erik@thiele-hydraulik.de> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/223 X-Sequence-Number: 17322 On Mon, 22 Mar 2004, Erik Thiele wrote: > On Mon, 22 Mar 2004 06:21:28 -0800 (PST) > Stephan Szabo wrote: > > > > > On Mon, 22 Mar 2004, Erik Thiele wrote: > > > > However, foreign keys are implemented using "constraint triggers". They're > > really not documented much (because they're a not really separately > > supported implementation detail), but you can theoretically make > > triggers that are deferred to immediately before commit time. > > could you paste some code? > > > create table a (x integer); > > create or replace function alwaysfail() returns opaque as ' > begin > raise exception ''no no i dont want to''; > end; > ' language 'plpgsql'; > > MAGIC COMMAND INSERT HERE, INSTALL CALL OF alwaysfail() ON > MODIFICATION OF TABLE a AT COMMIT TIME; create constraint trigger a_foo1 after insert or update or delete on a initially deferred for each row execute procedure alwaysfail(); Like I said, it's an implementation detail, so it's not 100% guaranteed to exist forever, but it's almost certain to last until we have deferrable non-fk constraints. It does also obey set constraints so, set constraints a_foo1 immediate does the checks immediately just as if it were a deferred constraint.