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

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

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

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

The ability to place database objects into a logical partitioning of
data. For example, in Oracle, each user creates tables, views,
sequences, synonyms, and snapshots in their own schema. So if I were
to create a table called 'Employees', I could query it as:

SELECT * FROM employees;

But another user would have to query it as:

SELECT * FROM mascarm.employees;

A common case for this is to logically divide schema by departments.
You could do that now in PostgreSQL in the form of multiple
databases, but you couldn't query across them. For example, you might
have an "Accounting" schema, and an "Inventory" schema.
Occassionally, the accountants need to join tables from accounting
w/inventory. The inventory people (or the dba) would then grant
appropriate privileges for the accountants to do that, but the
accounts would have to fully qualify their queries:

SELECT * FROM inventory.orders;

So, if you want a logical division that also contain some shared
tables, views, or sequences (and hopefully snapshots, some day), in
Oracle, you can create public synonyms for the shared objects:

CREATE PUBLIC SYNONYM employees FOR mascarm.employees;

Now, anyone can query this table as:

SELECT * FROM employees;

Its a namespace thing, basically.

Hope that helps,

Mike Mascari
mascarm@mascari.com

-----Original Message-----
From: Christopher Kings-Lynne [SMTP:chriskl@familyhealth.com.au]
Sent: Monday, April 16, 2001 10:17 PM
To: pgsql-hackers@postgresql.org
Subject: RE: [HACKERS] Truncation of object names

Call me thick as two planks, but when you guys constantly refer to
'schema
support' in PostgreSQL, what exactly are you referring to?

Chris