Thread: Show schema in COPY error CONTEXT strings
Use case: when running a process that populates many inherited tables across schemas, having one fail gives the unhelpful error message: ERROR: invalid input syntax for integer: "abc" CONTEXT: COPY foo, line 1, column a: "abc" Unhelpful because "foo" does not uniquely identifies the table or statement in question, which was actually: COPY alpha.foo FROM STDIN; where 'alpha' was one of scores of schemas being populated. This patch changes the output to: ERROR: invalid input syntax for integer: "abc" CONTEXT: COPY alpha.foo, line 1, column a: "abc" I had to change the initial table in test/regress/sql/copy2.sql from a temp table to a real table, as I could not find an easy way to represent a wild card temp schema name inside of the test/regres/expected/copy2.out file. -- Greg Sabino Mullane greg@endpoint.com End Point Corporation PGP Key: 0x14964AC8
Attachment
Greg Sabino Mullane <greg@turnstep.com> writes:
> Use case: when running a process that populates many inherited
> tables across schemas, having one fail gives the unhelpful
> error message:
> ERROR: invalid input syntax for integer: "abc"
> CONTEXT: COPY foo, line 1, column a: "abc"
> Unhelpful because "foo" does not uniquely identifies the table
> or statement in question, which was actually: COPY alpha.foo FROM STDIN;
> where 'alpha' was one of scores of schemas being populated. This
> patch changes the output to:
> ERROR: invalid input syntax for integer: "abc"
> CONTEXT: COPY alpha.foo, line 1, column a: "abc"
We're really not going to address this type of complaint on a
one-error-message-at-a-time basis. See prior discussions --- a more
realistic (and standards compliant) approach will probably involve
adding fields to the verbose form of the error message.
regards, tom lane
-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160
> ERROR: invalid input syntax for integer: "abc"
> CONTEXT: COPY alpha.foo, line 1, column a: "abc"
> We're really not going to address this type of complaint on a
> one-error-message-at-a-time basis. See prior discussions --- a more
> realistic (and standards compliant) approach will probably involve
> adding fields to the verbose form of the error message.
Pointers to previous discussions welcome. I was simply trying to
fix a specific problem I was having, but some digging shows the
problem is already solved for most (all?) other similar cases:
# insert into public.foo (id) values ('mm');
ERROR: invalid input syntax for integer: "mm"
LINE 1: insert into public.foo (id) values ('mm');
# update public.foo set id='mm';
ERROR: invalid input syntax for integer: "mm"
LINE 1: update public.foo set id='mm';
# delete from public.foo where id = 'mm';
ERROR: invalid input syntax for integer: "mm"
LINE 1: delete from public.foo where id = 'mm';
Yes, I realize those are technically different context cases, but
from an application point of view, the COPY case is wrong and
needs fixing.
- --
Greg Sabino Mullane greg@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201005031242
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----
iEYEAREDAAYFAkve/PoACgkQvJuQZxSWSsjHiQCgoPZMcnP9viWoo4KY3y/I5NiA
1N0AoNyd5Fhs8M9WRkQ1LAS58Kz8x72S
=aIY9
-----END PGP SIGNATURE-----
"Greg Sabino Mullane" <greg@turnstep.com> writes: >> We're really not going to address this type of complaint on a >> one-error-message-at-a-time basis. See prior discussions --- a more >> realistic (and standards compliant) approach will probably involve >> adding fields to the verbose form of the error message. > Pointers to previous discussions welcome. The most recent one I can find is the thread starting at http://archives.postgresql.org/pgsql-hackers/2009-11/msg00846.php > I was simply trying to > fix a specific problem I was having, but some digging shows the > problem is already solved for most (all?) other similar cases: Um, no, it's not solved. There are a huge number of error messages that refer to database objects by name only, even though the name might be ambiguous. It's not reasonable to fix them one at a time, especially not in a fashion that breaks regression tests ;-). My own preference for what to do about this is to leave the primary message texts alone and add additional error-message fields for object name and schema. This would address the need without making messages uglier for the large fraction of users who don't really care; and it would also help us get closer to the SQL standard's expectations for error reporting. regards, tom lane
On Mon, May 3, 2010 at 1:01 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > My own preference for what to do about this is to leave the primary > message texts alone and add additional error-message fields for object > name and schema. This would address the need without making messages > uglier for the large fraction of users who don't really care; and it > would also help us get closer to the SQL standard's expectations for > error reporting. This might help people who use tools to parse the output, but I'm not sure that's who is having this problem. Presumably a sufficiently well-written tool can also keep track of which schema it was targeting in the first place. I have some reservations about cluttering up all of our error messages with schema names, but the status quo is pretty bad for people who have a whole bunch of nearly-identical schemas and are trying to divine to which one of them a particular error message pertains. ...Robert
Robert Haas <robertmhaas@gmail.com> writes:
> On Mon, May 3, 2010 at 1:01 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> My own preference for what to do about this is to leave the primary
>> message texts alone and add additional error-message fields for object
>> name and schema. �This would address the need without making messages
>> uglier for the large fraction of users who don't really care; and it
>> would also help us get closer to the SQL standard's expectations for
>> error reporting.
> This might help people who use tools to parse the output, but I'm not
> sure that's who is having this problem.
If you're using psql, "\set VERBOSITY verbose" would presumably show you
the extra fields, or we could invent a new setting that adds just these
fields. Likewise you can get it in the server log if you need it. I'm
not a fan of cramming more stuff into primary message texts on the
theory that that's the only useful field.
regards, tom lane