Show schema in COPY error CONTEXT strings

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