Обсуждение: Data visibility

Hi,

I'm trying to write a trigger function, that would update an
'associated' TEBLE on INSERT to master table:

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();

Note, that the trigger function ADJUST() *computs* the ID value for
MASTER. The value computed complies to all MASTER table constraints, so
it should be usable as foreing key for the insert statement that
follows, but it isn't - I get foreign key reference violation fault at
that point.

Now, normaly (e.g. in SQL sequence as typed into the psql command line
utility), when I:
BEGIN;
INSERT INTO master (id,...)...;
SELECT * from master;
ROLLBACK;
SELECT * from master;

... the first SELECT above shows the newly inserted values, despite the
fact, that the transaction didn't commit, yet. And when the transaction
rolls back, the data automagically disapear (second SELECT). I
understand this is normal.

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...).

Is this intentional?

Theoretically: would it violate corrent database application coding
style/standards (SQL standard?), if the new.* data was in fact visible
for statements inside such transaction, like the INSERT AUX above?

BTW: May be there is other solution for my "INSERT ... AUX" which I
cannot see myself? One thing, though: I cannot have TRIGGER AFTER INSERT
do the job of putting the correct data into AUX TABLE, since that table
takes intermediate data used during MASTER.ID computation, and it is
required, that those intermediate data never-ever get into the MASTER
table itself (or any other table apart from the AUX table - where they
must reside. It's one of the reasons for AUX table to exist).
--
-R

On Sun, 2006-10-15 at 20:01 +0200, Rafal Pietrak wrote:
>     new.id := 1000-old.id;

Sory, correction.

Of cource, this ID update looks more like the following (OLD.* isn't
valid at this point):

    new.id := 1000 - new.id;

--
-R

Rafal Pietrak <rafal@zorro.isa-geek.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

On Sun, 2006-10-15 at 15:15 -0400, Tom Lane wrote:
> 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.

Hmm. I tried that, But I'm stuck with finding a way to propagate the
'intermediate data' between BEFORE/AFTER triggers, *outside* of a TABLE
structure. That data is easily accesable inside the BEFORE TRIGGER as
simple variable.

But, would it work if I drop foreign key constraint on AUX table in
BEFORE INSERT trigger function, and resotre it back in AFTER INSERT? ...
simply rising an error (thus aborting INSERT) if the later fails? Are
there scenarios, leading to foreign key consistancy loss if I do that?

Ideas?

--
-R

Rafal Pietrak <rafal@zorro.isa-geek.com> writes:
> Hmm. I tried that, But I'm stuck with finding a way to propagate the
> 'intermediate data' between BEFORE/AFTER triggers, *outside* of a TABLE
> structure. That data is easily accesable inside the BEFORE TRIGGER as
> simple variable.

Um ... what data do you think is visible to the BEFORE trigger but not
the AFTER trigger?  They both see both states of the row, if that's what
you're wondering.

            regards, tom lane

On Sun, 2006-10-15 at 18:16 -0400, Tom Lane wrote:
> Rafal Pietrak <rafal@zorro.isa-geek.com> writes:
> > Hmm. I tried that, But I'm stuck with finding a way to propagate the
> > 'intermediate data' between BEFORE/AFTER triggers, *outside* of a TABLE
> > structure. That data is easily accesable inside the BEFORE TRIGGER as
> > simple variable.
>
> Um ... what data do you think is visible to the BEFORE trigger but not
> the AFTER trigger?  They both see both states of the row, if that's what
> you're wondering.

No, no. I don't 'think' the data is lost between BEFORE and AFTER. I
*make* them disapear.... as I cannot find any place to pass them over.
The MASTSTER table does not contain columns for them ... and will not.
But in the BEFORE trigger function, I have them as local variables.

Are there ways to have my private 'virtual placeholder'? - like *NEW*
and *OLD* are? Placeholders which are outside of any table, and
available just inside the INSERT transaction, but throuout *all* it's
triggers.

Taking reference from other languages, I might think of some sort of
static variables:
CREATE FUNCTION piko() ....$$ DECLARE priv RECORD STATIC; BEGIN ....
END;
... or
CREATE FUNCTION piko(priv RECORD STATIC) ....

so that another function used for another trigger on that same table
will have access to the same record, which would retain data throught
the transactoin?

Just ideas. But may be there are currently tools to have such
functionality?

But if nothing like this is available, I think I'll need an additional
table, identical to AUX - sort of AUX shadow, but without foreign key
constraint to MASTER, so that I can put my data there in BEFORE, then
copy those into the AUX in AFTER, and purge the aux-shadow on exit from
AFTER.

In any case, discussion helps. Thenx.

--
-R