Thread: Suggested improvement : Adjust SEQUENCES to accept an INCREMENT of functionname(parameters) instead of an integer

Hi all,

Am doing some work with sequences at the moment, and I'm finding it would be
useful to have sequences which use an increment amount decided by a function
call, instead of just a straight integer amount (as we presently do).

For my example, I'd use this to add random positive increments (specifically
to avoid easy predictability of the sequence), but it would be quite flexible.

i.e. CREATE SEQUENCE newseq INCREMENT trunc(random() * 10);

Could do the same for the START, MINVALUE and MAXVALUE parameters also.

Would others also benefit from this alteration?  If so, we might like to ask
for it to be added to Bruce's TODO list.

Regards and best wishes,

Justin Clift

On Fri, 22 Jun 2001, Justin Clift wrote:

> i.e. CREATE SEQUENCE newseq INCREMENT trunc(random() * 10);

Didn't you ask this like 2 weeks ago?

I said it once, I'll say it again.  Stop being lazy, and write a trigger.
The Postgres developers are *not* going to alter the core functionality of
their database to include something that has always been available.

http://www.postgresql.org/idocs/index.php?triggers.html

Read it, use it, love it.

--
+-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+
| Shaun M. Thomas                INN Database Programmer              |
| Phone: (309) 743-0812          Fax  : (309) 743-0830                |
| Email: sthomas@townnews.com    AIM  : trifthen                      |
| Web  : hamster.lee.net                                              |
|                                                                     |
|     "Most of our lives are about proving something, either to       |
|      ourselves or to someone else."                                 |
|                                           -- Anonymous              |
+-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+

> Am doing some work with sequences at the moment, and I'm finding it would
be
> useful to have sequences which use an increment amount decided by a
function
> call, instead of just a straight integer amount (as we presently do).
>
> For my example, I'd use this to add random positive increments
(specifically
> to avoid easy predictability of the sequence), but it would be quite
flexible.
>
> i.e. CREATE SEQUENCE newseq INCREMENT trunc(random() * 10);

Couldn't you just call nextval 0-9 random number of times before your
INSERTs?

> Could do the same for the START, MINVALUE and MAXVALUE parameters also.
>
> Would others also benefit from this alteration?  If so, we might like to
ask
> for it to be added to Bruce's TODO list.

I'm usually Mr. Features, but I don't see a whole lot of value in this one.
I've tried to come up with a scenario that this might be useful and they're
all contrived examples that could probably be best handled by creating my
own system rather than forcing the functionality into the existing
mechanism. Unless a special case were made for this type of SEQUENCE, I
imagine that this would drastically slow them down as a whole.

I'd much rather see int8 SEQUENCEs than a change in increment amounts.

Greg

On Thu, 28 Jun 2001, Shaun Thomas wrote:

> On Fri, 22 Jun 2001, Justin Clift wrote:
>
> > i.e. CREATE SEQUENCE newseq INCREMENT trunc(random() * 10);
>
> Didn't you ask this like 2 weeks ago?
>
> I said it once, I'll say it again.  Stop being lazy, and write a trigger.
> The Postgres developers are *not* going to alter the core functionality of
> their database to include something that has always been available.

Actually he asked it a week ago (see Jun 22 timestamp in his email). The
list server keeps regurgitating old messages and resending them. This has
been the behavior for last 2-3 months, and its hella annoying. I thought
its just me, but apparently its not.

At 12:10 PM 6/22/01 +1000, Justin Clift wrote:
>Hi all,
>
>Am doing some work with sequences at the moment, and I'm finding it would be
>useful to have sequences which use an increment amount decided by a function
>call, instead of just a straight integer amount (as we presently do).
>
>For my example, I'd use this to add random positive increments (specifically
>to avoid easy predictability of the sequence), but it would be quite
flexible.

To avoid predictability I usually prefer to have a sequence number and
prepend/append a random number (generated from /dev/urandom or some other
source of entropy).

e.g.
<bookingnumber>=<sequencenumber><random fixed X digit number>

So in order to generate a valid booking number the attacker must get BOTH
correct. For low security stuff like taxi/ticket booking numbers X=3 is
probably good enough, but adding more is no problem.

For web app session IDs I use <longrandomstring>.<sequencenumber>.

That way queries can use the index:
e.g.
select stuff from sessiontable where seqnum=<sequencenumber> and
seqstr=<longrandomstring>.

Given your example, I don't really see why you would need what you want.
Nor does it seem a better solution.

If SEQUENCES are going to be changed, I'd rather prefer to have an option
to use int8 sequences. But I believe at the moment there are other issues
in postgresql that have to be fixed first before int4 sequences become a
limiting factor/issue.

Regards,
Link.

