Thread: A history procedure that prevents duplicate entries

Hi all,

   I've been using a procedure to make a copy of data in my public
schema into a history schema on UPDATE and INSERTs.

   To prevent duplicate entries in the history, I have to lead in the
current data, compare it in my program and then decided whether
something has actually changed or not before doing an update. This
strikes me as wasteful coding and something I should be able to do in my
procedure.

   Given the following example tables and procedure, how could I go
about changing it to prevent duplicate/unchanged entries being saved to
the history schema? Even a pointer to a relevant section of the docs
would be appreciated... My knowledge of procedures is pretty weak. :)

Madi

CREATE TABLE radical
(
    rad_id        integer        primary key    default(nextval('id_seq')),
    rad_char    text        not null,
    rad_name    text
);

CREATE TABLE history.radical
(
    rad_id        integer        not null,
    rad_char    text        not null,
    rad_name    text,
    hist_id         integer         not null
default(nextval('hist_seq')),
    modified_date   timestamp       default now()
);

CREATE FUNCTION history_radical() RETURNS "trigger"
    AS $$
    DECLARE
        hist_radical RECORD;
    BEGIN
        SELECT INTO hist_radical * FROM public.radical WHERE rad_id=new.rad_id;
        INSERT INTO history.radical
            (rad_id, rad_char, rad_name)
            VALUES
            (hist_radical.rad_id, hist_radical.rad_char, hist_radical.rad_name);
        RETURN NULL;
    END;$$
LANGUAGE plpgsql;

CREATE TRIGGER trig_radical AFTER INSERT OR UPDATE ON "radical" FOR EACH
ROW EXECUTE PROCEDURE history_radical();

On 16 Aug 2009, at 4:24, Madison Kelly wrote:

> Hi all,

...

> CREATE FUNCTION history_radical() RETURNS "trigger"
>     AS $$
>     DECLARE
>         hist_radical RECORD;
>     BEGIN
>         SELECT INTO hist_radical * FROM public.radical WHERE
> rad_id=new.rad_id;

I assume you mean to only select an existing record here in case the
trigger is fired on an update? You are in fact always selecting at
least one record here because this is called from an AFTER INSERT OR
UPDATE trigger; the record has already been inserted or updated, so
the select statement will find the new (version of) the record.

I'm also not entirely sure what the value is of calling your procedure
on INSERT. If I interpreted you correctly the same data would be added
to the history the first time it gets updated (except for the
different timestamp and history id of course). I'd probably just call
this procedure on UPDATE, and on DELETE too. If you do want to fire on
INSERT I'd make it clear there was no data before that history entry,
for example by filling the record with NULL values or by adding a
column for the value of TG_OP to the history table.

Besides that, you don't need the SELECT statement or the RECORD-type
variable as the data you need is already in the NEW and OLD records.
But, you only have an OLD record when your trigger fired from an
UPDATE, so you need to check whether your trigger fired from INSERT or
UPDATE.

So, what you need is something like:

IF TG_OP = 'INSERT' THEN
    hist_radical := NEW;
ELSE -- TG_OP = 'UPDATE'
    hist_radical := OLD;
END IF;

INSERT INTO history.radical
    (rad_id, rad_char, rad_name)
VALUES
    (hist_radical.rad_id, hist_radical.rad_char, hist_radical.rad_name);

>         INSERT INTO history.radical
>             (rad_id, rad_char, rad_name)
>             VALUES
>             (hist_radical.rad_id, hist_radical.rad_char,
> hist_radical.rad_name);
>         RETURN NULL;
>     END;$$
> LANGUAGE plpgsql;
>
> CREATE TRIGGER trig_radical AFTER INSERT OR UPDATE ON "radical" FOR
> EACH ROW EXECUTE PROCEDURE history_radical();


Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4a87e8d010131556343596!

Hi all,

  I've been using a procedure to make a copy of data in my public schema into a history schema on UPDATE and INSERTs.

  To prevent duplicate entries in the history, I have to lead in the current data, compare it in my program and then decided whether something has actually changed or not before doing an update. This strikes me as wasteful coding and something I should be able to do in my procedure.

  Given the following example tables and procedure, how could I go about changing it to prevent duplicate/unchanged entries being saved to the history schema? Even a pointer to a relevant section of the docs would be appreciated... My knowledge of procedures is pretty weak. :)

Madi

CREATE TABLE radical
(
    rad_id        integer        primary key    default(nextval('id_seq')),
    rad_char    text        not null,
    rad_name    text
);

