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?