Alex Pilosov <alex@pilosoft.com> writes:
> Actually he asked it a week ago (see Jun 22 timestamp in his email). The
> list server keeps regurgitating old messages and resending them. This has
> been the behavior for last 2-3 months, and its hella annoying. I thought
> its just me, but apparently its not.

I think what's been happening a lot lately is:

1. Someone sends a message to a list they're not subscribed to.
   majordomo puts it into Marc's "to approve" queue and sends back a
   note saying the message is being delayed for administrator approval.

2. Not wanting to wait, the someone subscribes to the target list and
   resends his message.  Discussion ensues.

3. Some time later, Marc gets around to emptying the approval queue and
   allows the original version of the message to be posted.

I agree it's annoying, but I'm not sure what can be done about it.
Running the PG lists takes a lot of Marc's time already --- I don't
think it's reasonable to expect him to check for duplications of this
kind on top of everything else.  (And no, I don't want to drop the
non-subscriber restriction.  The lists have been wonderfully spam-free
lately, largely because of Marc's filters.)

Anyone have an idea about an automatic solution that wouldn't take any
extra admin time?  Maybe, when someone subscribes, automatically drop
any pending messages from that person in the approval queue?  That'd
eliminate this kind of duplication, but I'm not sure what the downside
might be, or how much work it'd be to set up.  (For "drop" read "return
to sender", anyway...)

            regards, tom lane

On Thu, 28 Jun 2001, Tom Lane wrote:

> Alex Pilosov <alex@pilosoft.com> writes:
> > Actually he asked it a week ago (see Jun 22 timestamp in his email). The
> > list server keeps regurgitating old messages and resending them. This has
> > been the behavior for last 2-3 months, and its hella annoying. I thought
> > its just me, but apparently its not.
>
> I think what's been happening a lot lately is:
>
> 1. Someone sends a message to a list they're not subscribed to.
>    majordomo puts it into Marc's "to approve" queue and sends back a
>    note saying the message is being delayed for administrator approval.
>
> 2. Not wanting to wait, the someone subscribes to the target list and
>    resends his message.  Discussion ensues.
>
> 3. Some time later, Marc gets around to emptying the approval queue and
>    allows the original version of the message to be posted.
>
> I agree it's annoying, but I'm not sure what can be done about it.
> Running the PG lists takes a lot of Marc's time already --- I don't
> think it's reasonable to expect him to check for duplications of this
> kind on top of everything else.  (And no, I don't want to drop the
> non-subscriber restriction.  The lists have been wonderfully spam-free
> lately, largely because of Marc's filters.)
>
> Anyone have an idea about an automatic solution that wouldn't take any
> extra admin time?  Maybe, when someone subscribes, automatically drop
> any pending messages from that person in the approval queue?  That'd
> eliminate this kind of duplication, but I'm not sure what the downside
> might be, or how much work it'd be to set up.  (For "drop" read "return
> to sender", anyway...)

Maybe rather do it the other way around: When someone subscribes, if there
are any pending messages from the person, they should go through
immediately, as if it was approved by Marc.

-alex

On Thu, 28 Jun 2001, Tom Lane wrote:


> Anyone have an idea about an automatic solution that wouldn't take any
> extra admin time?  Maybe, when someone subscribes, automatically drop
> any pending messages from that person in the approval queue?  That'd
> eliminate this kind of duplication, but I'm not sure what the downside
> might be, or how much work it'd be to set up.  (For "drop" read "return
> to sender", anyway...)

If the list can be subscribed to bounce the message or drop in /dev/null
with a message they must be subscribed to the list.

Call it tough love!



Rod
--
                 Remove the word 'try' from your vocabulary ...
                     Don't try.  Do it or don't do it ...
                                Steers try!

                                                            Don Aslett

Gregory Wood wrote:
> I'm usually Mr. Features, but I don't see a whole lot of value in this one.
> I've tried to come up with a scenario that this might be useful and they're
> all contrived examples that could probably be best handled by creating my
> own system rather than forcing the functionality into the existing
> mechanism. Unless a special case were made for this type of SEQUENCE, I
> imagine that this would drastically slow them down as a whole.
>
> I'd much rather see int8 SEQUENCEs than a change in increment amounts.

    I  totally  agree  on that. IMHO a second column containing a
    random number as kinda key serves alot better anyway, if  you
    just want to make it unguessable.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

> I agree it's annoying, but I'm not sure what can be done about it.
> Running the PG lists takes a lot of Marc's time already --- I don't
> think it's reasonable to expect him to check for duplications of this
> kind on top of everything else.  (And no, I don't want to drop the
> non-subscriber restriction.  The lists have been wonderfully spam-free
> lately, largely because of Marc's filters.)

If someone is subscribed to one of the lists, shouldn't they be able to
post to any list?  I think that is part of the problem because those
CC's often go to lists people don't subscribe to.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026