Обсуждение: bad error message

I got a really bad error message in postgres on a CREATE TABLE in 8.1.0:

    ERROR:  column "id" referenced in foreign key constraint does not exist

That seems odd-- I mean, I know I obviously made an error.  I'm just
used to more detailed errors.

I didn't see anything in the changelogs since 8.1.0 saying that it
was addressed-- I could have missed that-- but it also might not have
been.

So... where can i complain that the error message should include the
field name I tried to toss the references constraint in ?


// Jonathan Vanasco

am  Thu, dem 12.10.2006, um 15:27:08 -0400 mailte Jonathan Vanasco folgendes:
>
> I got a really bad error message in postgres on a CREATE TABLE in 8.1.0:
>
>     ERROR:  column "id" referenced in foreign key constraint does not
>     exist
>
> That seems odd-- I mean, I know I obviously made an error.  I'm just
> used to more detailed errors.

Can you show us your SQL? The message is clear: you create a new table
with a foreign key to an other table that doesn't exist. An example:

-- first, i create a table with a primary key
test=# create table t1 (id int primary key, foo text);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "t1"
CREATE TABLE
-- now i create a new table with a foreign key constraint to the first
-- table
test=# create table t2 (id int references t1(id));
CREATE TABLE
--
-- and now i make a mistake
--
test=# create table t3 (id int references t1(id_));
ERROR:  column "id_" referenced in foreign key constraint does not exist



HTH, Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47215,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

On Oct 12, 2006, at 3:44 PM, A. Kretschmer wrote:

> Can you show us your SQL? The message is clear: you create a new table
> with a foreign key to an other table that doesn't exist. An example:

Yes, I know that part.  The error message is bad though, because it
doesn't tell me exactly where the error is.

I got as an error
    ERROR:  column "id" referenced in foreign key constraint does not exist

I should have gotten something like
    ERROR:  column "id" referenced in foreign key constraint on column
"xyz" table "abc" does not exist

( the table "abc" is not necessary, i just wanted to be explicit
about the message )

In that create table statement, i had 10 columns each referencing an
'id' in another column.  I like very normalized DBs.

I had to go through each column individually to see where my error
was.  Postgres should have immediately told me which of the source
table columns that constraint failed on-- not just about the target
column name.

Jonathan Vanasco <postgres@2xlp.com> writes:
> Yes, I know that part.  The error message is bad though, because it
> doesn't tell me exactly where the error is.

> I got as an error
>     ERROR:  column "id" referenced in foreign key constraint does not exist

> I should have gotten something like
>     ERROR:  column "id" referenced in foreign key constraint on column
> "xyz" table "abc" does not exist

That's not necessarily all that much help, if you've got so many FK
constraints in your command that you don't know exactly where to look.
I think what you're really wishing for is an error cursor position.
8.2 has the infrastructure for this, eg

regression=# create table foo (a int, b int, c int);
CREATE TABLE
regression=# select a, b, q from foo;
ERROR:  column "q" does not exist
LINE 1: select a, b, q from foo;
                     ^
regression=#

but unfortunately the facility hasn't been extended to foreign key
constraint clauses in particular :-(.  Maybe next time.

            regards, tom lane

On Oct 12, 2006, at 7:39 PM, Tom Lane wrote:

> That's not necessarily all that much help, if you've got so many FK
> constraints in your command that you don't know exactly where to look.
> I think what you're really wishing for is an error cursor position.
> 8.2 has the infrastructure for this, eg
>
> regression=# create table foo (a int, b int, c int);
> CREATE TABLE
> regression=# select a, b, q from foo;
> ERROR:  column "q" does not exist
> LINE 1: select a, b, q from foo;
>                      ^
> regression=#
>
> but unfortunately the facility hasn't been extended to foreign key
> constraint clauses in particular :-(.  Maybe next time


I'll be glad  if  it makes it into a future release.  It would be a
GREAT feature.

Thats for the info on the cursor position.  thats a nice update in
the .2 branch.

am  Thu, dem 12.10.2006, um 19:39:37 -0400 mailte Tom Lane folgendes:
> I think what you're really wishing for is an error cursor position.
> 8.2 has the infrastructure for this, eg
>
> regression=# create table foo (a int, b int, c int);
> CREATE TABLE
> regression=# select a, b, q from foo;
> ERROR:  column "q" does not exist
> LINE 1: select a, b, q from foo;
>                      ^
> regression=#

Very cool feature ...


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47215,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net