views/stored procedures

Hi,

Help me please to understand such a thing:
Rollback of sequences doesn't work in transaction.
Is it right? Or may be there is another way to restore
it's value?
Running PG 7.02 on RH 6.0

Thanks ,
Igor.

Sequences are not supposed to be undoable. A number in the sequence must
"never" turn up twice. Just because you didn't commit the transaction
selecting a new number from the sequence doesn't mean you didn't use it for
something else.

The point being to always getting a unique number, not using every number in
the sequence. You would have to create your own table for that, remembering
"holes" of unused numbers in the sequence for later usage.

/Roger

-----Original Message-----
From: igor [mailto:igor_kh@mailru.com]
Sent: Monday, November 13, 2000 12:18 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Sequences in transaction

Hi,

Help me please to understand such a thing:
Rollback of sequences doesn't work in transaction.
Is it right? Or may be there is another way to restore
it's value?
Running PG 7.02 on RH 6.0

Thanks ,
Igor.

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.

Take care,

igor <igor_kh@mailru.com> writes:

Hi,

Help me please to understand such a thing:
Rollback of sequences doesn't work in transaction.
Is it right? Or may be there is another way to restore
it's value?
Running PG 7.02 on RH 6.0

Thanks ,
Igor.

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

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

Not even to ensure order. Some databases allow sequence caching (something
we should perhaps look at, if we don't already) which means that when a
session requests it's first number from a sequence a whole bundle are
actually given to the session. It may not even use them all, and certainly,
if another session is also allocated a bundle of sequence numbers from the
same sequence, then different transactions could commit them in any
combination of orders. However, the principle of uniqueness will not be
violated.

Cheers...

MikeA

-----Original Message-----
From: Mike Castle [mailto:dalgoda@ix.netcom.com]
Sent: 04 December 2000 23:30
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Sequences in transaction

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

**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
Nick West - Global Infrastructure Manager.

This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.

www.mimesweeper.com
**********************************************************************

On Tue, Dec 05, 2000 at 09:58:13AM -0000, Michael Ansley wrote:

Not even to ensure order. Some databases allow sequence caching (something

Doh! Of course. My bad.

Actually, I believe pgsql DOES allow for that. At least I remember reading
about it, but the details escape me.

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

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

On Tue, Dec 05, 2000 at 12:03:40PM -0500, Camm Maguire wrote:

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

What's wrong with a select ... order by .. limit 2 ?

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

Greetings! I've just found .. nothing! This works pretty well to my
surprise. Thanks so much for the suggestion. I did a little rewrite
which builds a doubly-linked list table of dates, with prior date and
next date columns maintained by triggers. I then retrieve adjacent
pairs of data table rows via a merge with this table. This appears to
be faster than issuing a subselect .... order by ... limit 1 for
each data row, but your key idea (to me, at least) is that I can avoid
sequential sequence numbers by making explicit reference to the order
of the date values themselves.

Thanks again!

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

On Tue, Dec 05, 2000 at 12:03:40PM -0500, Camm Maguire wrote:

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

What's wrong with a select ... order by .. limit 2 ?

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