Thread: Long table names
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.
> 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. That serial table name is fixed in 6.5.*. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
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
"Ross J. Reedstrom" <reedstrm@wallace.ece.rice.edu> writes:
> 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.
Right now, the algorithm is to preferentially truncate the longer name
component (table name or column name). There was some talk of adding
quasi-random hash characters to reduce the probability of name
collisions, but it's not been done.
Anyway, to answer the question Finn asked,
>> 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,
In theory you should only have to change NAMEDATALEN, rebuild, and
initdb. I think someone reported a few months on actually trying
this experiment (probably, around the same time we decided to put in
the name-truncation logic); check the pghackers archives for details.
regards, tom lane