Обсуждение: currval and DISCARD ALL

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.


thanks,
-nigel.

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

On 04/15/2013 05:57 PM, Adrian Klaver wrote:
> 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.
>>
<snip>
> 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, I read it (his blogs are always interesting!). I'm not disputing
that calling currval() at the wrong time is a bad idea.
I'm just wondering why DISCARD ALL clears everything but this?
from the docs:
"DISCARD ALL resets a session to its original state, discarding
temporary resources and resetting session-local configuration changes."
.. but at the beginning of a session currval(foo) would return an error,
whereas calling nexval(foo); DISCARD ALL; currval(foo); does not return
an error.. clearly something isn't getting reset to the original state.

If you create a TEMP sequence, then DISCARD ALL does clear the state,
probably because the underlying table disappears.

-nigel.

On 04/16/2013 08:07 AM, Nigel Heron wrote:
>
> On 04/15/2013 05:57 PM, Adrian Klaver wrote:
>> 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.
>>>
> <snip>
>> 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, I read it (his blogs are always interesting!). I'm not disputing
> that calling currval() at the wrong time is a bad idea.
> I'm just wondering why DISCARD ALL clears everything but this?

Well per the docs:

http://www.postgresql.org/docs/9.2/interactive/sql-discard.html

DISCARD ALL

is equivalent to:

SET SESSION AUTHORIZATION DEFAULT;
RESET ALL;
DEALLOCATE ALL;
CLOSE ALL;
UNLISTEN *;
SELECT pg_advisory_unlock_all();
DISCARD PLANS;
DISCARD TEMP;

AFAIK, none of the above affect sequences.


> -nigel.
>


--
Adrian Klaver
adrian.klaver@gmail.com