Re: TRIGGER BEFORE INSERT - Mailing list pgsql-general

Hi!

I'm re-posting this message again in hope someone would have a look at
the case again.   .. it's pending.

In postgres v7.2 I had a trigger function launched BEFORE INSERT, which
did everything I needed (like an UPDATE of other table inside of that
trigger function, and adjustment of the INSERTed ROW)

Currently (as of postgress v8.1.4) I have to put that function OUTSIDE
of a trigger and to achieve that same functionality, in addition to the
original INSERT to the original table, I have to:
    1. make a separate SELECT on that table.
    2. make a separate UPDATE on that table.

Does anyone have any idea how to 'optimise' that? Like folding-up the
three statements I need for this to work in v8.1.4 back to (or closer
to) the initial single statement?

I fear I lack the necesary SQL experience to optimise (I feel like lucky
to have a workaround).

Any help apreciated.

-R

On Tue, 2007-01-09 at 18:41 +0100, Rafal Pietrak wrote:
> On Tue, 2007-01-09 at 10:44 -0500, Tom Lane wrote:
> > Rafal Pietrak <rafal@zorro.isa-geek.com> writes:
> > > 1. either the new value of "test_days.dnia" as already present in the
> > > NEW row, is not visible to "UPDATE test_utarg" sub-statement of the same
> > > transaction. But earlier versions of Postgres did allow for that
> > > visibility.
> > > 2. or the constrainets in earlier postgres were checked on trigger
> > > transaction COMMIT, not along the way; so the constraint violation
> > > didn't occure then.
> >
> > Current versions of PG check foreign keys at the end of each
> > insert/update/delete statement, so your before-insert trigger is in fact
> > erroneous: the referenced key does not yet exist in the target table.
> > I think 7.2 did constraint checking only when the entire interactive
> > command finished, but there were enough cases where that was wrong
> > that we changed it.
> >
> > Consider declaring the foreign-key constraint as DEFERRED.
>
> No luck here.
>
> I've changed the trigger function to have triggers deferred, like the
> following:
>
> database=# CREATE OR REPLACE FUNCTION prado() RETURNS trigger AS $$
> DECLARE wydano INTEGER; BEGIN SET CONSTRAINTS  ALL DEFERRED ; UPDATE
> test_utarg SET dnia=new.id WHERE tm BETWEEN new.dnia AND new.dnia
> +'1day'::interval; GET DIAGNOSTICS wydano := ROW_COUNT; new.total :=
> wydano; RETURN new; END; $$ LANGUAGE plpgsql;
>
> and the results are still the same:
>
> database=# INSERT INTO test_days (dnia) VALUES ('2007-01-06');
> ERROR:  insert or update on table "test_utarg" violates foreign key
> constraint "test_utarg_dnia_fkey"
> DETAIL:  Key (dnia)=(3) is not present in table "test_days".
> CONTEXT:  SQL statement "UPDATE test_utarg SET dnia= $1  WHERE tm
> BETWEEN  $2  AND  $3 +'1day'::interval"
> PL/pgSQL function "prado" line 1 at SQL statement
> ------------------------------------------------------------
>
> But I've never before used a deferred constraints - so may be I haven't
> set it up correctly, in the above definition. Have I?
>
> But actually, I've found a workaround: I've encapsulated the above
> functionality inside of a function, which:
> 1. does an INSERT
> 2. subsequently does a SELECT of what i've just inserted (currently I'm
> stuck with postgres v8.1.4 - so I cannot use INSERT ... RETURNING).
> 3. then I UPDATE the logtable
> 4. then I UPDATE the record INSERTED in step (1).
>
> Originally, I had this functionality in a single "TRIGGER BEFORE"
> function (OK, it fired UPDATE within - but I had the 'fresh' ROW of data
> from step (1) all along with me, inside of that trigger function - no
> need to SELECT/UPDATE it in separate statements).
>
> So I get a performance panelty against my original schema.
>
> Is there a way to optimise?
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings