pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedTrigger/Sequence headache
5+ messages / 4 participants
[nested] [flat]
* Trigger/Sequence headache
@ 2006-02-12 22:27 Foster, Stephen <stephenlfoster@comcast.net>
2006-02-12 23:10 ` Re: Trigger/Sequence headache Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 1 reply; 5+ messages in thread
From: Foster, Stephen @ 2006-02-12 22:27 UTC (permalink / raw)
To: pgsql-sql
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?
I have a function to re-sequence the table and reset the sequence
counter in another application. Normal for that application because of
the constant adding and deleting. So if there isn't another answer then
I can write a version of that for this application and run it
weekly/monthly depending on need and use. It just drives me nuts with
large holes in the table.
Thanks
Lee Foster/
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Trigger/Sequence headache
2006-02-12 22:27 Trigger/Sequence headache Foster, Stephen <stephenlfoster@comcast.net>
@ 2006-02-12 23:10 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2006-02-12 23:25 ` Re: Trigger/Sequence headache Foster, Stephen <stephenlfoster@comcast.net>
0 siblings, 1 reply; 5+ messages in thread
From: Stephan Szabo @ 2006-02-12 23:10 UTC (permalink / raw)
To: Foster, Stephen <stephenlfoster@comcast.net>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Trigger/Sequence headache
2006-02-12 22:27 Trigger/Sequence headache Foster, Stephen <stephenlfoster@comcast.net>
2006-02-12 23:10 ` Re: Trigger/Sequence headache Stephan Szabo <sszabo@megazone.bigpanda.com>
@ 2006-02-12 23:25 ` Foster, Stephen <stephenlfoster@comcast.net>
2006-02-13 07:03 ` Re: Trigger/Sequence headache rlee0001 <robeddielee@hotmail.com>
2006-02-13 11:38 ` Re: Trigger/Sequence headache Markus Schaber <schabi@logix-tt.com>
0 siblings, 2 replies; 5+ messages in thread
From: Foster, Stephen @ 2006-02-12 23:25 UTC (permalink / raw)
To: 'Stephan Szabo' <sszabo@megazone.bigpanda.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Trigger/Sequence headache
2006-02-12 22:27 Trigger/Sequence headache Foster, Stephen <stephenlfoster@comcast.net>
2006-02-12 23:10 ` Re: Trigger/Sequence headache Stephan Szabo <sszabo@megazone.bigpanda.com>
2006-02-12 23:25 ` Re: Trigger/Sequence headache Foster, Stephen <stephenlfoster@comcast.net>
@ 2006-02-13 07:03 ` rlee0001 <robeddielee@hotmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: rlee0001 @ 2006-02-13 07:03 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Trigger/Sequence headache
2006-02-12 22:27 Trigger/Sequence headache Foster, Stephen <stephenlfoster@comcast.net>
2006-02-12 23:10 ` Re: Trigger/Sequence headache Stephan Szabo <sszabo@megazone.bigpanda.com>
2006-02-12 23:25 ` Re: Trigger/Sequence headache Foster, Stephen <stephenlfoster@comcast.net>
@ 2006-02-13 11:38 ` Markus Schaber <schabi@logix-tt.com>
1 sibling, 0 replies; 5+ messages in thread
From: Markus Schaber @ 2006-02-13 11:38 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2006-02-13 11:38 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-02-12 22:27 Trigger/Sequence headache Foster, Stephen <stephenlfoster@comcast.net>
2006-02-12 23:10 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2006-02-12 23:25 ` Foster, Stephen <stephenlfoster@comcast.net>
2006-02-13 07:03 ` rlee0001 <robeddielee@hotmail.com>
2006-02-13 11:38 ` Markus Schaber <schabi@logix-tt.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox