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 g...@endpoint.com End Point Corporation PGP Key: 0x14964AC8
Index: src/backend/commands/copy.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/commands/copy.c,v
retrieving revision 1.327
diff -c -r1.327 copy.c
*** src/backend/commands/copy.c 28 Apr 2010 16:10:41 -0000 1.327
--- src/backend/commands/copy.c 2 May 2010 15:40:52 -0000
***************
*** 119,124 ****
--- 119,125 ----
bool *force_notnull_flags; /* per-column CSV FNN flags */
/* these are just for error messages, see copy_in_error_callback */
+ const char *cur_schemaname; /* schema name for error messages */
const char *cur_relname; /* table name for error messages */
int cur_lineno; /* line number for
error messages */
const char *cur_attname; /* current att for error messages */
***************
*** 1569,1580 ****
{
/* can't usefully display the data */
if (cstate->cur_attname)
! errcontext("COPY %s, line %d, column %s",
! cstate->cur_relname,
cstate->cur_lineno,
! cstate->cur_attname);
else
! errcontext("COPY %s, line %d",
! cstate->cur_relname,
cstate->cur_lineno);
}
else
{
--- 1570,1582 ----
{
/* can't usefully display the data */
if (cstate->cur_attname)
! errcontext("COPY %s.%s, line %d, column %s",
! cstate->cur_schemaname,
cstate->cur_relname,
! cstate->cur_lineno,
cstate->cur_attname);
else
! errcontext("COPY %s.%s, line %d",
! cstate->cur_schemaname,
cstate->cur_relname,
! cstate->cur_lineno);
}
else
{
***************
*** 1584,1600 ****
char *attval;
attval = limit_printout_length(cstate->cur_attval);
! errcontext("COPY %s, line %d, column %s: \"%s\"",
! cstate->cur_relname,
cstate->cur_lineno,
! cstate->cur_attname, attval);
pfree(attval);
}
else if (cstate->cur_attname)
{
/* error is relevant to a particular column, value is
NULL */
! errcontext("COPY %s, line %d, column %s: null input",
! cstate->cur_relname,
cstate->cur_lineno,
! cstate->cur_attname);
}
else
{
--- 1586,1602 ----
char *attval;
attval = limit_printout_length(cstate->cur_attval);
! errcontext("COPY %s.%s, line %d, column %s: \"%s\"",
! cstate->cur_schemaname,
cstate->cur_relname,
! cstate->cur_lineno,
cstate->cur_attname, attval);
pfree(attval);
}
else if (cstate->cur_attname)
{
/* error is relevant to a particular column, value is
NULL */
! errcontext("COPY %s.%s, line %d, column %s: null input",
! cstate->cur_schemaname,
cstate->cur_relname,
! cstate->cur_lineno,
cstate->cur_attname);
}
else
{
***************
*** 1604,1611 ****
char *lineval;
lineval =
limit_printout_length(cstate->line_buf.data);
! errcontext("COPY %s, line %d: \"%s\"",
! cstate->cur_relname,
cstate->cur_lineno, lineval);
pfree(lineval);
}
else
--- 1606,1614 ----
char *lineval;
lineval =
limit_printout_length(cstate->line_buf.data);
! errcontext("COPY %s.%s, line %d: \"%s\"",
! cstate->cur_schemaname,
cstate->cur_relname,
! cstate->cur_lineno, lineval);
pfree(lineval);
}
else
***************
*** 1618,1625 ****
* regurgitate it without conversion. So we
have to punt and
* just report the line number.
*/
! errcontext("COPY %s, line %d",
! cstate->cur_relname,
cstate->cur_lineno);
}
}
}
--- 1621,1629 ----
* regurgitate it without conversion. So we
have to punt and
* just report the line number.
*/
! errcontext("COPY %s.%s, line %d",
! cstate->cur_schemaname,
cstate->cur_relname,
! cstate->cur_lineno);
}
}
}
***************
*** 1929,1934 ****
--- 1933,1939 ----
/* Initialize state variables */
cstate->fe_eof = false;
cstate->eol_type = EOL_UNKNOWN;
+ cstate->cur_schemaname =
get_namespace_name(RelationGetNamespace(cstate->rel));
cstate->cur_relname = RelationGetRelationName(cstate->rel);
cstate->cur_lineno = 0;
cstate->cur_attname = NULL;
Index: src/test/regress/expected/alter_table.out
===================================================================
RCS file: /projects/cvsroot/pgsql/src/test/regress/expected/alter_table.out,v
retrieving revision 1.119
diff -c -r1.119 alter_table.out
*** src/test/regress/expected/alter_table.out 23 Dec 2009 02:35:24 -0000
1.119
--- src/test/regress/expected/alter_table.out 2 May 2010 15:40:52 -0000
***************
*** 998,1004 ****
ERROR: column "........pg.dropped.1........" of relation "test" does not
exist
copy test from stdin;
ERROR: extra data after last expected column
! CONTEXT: COPY test, line 1: "10 11 12"
select * from test;
b | c
---+---
--- 998,1004 ----
ERROR: column "........pg.dropped.1........" of relation "test" does not
exist
copy test from stdin;
ERROR: extra data after last expected column
! CONTEXT: COPY public.test, line 1: "10 11 12"
select * from test;
b | c
---+---
Index: src/test/regress/expected/copy2.out
===================================================================
RCS file: /projects/cvsroot/pgsql/src/test/regress/expected/copy2.out,v
retrieving revision 1.29
diff -c -r1.29 copy2.out
*** src/test/regress/expected/copy2.out 17 Feb 2010 04:19:41 -0000 1.29
--- src/test/regress/expected/copy2.out 2 May 2010 15:40:52 -0000
***************
*** 1,4 ****
! CREATE TEMP TABLE x (
a serial,
b int,
c text not null default 'stuff',
--- 1,4 ----
! CREATE TABLE public.x (
a serial,
b int,
c text not null default 'stuff',
***************
*** 35,51 ****
-- missing data: should fail
COPY x from stdin;
ERROR: invalid input syntax for integer: ""
! CONTEXT: COPY x, line 1, column a: ""
COPY x from stdin;
ERROR: missing data for column "e"
! CONTEXT: COPY x, line 1: "2000 230 23 23"
COPY x from stdin;
ERROR: missing data for column "e"
! CONTEXT: COPY x, line 1: "2001 231 \N \N"
-- extra data: should fail
COPY x from stdin;
ERROR: extra data after last expected column
! CONTEXT: COPY x, line 1: "2002 232 40 50 60 70
80"
-- various COPY options: delimiters, oids, NULL string
COPY x (b, c, d, e) from stdin with oids delimiter ',' null 'x';
COPY x from stdin WITH DELIMITER AS ';' NULL AS '';
--- 35,51 ----
-- missing data: should fail
COPY x from stdin;
ERROR: invalid input syntax for integer: ""
! CONTEXT: COPY public.x, line 1, column a: ""
COPY x from stdin;
ERROR: missing data for column "e"
! CONTEXT: COPY public.x, line 1: "2000 230 23 23"
COPY x from stdin;
ERROR: missing data for column "e"
! CONTEXT: COPY public.x, line 1: "2001 231 \N \N"
-- extra data: should fail
COPY x from stdin;
ERROR: extra data after last expected column
! CONTEXT: COPY public.x, line 1: "2002 232 40 50 60
70 80"
-- various COPY options: delimiters, oids, NULL string
COPY x (b, c, d, e) from stdin with oids delimiter ',' null 'x';
COPY x from stdin WITH DELIMITER AS ';' NULL AS '';
Index: src/test/regress/expected/domain.out
===================================================================
RCS file: /projects/cvsroot/pgsql/src/test/regress/expected/domain.out,v
retrieving revision 1.45
diff -c -r1.45 domain.out
*** src/test/regress/expected/domain.out 11 Jun 2008 21:53:49 -0000
1.45
--- src/test/regress/expected/domain.out 2 May 2010 15:40:52 -0000
***************
*** 49,55 ****
-- Test copy
COPY basictest (testvarchar) FROM stdin; -- fail
ERROR: value too long for type character varying(5)
! CONTEXT: COPY basictest, line 1, column testvarchar: "notsoshorttext"
COPY basictest (testvarchar) FROM stdin;
select * from basictest;
testint4 | testtext | testvarchar | testnumeric
--- 49,55 ----
-- Test copy
COPY basictest (testvarchar) FROM stdin; -- fail
ERROR: value too long for type character varying(5)
! CONTEXT: COPY public.basictest, line 1, column testvarchar: "notsoshorttext"
COPY basictest (testvarchar) FROM stdin;
select * from basictest;
testint4 | testtext | testvarchar | testnumeric
***************
*** 130,136 ****
COPY domarrtest FROM stdin;
COPY domarrtest FROM stdin; -- fail
ERROR: value too long for type character varying(4)
! CONTEXT: COPY domarrtest, line 1, column testchar4arr: "{qwerty,w,e}"
select * from domarrtest;
testint4arr | testchar4arr
---------------+---------------------
--- 130,136 ----
COPY domarrtest FROM stdin;
COPY domarrtest FROM stdin; -- fail
ERROR: value too long for type character varying(4)
! CONTEXT: COPY public.domarrtest, line 1, column testchar4arr: "{qwerty,w,e}"
select * from domarrtest;
testint4arr | testchar4arr
---------------+---------------------
***************
*** 173,186 ****
-- Test copy
COPY nulltest FROM stdin; --fail
ERROR: null value in column "col3" violates not-null constraint
! CONTEXT: COPY nulltest, line 1: "a b \N d d"
COPY nulltest FROM stdin; --fail
ERROR: domain dcheck does not allow null values
! CONTEXT: COPY nulltest, line 1, column col5: null input
-- Last row is bad
COPY nulltest FROM stdin;
ERROR: new row for relation "nulltest" violates check constraint
"nulltest_col5_check"
! CONTEXT: COPY nulltest, line 3: "a b c \N a"
select * from nulltest;
col1 | col2 | col3 | col4 | col5
------+------+------+------+------
--- 173,186 ----
-- Test copy
COPY nulltest FROM stdin; --fail
ERROR: null value in column "col3" violates not-null constraint
! CONTEXT: COPY public.nulltest, line 1: "a b \N d d"
COPY nulltest FROM stdin; --fail
ERROR: domain dcheck does not allow null values
! CONTEXT: COPY public.nulltest, line 1, column col5: null input
-- Last row is bad
COPY nulltest FROM stdin;
ERROR: new row for relation "nulltest" violates check constraint
"nulltest_col5_check"
! CONTEXT: COPY public.nulltest, line 3: "a b c \N a"
select * from nulltest;
col1 | col2 | col3 | col4 | col5
------+------+------+------+------
Index: src/test/regress/output/constraints.source
===================================================================
RCS file: /projects/cvsroot/pgsql/src/test/regress/output/constraints.source,v
retrieving revision 1.49
diff -c -r1.49 constraints.source
*** src/test/regress/output/constraints.source 22 Mar 2010 17:43:28 -0000
1.49
--- src/test/regress/output/constraints.source 2 May 2010 15:40:52 -0000
***************
*** 278,284 ****
COPY COPY_TBL FROM '@abs_srcdir@/data/constrf.data';
ERROR: new row for relation "copy_tbl" violates check constraint "copy_con"
! CONTEXT: COPY copy_tbl, line 2: "7 check failed 6"
SELECT * FROM COPY_TBL;
x | y | z
---+---------------+---
--- 278,284 ----
COPY COPY_TBL FROM '@abs_srcdir@/data/constrf.data';
ERROR: new row for relation "copy_tbl" violates check constraint "copy_con"
! CONTEXT: COPY public.copy_tbl, line 2: "7 check failed 6"
SELECT * FROM COPY_TBL;
x | y | z
---+---------------+---
Index: src/test/regress/sql/copy2.sql
===================================================================
RCS file: /projects/cvsroot/pgsql/src/test/regress/sql/copy2.sql,v
retrieving revision 1.20
diff -c -r1.20 copy2.sql
*** src/test/regress/sql/copy2.sql 17 Feb 2010 04:19:41 -0000 1.20
--- src/test/regress/sql/copy2.sql 2 May 2010 15:40:52 -0000
***************
*** 1,4 ****
! CREATE TEMP TABLE x (
a serial,
b int,
c text not null default 'stuff',
--- 1,4 ----
! CREATE TABLE public.x (
a serial,
b int,
c text not null default 'stuff',
pgpZ8YDAJDKgV.pgp
Description: PGP signature
