Thread: gap in sequence numbers
Hi, Please consider the following query. The reftable inner query generates a table with 11 elements, 7 of them having the groupkey 1, and the 4 others the groupkey 2. This set is then grouped by the groupkey field, and after that a generate_series and a sequence are used to create an ordered id. My question is : why do the ids created by the sequence include a gap of 1 between the two groups ? ie, why the id 5 is not present in the result set ? I know I already ran into this kind of problem, and solved it by using nextval outside of the query, but i'd like to understand the real reason of this behaviour. create sequence temp_seq2 start with 1; select nextval('temp_seq2') as id, groupkey, generate_series(1, groupnb) as rang from ( select groupkey , count(*) as groupnb from ( select id , case when id <= 7 then 1 else 2 end as groupkey from generate_series(1, 11) as id ) as reftable group by groupkey ) as foo; drop sequence if exists temp_seq2; Thanks for your help, Vincent
On Fri, Sep 18, 2009 at 10:00 AM, Picavet Vincent <Vincent.Picavet@mediapost.fr> wrote: > Hi, > Please consider the following query. > The reftable inner query generates a table with 11 elements, 7 of them > having the groupkey 1, and the 4 others the groupkey 2. This set is then > grouped by the groupkey field, and after that a generate_series and a > sequence are used to create an ordered id. > > My question is : why do the ids created by the sequence include a gap of > 1 between the two groups ? > ie, why the id 5 is not present in the result set ? > > I know I already ran into this kind of problem, and solved it by using > nextval outside of the query, but i'd like to understand the real reason > of this behaviour. It's never safe to assume that functions in the select list are executed only once per returned row. You _must_ push it into a subquery if you need protection from this. Another case is like this: SELECT pg_advisory_lock(id) FROM foo WHERE id > 12345 LIMIT 100; Superficially you might assume that you will only acquire 100 locks but you never know, because there is no guarantee that the planner will not materialize the row behind the scenes based on its own needs. I'm not completely sure what _exactly_ is causing your exact query to do that...I expect it's something to do with how the SRF is expanded in-line like that (which is also kinda fringe behavior, although I use it often!). merlin