Thread: Truncation of char, varchar types
Excessively long values are currently silently truncated when they are inserted into char or varchar fields. This makes the entire notion of specifying a length limit for these types kind of useless, IMO. Needless to say, it's also not in compliance with SQL. How do people feel about changing this to raise an error in this situation? Does anybody rely on silent truncation? Should this be user-settable, or can those people resort to using triggers? -- Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
On Mon, Apr 09, 2001 at 09:20:42PM +0200, Peter Eisentraut wrote: > Excessively long values are currently silently truncated when they are > inserted into char or varchar fields. This makes the entire notion of > specifying a length limit for these types kind of useless, IMO. Needless > to say, it's also not in compliance with SQL. > > How do people feel about changing this to raise an error in this > situation? Does anybody rely on silent truncation? Should this be > user-settable, or can those people resort to using triggers? Yes, detecting and reporting errors early is a Good Thing. You don't do anybody any favors by pretending to save data, but really throwing it away. We have noticed here also that object (e.g. table) names get truncated in some places and not others. If you create a table with a long name, PG truncates the name and creates a table with the shorter name; but if you refer to the table by the same long name, PG reports an error. (Very long names may show up in machine- generated schemas.) Would patches for this, e.g. to refuse to create a table with an impossible name, be welcome? Nathan Myers ncm@zembu.com
After v7.1 is released ... ? On Mon, 9 Apr 2001, Peter Eisentraut wrote: > Excessively long values are currently silently truncated when they are > inserted into char or varchar fields. This makes the entire notion of > specifying a length limit for these types kind of useless, IMO. Needless > to say, it's also not in compliance with SQL. > > How do people feel about changing this to raise an error in this > situation? Does anybody rely on silent truncation? Should this be > user-settable, or can those people resort to using triggers? > > -- > Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/ > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy Systems Administrator @ hub.org primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
Nathan Myers wrote: > (Very long names may show up in machine- generated schemas.) Would > patches for this, e.g. to refuse to create a table with an impossible > name, be welcome? Yes. And throw in the picture also the length of sequences coming from SERIALs, etc. -- Alessio F. Bragadini alessio@albourne.com APL Financial Services http://village.albourne.com Nicosia, Cyprus phone: +357-2-755750 "It is more complicated than you think" -- The Eighth Networking Truth from RFC 1925
This is what I get in Oracle 8:
SQL> CREATE TABLE test (value VARCHAR (10));
Table created.
SQL> INSERT INTO test VALUES ('Mike Mascari');
INSERT INTO test VALUES ('Mike Mascari') *
ERROR at line 1:
ORA-01401: inserted value too large for column
SQL> quit
Of course, if the standard is ambiguous, retaining backwards
compatibility sure would be nice.
FWIW,
Mike Mascari
mascarm@mascari.com
-----Original Message-----
From: Zeugswetter Andreas SB [SMTP:ZeugswetterA@wien.spardat.at]
Sent: Tuesday, April 10, 2001 6:47 AM
To: 'Peter Eisentraut'; PostgreSQL Development
Subject: AW: [HACKERS] Truncation of char, varchar types
> Excessively long values are currently silently truncated when they
are
> inserted into char or varchar fields. This makes the entire notion
of
> specifying a length limit for these types kind of useless, IMO. Needless
> to say, it's also not in compliance with SQL.
To quote Tom "paragraph and verse please" :-)
> How do people feel about changing this to raise an error in this
> situation?
Can't do.
> Does anybody rely on silent truncation?
Yes, iirc the only thing you are allowed to do is issue a warning,
but the truncation is allowed and must succeed.
(checked in Informix and Oracle)
The appropriate SQLSTATE is: "01004" String data, right truncation
note that class 01 is a "success with warning".
Andreas
Nathan Myers writes: > We have noticed here also that object (e.g. table) names get truncated > in some places and not others. If you create a table with a long name, > PG truncates the name and creates a table with the shorter name; but > if you refer to the table by the same long name, PG reports an error. This seems odd, because the truncation happens in the scanner. Care to provide a test case? > (Very long names may show up in machine- generated schemas.) Would > patches for this, e.g. to refuse to create a table with an impossible > name, be welcome? Tom Lane is opposed to this, although a number of people seem to like it. Sounds like a configuration option to me. -- Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
ncm@zembu.com (Nathan Myers) writes:
> We have noticed here also that object (e.g. table) names get truncated
> in some places and not others. If you create a table with a long name,
> PG truncates the name and creates a table with the shorter name; but
> if you refer to the table by the same long name, PG reports an error.
Example please? This is clearly a bug. It is also demonstrably not
the case in ordinary scenarios:
play=> create table a1234567890123456789012345678901234567890(f1 int);
NOTICE: identifier "a1234567890123456789012345678901234567890" will be truncated to "a123456789012345678901234567890"
CREATE
play=> select * from a1234567890123456789012345678901234567890;
NOTICE: identifier "a1234567890123456789012345678901234567890" will be truncated to
"a123456789012345678901234567890"f1
----
(0 rows)
play=> select * from "a1234567890123456789012345678901234567890";
NOTICE: identifier "a1234567890123456789012345678901234567890" will be truncated to
"a123456789012345678901234567890"f1
----
(0 rows)
I have a vague recollection that we found/fixed one or more such bugs in
isolated contexts during 7.1 development, so the issue may be gone
already.
regards, tom lane
On Fri, Apr 13, 2001 at 01:16:43AM -0400, Tom Lane wrote: > ncm@zembu.com (Nathan Myers) writes: > > We have noticed here also that object (e.g. table) names get truncated > > in some places and not others. If you create a table with a long name, > > PG truncates the name and creates a table with the shorter name; but > > if you refer to the table by the same long name, PG reports an error. > > Example please? This is clearly a bug. Sorry, false alarm. When I got the test case, it turned out to be the more familiar problem: create table foo_..._bar1 (id1 ...); [notice, "foo_..._bar1" truncated to "foo_..._bar"] create table foo_..._bar (id2...); [error, foo_..._bar already exists] create index foo_..._bar_ix on foo_..._bar(id2); [notice, "foo_..._bar_ix"truncated to "foo_..._bar"] [error, foo_..._bar already exists] [error, attribute "id2" not found] It would be more helpful for the first "create" to fail so we don't end up cluttered with objects that shouldn't exist, and which interfere with operations on objects which should. But I'm not proposing that for 7.1. Nathan Myers ncm@zembu.com
ncm@zembu.com (Nathan Myers) writes:
> Sorry, false alarm. When I got the test case, it turned out to
> be the more familiar problem:
> create table foo_..._bar1 (id1 ...);
> [notice, "foo_..._bar1" truncated to "foo_..._bar"]
> create table foo_..._bar (id2 ...);
> [error, foo_..._bar already exists]
> create index foo_..._bar_ix on foo_..._bar(id2);
> [notice, "foo_..._bar_ix" truncated to "foo_..._bar"]
> [error, foo_..._bar already exists]
> [error, attribute "id2" not found]
> It would be more helpful for the first "create" to fail so we don't
> end up cluttered with objects that shouldn't exist, and which interfere
> with operations on objects which should.
Seems to me that if you want a bunch of CREATEs to be mutually
dependent, then you wrap them all in a BEGIN/END block.
regards, tom lane
On Fri, Apr 13, 2001 at 02:54:47PM -0400, Tom Lane wrote: > ncm@zembu.com (Nathan Myers) writes: > > Sorry, false alarm. When I got the test case, it turned out to > > be the more familiar problem: > > > create table foo_..._bar1 (id1 ...); > > [notice, "foo_..._bar1" truncated to "foo_..._bar"] > > create table foo_..._bar (id2 ...); > > [error, foo_..._bar already exists] > > create index foo_..._bar_ix on foo_..._bar(id2); > > [notice, "foo_..._bar_ix" truncated to "foo_..._bar"] > > [error, foo_..._bar already exists] > > [error, attribute "id2" not found] > > > It would be more helpful for the first "create" to fail so we don't > > end up cluttered with objects that shouldn't exist, and which interfere > > with operations on objects which should. > > Seems to me that if you want a bunch of CREATEs to be mutually > dependent, then you wrap them all in a BEGIN/END block. Yes, but... The second and third commands weren't supposed to be related to the first at all, never mind dependent on it. They were made dependent by PG crushing the names together. We are thinking about working around the name length limitation (encountered in migrating from other dbs) by allowing "foo.bar.baz" name syntax, as a sort of rudimentary namespace mechanism. It ain't schemas, but it's better than "foo__bar__baz". Nathan Myers ncm@zembu.com
ncm@zembu.com (Nathan Myers) writes:
>> Seems to me that if you want a bunch of CREATEs to be mutually
>> dependent, then you wrap them all in a BEGIN/END block.
> Yes, but... The second and third commands weren't supposed to be
> related to the first at all, never mind dependent on it. They were
> made dependent by PG crushing the names together.
Good point.
> We are thinking about working around the name length limitation
> (encountered in migrating from other dbs) by allowing "foo.bar.baz"
> name syntax, as a sort of rudimentary namespace mechanism.
Have you thought about simply increasing NAMEDATALEN in your
installation? If you really are generating names that aren't unique
in 31 characters, that seems like the way to go ...
regards, tom lane
On Fri, 13 Apr 2001, Tom Lane wrote: > ncm@zembu.com (Nathan Myers) writes: > >> Seems to me that if you want a bunch of CREATEs to be mutually > >> dependent, then you wrap them all in a BEGIN/END block. > > > Yes, but... The second and third commands weren't supposed to be > > related to the first at all, never mind dependent on it. They were > > made dependent by PG crushing the names together. > > Good point. > > > We are thinking about working around the name length limitation > > (encountered in migrating from other dbs) by allowing "foo.bar.baz" > > name syntax, as a sort of rudimentary namespace mechanism. > > Have you thought about simply increasing NAMEDATALEN in your > installation? If you really are generating names that aren't unique > in 31 characters, that seems like the way to go ... Tom (or others) -- Other than (a) it wastes a bit of space in the pg_ tables, and (b) it may screw up postgresql utility programs (pgaccess, pgadmin, etc.), is there any reason to keep the default at 32? Are there performance limitations? (Will C-based triggers and client programs and such need to be modified?) I don't think that my tables are incredibly verbose, autogenerated sequence and index names often push the limit. The problem w/everyone compiling it at a higher number is that it makes it difficult to transparently move a PG database from one server to another. Thanks! -- Joel Burton <jburton@scw.org> Director of Information Systems, Support Center of Washington
On Fri, Apr 13, 2001 at 04:27:15PM -0400, Tom Lane wrote: > ncm@zembu.com (Nathan Myers) writes: > > We are thinking about working around the name length limitation > > (encountered in migrating from other dbs) by allowing "foo.bar.baz" > > name syntax, as a sort of rudimentary namespace mechanism. > > Have you thought about simply increasing NAMEDATALEN in your > installation? If you really are generating names that aren't unique > in 31 characters, that seems like the way to go ... We discussed that, and will probably do it (too). One problem is that, having translated "foo.bar.baz" to "foo_bar_baz", you have a problem when you encounter "foo.bar_baz" in subsequent code. I.e., a separate delimiter character helps, even when name length isn't an issue. Also, accepting the names as they appear in the source code already means the number of changes needed is much smaller, even when you don't have true schema support. Nathan Myers ncm@zembu.com
Joel Burton <jburton@scw.org> writes:
>> Have you thought about simply increasing NAMEDATALEN in your
>> installation? If you really are generating names that aren't unique
>> in 31 characters, that seems like the way to go ...
> Other than (a) it wastes a bit of space in the pg_ tables, and (b) it may
> screw up postgresql utility programs (pgaccess, pgadmin, etc.), is there
> any reason to keep the default at 32? Are there performance limitations?
Those are pretty much the reasons, plus a compatibility issue:
NAMEDATALEN *is* visible to clients (that's why it's in postgres_ext.h).
So changing the default value would risk breaking clients that hadn't
been recompiled.
> (Will C-based triggers and client programs and such need to be modified?)
Not if they've been properly coded (written in terms of NAMEDATALEN not
a hard constant).
Obviously, these objections are not strong enough to keep us from
increasing the standard value of NAMEDATALEN if it seems that many
people are running into the limit. But AFAICT relatively few people
have such problems, and I'm hesitant to make everyone deal with a change
for the benefit of a few. Count me as a weak vote for leaving it where
it is ...
regards, tom lane
On Fri, 13 Apr 2001, Tom Lane wrote: > Obviously, these objections are not strong enough to keep us from > increasing the standard value of NAMEDATALEN if it seems that many > people are running into the limit. But AFAICT relatively few people > have such problems, and I'm hesitant to make everyone deal with a change > for the benefit of a few. Count me as a weak vote for leaving it where > it is ... Hmm... Of course, it's Bad to break things if one doesn't have to. But (IMHO) its also bad to leave it at a setting that makes some group of people (~ 3%?) have to recompile it, and a larger group (~ 10%) wish they did/knew how to. (I, in general, share your hesistancy to break something for the benefit of the few, 'cept I'm one of the few this time. ;-) ) For some changes, one could just prewarn the world that This Is Coming, and they should anticipate it with 6 months notice or such. In this case, though, it would seem that knowing it was coming wouldn't help any -- you'd still have to recompile your client for the 32char names and the 64 (?) char names, during the 7.1 -> 7.2 (or 7.5 -> 8.0 or whatever) transition period. I'd like to see it longer -- is there any sane way of doing this with notice, or, as I fear, would it always be a pain, regardless of how much advance notice the world rec'd? Thanks, -- Joel Burton <jburton@scw.org> Director of Information Systems, Support Center of Washington
ncm@zembu.com (Nathan Myers) writes:
> On Fri, Apr 13, 2001 at 04:27:15PM -0400, Tom Lane wrote:
>> Have you thought about simply increasing NAMEDATALEN in your
>> installation? If you really are generating names that aren't unique
>> in 31 characters, that seems like the way to go ...
> We discussed that, and will probably do it (too).
> One problem is that, having translated "foo.bar.baz" to "foo_bar_baz",
> you have a problem when you encounter "foo.bar_baz" in subsequent code.
So it's not really so much that NAMEDATALEN is too short for your
individual names, it's that you are concatenating names as a workaround
for the lack of schema support.
FWIW, I believe schemas are very high on the priority list for 7.2 ...
regards, tom lane
Call me thick as two planks, but when you guys constantly refer to 'schema
support' in PostgreSQL, what exactly are you referring to?
Chris
-----Original Message-----
From: pgsql-hackers-owner@postgresql.org
[mailto:pgsql-hackers-owner@postgresql.org]On Behalf Of Tom Lane
Sent: Saturday, 14 April 2001 5:46 AM
To: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Truncation of object names
ncm@zembu.com (Nathan Myers) writes:
> On Fri, Apr 13, 2001 at 04:27:15PM -0400, Tom Lane wrote:
>> Have you thought about simply increasing NAMEDATALEN in your
>> installation? If you really are generating names that aren't unique
>> in 31 characters, that seems like the way to go ...
> We discussed that, and will probably do it (too).
> One problem is that, having translated "foo.bar.baz" to "foo_bar_baz",
> you have a problem when you encounter "foo.bar_baz" in subsequent code.
So it's not really so much that NAMEDATALEN is too short for your
individual names, it's that you are concatenating names as a workaround
for the lack of schema support.
FWIW, I believe schemas are very high on the priority list for 7.2 ...
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl