pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedrules / triggers on insert. why after?
3+ messages / 3 participants
[nested] [flat]
* rules / triggers on insert. why after?
@ 2002-07-12 09:24 Ahti Legonkov <lego@127.0.0.1>
0 siblings, 2 replies; 3+ messages in thread
From: Ahti Legonkov @ 2002-07-12 09:24 UTC (permalink / raw)
To: pgsql-sql
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" FOREIGN KEY ("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 '
Begin
INSERT INTO reo ("TYPE") VALUES (''lreo'');
Return Null;
End;
' language 'plpgsql';
CREATE TRIGGER on_insert_lreo
BEFORE INSERT ON lreo FOR EACH ROW
EXECUTE 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: rules / triggers on insert. why after?
@ 2002-07-12 13:41 Jan Wieck <JanWieck@Yahoo.com>
parent: Ahti Legonkov <lego@127.0.0.1>
1 sibling, 0 replies; 3+ messages in thread
From: Jan Wieck @ 2002-07-12 13:41 UTC (permalink / raw)
To: Ahti Legonkov <lego@127.0.0.1>; +Cc: pgsql-sql
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 #
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: rules / triggers on insert. why after?
@ 2002-07-12 15:07 Tom Lane <tgl@sss.pgh.pa.us>
parent: Ahti Legonkov <lego@127.0.0.1>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2002-07-12 15:07 UTC (permalink / raw)
To: Ahti Legonkov <lego@127.0.0.1>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2002-07-12 15:07 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-07-12 09:24 rules / triggers on insert. why after? Ahti Legonkov <lego@127.0.0.1>
2002-07-12 13:41 ` Jan Wieck <JanWieck@Yahoo.com>
2002-07-12 15:07 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox