A few questions for new SERIAL data type:

o dropping a table including a serial data type does not drop the sequence
  corresponding to the table. This prevents re-creating the table.
  Do we have to remove the sequence by hand?

o explicit insertion to the serial column sets the value specified.
  This is good. However, next implicit insertion results in that
  previous value from the sequence + 1 is set.

  create table t (c text, i serial);
  insert into t values('a');
  insert into t values('b',100);
  insert into tvalues('c');
  select * from t;

   a   1
   b    100
   c    2

  This seems a little bit unnatural for me. may be i of the thrid row
  should be 101?

--
Tatsuo Ishii
t-ishii@sra.co.jp


Sat, 12 Sep 1998 10:23:55 +0900
t-ishii@sra.co.jp (Tatsuo Ishii)
SERIAL data type
Thus spake Tatsuo Ishii
> o explicit insertion to the serial column sets the value specified.
>   This is good. However, next implicit insertion results in that
>   previous value from the sequence + 1 is set.
> 
>   create table t (c text, i serial);
>   insert into t values('a');
>   insert into t values('b',100);
>   insert into tvalues('c');
>   select * from t;
> 
>    a   1
>    b    100
>    c    2
> 
>   This seems a little bit unnatural for me. may be i of the thrid row
>   should be 101?

Perhaps it should not be possible to specify a value for serial types.
Make it always use the next value no matter what is supplied.  Could
make dump and restore a problem of course but I can think of some
situations where it would make the programming simpler.

-- 
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 424 2871     (DoD#0082)    (eNTP)   |  what's for dinner.

Fri, 11 Sep 1998 22:14:40 -0400 (EDT)
darcy@druid.net (D'Arcy J.M. Cain)
Re: [HACKERS] SERIAL data type
t-ishii@sra.co.jp (Tatsuo Ishii) writes:
> A few questions for new SERIAL data type:
>		[...]
> o explicit insertion to the serial column sets the value specified.
>   This is good. However, next implicit insertion results in that
>   previous value from the sequence + 1 is set.
> 
>   create table t (c text, i serial);
>   insert into t values('a');
>   insert into t values('b',100);
>   insert into tvalues('c');
>   select * from t;
> 
>    a   1
>    b    100
>    c    2
> 
>   This seems a little bit unnatural for me. may be i of the thrid row
>   should be 101?

The action above is definitely 'not right'.  It is my opinion that either:

    1.  Explicit insertion into a serial column be dis-allowed.
    2.  If explicit insertion is allowed, then the sequence need to be set so
        the next implicit insertsion result in the last explicit insertion
        value + 1 being used (i.e. 101 instead of 2 in the example above).

Of these two options, I prefer #1.

Also, should we be able to specify a starting value for the sequence when the 
table is created?  For example "create table t (c text, i serial(1000));" to 
set the starting value of the sequence to 1000.
-- 
____       | Billy G. Allie    | Domain....: Bill.Allie@mug.org
|  /|      | 7436 Hartwell     | Compuserve: 76337,2061
|-/-|----- | Dearborn, MI 48126| MSN.......: B_G_Allie@email.msn.com
|/  |LLIE  | (313) 582-1540    | 



Fri, 11 Sep 1998 22:43:09 -0400
"Billy G. Allie" <Bill.Allie@mug.org>
Re: [HACKERS] SERIAL data type 
> o dropping a table including a serial data type does not drop the 
>   sequence corresponding to the table. This prevents re-creating the 
>   table. Do we have to remove the sequence by hand?

Yes, at the moment. I don't have specific plans to change/fix things for
v6.4, but might be possible. It would be OK if someone else wanted to
pick up the coding and carry it forward...

> 1.  Explicit insertion into a serial column be dis-allowed.
> 2.  If explicit insertion is allowed, then the sequence need to be set 
>     so the next implicit insertsion result in the last explicit 
>     insertion value + 1 being used
> Of these two options, I prefer #1.

I wonder if we can use the rewrite rules system to implement #1?

> Also, should we be able to specify a starting value for the sequence 
> when the table is created?  For example 
>   create table t (c text, i serial(1000));
> to set the starting value of the sequence to 1000.

There was some discussion of this and at least one person thought it was
an ugly feature (this is available on Sybase, right?). So I didn't even
try to implement it.

                       - Tom

Sat, 12 Sep 1998 06:09:41 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: [HACKERS] SERIAL data type
Hello Billy,

sabato, 12 settembre 98, you wrote:

BGA> t-ishii@sra.co.jp (Tatsuo Ishii) writes:
>> A few questions for new SERIAL data type:
>>               [...]
>> o explicit insertion to the serial column sets the value specified.
>>   This is good. However, next implicit insertion results in that
>>   previous value from the sequence + 1 is set.
>> 
>>   create table t (c text, i serial);
>>   insert into t values('a');
>>   insert into t values('b',100);
>>   insert into tvalues('c');
>>   select * from t;
>> 
>>    a   1
>>    b    100
>>    c    2
>> 
>>   This seems a little bit unnatural for me. may be i of the thrid row
>>   should be 101?

BGA> The action above is definitely 'not right'.  It is my opinion that either:

BGA>     1.  Explicit insertion into a serial column be dis-allowed.
BGA>     2.  If explicit insertion is allowed, then the sequence need to be set so
BGA>         the next implicit insertsion result in the last explicit insertion
BGA>         value + 1 being used (i.e. 101 instead of 2 in the example above).

BGA> Of these two options, I prefer #1.

BGA> Also, should we be able to specify a starting value for the sequence when the 
BGA> table is created?  For example "create table t (c text, i serial(1000));" to 
BGA> set the starting value of the sequence to 1000.

I would like to remember there's another problem with sequences.
If you increment a sequence during a transaction and then the transaction
rolls back, the sequence's original value don't be restored.

            Jose'



Tue, 15 Sep 1998 13:43:13 +0200
Sferacarta Software <sferac@bo.nettuno.it>
Re[2]: [HACKERS] SERIAL data type
SS> sabato, 12 settembre 98, you wrote:

BGA>> t-ishii@sra.co.jp (Tatsuo Ishii) writes:
>>> A few questions for new SERIAL data type:
>>>               [...]
>>> o explicit insertion to the serial column sets the value specified.
>>>   This is good. However, next implicit insertion results in that
>>>   previous value from the sequence + 1 is set.
>>> 
>>>   create table t (c text, i serial);
>>>   insert into t values('a');
>>>   insert into t values('b',100);
>>>   insert into tvalues('c');
>>>   select * from t;
>>> 
>>>    a   1
>>>    b    100
>>>    c    2
>>> 
>>>   This seems a little bit unnatural for me. may be i of the thrid row
>>>   should be 101?

BGA>> The action above is definitely 'not right'.  It is my opinion that either:

BGA>>     1.  Explicit insertion into a serial column be dis-allowed.
BGA>>     2.  If explicit insertion is allowed, then the sequence need to be set so
BGA>>         the next implicit insertsion result in the last explicit insertion
BGA>>         value + 1 being used (i.e. 101 instead of 2 in the example above).

BGA>> Of these two options, I prefer #1.

BGA>> Also, should we be able to specify a starting value for the sequence when the 
BGA>> table is created?  For example "create table t (c text, i serial(1000));" to 
BGA>> set the starting value of the sequence to 1000.

I would like to remember there's another problem with sequences.
If you increment a sequence during a transaction and then the transaction
rolls back, the sequence's original value don't be restored.

            Jose'



Wed, 16 Sep 1998 15:15:45 +0200
Sferacarta Software <sferac@bo.nettuno.it>
Re[3]: [HACKERS] SERIAL data type