Обсуждение: 1.8-Beta5 Bug: sequence properties dialog produces wrong sql statement order

pgAdminIII v1.8-Beta5
Windows XP
PostgreSQL 8.1, linux

I tried to adjust the Minimum value in the properties dialog of a sequence.
I wanted to adjust the range so that the present current value would
have fallen out of it, so I changed the current value too.
...In vain, because the SQL batch composed by the dialog shows that
the ALTER SEQUENCE ... MINVALUE .... statement comes first, and the
SELECT setval(....) comes only after that.
As expected, an error message popped up saying the minimum can't be
higher than the current value when I pressed OK.

Example:
Minvalue: 1 --> 50
Current value: 1--> 55
Maxvalue: 60

Correct SQL batch would look like this:
select setval('my_schema.my_seq', 55, true);
alter sequence my_seq minvalue 50;

However, what I get is this (wrong):
alter sequence my_seq minvalue 50;
select setval('my_schema.my_seq', 55, true);

Of course the general solution should be trickier than this example
shows, because of the cases like
Minvalue: 1 --> 100
Current value: 5 --> 500
Maxvalue: 10 --> 900

In this case, we need three SQL statements:
alter sequence my_seq maxvalue 900;
select setval('my_schema.my_seq', 500, true);
alter sequence my_seq minvalue 100;

I guess the general ordering of the statements should be as follows:
1 Any ALTER SEQUENCE MIN/MAXVALUE statements that widen the range
2 SETVAL
3 Any ALTER SEQUENCE MIN/MAXVALUE statements that narrow the range.

I wanted to adjust the minvalue of lots of sequences whose current
value was below the would-be minvalue, so it was a bit frustrating I
had to do each in two steps. Might be worth the effort to fix. Thanks
in advance. PgAdminIII 1.8 is great otherwise - and evolving really
quickly.

Regards,

HG

Horvath Gabor wrote:
> pgAdminIII v1.8-Beta5
> Windows XP
> PostgreSQL 8.1, linux
> 
> I tried to adjust the Minimum value in the properties dialog of a sequence.
> I wanted to adjust the range so that the present current value would
> have fallen out of it, so I changed the current value too.
> ...In vain, because the SQL batch composed by the dialog shows that
> the ALTER SEQUENCE ... MINVALUE .... statement comes first, and the
> SELECT setval(....) comes only after that.
> As expected, an error message popped up saying the minimum can't be
> higher than the current value when I pressed OK.

Thanks, fixed in SVN.

Regards, Dave

On 9/24/07, Dave Page <dpage@postgresql.org> wrote:
> Horvath Gabor wrote:
> > pgAdminIII v1.8-Beta5
> > Windows XP
> > PostgreSQL 8.1, linux
> >
> > I tried to adjust the Minimum value in the properties dialog of a sequence.
> > I wanted to adjust the range so that the present current value would
> > have fallen out of it, so I changed the current value too.
> > ...In vain, because the SQL batch composed by the dialog shows that
> > the ALTER SEQUENCE ... MINVALUE .... statement comes first, and the
> > SELECT setval(....) comes only after that.
> > As expected, an error message popped up saying the minimum can't be
> > higher than the current value when I pressed OK.
>
> Thanks, fixed in SVN.

Thanks!
I tried it in v1.8.0 RC1. Now the two statements are in reverse order
(setval first, then alter sequence), but it is still not working right
for the cases I sketched as follows:

Minvalue: 1 --> 100
Current value: 5 --> 500
Maxvalue: 10 --> 900

In this case, we need three SQL statements:
alter sequence my_seq maxvalue 900;
select setval('my_schema.my_seq', 500, true);
alter sequence my_seq minvalue 100;

I guess the general ordering of the statements should be as follows:
1 Any ALTER SEQUENCE MIN/MAXVALUE statements that widen the range
2 SETVAL
3 Any ALTER SEQUENCE MIN/MAXVALUE statements that narrow the range.

Regards,

Gábor.

Horvath Gabor a écrit :
> On 9/24/07, Dave Page <dpage@postgresql.org> wrote:
>> Horvath Gabor wrote:
>>> pgAdminIII v1.8-Beta5
>>> Windows XP
>>> PostgreSQL 8.1, linux
>>>
>>> I tried to adjust the Minimum value in the properties dialog of a sequence.
>>> I wanted to adjust the range so that the present current value would
>>> have fallen out of it, so I changed the current value too.
>>> ...In vain, because the SQL batch composed by the dialog shows that
>>> the ALTER SEQUENCE ... MINVALUE .... statement comes first, and the
>>> SELECT setval(....) comes only after that.
>>> As expected, an error message popped up saying the minimum can't be
>>> higher than the current value when I pressed OK.
>> Thanks, fixed in SVN.
> 
> Thanks!
> I tried it in v1.8.0 RC1. Now the two statements are in reverse order
> (setval first, then alter sequence), but it is still not working right
> for the cases I sketched as follows:
> 
> Minvalue: 1 --> 100
> Current value: 5 --> 500
> Maxvalue: 10 --> 900
> 
> In this case, we need three SQL statements:
> alter sequence my_seq maxvalue 900;
> select setval('my_schema.my_seq', 500, true);
> alter sequence my_seq minvalue 100;
> 
> I guess the general ordering of the statements should be as follows:
> 1 Any ALTER SEQUENCE MIN/MAXVALUE statements that widen the range
> 2 SETVAL
> 3 Any ALTER SEQUENCE MIN/MAXVALUE statements that narrow the range.
> 

We can't add this kind of functionnality now because we have a RC
release... we are in a sort of code freeze. Final 1.8 will be soon released.

