Обсуждение: Sequence bug

Hi guys,

Sorry, but I don't have any time to look at this right now... I found a
quoting bug in the sequence dialogue that prevents modification of them
(such as resetting the value) - should be trivial to sort. I also
suspect that resetting to zero won't work correctly because we need to
use something like setval('foo', $INCREMENT, false) to do that, however
that might require some more thought.

Can someone look at this please?

Cheers, Dave

Dave Page wrote:
> Hi guys,
>
> Sorry, but I don't have any time to look at this right now... I found a
> quoting bug in the sequence dialogue that prevents modification of them
> (such as resetting the value) - should be trivial to sort. I also
> suspect that resetting to zero won't work correctly because we need to
> use something like setval('foo', $INCREMENT, false) to do that, however
> that might require some more thought.

I found two issues, but not for the same version.
ALTER SEQUENCE "TestSequenz"
    RESTART WITH 1;
is perfectly ok for 7.4+. (but changing owner had some problems).

On 7.3, setval will be used; there, quoting used qtString instead of
qtIdent. Also, I added the third setval argument as false, this seems
the least surprise for users. Not checked with 7.3, please do that Dave.

Regards,
Andreas

> -----Original Message-----
> From: Andreas Pflug [mailto:pgadmin@pse-consulting.de]
> Sent: 21 October 2004 14:28
> To: Dave Page
> Cc: PgAdmin Hackers
> Subject: Re: [pgadmin-hackers] Sequence bug
>
> Dave Page wrote:
> > Hi guys,
> >
> > Sorry, but I don't have any time to look at this right
> now... I found
> > a quoting bug in the sequence dialogue that prevents
> modification of
> > them (such as resetting the value) - should be trivial to
> sort. I also
> > suspect that resetting to zero won't work correctly because
> we need to
> > use something like setval('foo', $INCREMENT, false) to do that,
> > however that might require some more thought.
>
> I found two issues, but not for the same version.
> ALTER SEQUENCE "TestSequenz"
>     RESTART WITH 1;
> is perfectly ok for 7.4+. (but changing owner had some problems).
>
> On 7.3, setval will be used; there, quoting used qtString
> instead of qtIdent. Also, I added the third setval argument
> as false, this seems the least surprise for users. Not
> checked with 7.3, please do that Dave.

Hmm, it works OK (thanks), but not as you would expect. With setval's
third arg = false, you can set the sequence value to 1, which is
displayed as expected as the 'Current Value', but then a 'SELECT
nextval()' also returns 1 which is not what you would expect (especially
if you are not the same user, or you forgot what you did earlier). Maybe
it's better not to try to allow resetting to 0 with 7,3?

Regards, Dave.

Dave Page wrote:

>
> Hmm, it works OK (thanks), but not as you would expect. With setval's
> third arg = false, you can set the sequence value to 1, which is
> displayed as expected as the 'Current Value', but then a 'SELECT
> nextval()' also returns 1 which is not what you would expect (especially
> if you are not the same user, or you forgot what you did earlier).

But this is consistent with ALTER SEQUENCE ... RESTART ...; I just checked.

> Maybe it's better not to try to allow resetting to 0 with 7,3?

Why not? 0 sounds like a legal int value to me...

Regards,
Andreas

> -----Original Message-----
> From: Andreas Pflug [mailto:pgadmin@pse-consulting.de]
> Sent: 21 October 2004 14:54
> To: Dave Page
> Cc: PgAdmin Hackers
> Subject: Re: [pgadmin-hackers] Sequence bug
>
> Dave Page wrote:
>
> >
> > Hmm, it works OK (thanks), but not as you would expect.
> With setval's
> > third arg = false, you can set the sequence value to 1, which is
> > displayed as expected as the 'Current Value', but then a 'SELECT
> > nextval()' also returns 1 which is not what you would expect
> > (especially if you are not the same user, or you forgot
> what you did earlier).
>
> But this is consistent with ALTER SEQUENCE ... RESTART ...; I
> just checked.

Dunno, didn't check that, but it's certainly not what the novice user
might expect. I.e. Current val = 1, nextval = 1 !!

> > Maybe it's better not to try to allow resetting to 0 with 7,3?
>
> Why not? 0 sounds like a legal int value to me...

Hmm, it's not though is it, because (assuming a basic new sequence with
no odd values set), start = 1, min = 1, max = whatever. PostgreSQL will
error if you try to set it to zero. The closest thing to zero is to set
it to 1 and unset is_called.

I would say the least confusing behaviour would be to use

setval('foo', x, true);

If the user tries to set x >= minimum, or use:

setval('foo', minimum, false);

If x == (minimum - increment)

Regards, Dave

Dave Page wrote:
>
> Hmm, it's not though is it, because (assuming a basic new sequence with
> no odd values set), start = 1, min = 1, max = whatever. PostgreSQL will
> error if you try to set it to zero. The closest thing to zero is to set
> it to 1 and unset is_called.
>
> I would say the least confusing behaviour would be to use
>
> setval('foo', x, true);

I don't agree.
If you CREATE SEQUENCE foo START 100, nextval will return 100. RESTART
100 (7.4/8.0) will lead to the same behaviour, so setval('foo', 100,
false) would be the equivalent.

Actually, currval() would return just 100 too, whether is foo.isCalled
is true or false, i.e. you can't know from that which value will be
returned by nextval(), 100 or 101.
We *could* calculate this when displaying the sequence property, todo
for 1.4?

Regards,
Andreas

> -----Original Message-----
> From: Andreas Pflug [mailto:pgadmin@pse-consulting.de]
> Sent: 21 October 2004 15:26
> To: Dave Page
> Cc: PgAdmin Hackers
> Subject: Re: [pgadmin-hackers] Sequence bug
>
> Dave Page wrote:
> >
> > Hmm, it's not though is it, because (assuming a basic new sequence
> > with no odd values set), start = 1, min = 1, max = whatever.
> > PostgreSQL will error if you try to set it to zero. The
> closest thing
> > to zero is to set it to 1 and unset is_called.
> >
> > I would say the least confusing behaviour would be to use
> >
> > setval('foo', x, true);
>
> I don't agree.
> If you CREATE SEQUENCE foo START 100, nextval will return
> 100. RESTART 100 (7.4/8.0) will lead to the same behaviour,
> so setval('foo', 100,
> false) would be the equivalent.

Yes, but pgAdmin displays Current Value = 100. If I do a SELECT
nextval('foo'), then I would expect to get 101 if the current value =
100. It just seems wrong somehow...

> Actually, currval() would return just 100 too, whether is

Not in 7.3. currval() will return "ERROR:  foobar.currval is not yet
defined in this session" until you've done at least one nextval().
Following which, currval()'s behaviour will be what you would expect of
course. Maybe the thing to do is simply do the same in pga, but in a
more friendly way, rather than display what is effectively a bogus
value?

> foo.isCalled is true or false, i.e. you can't know from that
> which value will be returned by nextval(), 100 or 101.
> We *could* calculate this when displaying the sequence
> property, todo for 1.4?

Yeah, think so.

/D