Re: [HACKERS] Re: [SQL] Column name's length - Mailing list pgsql-hackers

Zalman Stern <zalman@netcom.com> writes:
>> How about something like this: if the code finds that the names are
>> too long when forming an implicit index name, it truncates the names
>> to fit, and you are OK as long as the truncated name is unique.

> This only partially solves the problem and can introduce bugs into code
> which is only reading from a database. When someone is setting up the
> database to work on the system, they'll in theory get a failure so they
> know it won't work. This really isn't true for our software though because
> we have functions which dynamically query a table to see what columns it
> has. In theory two queries for different longnames can resolve to the same
> column name.

Um, no, I don't think this has anything to do with whether you can
distinguish the names of different columns in a table.

What we are talking about is the names generated for indexes and
sequences that are needed to implement PRIMARY KEY and SERIAL column
attributes.  Ideally these names are completely invisible to an SQL
application --- there's certainly no direct need for the app to know
about them.  We could eliminate the whole issue if we generated names
along the lines of "pg_pkey_idx_48812091".  But when you are looking at
the system catalogs it is useful to be able to tell what's what by eye.
So we compromise by generating names that include the table and column
name for which we're creating an implicit index or sequence.

The problem is that this implementation-detail-that-should-be-invisible
*is* visible to an SQL application, because it restricts the SQL app's
choice of table and column names.  We need to avoid that restriction,
or at least reduce it as much as we can.  I'm willing to sacrifice
a little bit of SQL naming freedom to preserve readability of the names
generated behind the scenes, but putting a hard limit on name length
is too much sacrifice.  (This is more a question of designer's taste
than anything else --- you're certainly free to argue for a different
tradeoff point.  But it is a tradeoff; there's no perfect solution.)

> It is also a backwards compatibility hassle if you ever want to increase
> the number of significant characters in the name. This is because the
> existing database only knows the first 32 characters and *must* ignore
> anything after that in lookups. You would have to keep track of which names
> are "old style" and which are new. Why set yourself up like that?

No, because the SQL app should never need to know these names at all.
If they change, it won't affect app code.  Increasing NAMEDATALEN
could not cause two table+field names to conflict where they did not
conflict before, so I see no risk there.
        regards, tom lane