Horton
Menu

Thread

  1. Truncation of char, varchar types

    Peter Eisentraut <peter_e@gmx.net> — 2001-04-09T19:20:42Z

    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/

  2. Re: Truncation of char, varchar types

    Marc G. Fournier <scrappy@hub.org> — 2001-04-09T19:27:37Z

    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

  3. Re: Truncation of char, varchar types

    Nathan Myers <ncm@zembu.com> — 2001-04-09T20:30:26Z

    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

  4. Re: Truncation of char, varchar types

    Alessio Bragadini <alessio@albourne.com> — 2001-04-10T11:44:34Z

    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

  5. Re: Truncation of char, varchar types

    Peter Eisentraut <peter_e@gmx.net> — 2001-04-10T17:05:51Z

    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/

  6. Re: Truncation of char, varchar types

    Tom Lane <tgl@sss.pgh.pa.us> — 2001-04-13T05:16:43Z

    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

  7. Truncation of object names

    Nathan Myers <ncm@zembu.com> — 2001-04-13T17:50:31Z

    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

  8. Re: Truncation of object names

    Tom Lane <tgl@sss.pgh.pa.us> — 2001-04-13T18:54:47Z

    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

  9. Re: Truncation of object names

    Nathan Myers <ncm@zembu.com> — 2001-04-13T20:12:38Z

    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

  10. Re: Truncation of object names

    Tom Lane <tgl@sss.pgh.pa.us> — 2001-04-13T20:27:15Z

    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

  11. Re: Truncation of object names

    Joel Burton <jburton@scw.org> — 2001-04-13T20:33:09Z

    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

  12. Re: Truncation of object names

    Tom Lane <tgl@sss.pgh.pa.us> — 2001-04-13T20:55:24Z

    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

  13. Re: Truncation of object names

    Nathan Myers <ncm@zembu.com> — 2001-04-13T20:59:29Z

    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

  14. Re: Truncation of object names

    Joel Burton <jburton@scw.org> — 2001-04-13T21:08:46Z

    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

  15. Re: Truncation of object names

    Tom Lane <tgl@sss.pgh.pa.us> — 2001-04-13T21:45:52Z

    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

  16. RE: Truncation of object names

    Christopher Kings-Lynne <chriskl@familyhealth.com.au> — 2001-04-17T02:16:41Z

    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