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

[D. Dante Lorenso]

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

[D. Dante Lorenso]

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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Richard Huxton]

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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match