Truncation of char, varchar types

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

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

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 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

Zeugswetter Andreas SB wrote:

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)

? As much as I remember, Oracle raises an error. But it's been a few
years since I last touched it, so maybe I'm wrong.

--
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

Zeugswetter Andreas SB writes:

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" :-)

SQL 1992, 9.2 GR 3 e)

"""
If the data type of T is variable-length character string and
the length in characters M of V is greater than the maximum
length in characters L of T, then,

Case:

i) If the rightmost M-L characters of V are all <space>s, then
the value of T is set to the first L characters of V and
the length in characters of T is set to L.

ii) If one or more of the rightmost M-L characters of V are
not <space>s, then an exception condition is raised: data
^^^^^^^^^
exception-string data, right truncation.
"""

Similarly in SQL 1999 and for other data types.

How do people feel about changing this to raise an error in this
situation?

Can't do.

Why not?

--
Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/

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/

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" :-)

SQL 1992, 9.2 GR 3 e)

"""
If the data type of T is variable-length character string and
the length in characters M of V is greater than the maximum
length in characters L of T, then,

Case:

i) If the rightmost M-L characters of V are all <space>s, then
the value of T is set to the first L characters of V and
the length in characters of T is set to L.

ii) If one or more of the rightmost M-L characters of V are
not <space>s, then an exception condition is raised: data
^^^^^^^^^
exception-string data, right truncation.
"""

Thank you. Is an "exception condition" necessarily an error, or
is a warning also an exception condition ?

Similarly in SQL 1999 and for other data types.

How do people feel about changing this to raise an error in this
situation?

Can't do.

Why not?

Because other db's only raise a warning. Of course we don't want to
copy that behavior if they are not conformant. See above question.

Andreas

Zeugswetter Andreas SB writes:

Thank you. Is an "exception condition" necessarily an error, or
is a warning also an exception condition ?

A warning/notice is called a "completion condition".

Because other db's only raise a warning. Of course we don't want to
copy that behavior if they are not conformant. See above question.

Someone said Oracle raises an error. Informix seems to be the only other
db that truncates silently. I think Oracle wins here...

--
Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/

Thank you. Is an "exception condition" necessarily an error, or
is a warning also an exception condition ?

A warning/notice is called a "completion condition".

Because other db's only raise a warning. Of course we don't want to
copy that behavior if they are not conformant. See above question.

Someone said Oracle raises an error.

Yes, I am very sorry.

Informix seems to be the only other db that truncates silently.

Raises a warning instead of error. Would need to check Sybase and DB2, but ...

I think Oracle wins here...

Yes, good. Do we want this in 7.1.0 ? Seems, yes :-(

Andreas

Someone said Oracle raises an error.
Informix seems to be the only other db that truncates silently.

Raises a warning instead of error. Would need to check Sybase and DB2, but ...
Yes, good. Do we want this in 7.1.0 ? Seems, yes :-(

No, pretty sure this will not fly for the 7.1.x cycle, no matter what we
end up deciding is the best approach (with SQL9x getting more votes than
most other proposals, but The Right Thing having the most votes of all
;)

We are at the tail end of the beta cycle, and a change in behavior which
is this fundamental needs more time and discussion. All imho of
course...

- Thomas

Zeugswetter Andreas SB writes:

Yes, good. Do we want this in 7.1.0 ? Seems, yes :-(

No way. I'm just giving some food for thought while development is slow.

In any case there seems to be support for the proposed feature. I'm just
waiting for someone to complain that he relies on the existing behaviour,
but I doubt that.

--
Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/

Zeugswetter Andreas SB wrote:

Yes, good. Do we want this in 7.1.0 ? Seems, yes :-(

I agree this change is very good idea, but 7.2 is probably a better target.

- Mark Butler

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