Обсуждение: BUG? serials and primary keys (was Re: [INTERFACES] Bug in psql?)

Grr, sorry this is so late - I'd changed MUAs and didn't notice that my
posts where BOUNCING,
not propogating.

Tome Lane writes:
> You didn't say which version you are using, but 6.5-current returns a
> more helpful error message:
> 
> ERROR:  CREATE TABLE/SERIAL implicit sequence name must be less than 32 charac
ters
>   Sum of lengths of 'globalafvigelse' and 'globalafvigelse' must be less than 27


Hmm, this is rather user unfriendly (but at least an accurate error
message.) It's also not compatible, I think, with other RDBMS that allow
'serial' types, is it? Any problem with truncating the field name? I.e.
are there are places in the code that build this sequence name,
rather than looking it up by oid or some such? Only placew I think it's
used is in the as the default for the serial field, and there what ever
gets constructed can be dropped in.  If it's not used elsewhere, we
should shorten it.

Well, at least, add it to the TODO list for testing  - see if anything
breaks if we just hack it off at 27 chars. Same goes for all the
implicit indicies, I guess.

Hmm, this raises another point: problem with serial in 6.4.2 with
MixedCase table of field names (wrapped for your email viewing
pleasure):

test=> create table "TestTable" ("Field" serial primary key, some text);
NOTICE:  CREATE TABLE will create implicit sequence TestTable_Field_seq
for SERIAL column TestTable.Field
NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index
TestTable_pkey for table TestTable
CREATE
test=> insert into "TestTable" (some) values ('test text');
ERROR:  testtable_field_seq.nextval: sequence does not exist
test=> \ds

Database    = test+------------------+----------------------------------+----------+|  Owner           |
Relation            |   Type   |+------------------+----------------------------------+----------+| reedstrm         |
TestTable_Field_seq             | sequence |+------------------+----------------------------------+----------+
 
test=> 

Anybody test this on 6.5? 

I seem to remember it being reported many weeks ago in another context -
ah yes, the problem was using a functionname as a default which had
mixed case in it. In that case, the standard quoting didn't seem to
work, either.  I think it was resolved. Anyone remember?

Ross (a.k.a. Mister MixedCase)

P.S. my mixed case mess comes from prototyping in MS-Access, and
transfering to PostgreSQL. Given the number of Access Q.s that've been
turning up, I bet we see a lot of this.

-- 
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu> 
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St.,  Houston, TX 77005

"Ross J. Reedstrom" <reedstrm@rice.edu> writes:
> Any problem with truncating the field name?

I don't need to test it to see the problem with that idea:

create table averylongtablename (averylongfieldname1 serial,averylongfieldname2 serial);

We'd need to add code to ensure uniqueness of the truncated names,
which is doable but it's not a trivial change.

Another possibility is to use user-unfriendly names for the subsidiary
objects, likepg_serial_seq_69845873
but I can't say that I like that either... it's nice to be able to
look at a sequence and know what it's for...

> Hmm, this raises another point: problem with serial in 6.4.2 with
> MixedCase table of field names (wrapped for your email viewing
> pleasure):

Yes, that was reported recently --- I believe Thomas is looking at it.
        regards, tom lane

> test=> create table "TestTable" ("Field" serial primary key, some text);
> NOTICE:  CREATE TABLE will create implicit sequence TestTable_Field_seq
> for SERIAL column TestTable.Field
> NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index
> TestTable_pkey for table TestTable
> CREATE
> test=> insert into "TestTable" (some) values ('test text');
> ERROR:  testtable_field_seq.nextval: sequence does not exist
> test=> \ds
> 
> Database    = test
>  +------------------+----------------------------------+----------+
>  |  Owner           |             Relation             |   Type   |
>  +------------------+----------------------------------+----------+
>  | reedstrm         | TestTable_Field_seq              | sequence |
>  +------------------+----------------------------------+----------+
> test=> 
> 
> Anybody test this on 6.5? 

We are working on a fix for the case thing right now.

--  Bruce Momjian                        |  http://www.op.net/~candle maillist@candle.pha.pa.us            |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026