pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feed[INTERFACES] Bug in psql?
10+ messages / 7 participants
[nested] [flat]
* [INTERFACES] Bug in psql?
@ 1999-05-10 23:44 leif@danmos.dk
1999-05-11 07:16 ` Re: [INTERFACES] Bug in psql? Charles Stroom <charles@stroom-schreurs.demon.nl>
1999-05-11 12:50 ` Re: [INTERFACES] Bug in psql? Bruce Tong <zztong@laxmi.ev.net>
1999-05-11 14:01 ` Re: [INTERFACES] Bug in psql? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 3 replies; 10+ messages in thread
From: leif@danmos.dk @ 1999-05-10 23:44 UTC (permalink / raw)
To: pgsql-interfaces
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)
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [INTERFACES] Bug in psql?
1999-05-10 23:44 [INTERFACES] Bug in psql? leif@danmos.dk
@ 1999-05-11 07:16 ` Charles Stroom <charles@stroom-schreurs.demon.nl>
2 siblings, 0 replies; 10+ messages in thread
From: Charles Stroom @ 1999-05-11 07:16 UTC (permalink / raw)
To: pgsql-interfaces
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/
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [INTERFACES] Bug in psql?
1999-05-10 23:44 [INTERFACES] Bug in psql? leif@danmos.dk
@ 1999-05-11 12:50 ` Bruce Tong <zztong@laxmi.ev.net>
1999-05-11 13:32 ` Re: [INTERFACES] Bug in psql? José Soares <jose@sferacarta.com>
2 siblings, 1 reply; 10+ messages in thread
From: Bruce Tong @ 1999-05-11 12:50 UTC (permalink / raw)
To: leif@danmos.dk; +Cc: pgsql-interfaces
> 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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [INTERFACES] Bug in psql?
1999-05-10 23:44 [INTERFACES] Bug in psql? leif@danmos.dk
1999-05-11 12:50 ` Re: [INTERFACES] Bug in psql? Bruce Tong <zztong@laxmi.ev.net>
@ 1999-05-11 13:32 ` José Soares <jose@sferacarta.com>
0 siblings, 0 replies; 10+ messages in thread
From: José Soares @ 1999-05-11 13:32 UTC (permalink / raw)
To: Bruce Tong <zztong@laxmi.ev.net>; +Cc: leif@danmos.dk; pgsql-interfaces
The error is about name lenght. Max. 31 char.
SERIAL types automatically creates an index name like:
globalafvigelse_globalafviggelse_key
which is longer than 31 char.
You have to cut the table/key name to fit into 26 char (the sum of both).
take a look:
$ psql prova < 1
create table globalafvigelse
(
globalafvigelse serial ,
startdato date,
slutdato date,
dagskema varchar(50),
primary key (globalafvigelse)
);
ERROR: CREATE TABLE/SERIAL implicit sequence name must be less than 32
characte
rs
Sum of lengths of 'globalafvigelse' and 'globalafvigelse' must be less
t
han 27
create table globalafvigelse
(
globalafvig serial ,
startdato date,
slutdato date,
dagskema varchar(50),
primary key (globalafvig)
);
NOTICE: CREATE TABLE will create implicit sequence
globalafvigelse_globalafvig_
seq for SERIAL column globalafvigelse.globalafvig
NOTICE: CREATE TABLE/UNIQUE will create implicit index
globalafvigelse_globalaf
vig_key for table globalafvigelse
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index
globalafvigelse_pke
y for table globalafvigelse
CREATE
EOF
$ psql -c '\d globalafvigelse'
Table = globalafvigelse
+----------------------------------+----------------------------------+-------+
| Field | Type |
Length|
+----------------------------------+----------------------------------+-------+
| globalafvig | int4 not null default nextval('g | 4
|
| startdato | date | 4
|
| slutdato | date | 4
|
| dagskema | varchar() | 50
|
+----------------------------------+----------------------------------+-------+
Indices: globalafvigelse_globalafvig_key
globalafvigelse_pkey
Jose'
Bruce Tong ha scritto:
> > 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
--
______________________________________________________________
PostgreSQL 6.5.0 on i586-pc-linux-gnu, compiled by gcc 2.7.2.3
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Jose'
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [INTERFACES] Bug in psql?
1999-05-10 23:44 [INTERFACES] Bug in psql? leif@danmos.dk
@ 1999-05-11 14:01 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-05-11 16:52 ` Re: [INTERFACES] Bug in psql? Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>
2 siblings, 1 reply; 10+ messages in thread
From: Tom Lane @ 1999-05-11 14:01 UTC (permalink / raw)
To: leif@danmos.dk; +Cc: pgsql-interfaces
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [INTERFACES] Bug in psql?
1999-05-10 23:44 [INTERFACES] Bug in psql? leif@danmos.dk
1999-05-11 14:01 ` Re: [INTERFACES] Bug in psql? Tom Lane <tgl@sss.pgh.pa.us>
@ 1999-05-11 16:52 ` Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>
0 siblings, 0 replies; 10+ messages in thread
From: reedstrm@wallace.ece.rice.edu @ 1999-05-11 16:52 UTC (permalink / raw)
To: pgsql-interfaces; pgsql-hackers@postgreSQL.org
> 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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [INTERFACES] Bug in psql?
@ 1999-05-11 13:21 leif@danmos.dk
1999-05-11 14:01 ` Re: [INTERFACES] Bug in psql? Thomas Lockhart <lockhart@alumni.caltech.edu>
1999-05-11 15:13 ` Re: [INTERFACES] Bug in psql? José Soares <jose@sferacarta.com>
0 siblings, 2 replies; 10+ messages in thread
From: leif@danmos.dk @ 1999-05-11 13:21 UTC (permalink / raw)
To: pgsql-interfaces
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [INTERFACES] Bug in psql?
1999-05-11 13:21 Re: [INTERFACES] Bug in psql? leif@danmos.dk
@ 1999-05-11 14:01 ` Thomas Lockhart <lockhart@alumni.caltech.edu>
1 sibling, 0 replies; 10+ messages in thread
From: Thomas Lockhart @ 1999-05-11 14:01 UTC (permalink / raw)
To: leif@danmos.dk; +Cc: pgsql-interfaces
> > > 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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [INTERFACES] Bug in psql?
1999-05-11 13:21 Re: [INTERFACES] Bug in psql? leif@danmos.dk
@ 1999-05-11 15:13 ` José Soares <jose@sferacarta.com>
1 sibling, 0 replies; 10+ messages in thread
From: José Soares @ 1999-05-11 15:13 UTC (permalink / raw)
To: leif@danmos.dk; +Cc: pgsql-interfaces
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'
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [INTERFACES] Bug in psql?
@ 1999-05-13 09:39 leif@danmos.dk
0 siblings, 0 replies; 10+ messages in thread
From: leif@danmos.dk @ 1999-05-13 09:39 UTC (permalink / raw)
To: pgsql-interfaces
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)
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~1999-05-13 09:39 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-05-10 23:44 [INTERFACES] Bug in psql? leif@danmos.dk
1999-05-11 07:16 ` Charles Stroom <charles@stroom-schreurs.demon.nl>
1999-05-11 12:50 ` Bruce Tong <zztong@laxmi.ev.net>
1999-05-11 13:32 ` José Soares <jose@sferacarta.com>
1999-05-11 14:01 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-05-11 16:52 ` Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>
1999-05-11 13:21 Re: [INTERFACES] Bug in psql? leif@danmos.dk
1999-05-11 14:01 ` Thomas Lockhart <lockhart@alumni.caltech.edu>
1999-05-11 15:13 ` José Soares <jose@sferacarta.com>
1999-05-13 09:39 Re: [INTERFACES] Bug in psql? leif@danmos.dk
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox