Re: Sequences in transaction - Mailing list pgsql-general

Greetings, and thank you for your insightful reply.

I've been reading the faq's on this, and am trying to figure out the
best way to do this within the design of postgresql.  Problem is, I
need to be able to *quickly* select a pair of *adjacent* rows in a
table.  t2.seq = t1.seq + 1 seems to work pretty well.  Of course, I
could instead add a 'backward seq value' column to the table, and add
in an extra merge.  In other words, my query currently looks like

select dates.asof,t1.a + t2.b from dates, data t1, data t2
    where t1.seq = dates.seq
    and t1.id = t2.id
    and t2.seq = t1.seq + 1

seq being a primary key in dates, and a foreign key in data.  So is
this better:

select dates.asof,t1.a + t2.b from dates, data t1, data t2
    where t1.seq = dates.seq
    and t1.id = t2.id
    and t2.seq = dates.nseq

and add a trigger to dates to update nseq on insert,update and delete?

Thanks!

Mike Castle <dalgoda@ix.netcom.com> writes:

> On Mon, Dec 04, 2000 at 05:56:04PM -0500, Camm Maguire wrote:
> > Greetings!  I've run into this too, and it appears to me not to roll
> > back either.  A pity, as it forced us to make a separate table to hold
> > the last sequence value and update the table in a trigger.
>
> This is a dangerous thing if you ever have more than one update going on.
>
> One transaction reads the value, increments it, saves it back.  Another
> transaction does the same thing.  Now you have two transactions trying to
> use the same value.  Sure, one will probably fail a uniqueness constraint,
> but then you have to increment again.  Meanwhile, another transaction comes
> in and steals the next number, and one of the first two clashes again.  And
> again.  And again.  And again.  You have NO way of guaranteeing that
> starvation will never be an issue.
>
> Solution:
>
> Don't require that your sequence values be absolutely sequential.  They're
> there to ensure uniquness and order.  Not to be used as counters.
>
> mrc
> --
>        Mike Castle       Life is like a clock:  You can work constantly
>   dalgoda@ix.netcom.com  and be right all the time, or not work at all
> www.netcom.com/~dalgoda/ and be right at least twice a day.  -- mrc
>     We are all of us living in the shadow of Manhattan.  -- Watchmen
>
>

--
Camm Maguire                             camm@enhanced.com
==========================================================================
"The earth is but one country, and mankind its citizens."  --  Baha'u'llah