Thread: Update inside (Insert) Trigger and Unique constraint...

Update inside (Insert) Trigger and Unique constraint...

From
"D. Dante Lorenso"
Date:
I'm trying to build a table that will store a history of records
by enumerating the records.  I want the newest record to always
be number ZERO, so I created a trigger on my table to handle the
assignment of version numbers:

    CREATE TRIGGER "trg_audio_file_insert" BEFORE INSERT
    ON "public"."audio_file" FOR EACH ROW
    EXECUTE PROCEDURE "public"."trg_audio_file_insert"();

My trigger function looks like this...

    CREATE FUNCTION "public"."trg_audio_file_insert" () RETURNS trigger AS'
    BEGIN
       ...
        /* rollback the version number of previous versions of this
audio_id */
        UPDATE audio_file SET
            afile_version = afile_version + 1
        WHERE acct_id = NEW.acct_id
        AND audio_id = NEW.audio_id;

        /* newly inserted row is always the latest version ''0'' */
        NEW.afile_version := 0;

       ...
        /* yeah, that worked */
        RETURN NEW;
    END;
    'LANGUAGE 'plpgsql';

There exists a unique constraint on the 'the audio_id / audio_version'
columns.  However, when I insert records into this table, I'm getting an
error like:

    duplicate key violates unique constraint "idx_audio_file_id_version"
    CONTEXT:  PL/pgSQL function "trg_audio_file_insert" line 18 at SQL
statement

I don't understand WHY there could be a violation of the constraint when
I clearly asked for the update to be performed prior to the assigning of
NEW.afile_version := 0;.  Yes, there exist two records with my acct_id and
audio_id with versions 0 and 1 already.  The update should roll them to
1 and 2 then the insert at 0 should be unique still.

Why isn't this working?  What's the deal with ordering when it comes to
triggers?  Is the update not performed when I tell it to?

Dante




Re: Update inside (Insert) Trigger and Unique constraint...

From
"D. Dante Lorenso"
Date:
NEVERMIND...  This is not a trigger problem.  It's a unique
constraint problem...  If I have a unique constraint on
a column like 'afile_version', and want to do an update on
that column to add one to each number, is there a way to
add an 'ORDER BY' to the update?

    UPDATE audio_file SET
        afile_version = afile_version + 1
    ORDER BY afile_version DESC;

???

The problem is that with

    0 --> 1
    1 --> 2
    2 --> 3
    insert 0

The update would update 0 to 1 and hit a constraint violation.
I needed to start from the bottom and work my way up...

    2 --> 3
    1 --> 2
    0 --> 1
    insert 0

So, I wrote a FOR LOOP like this:

    FOR my_rec IN
        SELECT afile_id
        FROM audio_file
        ORDER BY afile_version DESC
    LOOP
        /* roll back the version... */
        UPDATE audio_file SET
            afile_version = afile_version + 1
        WHERE afile_id = my_rec.afile_id;
    END LOOP;

And that does the trick, but I guess I might also be able
to do something like this?:

    UPDATE audio_file SET
        afile_version = afile_version + 1
    WHERE afile_id IN (
        SELECT afile_id
        FROM audio_file
        ORDER BY afile_version DESC
    );

Yeah, so I guess I figured this out on my own, but from a
performance viewpoint, would the second method be better
... or the first?  Does it matter?

Dante




D. Dante Lorenso wrote:

> I'm trying to build a table that will store a history of records
> by enumerating the records.  I want the newest record to always
> be number ZERO, so I created a trigger on my table to handle the
> assignment of version numbers:
>
>    CREATE TRIGGER "trg_audio_file_insert" BEFORE INSERT
>    ON "public"."audio_file" FOR EACH ROW
>    EXECUTE PROCEDURE "public"."trg_audio_file_insert"();
>
> My trigger function looks like this...
>
>    CREATE FUNCTION "public"."trg_audio_file_insert" () RETURNS trigger
> AS'
>    BEGIN
>       ...
>        /* rollback the version number of previous versions of this
> audio_id */
>        UPDATE audio_file SET
>            afile_version = afile_version + 1
>        WHERE acct_id = NEW.acct_id
>        AND audio_id = NEW.audio_id;
>             /* newly inserted row is always the latest version ''0'' */
>        NEW.afile_version := 0;
>
>       ...
>        /* yeah, that worked */
>        RETURN NEW;
>    END;
>    'LANGUAGE 'plpgsql';
>
> There exists a unique constraint on the 'the audio_id / audio_version'
> columns.  However, when I insert records into this table, I'm getting an
> error like:
>
>    duplicate key violates unique constraint "idx_audio_file_id_version"
>    CONTEXT:  PL/pgSQL function "trg_audio_file_insert" line 18 at SQL
> statement
>
> I don't understand WHY there could be a violation of the constraint when
> I clearly asked for the update to be performed prior to the assigning of
> NEW.afile_version := 0;.  Yes, there exist two records with my acct_id
> and
> audio_id with versions 0 and 1 already.  The update should roll them to
> 1 and 2 then the insert at 0 should be unique still.
>
> Why isn't this working?  What's the deal with ordering when it comes to
> triggers?  Is the update not performed when I tell it to?
>
> Dante
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings




Re: Update inside (Insert) Trigger and Unique constraint...

From
Richard Huxton
Date:
On Wednesday 21 January 2004 20:12, D. Dante Lorenso wrote:
> NEVERMIND...  This is not a trigger problem.  It's a unique
> constraint problem...  If I have a unique constraint on
> a column like 'afile_version', and want to do an update on
> that column to add one to each number, is there a way to
> add an 'ORDER BY' to the update?
[snip]
>     FOR my_rec IN
>         SELECT afile_id
>         FROM audio_file
>         ORDER BY afile_version DESC
>     LOOP
>         /* roll back the version... */
>         UPDATE audio_file SET
>             afile_version = afile_version + 1
>         WHERE afile_id = my_rec.afile_id;
>     END LOOP;

This was mentioned in the last couple of weeks on one of the lists - don't
know which. Someone suggested doing UPDATE ...version=-version followed by
UPDATE ...version=(-version)+1

> And that does the trick, but I guess I might also be able
> to do something like this?:

Nope - or rather, if it does work I think it's down to chance.

>     UPDATE audio_file SET
>         afile_version = afile_version + 1
>     WHERE afile_id IN (
>         SELECT afile_id
>         FROM audio_file
>         ORDER BY afile_version DESC
>     );

PS - this is really a bug, but it doesn't seem to bite very often, and there
are work-arounds, so it hasn't reached the top of any developer's list yet.

--
  Richard Huxton
  Archonet Ltd