Re: currval and DISCARD ALL

On 04/15/2013 02:42 PM, Nigel Heron wrote:
> Hi,
> is there a way to clear the session state of sequence values fetched by
> currval(regclass)? "DISCARD ALL" doesn't seem to do it.
>
> eg. (w/ pg 9.2.4)
> test=# CREATE SEQUENCE foo_seq;
> CREATE SEQUENCE
> test=# SELECT nextval('foo_seq');
> -[ RECORD 1 ]
> nextval | 1
>
> test=# SELECT currval('foo_seq');
> -[ RECORD 1 ]
> currval | 1
>
> test=# DISCARD ALL;
> DISCARD ALL
> test=# SELECT currval('foo_seq');
> -[ RECORD 1 ]
> currval | 1
>
>
> I'm trying to migrate a large web app to work with pgbouncer's
> transaction pool mode and it would be easier to identify issues if
> currval() would return the usual "ERROR:  currval of sequence "foo_seq"
> is not yet defined in this session" if nextval() wasn't called in the
> same pgbouncer session instead of getting old numbers from past
> transactions.

Might want to take a look at:

http://www.depesz.com/2012/12/02/what-is-the-point-of-bouncing/

for some hints on dealing with sequences and pgBouncer.

>
>
> thanks,
> -nigel.
>
>


--
Adrian Klaver
adrian.klaver@gmail.com