Nevertheless, we can add it to our todo list.

Regards.


-- 
Guillaume.http://www.postgresqlfr.orghttp://dalibo.com

Guillaume Lelarge wrote:
>> I guess the general ordering of the statements should be as follows:
>> 1 Any ALTER SEQUENCE MIN/MAXVALUE statements that widen the range
>> 2 SETVAL
>> 3 Any ALTER SEQUENCE MIN/MAXVALUE statements that narrow the range.
>>
> 
> We can't add this kind of functionnality now because we have a RC
> release... we are in a sort of code freeze. Final 1.8 will be soon released.
> 
> Nevertheless, we can add it to our todo list.

I was thinking of it as a bug rather than a feature - and as such was
looking at fixing it atm. That said, if I find any wierd corner cases, I
might hold off for now. Noone else has complained in the last 4 years!

/D

Dave Page a écrit :
> Guillaume Lelarge wrote:
>>> I guess the general ordering of the statements should be as follows:
>>> 1 Any ALTER SEQUENCE MIN/MAXVALUE statements that widen the range
>>> 2 SETVAL
>>> 3 Any ALTER SEQUENCE MIN/MAXVALUE statements that narrow the range.
>>>
>> We can't add this kind of functionnality now because we have a RC
>> release... we are in a sort of code freeze. Final 1.8 will be soon released.
>>
>> Nevertheless, we can add it to our todo list.
> 
> I was thinking of it as a bug rather than a feature - and as such was
> looking at fixing it atm. That said, if I find any wierd corner cases, I
> might hold off for now. Noone else has complained in the last 4 years!
> 

If you add this, I think we need a RC2.


-- 
Guillaume.http://www.postgresqlfr.orghttp://dalibo.com

Horvath Gabor wrote:
> On 9/24/07, Dave Page <dpage@postgresql.org> wrote:
>> Horvath Gabor wrote:
>>> pgAdminIII v1.8-Beta5
>>> Windows XP
>>> PostgreSQL 8.1, linux
>>>
>>> I tried to adjust the Minimum value in the properties dialog of a sequence.
>>> I wanted to adjust the range so that the present current value would
>>> have fallen out of it, so I changed the current value too.
>>> ...In vain, because the SQL batch composed by the dialog shows that
>>> the ALTER SEQUENCE ... MINVALUE .... statement comes first, and the
>>> SELECT setval(....) comes only after that.
>>> As expected, an error message popped up saying the minimum can't be
>>> higher than the current value when I pressed OK.
>> Thanks, fixed in SVN.
> 
> Thanks!
> I tried it in v1.8.0 RC1. Now the two statements are in reverse order
> (setval first, then alter sequence), but it is still not working right
> for the cases I sketched as follows:
> 
> Minvalue: 1 --> 100
> Current value: 5 --> 500
> Maxvalue: 10 --> 900
> 
> In this case, we need three SQL statements:
> alter sequence my_seq maxvalue 900;
> select setval('my_schema.my_seq', 500, true);
> alter sequence my_seq minvalue 100;
> 
> I guess the general ordering of the statements should be as follows:
> 1 Any ALTER SEQUENCE MIN/MAXVALUE statements that widen the range
> 2 SETVAL
> 3 Any ALTER SEQUENCE MIN/MAXVALUE statements that narrow the range.

thanks, fixed in SVN.

Regards, Dave

Guillaume Lelarge wrote:
> Dave Page a écrit :
>> Guillaume Lelarge wrote:
>>>> I guess the general ordering of the statements should be as follows:
>>>> 1 Any ALTER SEQUENCE MIN/MAXVALUE statements that widen the range
>>>> 2 SETVAL
>>>> 3 Any ALTER SEQUENCE MIN/MAXVALUE statements that narrow the range.
>>>>
>>> We can't add this kind of functionnality now because we have a RC
>>> release... we are in a sort of code freeze. Final 1.8 will be soon released.
>>>
>>> Nevertheless, we can add it to our todo list.
>> I was thinking of it as a bug rather than a feature - and as such was
>> looking at fixing it atm. That said, if I find any wierd corner cases, I
>> might hold off for now. Noone else has complained in the last 4 years!
>>
> 
> If you add this, I think we need a RC2.

Why? It's a far more simple change than the pgAgent query was and we had
no problem with that.

I've tested what I've committed - if a couple of others can check that
there is no regression, I think thats fine.

/D

Dave Page a écrit :
> Guillaume Lelarge wrote:
>> Dave Page a écrit :
>>> Guillaume Lelarge wrote:
>>>>> I guess the general ordering of the statements should be as follows:
>>>>> 1 Any ALTER SEQUENCE MIN/MAXVALUE statements that widen the range
>>>>> 2 SETVAL
>>>>> 3 Any ALTER SEQUENCE MIN/MAXVALUE statements that narrow the range.
>>>>>
>>>> We can't add this kind of functionnality now because we have a RC
>>>> release... we are in a sort of code freeze. Final 1.8 will be soon released.
>>>>
>>>> Nevertheless, we can add it to our todo list.
>>> I was thinking of it as a bug rather than a feature - and as such was
>>> looking at fixing it atm. That said, if I find any wierd corner cases, I
>>> might hold off for now. Noone else has complained in the last 4 years!
>>>
>> If you add this, I think we need a RC2.
> 
> Why? It's a far more simple change than the pgAgent query was and we had
> no problem with that.
> 

It's not really a simple change, but simpler than what I thought at first.

> I've tested what I've committed - if a couple of others can check that
> there is no regression, I think thats fine.
> 

Seems fine to me.


-- 
Guillaume.http://www.postgresqlfr.orghttp://dalibo.com