Re: [HACKERS] Long table names

On Mon, Aug 16, 1999 at 07:45:47PM +0100, Finn Kettner wrote:

Hi there.

I remember someone talking about compiling pgsql so that it could use
table names longer than 32 bytes, but that it would require some
changes in the source code. Could anyone tell me what changes these
are, and how safe it would be to do it (that is, should I assume that
I could just compile a newer version making the same changes to the
sources, and have anyone experienced anything broken using the longer
table names)???

Yours faithfully.
Finn Kettner.
PS. The main reasong for the longer table names is not the tables
themself, but the indexes etc. that are constructed automatically
using e.g. serial fields.

Finn -
The subsidary problem has been partially fixed in 6.5, at the slight cost
of making it slightly more difficult to predict the name of the serial
(or index?). Here's an example from one of my databases:

I've got a table named "PersonnelAddresses", with a primary key of serial type,
called "PerAddrID", as so:

idas_proto=> \d "PersonnelAddresses" 
Table    = PersonnelAddresses
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                | Length|
+----------------------------------+----------------------------------+-------+
| PerAddrID                        | int4 not null default nextval('" |     4 |
| PerIndex                         | int4                             |     4 |
| InstID                           | int4                             |     4 |
| Department                       | text                             |   var |
| Street                           | text                             |   var |
| Street2                          | text                             |   var |
| City                             | text                             |   var |
| spID                             | int4                             |     4 |
| PostalCode                       | text                             |   var |
| CountryID                        | int4                             |     4 |
| Organization                     | text                             |   var |
| AddrType                         | text                             |   var |
+----------------------------------+----------------------------------+-------+
Index:    PersonnelAddresses_pkey

The complete default for "PerAddrID" is:

nextval('"PersonnelAddresse_PerAddrID_seq"')

As you can see, the table name has been truncated to make the whole
thing fit into 32 characters. You'd need to check the source to see the
exact algorithm: I'm not sure if it starts trimming on the field name,
ever. In general, it means long, common prefixes to table names (like
my "Personnel", above), are bad, because they might lead to ambigous names
for auto generated things, like sequences. Some thought has gone into what
the Right Thing to do is, but I'm not clear if a consensus has emerged.

Ross

--
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005