Thread

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

Justin Clift <aa2@bigpond.net.au> — 2001-06-22T02:10:51Z

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

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

Shaun Thomas <sthomas@townnews.com> — 2001-06-28T12:27:54Z

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              |
+-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+

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

Gregory Wood <gregw@com-stock.com> — 2001-06-28T14:07:02Z

> 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

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

alex@pilosoft.com — 2001-06-28T14:51:42Z

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.

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

Lincoln Yeoh <lyeoh@pop.jaring.my> — 2001-06-28T16:01:44Z

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.

Repeated messages (was Re: Suggested improvement : Adjust SEQUENCES)

Tom Lane <tgl@sss.pgh.pa.us> — 2001-06-28T16:19:17Z

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

Re: Repeated messages (was Re: Suggested improvement : Adjust SEQUENCES)

alex@pilosoft.com — 2001-06-28T16:49:29Z

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

Re: Repeated messages (was Re: Suggested improvement : Adjust SEQUENCES)

Bruce Momjian <pgman@candle.pha.pa.us> — 2001-06-28T17:12:17Z

> 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

Re: Repeated messages (was Re: Suggested improvement : Adjust SEQUENCES)

alex@pilosoft.com — 2001-06-28T17:26:44Z

On Thu, 28 Jun 2001, Bruce Momjian wrote:

> > 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.
I believe its the way it currently is because hub.org has multiple mailing
lists, and pgsql-related ones are only a subset. Its a normal majordomo
behaviour to have access-control per list. 

Majordomo is a pain in the rear to modify, from what I reckon. It'd be
nice if all pgsql-* lists shared one allowed-poster-list, but not sure if
its easy to do.

Re: Repeated messages (was Re: Suggested improvement : Adjust SEQUENCES)

Roderick A. Anderson <raanders@tincan.org> — 2001-06-28T17:50:04Z

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

Re: Repeated messages (was Re: Suggested improvement : Adjust SEQUENCES)

Bruce Momjian <pgman@candle.pha.pa.us> — 2001-06-28T18:00:03Z

> I believe its the way it currently is because hub.org has multiple mailing
> lists, and pgsql-related ones are only a subset. Its a normal majordomo
> behaviour to have access-control per list. 
> 
> Majordomo is a pain in the rear to modify, from what I reckon. It'd be
> nice if all pgsql-* lists shared one allowed-poster-list, but not sure if
> its easy to do.

We do have a loopback address that allows posting to any of our lists so
I assumed that could be used somehow.

-- 
  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

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

Jan Wieck <janwieck@yahoo.com> — 2001-06-28T18:17:50Z

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

Re: Repeated messages (was Re: Suggested improvement : Adjust SEQUENCES)

Patrick Welche <prlw1@newn.cam.ac.uk> — 2001-06-28T18:22:49Z

On Thu, Jun 28, 2001 at 02:00:03PM -0400, Bruce Momjian wrote:
> > I believe its the way it currently is because hub.org has multiple mailing
> > lists, and pgsql-related ones are only a subset. Its a normal majordomo
> > behaviour to have access-control per list. 
> > 
> > Majordomo is a pain in the rear to modify, from what I reckon. It'd be
> > nice if all pgsql-* lists shared one allowed-poster-list, but not sure if
> > its easy to do.
> 
> We do have a loopback address that allows posting to any of our lists so
> I assumed that could be used somehow.

Does it still exist? I'm sure I tried pgsql-loopback and nothing happened..

Patrick

Re: Repeated messages (was Re: Suggested improvement : Adjust SEQUENCES)

Vince Vielhaber <vev@michvhf.com> — 2001-06-28T19:00:39Z

On Thu, 28 Jun 2001, Bruce Momjian wrote:

> > I believe its the way it currently is because hub.org has multiple mailing
> > lists, and pgsql-related ones are only a subset. Its a normal majordomo
> > behaviour to have access-control per list.
> >
> > Majordomo is a pain in the rear to modify, from what I reckon. It'd be
> > nice if all pgsql-* lists shared one allowed-poster-list, but not sure if
> > its easy to do.
>
> We do have a loopback address that allows posting to any of our lists so
> I assumed that could be used somehow.

You're referring to loophole and Marc dropped that when he upgraded to
majordomo2 and refuses to bring it back.

Vince.
-- 
==========================================================================
Vince Vielhaber -- KA8CSH    email: vev@michvhf.com    http://www.pop4.net
         56K Nationwide Dialup from $16.00/mo at Pop4 Networking
        Online Campground Directory    http://www.camping-usa.com
       Online Giftshop Superstore    http://www.cloudninegifts.com
==========================================================================

Re: Repeated messages (was Re: Suggested improvement : Adjust SEQUENCES)

Bruce Momjian <pgman@candle.pha.pa.us> — 2001-06-28T19:44:00Z

> > We do have a loopback address that allows posting to any of our lists so
> > I assumed that could be used somehow.
> 
> You're referring to loophole and Marc dropped that when he upgraded to
> majordomo2 and refuses to bring it back.

Oh, I see.  Nevermind.

-- 
  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