Thread: Resetting a sequence's last value...

I have a database application that makes extensive use of currval(),
but after a certain point in the transaction, I'd like to reset the
state of a given sequence so that the backend will act as if the
sequence hasn't been called.  Something like resetval() or the like is
what I'm looking for, but it doesn't exist as far as I can tell.
Other than reconnecting to the backend, are there other programmatic
alternatives?  -sc

--
Sean Chittenden

On Sunday 27 April 2003 13:06, Sean Chittenden wrote:
> I have a database application that makes extensive use of currval(),
> but after a certain point in the transaction, I'd like to reset the
> state of a given sequence so that the backend will act as if the
> sequence hasn't been called.  Something like resetval() or the like is
> what I'm looking for, but it doesn't exist as far as I can tell.
> Other than reconnecting to the backend, are there other programmatic
> alternatives?  -sc

Umm.. setval doesn't work?

file:/usr/local/doc/postgresql/html/functions-sequence.html is where I looked
at my local 7.3.2 installation..

 Shridhar

> > I have a database application that makes extensive use of
> > currval(), but after a certain point in the transaction, I'd like
> > to reset the state of a given sequence so that the backend will
> > act as if the sequence hasn't been called.  Something like
> > resetval() or the like is what I'm looking for, but it doesn't
> > exist as far as I can tell.  Other than reconnecting to the
> > backend, are there other programmatic alternatives?  -sc
>
> Umm.. setval doesn't work?
>
> file:/usr/local/doc/postgresql/html/functions-sequence.html is where I looked
> at my local 7.3.2 installation..

That won't work because it'll return the last value.  In this case,
I'm looking to have things return an error if someone calls currval()
on the sequence.  -sc

--
Sean Chittenden

Sean Chittenden <sean@chittenden.org> writes:
> I'm looking to have things return an error if someone calls currval()
> on the sequence.  -sc

There isn't any mechanism for that, although I imagine it wouldn't be
hard to add such a function to commands/sequence.c.  But the requirement
seems a bit bizarre.  Maybe there's a better way to do whatever you're
after?

            regards, tom lane

> > I'm looking to have things return an error if someone calls
> > currval() on the sequence.  -sc
>
> There isn't any mechanism for that, although I imagine it wouldn't
> be hard to add such a function to commands/sequence.c.  But the
> requirement seems a bit bizarre.  Maybe there's a better way to do
> whatever you're after?

Controlling/policing the source tree is the other option.  Here's a
quick description:

I have a transaction table that I use to record meta data about the
connection (current user, ip that they're connecting from, time,
etc.).  The transaction ID (txn_id) returned from the start_txn()
function calls nextval() on the txn_id_seq.  Every subsequent table
calls currval() on that particular sequence, however after certain
policy based places in the transaction, I need the caller to start a
new transaction.  If a programmer lapses to call start_txn() again,
then the audit trail gets really fuzzy and it becomes impossible to
differentiate a 1st transaction from a 2nd transaction.  As opposed to
policing the source tree for possible errors, I'd like a technological
solution that will allow me to call, "invalidate_currval('txn_id')" or
some such that way the next time someone tries to call
currval('txn_id'), they'll get a nice big fat juicy error that'll
abort the remainder of their transaction.

Thoughts?  I got a post offlist suggesting writing a C function, but
that's pretty dependent on the backend and would have to be recompiled
from version to version, something I'm not too wild about.  -sc

--
Sean Chittenden

On Sun, Apr 27, 2003 at 12:39:37 -0700,
  Sean Chittenden <sean@chittenden.org> wrote:
>
> Thoughts?  I got a post offlist suggesting writing a C function, but
> that's pretty dependent on the backend and would have to be recompiled
> from version to version, something I'm not too wild about.  -sc

What would seem logical to me is using the iscalled parameter to setval
to determine this. If you call setval('seq', 1, false), I think it
would be reasonable for the currval('seq') to be undefined until
nextval('seq') or setval with iscalled=true is called.

Bruno Wolff III <bruno@wolff.to> writes:
>   Sean Chittenden <sean@chittenden.org> wrote:
>> Thoughts?  I got a post offlist suggesting writing a C function, but
>> that's pretty dependent on the backend and would have to be recompiled
>> from version to version, something I'm not too wild about.  -sc

> What would seem logical to me is using the iscalled parameter to setval
> to determine this. If you call setval('seq', 1, false), I think it
> would be reasonable for the currval('seq') to be undefined until
> nextval('seq') or setval with iscalled=true is called.

I don't believe he wants to do a setval, as the effects of that are
global.

            regards, tom lane

> > Thoughts?  I got a post offlist suggesting writing a C function,
> > but that's pretty dependent on the backend and would have to be
> > recompiled from version to version, something I'm not too wild
> > about.  -sc
>
> What would seem logical to me is using the iscalled parameter to
> setval to determine this. If you call setval('seq', 1, false), I
> think it would be reasonable for the currval('seq') to be undefined
> until nextval('seq') or setval with iscalled=true is called.

setval:
    Reset the sequence object's counter value. The two-parameter
    form sets the sequence's last_value field to the specified
    value and sets its is_called field to true, meaning that the
    next nextval will advance the sequence before returning a
    value. In the three-parameter form, is_called may be set
    either true or false. If it's set to false, the next nextval
    will return exactly the specified value, and sequence
    advancement commences with the following nextval.

That gives me an idea for the API, however.  If the 2nd parameter is
NULL, it could reset is_called without changing the value.  In all
situations, I must maintain that a transaction ID is unique to the
entire database and must always be the case.

SELECT setval('txn_id_seq', NULL);

Hrm...  I may look into this later this week if something interesting
doesn't pop up or someone else doesn't beat me to the punch.  -sc

--
Sean Chittenden