Обсуждение: 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

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