Обсуждение: [INTERFACES] Bug in psql?
Hello,
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.
create table globalafvigelse
(
globalafvigelse serial ,
startdato date,
slutdato date,
dagskema varchar(50),
primary key (globalafvigelse)
);
Greetings,
Leif
(leif@danmos.dk)
> 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. Have you tried putting the following into a file and using... # psql -f foo.sql ... to run it? I _think_ this sometimes gives different messages than entering it directly on the psql command line, although I'm not sure why. I could be way off here, but its worth a try. > create table globalafvigelse > ( > globalafvigelse serial , > startdato date, > slutdato date, > dagskema varchar(50), > primary key (globalafvigelse) > ); What is type "serial"? It probably not incorrect, but I've not heard of it before. Bruce Tong | Got me an office; I'm there late at night. Systems Programmer | Just send me e-mail, maybe I'll write. Electronic Vision / FITNE | zztong@laxmi.ev.net | -- Joe Walsh for the 21st Century
Hello Bruce,
On: Tue, 11 May 1999 08:50:07 -0400 (EDT)
Bruce Tong <zztong@laxmi.ev.net> wrote:
> > 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.
>
>
> Have you tried putting the following into a file and using...
>
> # psql -f foo.sql
I actually did from a file, but with the \i command.
>
> ... to run it? I _think_ this sometimes gives different messages than
> entering it directly on the psql command line, although I'm not sure why.
> I could be way off here, but its worth a try.
>
> > create table globalafvigelse
> > (
> > globalafvigelse serial ,
> > startdato date,
> > slutdato date,
> > dagskema varchar(50),
> > primary key (globalafvigelse)
> > );
>
> What is type "serial"? It probably not incorrect, but I've not heard of it
> before.
>
The data type serial is a standard data type, which is also included in
PostgreSQL ;-). M$Access calls it 'Autonumber' and is simply an integer (I
think it is most common to be an int4) that is automatically incremented for
each insert. I think that it is implemented in PostgreSQL using a sequenser.
I tried to change the name of the serial field: globalafvigelse -> id,
and then it works. I think it is because postgresql automatically creates a
sequence called <table name>_<field name>_seq, which is limited in size.
Greetings,
Leif
> > > 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.
> I tried to change the name of the serial field: globalafvigelse -> id,
> and then it works. I think it is because postgresql automatically creates a
> sequence called <table name>_<field name>_seq, which is limited in size.
>From the current v6.5 prerelease:
postgres=> create table globalafvigelse
postgres-> (globalafvigelse serial ,
postgres-> startdato date,
postgres-> slutdato date,
postgres-> dagskema varchar(50),
postgres-> primary key (globalafvigelse)
postgres-> );
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
Sorry, the thread subject didn't catch my attention. Older code didn't
check length, as you surmised.
- Tom
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California
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
This is forced by the naming conventions for the underlying sequence and
index objects, which look like "TABLE_FIELD_seq" and so forth.
regards, tom lane
<tt>The error is about name lenght. Max. 31 char.</tt><br /><tt>SERIAL types automatically creates an index name
like:</tt><br/><tt> globalafvigelse_globalafviggelse_key</tt><br /><tt>which is longer than 31 char.</tt><br
/><tt>Youhave to cut the table/key name to fit into 26 char (the sum of both).</tt><br /><tt>take a look:</tt><br
/><tt></tt> <tt></tt><p><tt>$psql prova < 1</tt><br /><tt> create table globalafvigelse</tt><br /><tt> (</tt><br
/><tt> globalafvigelse serial ,</tt><br /><tt> startdato date,</tt><br /><tt> slutdato date,</tt><br
/><tt> dagskema varchar(50),</tt><br /><tt> primary key (globalafvigelse)</tt><br /><tt> );</tt><br
/><tt>ERROR: CREATE TABLE/SERIAL implicit sequence name must be less than 32 characte</tt><br /><tt>rs</tt><br
/><tt> Sum of lengths of 'globalafvigelse' and 'globalafvigelse' must be less t</tt><br /><tt>han
27</tt><tt></tt><p><tt>createtable globalafvigelse</tt><br /><tt> (</tt><br /><tt> globalafvig serial ,</tt><br
/><tt> startdato date,</tt><br /><tt> slutdato date,</tt><br /><tt> dagskema varchar(50),</tt><br /><tt>
primarykey (globalafvig)</tt><br /><tt> );</tt><br /><tt>NOTICE: CREATE TABLE will create implicit sequence
globalafvigelse_globalafvig_</tt><br/><tt>seq for SERIAL column globalafvigelse.globalafvig</tt><br /><tt>NOTICE:
CREATETABLE/UNIQUE will create implicit index globalafvigelse_globalaf</tt><br /><tt>vig_key for table
globalafvigelse</tt><br/><tt>NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index globalafvigelse_pke</tt><br
/><tt>yfor table globalafvigelse</tt><br /><tt>CREATE</tt><br /><tt>EOF</tt><tt></tt><p><tt>$ psql -c '\d
globalafvigelse'</tt><br/><tt>Table = globalafvigelse</tt><br
/><tt>+----------------------------------+----------------------------------+-------+</tt><br/><tt>|
Field | Type | Length|</tt><br
/><tt>+----------------------------------+----------------------------------+-------+</tt><br/><tt>|
globalafvig | int4 not null default nextval('g | 4 |</tt><br /><tt>|
startdato | date | 4 |</tt><br /><tt>|
slutdato | date | 4 |</tt><br /><tt>|
dagskema | varchar() | 50 |</tt><br
/><tt>+----------------------------------+----------------------------------+-------+</tt><br/><tt>Indices:
globalafvigelse_globalafvig_key</tt><br/><tt> globalafvigelse_pkey</tt><br /><tt></tt> <p>Jose' <br />
<p>BruceTong ha scritto: <blockquote type="CITE">> I tried to create the table below using psql, but it bombed out
<br/>> with a message about loosing the backend, though the backend was <br />> still running nicely. It seems to
bea problem with the long <br />> field name of the serial (and primary key) column. <p>Have you tried putting the
followinginto a file and using... <p># psql -f foo.sql <p>... to run it? I _think_ this sometimes gives different
messagesthan <br />entering it directly on the psql command line, although I'm not sure why. <br />I could be way off
here,but its worth a try. <p>> create table globalafvigelse <br />> ( <br />> globalafvigelse serial ,
<br/>> startdato date, <br />> slutdato date, <br />> dagskema varchar(50), <br />> primary
key(globalafvigelse) <br />> ); <p>What is type "serial"? It probably not incorrect, but I've not heard of it <br
/>before.<p>Bruce Tong | Got me an office; I'm there late at night. <br />Systems Programmer
| Just send me e-mail, maybe I'll write. <br />Electronic Vision / FITNE | <br />zztong@laxmi.ev.net | -- Joe
Walshfor the 21st Century</blockquote><p>-- <br />______________________________________________________________ <br
/>PostgreSQL6.5.0 on i586-pc-linux-gnu, compiled by gcc 2.7.2.3 <br
/>^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^<br />Jose' <br />
leif@danmos.dk ha scritto: > Hello Bruce, > > On: Tue, 11 May 1999 08:50:07 -0400 (EDT) > Bruce Tong <zztong@laxmi.ev.net> wrote: > > > > 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. > > > > > > Have you tried putting the following into a file and using... > > > > # psql -f foo.sql > > I actually did from a file, but with the \i command. > > > > > ... to run it? I _think_ this sometimes gives different messages than > > entering it directly on the psql command line, although I'm not sure why. > > I could be way off here, but its worth a try. > > > > > create table globalafvigelse > > > ( > > > globalafvigelse serial , > > > startdato date, > > > slutdato date, > > > dagskema varchar(50), > > > primary key (globalafvigelse) > > > ); > > > > What is type "serial"? It probably not incorrect, but I've not heard of it > > before. > > > The data type serial is a standard data type, which is also included in > SERIAL is a very usefull data type but it is not standard. :) > PostgreSQL ;-). M$Access calls it 'Autonumber' and is simply an integer (I > think it is most common to be an int4) that is automatically incremented for > each insert. I think that it is implemented in PostgreSQL using a sequenser. > ______________________________________________________________ PostgreSQL 6.5.0 on i586-pc-linux-gnu, compiled by gcc 2.7.2.3 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ Jose'
> 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
I have a (newbie) question on pgaccess. Have installed postgresql-6.4.2
from source on Linux RH5.1 (with some updates). Everything seems ok,
psql runs fine.
When I use pgaccess, it works and connects, also from another user.
However, this is the RH pgaccess, 0.86.
I have also available pgaccess from source (0.96), but this version
opens the DB ("regression", I can see the tables), gives an error
message and, after clicking the "OK" on the error message window,
quits. The error message in the window reads:
" Tcl error executing pg_exec select relname from pg_class where relname='pga_queries'
Invalid optionpg_result result ?option? where ?option is -status -conn -assign arrayVarName
-assignbyidx arrayVarName -numTuples -attributes -lAttributes -numAttrs -getTuple
tupleNumber -tupleArray tupleNumber arrayVarName -clear -oid
"
After the OK, I get also some more error messages on the screen:
Error in startup script: First argument is not a valid query result while executing
"pg_result $pgres -numTuples" (procedure "open_database" line 36) invoked from within
"open_database" (procedure "main" line 21) invoked from within
"main $argc $argv" (file "pgaccess.tcl" line 5044)
Can it be that my 'wish' is too old (seems I have wish8.0)?
Anyone had similar problems?
Thanks
Charles Stroom
email: charles@stroom-schreurs.demon.nl
url: http://www.stroom-schreurs.demon.nl/
Hi again,
Thanks to all of you who responded to this one. It was a great
help. I think I got the picture: don't let the length of <table name>
plus <field name> exceed 26/27 characters for the serial type. I guess
this goes for primary key as well.
Earlier, I wrote:
> 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.
I am using v6.4.2. I am glad to learn it has been fixed (with
an error message in stead of crashing.)
>
> create table globalafvigelse
> (
> globalafvigelse serial ,
> startdato date,
> slutdato date,
> dagskema varchar(50),
> primary key (globalafvigelse)
> );
Greetings,
Leif
(leif@danmos.dk)