CREATE TABLE history.radical
(
    rad_id        integer        not null,
    rad_char    text        not null,
    rad_name    text,
    hist_id         integer         not null default(nextval('hist_seq')),
    modified_date   timestamp       default now()
);

CREATE FUNCTION history_radical() RETURNS "trigger"
    AS $$
    DECLARE
        hist_radical RECORD;
    BEGIN
        SELECT INTO hist_radical * FROM public.radical WHERE rad_id=new.rad_id;
        INSERT INTO history.radical
            (rad_id, rad_char, rad_name)
            VALUES
            (hist_radical.rad_id, hist_radical.rad_char, hist_radical.rad_name);
        RETURN NULL;
    END;$$
LANGUAGE plpgsql;

CREATE TRIGGER trig_radical AFTER INSERT OR UPDATE ON "radical" FOR EACH ROW EXECUTE PROCEDURE history_radical();

Alban Hertroys wrote:
> On 16 Aug 2009, at 4:24, Madison Kelly wrote:
>
>> Hi all,
>
> ...
>
>> CREATE FUNCTION history_radical() RETURNS "trigger"
>>     AS $$
>>     DECLARE
>>         hist_radical RECORD;
>>     BEGIN
>>         SELECT INTO hist_radical * FROM public.radical WHERE
>> rad_id=new.rad_id;
>
> I assume you mean to only select an existing record here in case the
> trigger is fired on an update? You are in fact always selecting at least
> one record here because this is called from an AFTER INSERT OR UPDATE
> trigger; the record has already been inserted or updated, so the select
> statement will find the new (version of) the record.
>
> I'm also not entirely sure what the value is of calling your procedure
> on INSERT. If I interpreted you correctly the same data would be added
> to the history the first time it gets updated (except for the different
> timestamp and history id of course). I'd probably just call this
> procedure on UPDATE, and on DELETE too. If you do want to fire on INSERT
> I'd make it clear there was no data before that history entry, for
> example by filling the record with NULL values or by adding a column for
> the value of TG_OP to the history table.

The INSERT is there mainly for my convenience. If I am going to the
history schema to get data, it's convenient to know that is has a
complete copy of the data in the public schema, too.

> Besides that, you don't need the SELECT statement or the RECORD-type
> variable as the data you need is already in the NEW and OLD records.
> But, you only have an OLD record when your trigger fired from an UPDATE,
> so you need to check whether your trigger fired from INSERT or UPDATE.
>
> So, what you need is something like:
>
> IF TG_OP = 'INSERT' THEN
>     hist_radical := NEW;
> ELSE -- TG_OP = 'UPDATE'
>     hist_radical := OLD;
> END IF;
>
> INSERT INTO history.radical
>     (rad_id, rad_char, rad_name)
> VALUES
>     (hist_radical.rad_id, hist_radical.rad_char, hist_radical.rad_name);
>
>
> Alban Hertroys

To help me improve my understanding of procedures, how would this
prevent an UPDATE from creating a new entry in the history schema when
all the column values are the same as the last entry in history?

Thanks!!

Madi

On 16 Aug 2009, at 17:38, Madison Kelly wrote:

>> Besides that, you don't need the SELECT statement or the RECORD-
>> type variable as the data you need is already in the NEW and OLD
>> records. But, you only have an OLD record when your trigger fired
>> from an UPDATE, so you need to check whether your trigger fired
>> from INSERT or UPDATE.
>> So, what you need is something like:
>> IF TG_OP = 'INSERT' THEN       hist_radical := NEW;
>> ELSE -- TG_OP = 'UPDATE'
>>    hist_radical := OLD;
>> END IF;
>> INSERT INTO history.radical
>>    (rad_id, rad_char, rad_name)
>> VALUES
>>    (hist_radical.rad_id, hist_radical.rad_char,
>> hist_radical.rad_name);
>
> To help me improve my understanding of procedures, how would this
> prevent an UPDATE from creating a new entry in the history schema
> when all the column values are the same as the last entry in history?


It doesn't, as it wasn't entirely clear to me how you wanted it to
behave.

To prevent duplicate history entries from updates you would need to
compare the values of NEW and OLD and return if they're equal. In 8.4
that's as simple as checking that NEW IS DISTINCT FROM OLD, but in
earlier versions it's a bit more involved. There was a discussion
about this very topic here recently.

Alban Hertroys

--
Screwing up is the correct approach to attaching something to the
ceiling.


!DSPAM:737,4a8bd41d10131434511488!