REFERENCES error message complaint, suggestion

Started by Karl O. Pincabout 22 years ago23 messagesgeneral
Jump to latest
#1Karl O. Pinc
kop@meme.com

FYI,

It'd be nice if the error message from a REFERENCES
constraint mentioned the column name into which
the bad data was attempted to be inserted.

In PostgreSQL 7.3:

sandbox=> insert into foo (id, b) values (3, 2);
ERROR: b_is_fkey referential integrity violation - key referenced from
foo not found in bar

(I only know that column 'b' is involved above because I
manually gave the constraint the 'b_is_fkey' name.)

Also, IIRC, CHECK constraint violations don't contain the text of
the constraint, so you're again reduced to relying on supplied
constraint names for a clue as to what went wrong. It'd be
nice to see the constraint itself in the error message.

Karl <kop@meme.com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Karl O. Pinc (#1)
Re: REFERENCES error message complaint, suggestion

"Karl O. Pinc" <kop@meme.com> writes:

It'd be nice if the error message from a REFERENCES
constraint mentioned the column name into which
the bad data was attempted to be inserted.

You mean like this?

regression=# create table foo (pk int primary key);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo"
CREATE TABLE
regression=# create table bar (fk int references foo);
CREATE TABLE
regression=# insert into bar values(1);
ERROR: insert or update on table "bar" violates foreign key constraint "$1"
DETAIL: Key (fk)=(1) is not present in table "foo".
regression=# select version();
version
---------------------------------------------------------------
PostgreSQL 7.4.1 on hppa-hp-hpux10.20, compiled by GCC 2.95.3
(1 row)

regards, tom lane

#3Karl O. Pinc
kop@meme.com
In reply to: Tom Lane (#2)
Re: REFERENCES error message complaint, suggestion

On 2004.03.03 21:37 Tom Lane wrote:

"Karl O. Pinc" <kop@meme.com> writes:

It'd be nice if the error message from a REFERENCES
constraint mentioned the column name into which
the bad data was attempted to be inserted.

You mean like this?

DETAIL: Key (fk)=(1) is not present in table "foo".

Exactly! :)

Does 7.4 do something similar with CHECK constraints?

Karl <kop@meme.com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein

#4Tom Lane
tgl@sss.pgh.pa.us
In reply to: Karl O. Pinc (#3)
Re: REFERENCES error message complaint, suggestion

"Karl O. Pinc" <kop@meme.com> writes:

Does 7.4 do something similar with CHECK constraints?

Nope, just

regression=# create table baz(f1 int check (f1 > 0));
CREATE TABLE
regression=# insert into baz values(-1);
ERROR: new row for relation "baz" violates check constraint "baz_f1"
regression=#

I think this is sufficient though, and that database designers ought to
choose helpful names for constraints. I should have said something like
... constraint "f1 must be positive" check (f1 > 0)
if I were concerned about the error message quality.

I didn't care much for your suggestion of showing the constraint
expression, because to the average non-programmer it would just be
noise.

regards, tom lane

#5Karl O. Pinc
kop@meme.com
In reply to: Tom Lane (#4)
Re: REFERENCES error message complaint, suggestion

On 2004.03.04 09:13 Tom Lane wrote:

"Karl O. Pinc" <kop@meme.com> writes:

Does 7.4 do something similar with CHECK constraints?

Nope, just

regression=# create table baz(f1 int check (f1 > 0));
CREATE TABLE
regression=# insert into baz values(-1);
ERROR: new row for relation "baz" violates check constraint "baz_f1"
regression=#

I think this is sufficient though, and that database designers ought
to
choose helpful names for constraints.

A note somewhere in the documentation that this is a useful practice
would
be helpful to newbies. (I'm going back and adding constraint names now,
after the fact.)

I should have said something
like
... constraint "f1 must be positive" check (f1 > 0)
if I were concerned about the error message quality.

I tried this (in 7.3) only using single quotes instead of double quotes
and got syntax errors. Where do I look in the docs to learn
about this aspect of the syntax? (I also note that the examples
often single quote the plpgsql language keyword in CREATE FUNCTION
statements, but I haven't and although I read through the whole
manual didn't notice where such quotes are allowed and/or
required.)

I didn't care much for your suggestion of showing the constraint
expression, because to the average non-programmer it would just be
noise.

You're right about that.

Karl <kop@meme.com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein

#6Tom Lane
tgl@sss.pgh.pa.us
In reply to: Karl O. Pinc (#5)
Re: REFERENCES error message complaint, suggestion

"Karl O. Pinc" <kop@meme.com> writes:

On 2004.03.04 09:13 Tom Lane wrote:

I should have said something
like
... constraint "f1 must be positive" check (f1 > 0)
if I were concerned about the error message quality.

I tried this (in 7.3) only using single quotes instead of double quotes
and got syntax errors. Where do I look in the docs to learn
about this aspect of the syntax?

Double quotes are for names (identifiers). Single quotes are for string
literals (constants). In this case you're trying to write a nonstandard
name for a constraint, so you want double quotes.

(I also note that the examples
often single quote the plpgsql language keyword in CREATE FUNCTION
statements, but I haven't and although I read through the whole
manual didn't notice where such quotes are allowed and/or
required.)

Those examples are old; the preferred syntax these days is to write the
language name as an identifier. But we used to, and still do, accept a
string literal for the language name. This is mentioned (very briefly)
on the CREATE FUNCTION reference page:

langname

The name of the language that the function is implemented in. May
be SQL, C, internal, or the name of a user-defined procedural
language. (See also createlang.) For backward compatibility, the
name may be enclosed by single quotes.

regards, tom lane

#7Enrico Weigelt
weigelt@metux.de
In reply to: Tom Lane (#6)
Re: REFERENCES error message complaint, suggestion

* Tom Lane <tgl@sss.pgh.pa.us> [2004-03-04 11:24:11 -0500]:

<snip>

Double quotes are for names (identifiers). Single quotes are
for string literals (constants).

BTW: is this general SQL syntax or just PostgeSQL ?

mysql does no distinction (which is IMHO very unclean), and it gets
even worse since mysqldump's output does it exactly the wrong way:
identifiers in '' and string constants in "" ...

cu
--
---------------------------------------------------------------------
Enrico Weigelt == metux IT services

phone: +49 36207 519931 www: http://www.metux.de/
fax: +49 36207 519932 email: contact@metux.de
cellphone: +49 174 7066481
---------------------------------------------------------------------
-- DSL-Zugang ab 0 Euro. -- statische IP -- UUCP -- Hosting --
---------------------------------------------------------------------

#8Uwe C. Schroeder
uwe@oss4u.com
In reply to: Tom Lane (#4)
Re: REFERENCES error message complaint, suggestion

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On the other hand I think this might have some value. There are some DB
maintenance GUIs out there that generate the constraint names on their own,
either giving you a more or less visible way to set constraint names, or just
omitting that possibility at all.
I agree that basically one can ask the database what constraint is which (i.e.
using pgadmin3). On the other hand having a clear error in the logfile might
be convenient.

Just a thought.

UC

On Thursday 04 March 2004 07:13 am, Tom Lane wrote:

"Karl O. Pinc" <kop@meme.com> writes:

Does 7.4 do something similar with CHECK constraints?

Nope, just

regression=# create table baz(f1 int check (f1 > 0));
CREATE TABLE
regression=# insert into baz values(-1);
ERROR: new row for relation "baz" violates check constraint "baz_f1"
regression=#

I think this is sufficient though, and that database designers ought to
choose helpful names for constraints. I should have said something like
... constraint "f1 must be positive" check (f1 > 0)
if I were concerned about the error message quality.

I didn't care much for your suggestion of showing the constraint
expression, because to the average non-programmer it would just be
noise.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

- --
Open Source Solutions 4U, LLC 2570 Fleetwood Drive
Phone: +1 650 872 2425 San Bruno, CA 94066
Cell: +1 650 302 2405 United States
Fax: +1 650 872 2417
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)

iD8DBQFAR59ajqGXBvRToM4RAosjAKDG7vLq7M6nXzHi3WfximIlJFvVkgCgyAwk
aKUVIxSFjiKIpJ3juIq7WPE=
=Lk8d
-----END PGP SIGNATURE-----

#9Bruce Momjian
bruce@momjian.us
In reply to: Tom Lane (#4)
Re: REFERENCES error message complaint, suggestion

Tom Lane <tgl@sss.pgh.pa.us> writes:

I think this is sufficient though, and that database designers ought to
choose helpful names for constraints. I should have said something like
... constraint "f1 must be positive" check (f1 > 0)
if I were concerned about the error message quality.

Consider the case of a query like
"INSERT INTO foo (SELECT ... FROM baz)"
or
"UPDATE foo set x = func(y,z)"

It's great to know which constraint was violated but that doesn't really help
you figure out *why* it was violated.

--
greg

#10Bruce Momjian
bruce@momjian.us
In reply to: Bruce Momjian (#9)
Re: REFERENCES error message complaint, suggestion

Greg Stark <gsstark@MIT.EDU> writes:

It's great to know which constraint was violated but that doesn't really help
you figure out *why* it was violated.

On further thought it would never be feasible to do what the other poster is
really looking for. At least for table constraints it would require poking
through the expression to determine which columns might have caused the
violation.

Perhaps a better idea would be a debugging log message that dumped the entire
contents of a row update or insertion that fails due to any constraint. That
would be disabled normally but easy to enable and produce information that
would be very helpful for a dba loading data or doing large updates.

Especially if there's an option to complete the operation producing all the
errors before forcing the rollingback of the transaction.

--
greg

#11Glen Parker
glenebob@nwlink.com
In reply to: Bruce Momjian (#9)
ERROR: function round(double precision, integer) does not exist - WTF?

I'm having trouble with the round() function. What I am trying to do works
fine on 7.2.*, but not in 7.3.* or 7.4.*

DB=# select version();
PostgreSQL 7.4.1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.2
20031107 (Red Hat Linux 3.3.2-2)

DB=# select round(1.25::float);
round
-------
1
(1 row)

oms=# select round(1.25::decimal, 2);
round
-------
1.25
(1 row)

BUT.............

DB=# select round(1.25::float, 2);
ERROR: function round(double precision, integer) does not exist
HINT: No function matches the given name and argument types. You may need
to add explicit type casts.

What the heck? This can't be right... Can it?

Thx!
Glen Parker
glenebob@nwlink.com

#12Joshua D. Drake
jd@commandprompt.com
In reply to: Glen Parker (#11)
Re: ERROR: function round(double precision, integer) does

oms=# select round(1.25::decimal, 2);
round
-------
1.25
(1 row)

BUT.............

DB=# select round(1.25::float, 2);

select round(1.25::numeric, 2);

?

Sincerely,

Joshua D. Drake

Show quoted text

ERROR: function round(double precision, integer) does not exist
HINT: No function matches the given name and argument types. You may need
to add explicit type casts.

What the heck? This can't be right... Can it?

Thx!
Glen Parker
glenebob@nwlink.com

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

#13Glen Parker
glenebob@nwlink.com
In reply to: Joshua D. Drake (#12)
Re: ERROR: function round(double precision, integer) does

From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of
Joshua D. Drake

oms=# select round(1.25::decimal, 2);
round
-------
1.25
(1 row)

BUT.............

DB=# select round(1.25::float, 2);

select round(1.25::numeric, 2);

Yeah I know, but in the interest of portability, I have to use the full-on
SQL cast (cast(1234.123 as numeric)). Besides, I'd have to fix (for lack of
a better word) piles of queries.

This popped up because we're doing dev work against a 7.4 server but
deploying against a 7.2 server. To make matters worse, here's one of the
errors we get on 7.2 after doing the casts and re-deploying:

DB=# select 1234::numeric > 1234::float;
ERROR: Unable to identify an operator '>' for types 'numeric' and 'double
precision'
You will have to retype this query using an explicit cast

Again, WTF??? Since this is on 7.2 (and fixed on >= 7.3), I'm not too
worried about it, but it, too, is quite broken.

The result is that development work is suddenly a big pain in the a$$ where
these queries are concerned. I'm considering Postgres to be broken. Am I
wrong? Is there an actual reason for this behavior?

Thx again,
Glen Parker
glenebob@nwlink.com

#14Tom Lane
tgl@sss.pgh.pa.us
In reply to: Glen Parker (#11)
Re: ERROR: function round(double precision, integer) does not exist - WTF?

"Glen Parker" <glenebob@nwlink.com> writes:

DB=# select round(1.25::float, 2);
ERROR: function round(double precision, integer) does not exist

What the heck? This can't be right... Can it?

regression=# \df round
List of functions
Result data type | Schema | Name | Argument data types
------------------+------------+-------+---------------------
double precision | pg_catalog | round | double precision
numeric | pg_catalog | round | numeric
numeric | pg_catalog | round | numeric, integer
(3 rows)

Looks right to me: the only 2-arg flavor of round() is on numeric,
not float8.

You could always cast the float to numeric, of course. I think in
7.2 such conversions were allowed silently, but we have reduced the
number of implicit type coercions.

regards, tom lane

#15Dann Corbit
DCorbit@connx.com
In reply to: Tom Lane (#14)
Re: ERROR: function round(double precision, integer) does not exist - WTF?

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Thursday, March 04, 2004 6:18 PM
To: Glen Parker
Cc: Pg-General
Subject: Re: [GENERAL] ERROR: function round(double
precision, integer) does not exist - WTF?

"Glen Parker" <glenebob@nwlink.com> writes:

DB=# select round(1.25::float, 2);
ERROR: function round(double precision, integer) does not exist

What the heck? This can't be right... Can it?

regression=# \df round
List of functions
Result data type | Schema | Name | Argument data types
------------------+------------+-------+---------------------
double precision | pg_catalog | round | double precision
numeric | pg_catalog | round | numeric
numeric | pg_catalog | round | numeric, integer
(3 rows)

Looks right to me: the only 2-arg flavor of round() is on
numeric, not float8.

You could always cast the float to numeric, of course. I
think in 7.2 such conversions were allowed silently, but we
have reduced the number of implicit type coercions.

Why?

#16Karl O. Pinc
kop@meme.com
In reply to: Bruce Momjian (#10)
Re: REFERENCES error message complaint, suggestion

On 2004.03.04 17:19 Greg Stark wrote:

Greg Stark <gsstark@MIT.EDU> writes:

It's great to know which constraint was violated but that doesn't

really help

you figure out *why* it was violated.

On further thought it would never be feasible to do what the other
poster is
really looking for. At least for table constraints it would require
poking
through the expression to determine which columns might have caused
the
violation.

This is exactly the kind of thing that exception handling mechanisims
with throws, catches, and first class continuations are great for.
Even without continuations, each exception handler can translate the
error it catches into something appropriate at the current level
of abstraction, what went wrong from it's perspective,
it means to the result that will be produced, and what
steps might be taken to avoid the problem. If each layer
of abstraction, application, SQL, constraint, etc., takes the care
to catch it's errors and pass them on you wind up with an
annoted stack trace, the topmost level of which should be
meaningful to the end-user.

Of course most programmers don't bother to even check for error
conditons
when they are returned as results, so it's probably too
much to expect that the users will ever get good errors,
but it should be possible for postgresql to deliver something
of a stack trace. ?

BTW, I like the idea of two classes of error messages, one to be
logged for the DBA and one for the user.

Karl <kop@meme.com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein

#17Tom Lane
tgl@sss.pgh.pa.us
In reply to: Glen Parker (#13)
Re: ERROR: function round(double precision, integer) does

"Glen Parker" <glenebob@nwlink.com> writes:

This popped up because we're doing dev work against a 7.4 server but
deploying against a 7.2 server. To make matters worse, here's one of the
errors we get on 7.2 after doing the casts and re-deploying:

DB=# select 1234::numeric > 1234::float;
ERROR: Unable to identify an operator '>' for types 'numeric' and 'double
precision'
You will have to retype this query using an explicit cast

Again, WTF??? Since this is on 7.2 (and fixed on >= 7.3), I'm not too
worried about it, but it, too, is quite broken.

This sort of problem is exactly why we tightened the implicit casting
rules.

In the above example, the parser has to choose between casting the
numeric to float and applying float greater-than, or casting the float
to numeric and applying numeric greater-than. In 7.2 and before these
two alternatives look equally good and the parser cannot make a choice,
so it fails as above. In 7.3 and later, only the numeric-to-float cast
is allowed to be applied implicitly, so the parser is forced down the
road that leads to choosing float comparison.

(If you're wondering why we like that direction better than the other,
it's because the SQL spec says that operations combining exact and
inexact numeric values yield inexact results. So numeric + float has to
be implemented as float addition.)

The result is that development work is suddenly a big pain in the a$$ where
these queries are concerned.

It's taken us quite a while to get these things right...

regards, tom lane

#18Andrew Sullivan
ajs@crankycanuck.ca
In reply to: Glen Parker (#13)
Re: ERROR: function round(double precision, integer) does

On Thu, Mar 04, 2004 at 06:12:36PM -0800, Glen Parker wrote:

This popped up because we're doing dev work against a 7.4 server but
deploying against a 7.2 server.

Don't do that. You're going to have loads of trouble.

these queries are concerned. I'm considering Postgres to be broken. Am I
wrong?

Yes, you are. These are different pieces of software, and they'll
have different behaviour. It's not like developers renumber the
versions for fun.

A

--
Andrew Sullivan | ajs@crankycanuck.ca
The fact that technology doesn't work is no bar to success in the marketplace.
--Philip Greenspun

#19Bruce Momjian
bruce@momjian.us
In reply to: Enrico Weigelt (#7)
Re: REFERENCES error message complaint, suggestion

Enrico Weigelt wrote:

* Tom Lane <tgl@sss.pgh.pa.us> [2004-03-04 11:24:11 -0500]:

<snip>

Double quotes are for names (identifiers). Single quotes are
for string literals (constants).

BTW: is this general SQL syntax or just PostgeSQL ?

We follow the SQL standard on this, and I think most other vendors do as
well.

mysql does no distinction (which is IMHO very unclean), and it gets
even worse since mysqldump's output does it exactly the wrong way:
identifiers in '' and string constants in "" ...

That is pretty amazing.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073
#20Tom Lane
tgl@sss.pgh.pa.us
In reply to: Enrico Weigelt (#7)
Re: REFERENCES error message complaint, suggestion

Enrico Weigelt <weigelt@metux.de> writes:

* Tom Lane <tgl@sss.pgh.pa.us> [2004-03-04 11:24:11 -0500]:

Double quotes are for names (identifiers). Single quotes are
for string literals (constants).

BTW: is this general SQL syntax or just PostgeSQL ?

This is SQL92 standard behavior. Relevant extracts from the standard:

<delimited identifier> ::=
<double quote> <delimited identifier body> <double quote>

<character string literal> ::=
[ <introducer><character set specification> ]
<quote> [ <character representation>... ] <quote>
[ { <separator>... <quote> [ <character representation>... ] <quote> }... ]

<double quote> ::= "

<quote> ::= '

mysql does no distinction (which is IMHO very unclean),

How can they have no distinction? Suppose I write

select 'col' from tab;
select "col" from tab;

If there is a column tab.col, what am I going to get in each case?

regards, tom lane

#21Rod K
rod@23net.net
In reply to: Tom Lane (#20)
#22Chris Boget
chris@wild.net
In reply to: Karl O. Pinc (#1)
#23Michael Chaney
mdchaney@michaelchaney.com
In reply to: Chris Boget (#22)