Thread: REFERENCES error message complaint, suggestion
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
"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
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
"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
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
"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
-----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-----
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
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
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
> 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 > 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
> 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
"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
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
"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
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
* 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 -- ---------------------------------------------------------------------
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
MySQL allows double quotes or single quotes around string literals. Back
ticks indicate identifiers.
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Tom Lane
> Sent: Friday, March 05, 2004 10:12 AM
> To: weigelt@metux.de
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>
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
> 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
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 mdchaney@michaelchaney.com http://www.michaelchaney.com/