Skip to content

Commit 1939d26

Browse files
tglsfdctglsfdc
committed
Add test scaffolding for soft error reporting from input functions.
pg_input_is_valid() returns boolean, while pg_input_error_message() returns the primary error message if the input is bad, or NULL if the input is OK. The main reason for having two functions is so that we can test both the details-wanted and the no-details-wanted code paths. Although these are primarily designed with testing in mind, it could well be that they'll be useful to end users as well. This patch is mostly by me, but it owes very substantial debt to earlier work by Nikita Glukhov, Andrew Dunstan, and Amul Sul. Thanks to Andres Freund for review. Discussion: https://postgr.es/m/3bbbb0df-7382-bf87-9737-340ba096e034@postgrespro.ru
1 parent d9f7f5d commit 1939d26

File tree

7 files changed

+277
-1
lines changed

7 files changed

+277
-1
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 101 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -24683,6 +24683,107 @@ SELECT collation for ('foo' COLLATE "de_DE");
2468324683

2468424684
</sect2>
2468524685

24686+
<sect2 id="functions-info-validity">
24687+
<title>Data Validity Checking Functions</title>
24688+
24689+
<para>
24690+
The functions shown in <xref linkend="functions-info-validity-table"/>
24691+
can be helpful for checking validity of proposed input data.
24692+
</para>
24693+
24694+
<table id="functions-info-validity-table">
24695+
<title>Data Validity Checking Functions</title>
24696+
<tgroup cols="1">
24697+
<thead>
24698+
<row>
24699+
<entry role="func_table_entry"><para role="func_signature">
24700+
Function
24701+
</para>
24702+
<para>
24703+
Description
24704+
</para>
24705+
<para>
24706+
Example(s)
24707+
</para></entry>
24708+
</row>
24709+
</thead>
24710+
24711+
<tbody>
24712+
<row>
24713+
<entry role="func_table_entry"><para role="func_signature">
24714+
<indexterm>
24715+
<primary>pg_input_is_valid</primary>
24716+
</indexterm>
24717+
<function>pg_input_is_valid</function> (
24718+
<parameter>string</parameter> <type>text</type>,
24719+
<parameter>type</parameter> <type>text</type>
24720+
)
24721+
<returnvalue>boolean</returnvalue>
24722+
</para>
24723+
<para>
24724+
Tests whether the given <parameter>string</parameter> is valid
24725+
input for the specified data type, returning true or false.
24726+
</para>
24727+
<para>
24728+
This function will only work as desired if the data type's input
24729+
function has been updated to report invalid input as
24730+
a <quote>soft</quote> error. Otherwise, invalid input will abort
24731+
the transaction, just as if the string had been cast to the type
24732+
directly.
24733+
</para>
24734+
<para>
24735+
<literal>pg_input_is_valid('42', 'integer')</literal>
24736+
<returnvalue>t</returnvalue>
24737+
</para>
24738+
<para>
24739+
<literal>pg_input_is_valid('42000000000', 'integer')</literal>
24740+
<returnvalue>f</returnvalue>
24741+
</para>
24742+
<para>
24743+
<literal>pg_input_is_valid('1234.567', 'numeric(7,4)')</literal>
24744+
<returnvalue>f</returnvalue>
24745+
</para></entry>
24746+
</row>
24747+
<row>
24748+
<entry role="func_table_entry"><para role="func_signature">
24749+
<indexterm>
24750+
<primary>pg_input_error_message</primary>
24751+
</indexterm>
24752+
<function>pg_input_error_message</function> (
24753+
<parameter>string</parameter> <type>text</type>,
24754+
<parameter>type</parameter> <type>text</type>
24755+
)
24756+
<returnvalue>text</returnvalue>
24757+
</para>
24758+
<para>
24759+
Tests whether the given <parameter>string</parameter> is valid
24760+
input for the specified data type; if not, return the error
24761+
message that would have been thrown. If the input is valid, the
24762+
result is NULL. The inputs are the same as
24763+
for <function>pg_input_is_valid</function>.
24764+
</para>
24765+
<para>
24766+
This function will only work as desired if the data type's input
24767+
function has been updated to report invalid input as
24768+
a <quote>soft</quote> error. Otherwise, invalid input will abort
24769+
the transaction, just as if the string had been cast to the type
24770+
directly.
24771+
</para>
24772+
<para>
24773+
<literal>pg_input_error_message('42000000000', 'integer')</literal>
24774+
<returnvalue>value "42000000000" is out of range for type integer</returnvalue>
24775+
</para>
24776+
<para>
24777+
<literal>pg_input_error_message('1234.567', 'numeric(7,4)')</literal>
24778+
<returnvalue>numeric field overflow</returnvalue>
24779+
</para></entry>
24780+
</row>
24781+
</tbody>
24782+
</tgroup>
24783+
</table>
24784+
24785+
</sect2>
24786+
2468624787
<sect2 id="functions-info-snapshot">
2468724788
<title>Transaction ID and Snapshot Information Functions</title>
2468824789

