Обсуждение: Inconsistent Errors on Row Comparisons
Howdy,
I'm working on functions to compare result sets for pgTAP. In the
process, I found what appears to be an inconsistency in error handling
when comparing incomparable results. I'm testing in 8.4RC2, but the
issue may go back for all I know. Perhaps it's intentional?
This is what I see. This query:
VALUES (1, 2), (3, 4) EXCEPT VALUES (1, 'foo'), (3, 'bar');
Throws 42804 DATATYPE MISMATCH. Meanwhile, this query:
VALUES (1, 2), (3, 4) EXCEPT VALUES (1), (3);
Throws 42601 SYNTAX ERROR. It'd be nice if the error was a bit more
specific (maybe tell me that there are different numbers of columns,
perhaps 54011?), but at least it's distinct from the data type mismatch.
However, when I do a row-by-row comparison of rows in cursors, I get a
different behavior. The attached test case has the details, but
assuming a function `restults_eq(refcursor, refcursor)` that does the
row-by-row comparison, this code:
DECLARE cwant CURSOR FOR VALUES (1, 2), (3, 4);
DECLARE chave CURSOR FOR VALUES (1, 'foo'), (3, 'bar');
SELECT results_eq( 'cwant'::refcursor, 'chave'::refcursor );
Throws 42804 DATATYPE MISMATCH, as expected. On the other hand, this
code:
DECLARE cwant2 CURSOR FOR VALUES (1, 2), (3, 4);
DECLARE chave2 CURSOR FOR VALUES (1), (3);
SELECT results_eq( 'cwant2'::refcursor, 'chave2'::refcursor );
Also throws Throws 42804 DATATYPE MISMATCH. For consistency with the
row comparisons done by EXCEPT and friends, should it not throw 42601
SYNTAX ERROR?
Thanks,
David
Вложения
"David E. Wheeler" <david@kineticode.com> writes:
> This is what I see. This query:
> VALUES (1, 2), (3, 4) EXCEPT VALUES (1, 'foo'), (3, 'bar');
> Throws 42804 DATATYPE MISMATCH.
Yeah ...
> Meanwhile, this query:
> VALUES (1, 2), (3, 4) EXCEPT VALUES (1), (3);
> Throws 42601 SYNTAX ERROR.
Not for me:
regression=# VALUES (1, 2), (3, 4) EXCEPT VALUES (1), (3);
ERROR: each EXCEPT query must have the same number of columns
regards, tom lane
On Jun 30, 2009, at 10:28 AM, Tom Lane wrote: >> VALUES (1, 2), (3, 4) EXCEPT VALUES (1), (3); > >> Throws 42601 SYNTAX ERROR. > > Not for me: > > regression=# VALUES (1, 2), (3, 4) EXCEPT VALUES (1), (3); > ERROR: each EXCEPT query must have the same number of columns Turn on verbosity: try=# \set VERBOSITY verbose try=# VALUES (1, 2), (3, 4) EXCEPT VALUES (1), (3); ERROR: 42601: each EXCEPT query must have the same number of columns LOCATION: transformSetOperationTree, analyze.c:1502 42601 is a SYNTAX ERROR. The inconsistency when comparing records from cursors stands, too. Best, David
"David E. Wheeler" <david@kineticode.com> writes:
> On Jun 30, 2009, at 10:28 AM, Tom Lane wrote:
>> Not for me:
>>
>> regression=# VALUES (1, 2), (3, 4) EXCEPT VALUES (1), (3);
>> ERROR: each EXCEPT query must have the same number of columns
> Turn on verbosity:
> try=# \set VERBOSITY verbose
> try=# VALUES (1, 2), (3, 4) EXCEPT VALUES (1), (3);
> ERROR: 42601: each EXCEPT query must have the same number of columns
> LOCATION: transformSetOperationTree, analyze.c:1502
> 42601 is a SYNTAX ERROR.
Oh, you're complaining about the SQLSTATE not the error text.
I guess that to the extent that any actual thought went into it
(which may not have been much) the reasoning was that you'd have to
change the syntax of your query in order to fix this. But I guess
a case could be made for ERRCODE_DATATYPE_MISMATCH there. I definitely
do not agree with your suggestion of ERRCODE_TOO_MANY_COLUMNS --- that's
from Program Limit Exceeded category which is 100% the wrong thing.
The other errors are coming from within record_eq(), where what it's
got is two composite values that don't match as to structure. It
seems fairly clear that DATATYPE_MISMATCH is the right thing there.
So if we feel that these errors should match, I'd vote for changing to
DATATYPE_MISMATCH, not changing to SYNTAX_ERROR. But I'm not entirely
convinced that there's a reason to make them match. I'm not sure that
they really have the same cause when you look at it concretely.
regards, tom lane
]On Jun 30, 2009, at 11:00 AM, Tom Lane wrote: > Oh, you're complaining about the SQLSTATE not the error text. > I guess that to the extent that any actual thought went into it > (which may not have been much) the reasoning was that you'd have to > change the syntax of your query in order to fix this. But I guess > a case could be made for ERRCODE_DATATYPE_MISMATCH there. I > definitely > do not agree with your suggestion of ERRCODE_TOO_MANY_COLUMNS --- > that's > from Program Limit Exceeded category which is 100% the wrong thing. Yeah, that was just an aside. I liked that I got different errors when there were different numbers of columns than when the data types of the columns disagreed. I'm not sure that SYNTAX ERROR is a great code for when the count disagrees, but at least it's distinct from the column data type error. And I'm going on SQLSTATE here because I'm doing exception handling in pl/PgSQL and want to handle the two errors differently. > The other errors are coming from within record_eq(), where what it's > got is two composite values that don't match as to structure. It > seems fairly clear that DATATYPE_MISMATCH is the right thing there. I see, it's thinking of the two row objects as distinct types, rather than complaining about different numbers of columns. > So if we feel that these errors should match, I'd vote for changing to > DATATYPE_MISMATCH, not changing to SYNTAX_ERROR. But I'm not entirely > convinced that there's a reason to make them match. I'm not sure that > they really have the same cause when you look at it concretely. Okay. I'll have to see what I can do with SQLERRM then. But isn't it localized? Best, David
"David E. Wheeler" <david@kineticode.com> writes:
> Yeah, that was just an aside. I liked that I got different errors when
> there were different numbers of columns than when the data types of
> the columns disagreed. I'm not sure that SYNTAX ERROR is a great code
> for when the count disagrees, but at least it's distinct from the
> column data type error.
> And I'm going on SQLSTATE here because I'm doing exception handling in
> pl/PgSQL and want to handle the two errors differently.
So really what you're wishing for is that we treat different-numbers-of-
columns as a whole new SQLSTATE inside category 42. What's the argument
for needing to handle this differently from DATATYPE_MISMATCH?
> Okay. I'll have to see what I can do with SQLERRM then. But isn't it
> localized?
Yeah, it is. You don't really want code looking at that to decide what
to do, if you can possibly avoid it. It's intended for human consumption.
regards, tom lane
On Jun 30, 2009, at 11:18 AM, Tom Lane wrote:
> So really what you're wishing for is that we treat different-numbers-
> of-
> columns as a whole new SQLSTATE inside category 42. What's the
> argument
> for needing to handle this differently from DATATYPE_MISMATCH?
For my results_eq() in pgTAP, it could output different diagnostics.
I'm already doing this for the set_eq() function I wrote, which uses
EXCEPT. For that function, if you pass two statements with different
numbers of columns, pgTAP says:
# Failed test 148 # Number of columns differs between queries
While for a call with the same numbers of columns but different data
types (say int,text and inet,text), pgTAP says:
# Failed test 149 # Column types differ between queries
Essentially, while on a row object-level, they are different types,
the caller of my function doesn't know that it's comparing rows, just
that it's comparing result sets. So I like to give as much information
as possible about the difference in the result sets of the queries.
Hell, ideally it'd actually say something like:
# Failed test 148 # Number of columns differs between queries # have: 4 columns #
want:3 columns
# Failed test 149 # Column types differ between queries # have: (integer,text) # want:
(inet,text)
This gives the tester a lot of information to help diagnose the test
failure. I don't know that I can gather that kind of information,
though.
>> Okay. I'll have to see what I can do with SQLERRM then. But isn't it
>> localized?
>
> Yeah, it is. You don't really want code looking at that to decide
> what
> to do, if you can possibly avoid it. It's intended for human
> consumption.
As I thought, thanks.
Best,
David
On Jun 30, 2009, at 11:27 AM, David E. Wheeler wrote: > # Failed test 148 > # Number of columns differs between queries > # have: 4 columns > # want: 3 columns > > # Failed test 149 > # Column types differ between queries > # have: (integer,text) > # want: (inet,text) > > This gives the tester a lot of information to help diagnose the test > failure. I don't know that I can gather that kind of information, > though. Actually, I can for `set_eq()`, since it creates a temporary table, I can just get the list of types from the system catalog. Is there a way to get a RECORD object to tell me what data types it contains? Then I could use the same error for both situations, since the difference in the number of columns is implicit in the list of data types: # Failed test 148 # Column types differ between queries # have: (integer,text,integer) # want:(inet,text) # Failed test 149 # Column types differ between queries # have: (integer,text) # want: (inet,text) Thanks, David
"David E. Wheeler" <david@kineticode.com> writes:
> On Jun 30, 2009, at 11:18 AM, Tom Lane wrote:
>> What's the argument
>> for needing to handle this differently from DATATYPE_MISMATCH?
> For my results_eq() in pgTAP, it could output different diagnostics.
Well, that's not terribly compelling ;-). I wouldn't have any big
objection to splitting out ERRCODE_COLUMN_COUNT_MISMATCH as a separate
SQLSTATE for 8.5 and beyond, but I doubt we'd consider back-patching
such a change. It's not clear to me whether you need a solution that
works in back branches.
regards, tom lane
"David E. Wheeler" <david@kineticode.com> writes:
> Is there a way
> to get a RECORD object to tell me what data types it contains?
Not at the SQL level. Of course, if you're writing C, you can do
something similar to what record_eq and friends do.
regards, tom lane
On Tue, Jun 30, 2009 at 11:27:20AM -0700, David Wheeler wrote: > On Jun 30, 2009, at 11:18 AM, Tom Lane wrote: > >> So really what you're wishing for is that we treat different-numbers- >> of- >> columns as a whole new SQLSTATE inside category 42. What's the >> argument >> for needing to handle this differently from DATATYPE_MISMATCH? > > For my results_eq() in pgTAP, it could output different diagnostics. I'm > already doing this for the set_eq() function I wrote, which uses EXCEPT. > For that function, if you pass two statements with different numbers of > columns, pgTAP says: > > # Failed test 148 > # Number of columns differs between queries > > While for a call with the same numbers of columns but different data > types (say int,text and inet,text), pgTAP says: > > # Failed test 149 > # Column types differ between queries > > Essentially, while on a row object-level, they are different types, the > caller of my function doesn't know that it's comparing rows, just that > it's comparing result sets. So I like to give as much information as > possible about the difference in the result sets of the queries. Hell, > ideally it'd actually say something like: > > # Failed test 148 > # Number of columns differs between queries > # have: 4 columns > # want: 3 columns Shouldn't that just read: have: (int, int, text, point) want: (int, int, text) Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
On Jun 30, 2009, at 11:46 AM, Tom Lane wrote: >> For my results_eq() in pgTAP, it could output different diagnostics. > > Well, that's not terribly compelling ;-). Pbbbbllt. > I wouldn't have any big > objection to splitting out ERRCODE_COLUMN_COUNT_MISMATCH as a separate > SQLSTATE for 8.5 and beyond, but I doubt we'd consider back-patching > such a change. It's not clear to me whether you need a solution that > works in back branches. Makes sense. Best, David
On Jun 30, 2009, at 11:48 AM, Tom Lane wrote: >> Is there a way >> to get a RECORD object to tell me what data types it contains? > > Not at the SQL level. Of course, if you're writing C, you can do > something similar to what record_eq and friends do. Pity. I'm trying to keep C out of pgTAP (for the most part) so that folks can just distribute a copy of it with their modules. But I can at least include that information in the diagnostics from set_eq(). Best, David
On Jun 30, 2009, at 11:54 AM, David Fetter wrote: >> # Failed test 148 >> # Number of columns differs between queries >> # have: 4 columns >> # want: 3 columns > > Shouldn't that just read: > > have: (int, int, text, point) > want: (int, int, text) Yes, that's my ideal, but Tom says I need to write C code to get that information from RECORD objects, alas. :-( Best, David
On Tue, Jun 30, 2009 at 01:10:01PM -0700, David Wheeler wrote: > On Jun 30, 2009, at 11:54 AM, David Fetter wrote: > >>> # Failed test 148 >>> # Number of columns differs between queries >>> # have: 4 columns >>> # want: 3 columns >> >> Shouldn't that just read: >> >> have: (int, int, text, point) >> want: (int, int, text) > > Yes, that's my ideal, but Tom says I need to write C code to get that > information from RECORD objects, alas. :-( Would this be the first C piece? If not, it might be worth doing. Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
On Jun 30, 2009, at 1:40 PM, David Fetter wrote:
>> Yes, that's my ideal, but Tom says I need to write C code to get that
>> information from RECORD objects, alas. :-(
>
> Would this be the first C piece? If not, it might be worth doing.
I don't understand the question. But yes, I think it'd be worth doing.
I'd like to have functions like:
pg_record_attrs(RECORD) RETURNS SETOF regtype[]
And maybe another function to return attribute names. If it could
returns both names and types, that'd be cool, but I'm not sure what
kind of data type that would be.
Does this sound interesting to other folks?
Best,
David
On Tue, Jun 30, 2009 at 02:01:26PM -0700, David Wheeler wrote: > On Jun 30, 2009, at 1:40 PM, David Fetter wrote: > >>> Yes, that's my ideal, but Tom says I need to write C code to get that >>> information from RECORD objects, alas. :-( >> >> Would this be the first C piece? If not, it might be worth doing. > > I don't understand the question. I was thinking of this as part of PgTAP. > But yes, I think it'd be worth doing. I'd like to have functions > like: > > pg_record_attrs(RECORD) RETURNS SETOF regtype[] > > And maybe another function to return attribute names. If it could > returns both names and types, that'd be cool, but I'm not sure what kind > of data type that would be. It's possible to have it return SETOF RECORD with OUT parameters, I think. > Does this sound interesting to other folks? Sure. Maybe that should go in pg_catalog in 8.5... Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
On Jun 30, 2009, at 3:05 PM, David Fetter wrote: >>> Would this be the first C piece? If not, it might be worth doing. >> >> I don't understand the question. > > I was thinking of this as part of PgTAP. Oh. There is a piece of C, but it's just an implementation of pg_typeof() so that pgTAP can use it in versions of PostgreSQL < 8.4. In 8.4 and later, no C code is built. Have been trying to keep it that way for now. >> But yes, I think it'd be worth doing. I'd like to have functions >> like: >> >> pg_record_attrs(RECORD) RETURNS SETOF regtype[] >> >> And maybe another function to return attribute names. If it could >> returns both names and types, that'd be cool, but I'm not sure what >> kind >> of data type that would be. > > It's possible to have it return SETOF RECORD with OUT parameters, I > think. Out parameters? I was thinking of SETOF RECORD with two attributes in each record: name and type. Are there other attributes of RECORD attributes that might be of interest? >> Does this sound interesting to other folks? > > Sure. Maybe that should go in pg_catalog in 8.5... Right, that was my thought. I could then throw it in pgTAP when building on < 8.5. Best, David
On Tue, Jun 30, 2009 at 03:16:09PM -0700, David Wheeler wrote: > On Jun 30, 2009, at 3:05 PM, David Fetter wrote: > >>>> Would this be the first C piece? If not, it might be worth >>>> doing. >>> >>> I don't understand the question. >> >> I was thinking of this as part of PgTAP. > > Oh. There is a piece of C, but it's just an implementation of > pg_typeof() so that pgTAP can use it in versions of PostgreSQL < > 8.4. In 8.4 and later, no C code is built. Have been trying to > keep it that way for now. OK, so the can is already open, but you're trying to discourage the worms from escaping. :) >>> But yes, I think it'd be worth doing. I'd like to have functions >>> like: >>> >>> pg_record_attrs(RECORD) RETURNS SETOF regtype[] >>> >>> And maybe another function to return attribute names. If it could >>> returns both names and types, that'd be cool, but I'm not sure what >>> kind >>> of data type that would be. >> >> It's possible to have it return SETOF RECORD with OUT parameters, I >> think. > > Out parameters? CREATE OR REPLACE FUNCTION pg_record_info( IN r RECORD, OUT "regtype" RETYPE, OUT "name" NAME, ) RETURNS SETOF RECORD LANGUAGE C AS 'filename', 'pg_record_info'; > I was thinking of SETOF RECORD with two attributes in each record: > name and type. Are there other attributes of RECORD attributes that > might be of interest? > >>> Does this sound interesting to other folks? >> >> Sure. Maybe that should go in pg_catalog in 8.5... > > Right, that was my thought. I could then throw it in pgTAP when > building on < 8.5. Right :) Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate