From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Wanglin <jluwln(at)163(dot)com>, pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: using the nextval('sequence_name') in sql, the result maybe is not right |
Date: | 2018-09-26 13:35:26 |
Message-ID: | fedf7f8c-e933-20db-fed6-c6ef1f2eed95@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 9/26/18 5:05 AM, Wanglin wrote:
> Hi, all:
> PostgreSQL version : 10.3. I use "nextval" in the sql , but I
> think the result is not right, maybe it is a bug.
> *The test case as bellow:*
> create sequence seq1;
> select nextval('seq1');
> create table tx1(id1 int, id2 int);
> insert into tx1 select generate_series(1,100), random()*102;
> explain verbose select * from tx1 where id2 = nextval('seq1');;
> select * from tx1 where id2 = nextval('seq1');
> postgres=# explain verbose select * from tx1 where id2 =
> nextval('seq1');; QUERY PLAN
> ------------------------------------------------------------ Seq Scan on
> public.tx1 (cost=0.00..43.90 rows=11 width=8) Output: id1, id2 Filter:
> (tx1.id2 = nextval('seq1'::regclass)) (3 rows)
>
> postgres=# select * from tx1 where id2 = nextval('seq1'); *-- here,
> **may be the result is not right* id1 | id2 -----+----- 56 | 57 (1 row)
>
> :: I think "nextval('seq1')" equal 2, so "select * from tx1 where id2 =
> nextval('seq1')" equals "select * from tx1 where id2 = 2", is it ?
As Alban pointed out calling nextval() increments the sequence. As your
EXPLAIN shows Postgres your SELECT is doing a sequence scan. Using your
test code here I get:
select * from tx1;
id1 | id2
-----+-----
1 | 27
2 | 42
3 | 93
4 | 2
5 | 85
So going in sequence Postgres is going to compare 27 to nextval()(which
is 2), not find it move to 42 = nextval()(=3) not find it and so on.
If I do:
select * from tx1 order by id2;
I get:
id1 | id2
-----+-----
20 | 0
<values removed for clarity>
2 | 42
17 | 43
63 | 45
88 | 45
27 | 46
52 | 47
47 | 47
alter sequence seq1 restart;
select * from tx1 where id2 = nextval('seq1') order by id2;
id1 | id2
-----+-----
47 | 47
The sequence catches up with the values because there are duplicate 47
values in id2.
>
> Thanks,
> Wanglin
>
>
>
--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | greigwise | 2018-09-26 14:24:41 | Re: Out of Memory |
Previous Message | Alban Hertroys | 2018-09-26 13:18:10 | Re: using the nextval('sequence_name') in sql, the result maybe is not right |