Thread: Trigger/Sequence headache
<div class="Section1"><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">This is going to be one of those stupid problems of mine.<span style="mso-spacerun:yes"> </span>I havean insert trigger setup to verify that duplicate or repeating information isn’t storage in the table.<span style="mso-spacerun:yes"> </span>If trigger function finds the information as a duplicate it returns a NULL and the informationisn’t added; that works.<span style="mso-spacerun:yes"> </span>The problem I’m having is that it is incrementingthe sequence counter even when the data isn’t added.<span style="mso-spacerun:yes"> </span>Is this somethingthat I have to live with or should I be returning something other than a NULL?</span></font><p class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">I have a function to re-sequence the table and reset the sequence counter in another application.<spanstyle="mso-spacerun:yes"> </span></span></font><font face="Arial" size="2"><span style="font-size:10.0pt;font-family:Arial">Normal</span></font><fontface="Arial" size="2"><span style="font-size:10.0pt;font-family:Arial">for that application because of the constant adding and deleting.<span style="mso-spacerun:yes"> </span>So if there isn’t another answer then I can write a version of that for this applicationand run it weekly/monthly depending on need and use.<span style="mso-spacerun:yes"> </span>It just drives menuts with large holes in the table.</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">Thanks</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">Lee Foster/</span></font></div>
On Sun, 12 Feb 2006, Foster, Stephen wrote: > This is going to be one of those stupid problems of mine. I have an > insert trigger setup to verify that duplicate or repeating information > isn't storage in the table. If trigger function finds the information > as a duplicate it returns a NULL and the information isn't added; that > works. The problem I'm having is that it is incrementing the sequence > counter even when the data isn't added. Is this something that I have > to live with or should I be returning something other than a NULL? The sequence is going to increment upon getting the value. However, I think, if instead of using a default, you got the next value in the trigger after you determined that it wasn't a duplicate and set the field, it wouldn't increment for this case. This changes some other behaviors a little (for example DEFAULT in updates as well), so you'd need to see whether it'd be acceptable. Of course, errors, rollbacks and deletes will still leave holes.
That's what I thought was going to be the answer. I was just hoping I was making a mistake somehow. It's no big deal but I like things organized and hate giant holes. Ok, one more thing for one of the batch jobs. No problem I have a cleanup routine. Thanks for the help, Lee Foster/ -----Original Message----- From: Stephan Szabo [mailto:sszabo@megazone.bigpanda.com] Sent: Sunday, February 12, 2006 5:11 PM To: Foster, Stephen Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] Trigger/Sequence headache On Sun, 12 Feb 2006, Foster, Stephen wrote: > This is going to be one of those stupid problems of mine. I have an > insert trigger setup to verify that duplicate or repeating information > isn't storage in the table. If trigger function finds the information > as a duplicate it returns a NULL and the information isn't added; that > works. The problem I'm having is that it is incrementing the sequence > counter even when the data isn't added. Is this something that I have > to live with or should I be returning something other than a NULL? The sequence is going to increment upon getting the value. However, I think, if instead of using a default, you got the next value in the trigger after you determined that it wasn't a duplicate and set the field, it wouldn't increment for this case. This changes some other behaviors a little (for example DEFAULT in updates as well), so you'd need to see whether it'd be acceptable. Of course, errors, rollbacks and deletes will still leave holes.
Hi, Stephen, Foster, Stephen wrote: > That's what I thought was going to be the answer. I was just hoping I > was making a mistake somehow. It's no big deal but I like things > organized and hate giant holes. > > Ok, one more thing for one of the batch jobs. No problem I have a > cleanup routine. Out of curiosity: Could you explain what's the problem with the holes? Bigserial should provide enough number space that holes are no problem. Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
Stephen,
You don't need to use a seperate batch to clean up the table. As
Stephan pointed out, you can call nextval after you determine that the
new row isn't a duplicate.
In case you misunderstood what Stephan had suggested let me try to
explain what is happening.
When PostgreSQL receives an INSERT request it aggregates field values
for the new row from several sources.
1) The statement itself (INSERT INTO ... VALUES ...)
2) The fields' DEFAULT values (provided by the CREATE TABLE statement)
3) Any CONSTRANTs are taken into consideration
4) BEFORE triggers are called in alphabetical order
5) The new row is inserted
6) AFTER triggers are called in alphabetical order
The sequence is being incrimented when NEXTVAL is evaluated. NEXTVAL
can be evaluated in steps 1, 2 or 4 depending on how your application
is written. Since you want to be able to cancel the operation in step 4
without NEXTVAL having been evaluated, this is where you should call
NEXTVAL (instead of in steps 1 or 2). So in your trigger you want to do
something like this:
-- Check for duplicates here...RETURN NULL;
NEW.ID = NEXTVAL('SEQ_MYTABLE_ID');
RETURN NEW;
Also, are you sure you want to return NULL rather than raise an
exception? Either way will work depending on what the desired behavour
is. The thing is that silently dropping rows might be the source of a
debugging nightmare later on where-as raising an exception is a bit
easier to detect. Just me $0.02. To raise an exception see RAISE
EXCEPTION in the postgresql manual. Its really easy but will cancel the
current transaction so it may not be desirable for your uses.
-Robert