‎src/backend/utils/adt/misc.c

Lines changed: 129 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -32,6 +32,8 @@
3232
#include "common/keywords.h"
3333
#include "funcapi.h"
3434
#include "miscadmin.h"
35+
#include "nodes/miscnodes.h"
36+
#include "parser/parse_type.h"
3537
#include "parser/scansup.h"
3638
#include "pgstat.h"
3739
#include "postmaster/syslogger.h"
@@ -45,6 +47,25 @@
4547
#include "utils/ruleutils.h"
4648
#include "utils/timestamp.h"
4749

50+
51+
/*
52+
* structure to cache metadata needed in pg_input_is_valid_common
53+
*/
54+
typedef struct ValidIOData
55+
{
56+
Oid typoid;
57+
int32 typmod;
58+
bool typname_constant;
59+
Oid typiofunc;
60+
Oid typioparam;
61+
FmgrInfo inputproc;
62+
} ValidIOData;
63+
64+
static bool pg_input_is_valid_common(FunctionCallInfo fcinfo,
65+
text *txt, text *typname,
66+
ErrorSaveContext *escontext);
67+
68+
4869
/*
4970
* Common subroutine for num_nulls() and num_nonnulls().
5071
* Returns true if successful, false if function should return NULL.
@@ -640,6 +661,114 @@ pg_column_is_updatable(PG_FUNCTION_ARGS)
640661
}
641662

642663

664+
/*
665+
* pg_input_is_valid - test whether string is valid input for datatype.
666+
*
667+
* Returns true if OK, false if not.
668+
*
669+
* This will only work usefully if the datatype's input function has been
670+
* updated to return "soft" errors via errsave/ereturn.
671+
*/
672+
Datum
673+
pg_input_is_valid(PG_FUNCTION_ARGS)
674+
{
675+
text *txt = PG_GETARG_TEXT_PP(0);
676+
text *typname = PG_GETARG_TEXT_PP(1);
677+
ErrorSaveContext escontext = {T_ErrorSaveContext};
678+
679+
PG_RETURN_BOOL(pg_input_is_valid_common(fcinfo, txt, typname,
680+
&escontext));
681+
}
682+
683+
/*
684+
* pg_input_error_message - test whether string is valid input for datatype.
685+
*
686+
* Returns NULL if OK, else the primary message string from the error.
687+
*
688+
* This will only work usefully if the datatype's input function has been
689+
* updated to return "soft" errors via errsave/ereturn.
690+
*/
691+
Datum
692+
pg_input_error_message(PG_FUNCTION_ARGS)
693+
{
694+
text *txt = PG_GETARG_TEXT_PP(0);
695+
text *typname = PG_GETARG_TEXT_PP(1);
696+
ErrorSaveContext escontext = {T_ErrorSaveContext};
697+
698+
/* Enable details_wanted */
699+
escontext.details_wanted = true;
700+
701+
if (pg_input_is_valid_common(fcinfo, txt, typname,
702+
&escontext))
703+
PG_RETURN_NULL();
704+
705+
Assert(escontext.error_occurred);
706+
Assert(escontext.error_data != NULL);
707+
Assert(escontext.error_data->message != NULL);
708+
709+
PG_RETURN_TEXT_P(cstring_to_text(escontext.error_data->message));
710+
}
711+
712+
/* Common subroutine for the above */
713+
static bool
714+
pg_input_is_valid_common(FunctionCallInfo fcinfo,
715+
text *txt, text *typname,
716+
ErrorSaveContext *escontext)
717+
{
718+
char *str = text_to_cstring(txt);
719+
ValidIOData *my_extra;
720+
Datum converted;
721+
722+
/*
723+
* We arrange to look up the needed I/O info just once per series of
724+
* calls, assuming the data type doesn't change underneath us.
725+
*/
726+
my_extra = (ValidIOData *) fcinfo->flinfo->fn_extra;
727+
if (my_extra == NULL)
728+
{
729+
fcinfo->flinfo->fn_extra =
730+
MemoryContextAlloc(fcinfo->flinfo->fn_mcxt,
731+
sizeof(ValidIOData));
732+
my_extra = (ValidIOData *) fcinfo->flinfo->fn_extra;
733+
my_extra->typoid = InvalidOid;
734+
/* Detect whether typname argument is constant. */
735+
my_extra->typname_constant = get_fn_expr_arg_stable(fcinfo->flinfo, 1);
736+
}
737+
738+
/*
739+
* If the typname argument is constant, we only need to parse it the first
740+
* time through.
741+
*/
742+
if (my_extra->typoid == InvalidOid || !my_extra->typname_constant)
743+
{
744+
char *typnamestr = text_to_cstring(typname);
745+
Oid typoid;
746+
747+
/* Parse type-name argument to obtain type OID and encoded typmod. */
748+
parseTypeString(typnamestr, &typoid, &my_extra->typmod, false);
749+
750+
/* Update type-specific info if typoid changed. */
751+
if (my_extra->typoid != typoid)
752+
{
753+
getTypeInputInfo(typoid,
754+
&my_extra->typiofunc,
755+
&my_extra->typioparam);
756+
fmgr_info_cxt(my_extra->typiofunc, &my_extra->inputproc,
757+
fcinfo->flinfo->fn_mcxt);
758+
my_extra->typoid = typoid;
759+
}
760+
}
761+
762+
/* Now we can try to perform the conversion. */
763+
return InputFunctionCallSafe(&my_extra->inputproc,
764+
str,
765+
my_extra->typioparam,
766+
my_extra->typmod,
767+
(Node *) escontext,
768+
&converted);
769+
}
770+
771+
643772
/*
644773
* Is character a valid identifier start?
645774
* Must match scan.l's {ident_start} character class.

‎src/include/catalog/catversion.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -57,6 +57,6 @@
5757
*/
5858

