A recent message from a would-be mysql converter led me to realize
that we don't check for array decoration when we expand "serial".
So this is accepted but doesn't do what one might expect:

regression=# create table foo (f1 serial[11]);
NOTICE: CREATE TABLE will create implicit sequence "foo_f1_seq" for serial column "foo.f1"
CREATE TABLE
regression=# \d foo
Table "public.foo"
Column | Type | Modifiers
--------+---------+--------------------------------------------------
f1 | integer | not null default nextval('foo_f1_seq'::regclass)


Should we throw an error for this? If not, what behavior would be
sane?

regards, tom lane

Search Discussions

  • Joshua D. Drake at Mar 21, 2008 at 4:59 pm

    On Fri, 21 Mar 2008 12:55:26 -0400 Tom Lane wrote:

    regression=# create table foo (f1 serial[11]);
    NOTICE: CREATE TABLE will create implicit sequence "foo_f1_seq" for
    serial column "foo.f1" CREATE TABLE
    regression=# \d foo
    Table "public.foo"
    Column | Type | Modifiers
    --------+---------+--------------------------------------------------
    f1 | integer | not null default nextval('foo_f1_seq'::regclass)


    Should we throw an error for this? If not, what behavior would be
    sane?
    Interesting? Would be to create 11 sequences that can update each
    element of the array. Sane? None. We should throw an error.

    Sincerely,

    Joshua D. Drake


    - --
    The PostgreSQL Company since 1997: http://www.commandprompt.com/
    PostgreSQL Community Conference: http://www.postgresqlconference.org/
    United States PostgreSQL Association: http://www.postgresql.us/
    Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
  • Shane Ambler at Mar 22, 2008 at 8:17 am

    Joshua D. Drake wrote:
    -----BEGIN PGP SIGNED MESSAGE-----
    Hash: SHA1

    On Fri, 21 Mar 2008 12:55:26 -0400
    Tom Lane wrote:
    regression=# create table foo (f1 serial[11]);
    NOTICE: CREATE TABLE will create implicit sequence "foo_f1_seq" for
    serial column "foo.f1" CREATE TABLE
    regression=# \d foo
    Table "public.foo"
    Column | Type | Modifiers
    --------+---------+--------------------------------------------------
    f1 | integer | not null default nextval('foo_f1_seq'::regclass)


    Should we throw an error for this? If not, what behavior would be
    sane?
    Interesting? Would be to create 11 sequences that can update each
    element of the array.
    Would you increment one element at a time? The first element in the
    first nextval, the second element in the next... or would it increment
    the first till it was 10 then the second till it was 10.... Or would you
    increment each element by one for each nextval so each element is the
    same number (use same sequence)?

    I would think the most elegant solution would be to create an
    array_sequence type. Which would open a great multitude of rule
    definitions on how to define how each element is incremented. Well
    probably a simple syntax that can end up with a complex list of rules
    saved for the sequence that could be hard to decipher later or by the
    next dba to come along.

    As much as I can see at least one use for this (think number plate
    sequences - 0-36 for each element) and some curiosity as a challenging
    project, I do think this would be better handled by functions designed
    specifically for the app that wants them.


    Hmmmm, It could be an intriguing feature, but I'm not sure it would get
    much use.

    CREATE SEQUENCE_ARRAY my_silly_seq AS
    integer[11] ON INCREMENT APPLY FROM ELEMENT 0,
    ELEMENT 0 FROM 0 TO 36 ON LIMIT INCREMENT ELEMENT 1 AND RESET TO 0,
    ELEMENT 1 FROM 0 TO 9 ON LIMIT INCREMENT ELEMENT 2 AND RESET TO 0,
    ...

    Could there be char[] array that can increment from 0-9 then a-z before
    rolling back to 0?

    Guess I got too much time on my hands... I'll go find something better
    to do now. ;-)

    Sane? None. We should throw an error.
    +1 for the error



    --

    Shane Ambler
    pgSQL (at) Sheeky (dot) Biz

    Get Sheeky @ http://Sheeky.Biz
  • Gurjeet Singh at Mar 21, 2008 at 5:05 pm

    On Fri, Mar 21, 2008 at 10:25 PM, Tom Lane wrote:

    A recent message from a would-be mysql converter led me to realize
    that we don't check for array decoration when we expand "serial".
    So this is accepted but doesn't do what one might expect:

    regression=# create table foo (f1 serial[11]);
    NOTICE: CREATE TABLE will create implicit sequence "foo_f1_seq" for
    serial column "foo.f1"
    CREATE TABLE
    regression=# \d foo
    Table "public.foo"
    Column | Type | Modifiers
    --------+---------+--------------------------------------------------
    f1 | integer | not null default nextval('foo_f1_seq'::regclass)


    Should we throw an error for this?

    +1

    If not, what behavior would be
    sane?
    I don't see any sane explanation if we do provide that syntax!!!

    Best regards,
    --
    gurjeet[.singh]@EnterpriseDB.com
    singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com

    EnterpriseDB http://www.enterprisedb.com

    17° 29' 34.37"N, 78° 30' 59.76"E - Hyderabad *
    18° 32' 57.25"N, 73° 56' 25.42"E - Pune
    37° 47' 19.72"N, 122° 24' 1.69" W - San Francisco

    http://gurjeet.frihost.net

    Mail sent from my BlackLaptop device

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedMar 21, '08 at 4:55p
activeMar 22, '08 at 8:17a
posts4
users4
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase