REFERENCES error message complaint, suggestion

[Karl O. Pinc]

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 <ko*@meme.com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Tom Lane]

"Karl O. Pinc" <ko*@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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Karl O. Pinc]

On 2004.03.03 21:37 Tom Lane wrote:

"Karl O. Pinc" <ko*@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 <ko*@meme.com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Tom Lane]

"Karl O. Pinc" <ko*@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

[Karl O. Pinc]

On 2004.03.04 09:13 Tom Lane wrote:

"Karl O. Pinc" <ko*@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 <ko*@meme.com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Tom Lane]

"Karl O. Pinc" <ko*@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

---------------------------(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

[Uwe C. Schroeder]

-----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" <ko*@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: [phone] San Bruno, CA 94066
Cell: [phone] United States
Fax: [phone]
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)

iD8DBQFAR59ajqGXBvRToM4RAosjAKDG7vLq7M6nXzHi3Wfxim IlJFvVkgCgyAwk
aKUVIxSFjiKIpJ3juIq7WPE=
=Lk8d
-----END PGP SIGNATURE-----
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Greg Stark]

Tom Lane <tg*@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
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Greg Stark]

Greg Stark <gs*****@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
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Glen Parker]

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
gl******@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

[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);
?

Sincerely,

Joshua D. Drake

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
gl******@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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Glen Parker]

> From: pg*****************@postgresql.org

[mailto:pg*****************@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
gl******@nwlink.com
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Tom Lane]

"Glen Parker" <gl******@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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Karl O. Pinc]

On 2004.03.04 17:19 Greg Stark wrote:

Greg Stark <gs*****@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 <ko*@meme.com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Tom Lane]

"Glen Parker" <gl******@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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Andrew Sullivan]

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 | aj*@crankycanuck.ca
The fact that technology doesn't work is no bar to success in the marketplace.
--Philip Greenspun

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Enrico Weigelt]

* Tom Lane <tg*@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: [phone] www: http://www.metux.de/
fax: [phone] email: co*****@metux.de
cellphone: [phone]
---------------------------------------------------------------------
-- DSL-Zugang ab 0 Euro. -- statische IP -- UUCP -- Hosting --
---------------------------------------------------------------------

---------------------------(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

[Tom Lane]

Enrico Weigelt <we*****@metux.de> writes:

* Tom Lane <tg*@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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Rod K]

MySQL allows double quotes or single quotes around string literals. Back
ticks indicate identifiers.

-----Original Message-----
From: pg*****************@postgresql.org
[mailto:pg*****************@postgresql.org]On Behalf Of Tom Lane
Sent: Friday, March 05, 2004 10:12 AM
To: we*****@metux.de
Cc: pg***********@postgresql.org
Subject: Re: [GENERAL] REFERENCES error message complaint, suggestion
Enrico Weigelt <we*****@metux.de> writes:

* Tom Lane <tg*@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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Bruce Momjian]

Enrico Weigelt wrote:

* Tom Lane <tg*@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
pg***@candle.pha.pa.us | [phone]
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

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

[Chris Boget]

> 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?

select 'col' from tab;

+-----+
| col |
+-----+
| col |
| col |
| col |
+-----+
3 rows found in set (0.00 sec)

select "col" from tab;
+-----+
| col |
+-----+
| col |
| col |
| col |
+-----+
3 rows found in set (0.00 sec)

MySQL is definitely *not* 100% SQL92 compliant.

Chris


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

[Michael Chaney]

On Fri, Mar 05, 2004 at 09:53:50AM -0600, Chris Boget wrote:

MySQL is definitely *not* 100% SQL92 compliant.

Nah... can't be...

Not to be a jerk, but did you just figure this out?

Michael
--
Michael Darrin Chaney
md******@michaelchaney.com
http://www.michaelchaney.com/

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org