Received: from sss.pgh.pa.us (unknown [192.204.191.242])
	by postgresql.org (Postfix) with ESMTP id EFEFE47593B
	for <pgsql-sql@postgresql.org>; Fri, 12 Jul 2002 11:07:24 -0400 (EDT)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.12.5/8.12.5) with ESMTP id g6CF7TAl009793;
	Fri, 12 Jul 2002 11:07:29 -0400 (EDT)
To: Ahti Legonkov <lego@127.0.0.1>
Cc: pgsql-sql@postgresql.org
Subject: Re: rules / triggers on insert. why after? 
In-reply-to: <3D2EA031.3000104@127.0.0.1> 
References: <3D2EA031.3000104@127.0.0.1>
Comments: In-reply-to Ahti Legonkov <lego@127.0.0.1>
	message dated "Fri, 12 Jul 2002 12:24:01 +0300"
Date: Fri, 12 Jul 2002 11:07:29 -0400
Message-ID: <9792.1026486449@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Archive-Number: 200207/188
X-Sequence-Number: 8567

Ahti Legonkov <lego@127.0.0.1> writes:
> CREATE TABLE reo (
>    "REO_ID" int4 DEFAULT nextval('"REO_ID_seq"'::text) NOT NULL,
>    "TYPE" varchar(64) NOT NULL,
>    CONSTRAINT "REO_ID_reo_ukey" UNIQUE ("REO_ID"),
>    CONSTRAINT reo_pkey PRIMARY KEY ("REO_ID")
> );

> CREATE TABLE lreo (
>    "CITY" varchar(64),
>    "STREET" varchar(64),
>    "PRICE" int4,
>    "REO_ID" int4 DEFAULT currval('"REO_ID_seq"'::text),
>    CONSTRAINT "REO_ID_land_reo_ukey" UNIQUE ("REO_ID"),
>    CONSTRAINT "fk_LREO_REO_ID" FOREIGN KEY ("REO_ID") REFERENCES reo 
> ("REO_ID") ON DELETE NO ACTION ON UPDATE NO ACTION NOT DEFERRABLE 
> INITIALLY IMMEDIATE
> );

That default for reo_id is too fragile to consider using in any case.
You are making way too many assumptions about when defaults will be
evaluated relative to other actions (such as rule/trigger firings).

I'd suggest that you have no default for column reo_id, and instead
have a BEFORE INSERT trigger for lreo that (a) inserts a row into reo
and (b) sets new.reo_id to the inserted key (which you could get from
currval at that point).

			regards, tom lane