5959
/* yyyymmddN */
60-
#define CATALOG_VERSION_NO 202212091
60+
#define CATALOG_VERSION_NO 202212092
6161

6262
#endif

‎src/include/catalog/pg_proc.dat

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -7060,6 +7060,14 @@
70607060
prorettype => 'regnamespace', proargtypes => 'text',
70617061
prosrc => 'to_regnamespace' },
70627062

7063+
{ oid => '8050', descr => 'test whether string is valid input for data type',
7064+
proname => 'pg_input_is_valid', provolatile => 's', prorettype => 'bool',
7065+
proargtypes => 'text text', prosrc => 'pg_input_is_valid' },
7066+
{ oid => '8051',
7067+
descr => 'get error message if string is not valid input for data type',
7068+
proname => 'pg_input_error_message', provolatile => 's', prorettype => 'text',
7069+
proargtypes => 'text text', prosrc => 'pg_input_error_message' },
7070+
70637071
{ oid => '1268',
70647072
descr => 'parse qualified identifier to array of identifiers',
70657073
proname => 'parse_ident', prorettype => '_text', proargtypes => 'text bool',

‎src/test/regress/expected/create_type.out

Lines changed: 25 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -249,6 +249,31 @@ select format_type('bpchar'::regtype, -1);
249249
bpchar
250250
(1 row)
251251

252+
-- Test non-error-throwing APIs using widget, which still throws errors
253+
SELECT pg_input_is_valid('(1,2,3)', 'widget');
254+
pg_input_is_valid
255+
-------------------
256+
t
257+
(1 row)
258+
259+
SELECT pg_input_is_valid('(1,2)', 'widget'); -- hard error expected
260+
ERROR: invalid input syntax for type widget: "(1,2)"
261+
SELECT pg_input_is_valid('{"(1,2,3)"}', 'widget[]');
262+
pg_input_is_valid
263+
-------------------
264+
t
265+
(1 row)
266+
267+
SELECT pg_input_is_valid('{"(1,2)"}', 'widget[]'); -- hard error expected
268+
ERROR: invalid input syntax for type widget: "(1,2)"
269+
SELECT pg_input_is_valid('("(1,2,3)")', 'mytab');
270+
pg_input_is_valid
271+
-------------------
272+
t
273+
(1 row)
274+
275+
SELECT pg_input_is_valid('("(1,2)")', 'mytab'); -- hard error expected
276+
ERROR: invalid input syntax for type widget: "(1,2)"
252277
-- Test creation of an operator over a user-defined type
253278
CREATE FUNCTION pt_in_widget(point, widget)
254279
RETURNS bool

‎src/test/regress/regress.c

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -183,6 +183,11 @@ widget_in(PG_FUNCTION_ARGS)
183183
coord[i++] = p + 1;
184184
}
185185

186+
/*
187+
* Note: DON'T convert this error to "soft" style (errsave/ereturn). We
188+
* want this data type to stay permanently in the hard-error world so that
189+
* it can be used for testing that such cases still work reasonably.
190+
*/
186191
if (i < NARGS)
187192
ereport(ERROR,
188193
(errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),

‎src/test/regress/sql/create_type.sql

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -192,6 +192,14 @@ select format_type('bpchar'::regtype, null);
192192
-- this behavior difference is intentional
193193
select format_type('bpchar'::regtype, -1);
194194

195+
-- Test non-error-throwing APIs using widget, which still throws errors
196+
SELECT pg_input_is_valid('(1,2,3)', 'widget');
197+
SELECT pg_input_is_valid('(1,2)', 'widget'); -- hard error expected
198+
SELECT pg_input_is_valid('{"(1,2,3)"}', 'widget[]');
199+
SELECT pg_input_is_valid('{"(1,2)"}', 'widget[]'); -- hard error expected
200+
SELECT pg_input_is_valid('("(1,2,3)")', 'mytab');
201+
SELECT pg_input_is_valid('("(1,2)")', 'mytab'); -- hard error expected
202+
195203
-- Test creation of an operator over a user-defined type
196204

197205
CREATE FUNCTION pt_in_widget(point, widget)

0 commit comments

Comments
 (0)