Обсуждение: 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