Search Postgresql Archives

Re: TRIGGER BEFORE INSERT

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Tue, 2007-01-09 at 10:44 -0500, Tom Lane wrote:
> Rafal Pietrak <rafal@xxxxxxxxxxxxxxxxxx> 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?


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux