Re: [INTERFACES] Bug in psql?

> leif@danmos.dk writes:
> >  I tried to create the table below using psql, but it bombed out
> > with a message about loosing the backend, though the backend was
> > still running nicely. It seems to be a problem with the long
> > field name of the serial (and primary key) column.
> 
> 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 characters
>         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? If not, shorten it, I say!

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 months ago in another context
- ah yes, the problem was using a functionname as a defualt 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. the 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