Обсуждение: rules / triggers on insert. why after?

Hi,

I have the following things in my database:

CREATE SEQUENCE "REO_ID_seq" INCREMENT 1 MINVALUE 1 MAXVALUE 2147483647 
CACHE 1;

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"
FOREIGNKEY ("REO_ID") REFERENCES reo 
 
("REO_ID") ON DELETE NO ACTION ON UPDATE NO ACTION NOT DEFERRABLE 
INITIALLY IMMEDIATE
);

---- either this
-- the rule won't work in 7.2.x, because it is done AFTER the Insert has
-- executed :(
CREATE RULE "rule_lreo_INSERT" AS ON INSERT    TO lreo    DO INSTEAD INSERT INTO reo("TYPE") values ('lreo');
---- or this
-- but why does not this work ???
CREATE FUNCTION insert_lreo() Returns opaque As '
BeginINSERT INTO reo ("TYPE") VALUES (''lreo'');Return Null;
End;
' language 'plpgsql';


CREATE TRIGGER on_insert_lreoBEFORE INSERT ON lreo FOR EACH ROWEXECUTE PROCEDURE insert_lreo();
---- end

On postgres 7.1.3 it was OK to do this:

INSERT INTO "lreo" ("STREET", "PRICE", "CITY")
VALUES ('street', 1234, 'the city');

but on postgres 7.2 and 7.2.1 I get an error that "REO_ID_seq.currval is 
not yet defined for this session." The trigger should execute *before* 
the insert, right?

Does anyone know why since postgres 7.2 the rules are executed *after* 
the insert?

Hope you can help me:)

-- 
Ahti Legonkov

Ahti Legonkov wrote:

> Does anyone know why since postgres 7.2 the rules are executed *after*
> the insert?

Because people where still complaining that they changed to execute
*before* in v6.4.


Jan

-- 

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #

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