Inconsistent error message for varchar(n)
Hi, hackers
When I try to create table that has a varchar(n) data type, I find an
inconsistent error message for it.
postgres=# CREATE TABLE tbl (s varchar(2147483647));
ERROR: length for type varchar cannot exceed 10485760
LINE 1: CREATE TABLE tbl (s varchar(2147483647));
^
postgres=# CREATE TABLE tbl (s varchar(2147483648));
ERROR: syntax error at or near "2147483648"
LINE 1: CREATE TABLE tbl (s varchar(2147483648));
^
I find that in gram.y the varchar has an integer parameter which
means its value don't exceed 2147483647.
The first error message is reported by anychar_typmodin(), and the later
is reported by gram.y. IMO, the syntax error for varchar(n) is more
confused.
Any thoughts?
--
Regrads,
Japin Li.
ChengDu WenWu Information Technology Co.,Ltd.
Japin Li <japinli@hotmail.com> writes:
postgres=# CREATE TABLE tbl (s varchar(2147483647));
ERROR: length for type varchar cannot exceed 10485760
LINE 1: CREATE TABLE tbl (s varchar(2147483647));
^
postgres=# CREATE TABLE tbl (s varchar(2147483648));
ERROR: syntax error at or near "2147483648"
LINE 1: CREATE TABLE tbl (s varchar(2147483648));
^
I'm having a very hard time getting excited about that. We could maybe
switch the grammar production to use generic expr_list syntax for the
typmod, like GenericType does. But that would just result in this:
regression=# CREATE TABLE tbl (s "varchar"(2147483648));
ERROR: value "2147483648" is out of range for type integer
LINE 1: CREATE TABLE tbl (s "varchar"(2147483648));
^
which doesn't seem any less confusing for a novice who doesn't know
that typmods are constrained to be integers.
There might be something to be said for switching all the hard-wired
type productions to use opt_type_modifiers and pushing the knowledge
that's in, eg, opt_float out to per-type typmodin routines. But any
benefit would be in reduction of the grammar size, and I'm dubious
that it'd be worth the trouble. I suspect that overall, the resulting
error messages would be slightly worse not better --- note for example
the poorer placement of the error cursor above. A related example is
regression=# CREATE TABLE tbl (s varchar(2,3));
ERROR: syntax error at or near ","
LINE 1: CREATE TABLE tbl (s varchar(2,3));
^
regression=# CREATE TABLE tbl (s "varchar"(2,3));
ERROR: invalid type modifier
LINE 1: CREATE TABLE tbl (s "varchar"(2,3));
^
That's explained by the comment in anychar_typmodin:
* we're not too tense about good error message here because grammar
* shouldn't allow wrong number of modifiers for CHAR
and we could surely improve that message, but anychar_typmodin can't give
a really on-point error cursor.
regards, tom lane
On Sat, 13 Nov 2021 at 23:42, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Japin Li <japinli@hotmail.com> writes:
postgres=# CREATE TABLE tbl (s varchar(2147483647));
ERROR: length for type varchar cannot exceed 10485760
LINE 1: CREATE TABLE tbl (s varchar(2147483647));
^postgres=# CREATE TABLE tbl (s varchar(2147483648));
ERROR: syntax error at or near "2147483648"
LINE 1: CREATE TABLE tbl (s varchar(2147483648));
^I'm having a very hard time getting excited about that. We could maybe
switch the grammar production to use generic expr_list syntax for the
typmod, like GenericType does. But that would just result in this:regression=# CREATE TABLE tbl (s "varchar"(2147483648));
ERROR: value "2147483648" is out of range for type integer
LINE 1: CREATE TABLE tbl (s "varchar"(2147483648));
^which doesn't seem any less confusing for a novice who doesn't know
that typmods are constrained to be integers.There might be something to be said for switching all the hard-wired
type productions to use opt_type_modifiers and pushing the knowledge
that's in, eg, opt_float out to per-type typmodin routines. But any
benefit would be in reduction of the grammar size, and I'm dubious
that it'd be worth the trouble. I suspect that overall, the resulting
error messages would be slightly worse not better --- note for example
the poorer placement of the error cursor above. A related example isregression=# CREATE TABLE tbl (s varchar(2,3));
ERROR: syntax error at or near ","
LINE 1: CREATE TABLE tbl (s varchar(2,3));
^
regression=# CREATE TABLE tbl (s "varchar"(2,3));
ERROR: invalid type modifier
LINE 1: CREATE TABLE tbl (s "varchar"(2,3));
^That's explained by the comment in anychar_typmodin:
* we're not too tense about good error message here because grammar
* shouldn't allow wrong number of modifiers for CHARand we could surely improve that message, but anychar_typmodin can't give
a really on-point error cursor.
Oh! I didn't consider this situation. Since the max size of varchar cannot
exceed 10485760, however, I cannot find this in documentation [1]https://www.postgresql.org/docs/devel/datatype-character.html. Is there
something I missed? Should we mention this in the documentation?
[1]: https://www.postgresql.org/docs/devel/datatype-character.html
--
Regrads,
Japin Li.
ChengDu WenWu Information Technology Co.,Ltd.
[ Please trim quotes appropriately when replying. Nobody wants to
read the whole history of the thread to get to your comment. ]
Japin Li <japinli@hotmail.com> writes:
Oh! I didn't consider this situation. Since the max size of varchar cannot
exceed 10485760, however, I cannot find this in documentation [1]. Is there
something I missed? Should we mention this in the documentation?
[1] https://www.postgresql.org/docs/devel/datatype-character.html
I dunno, that section doesn't really get into implementation limits.
For comparison, it doesn't bring up the point that string values are
constrained to 1GB; that's dealt with elsewhere. Since the limit on
typmod is substantially more than that, I'm not sure there's much point
in mentioning it specifically. Maybe there's a case for mentioning the
1GB limit here, though.
regards, tom lane
I wrote:
For comparison, it doesn't bring up the point that string values are
constrained to 1GB; that's dealt with elsewhere. Since the limit on
typmod is substantially more than that, I'm not sure there's much point
in mentioning it specifically.
Oh, wait, I was not counting the zeroes in that number :-(
Tracking it a bit further, the actual typmod limit is set by this:
/*
* MaxAttrSize is a somewhat arbitrary upper limit on the declared size of
* data fields of char(n) and similar types. It need not have anything
* directly to do with the *actual* upper limit of varlena values, which
* is currently 1Gb (see TOAST structures in postgres.h). I've set it
* at 10Mb which seems like a reasonable number --- tgl 8/6/00.
*/
#define MaxAttrSize (10 * 1024 * 1024)
So maybe that's something we *should* document, though we'd have to
explain that the limit on text and unconstrained varchar is different.
regards, tom lane
(From the writing style, I suspect the "tgl" here is me not Tom Lockhart.
I'm too lazy to dig in the git history to confirm it though.)
Tom Lane <tgl@sss.pgh.pa.us> writes:
Tracking it a bit further, the actual typmod limit is set by this:
/*
* MaxAttrSize is a somewhat arbitrary upper limit on the declared size of
* data fields of char(n) and similar types. It need not have anything
* directly to do with the *actual* upper limit of varlena values, which
* is currently 1Gb (see TOAST structures in postgres.h). I've set it
* at 10Mb which seems like a reasonable number --- tgl 8/6/00.
*/
#define MaxAttrSize (10 * 1024 * 1024)So maybe that's something we *should* document, though we'd have to
explain that the limit on text and unconstrained varchar is different.regards, tom lane
(From the writing style, I suspect the "tgl" here is me not Tom Lockhart.
I'm too lazy to dig in the git history to confirm it though.)
I was bored, and found this:
commit 022417740094620880488dd9b04fbb96ff11694b
Author: Tom Lane <tgl@sss.pgh.pa.us>
Date: 2000-08-07 20:16:13 +0000
TOAST mop-up work: update comments for tuple-size-related symbols such
as MaxHeapAttributeNumber. Increase MaxAttrSize to something more
reasonable (given what it's used for, namely checking char(n) declarations,
I didn't make it the full 1G that it could theoretically be --- 10Mb
seemed a more reasonable number). Improve calculation of MaxTupleSize.
which added the above comment and changed MaxAttrSize:
-#define MaxAttrSize (MaxTupleSize - MAXALIGN(sizeof(HeapTupleHeaderData)))
+#define MaxAttrSize (10 * 1024 * 1024)- ilmari
On Sun, Nov 14, 2021 at 10:33:19AM +0800, Japin Li wrote:
On Sat, 13 Nov 2021 at 23:42, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Japin Li <japinli@hotmail.com> writes:
postgres=# CREATE TABLE tbl (s varchar(2147483647));
ERROR: length for type varchar cannot exceed 10485760
LINE 1: CREATE TABLE tbl (s varchar(2147483647));
^postgres=# CREATE TABLE tbl (s varchar(2147483648));
ERROR: syntax error at or near "2147483648"
LINE 1: CREATE TABLE tbl (s varchar(2147483648));
^I'm having a very hard time getting excited about that. We could maybe
switch the grammar production to use generic expr_list syntax for the
typmod, like GenericType does. But that would just result in this:regression=# CREATE TABLE tbl (s "varchar"(2147483648));
ERROR: value "2147483648" is out of range for type integer
LINE 1: CREATE TABLE tbl (s "varchar"(2147483648));
^which doesn't seem any less confusing for a novice who doesn't know
that typmods are constrained to be integers.There might be something to be said for switching all the hard-wired
type productions to use opt_type_modifiers and pushing the knowledge
that's in, eg, opt_float out to per-type typmodin routines. But any
benefit would be in reduction of the grammar size, and I'm dubious
that it'd be worth the trouble. I suspect that overall, the resulting
error messages would be slightly worse not better --- note for example
the poorer placement of the error cursor above. A related example isregression=# CREATE TABLE tbl (s varchar(2,3));
ERROR: syntax error at or near ","
LINE 1: CREATE TABLE tbl (s varchar(2,3));
^
regression=# CREATE TABLE tbl (s "varchar"(2,3));
ERROR: invalid type modifier
LINE 1: CREATE TABLE tbl (s "varchar"(2,3));
^That's explained by the comment in anychar_typmodin:
* we're not too tense about good error message here because grammar
* shouldn't allow wrong number of modifiers for CHARand we could surely improve that message, but anychar_typmodin can't give
a really on-point error cursor.Oh! I didn't consider this situation. Since the max size of varchar cannot
exceed 10485760, however, I cannot find this in documentation [1]. Is there
something I missed? Should we mention this in the documentation?[1] https://www.postgresql.org/docs/devel/datatype-character.html
Sorry for my long delay in reviewing this issue. You are correct this
should be documented --- patch attached.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Indecision is a decision. Inaction is an action. Mark Batterson
Attachments:
varchar.difftext/x-diff; charset=us-asciiDownload
diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml
index 4cc9e59270..07c3654b21 100644
--- a/doc/src/sgml/datatype.sgml
+++ b/doc/src/sgml/datatype.sgml
@@ -1217,6 +1217,8 @@ SELECT '52093.89'::money::numeric::float8;
<type>char(<replaceable>n</replaceable>)</type> are aliases for <type>character
varying(<replaceable>n</replaceable>)</type> and
<type>character(<replaceable>n</replaceable>)</type>, respectively.
+ The length specification must be greater than zero and cannot
+ exceed 10485760.
<type>character</type> without length specifier is equivalent to
<type>character(1)</type>. If <type>character varying</type> is used
without length specifier, the type accepts strings of any size. The
8On Tue, Aug 16, 2022 at 09:56:17PM -0400, Bruce Momjian wrote:
On Sun, Nov 14, 2021 at 10:33:19AM +0800, Japin Li wrote:
Oh! I didn't consider this situation. Since the max size of varchar cannot
exceed 10485760, however, I cannot find this in documentation [1]. Is there
something I missed? Should we mention this in the documentation?[1] https://www.postgresql.org/docs/devel/datatype-character.html
Sorry for my long delay in reviewing this issue. You are correct this
should be documented --- patch attached.
Patch applied back to PG 10. Thanks for the report.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Indecision is a decision. Inaction is an action. Mark Batterson