From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Rafal Pietrak <rafal(at)zorro(dot)isa-geek(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Data visibility |
Date: | 2006-10-15 19:15:50 |
Message-ID: | 24765.1160939750@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Rafal Pietrak <rafal(at)zorro(dot)isa-geek(dot)com> writes:
> CREATE TABLE master (id int not null unique, info text, ....);
> CREATE TABLE aux (master int references master(id), info text, ...);
> CREATE FUNCTION adjust() RETURNS "trigger" AS $$ BEGIN
> new.id := 1000-old.id;
> INSERT INTO aux (master, info) VALUES (new.id, 'hello world');
> RETURN new;
> END $$ LANGUAGE plpgsql;
> CREATE TRIGGER pico BEFORE INSERT ON master FOR EACH ROW EXECUTE
> PROCEDURE adjust();
> But in my trigger function "adjust()", executed within a transaction
> opened by "INSERT INTO master", the 'fresh' data (set by "new.id :=
> value") isn't visible to other commands (like: INSERT INTO aux...).
Well, of course not: it's a BEFORE trigger, so the row insertion hasn't
actually happened yet. I think you need to split this operation into a
BEFORE trigger that changes the ID, and an AFTER trigger that propagates
the data into the other table.
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2006-10-15 19:20:26 | Re: Same-page UPDATEs in bloated tables |
Previous Message | Alvaro Herrera | 2006-10-15 18:25:10 | Re: Same-page UPDATEs in bloated tables |