Re: A problem with sequences...

On 21 Feb 2003, Doug McNaught wrote:

> Dmitry Tkach <dmitry(at)openratings(dot)com> writes:
>
> > select setval('answer_id_seq', id) from answer order by id desc limit 1;
> >
> > Now, for some reason this reports a correct value, but what actually
> > gets set is wrong!
>
> I'm guessing that 'setval' is getting called more than once here.
> Your 'LIMIT 1' controls how many rows are returned to the client, but
> the server is probably generating more rows internally. So this is

I'd totally missed that, it's probably doing a plan of seqscan + sort to
run the query.

> > select setval ('answer_id_seq', max(id)) from answer
>
> is right.

Or possibly:
select setval('answer_id_seq', id) from
(select id from answer order by id desc limit 1) as foo;

which might if the table gets big enough use an index scan.