Thread: trouble with the automatic indexes on CREATE TABLE
When doing CREATE TABLE (piped in from a file with my table definitions) I'
m getting errors that I think are related to the automatic index creation
on my UNIQUE columns:
ERROR: Relation 'phone_number_types' does not exist
ERROR: Cannot create index: 'phone_number__phone_number__key' already
exists
ERROR: Relation 'delivery_methods' does not exist
ERROR: Cannot create index: 'delivery_meth_delivery_meth_key' already
exists
ERROR: Relation 'phone_number_locations' does not exist
ERROR: Cannot create index: 'phone_number__phone_number__key' already
exists
I suspect that the name PostgreSQL (7.0.3) is picking for the
automatically created indexes may be colliding with other index names from
other colums. This is what I can't seem to figure out how to avoid.
For instance:
CREATE TABLE Delivery_Methods (
delivery_method_id INTEGER NOT NULL UNIQUE,
delivery_method VARCHAR(50) NOT NULL UNIQUE
);
I think it's having trouble when it truncates the column name and winds up
with the same key name for both columns. Is there a way to force it to
not truncate or to specify the index name for it to use?
Thanks,
-Bill
Bill McGonigle <mcgonigle@medicalmedia.com> writes:
> CREATE TABLE Delivery_Methods (
> delivery_method_id INTEGER NOT NULL UNIQUE,
> delivery_method VARCHAR(50) NOT NULL UNIQUE
> );
If you want to use names like that, you'd be well advised to increase
NAMEDATALEN. See the archives.
regards, tom lane
Excellent. Thanks. There is one discussion of NAMEDATALEN in the archives, pertaining to version 6.4.2. That discussion brings up a few questions/assumptions related to getting this to work reliably. I'd love to hear any comments/corrections/amplifications: 1) It was required that OIDNAMELEN be set to sizeof(Oid) + NAMEDATALEN. Looking through the 7.1 source tree, OID_MAX is now set to UINT_MAX, which should be plenty big. :) Should I change anything else? 2) NAMEDATALEN is defined in the ODBC sources. I found a message saying this was used in the 6.2 protocol but not the 6.3 protocol - Is it safe to assume it's not used in the version 7 protocol either? I'd hate to have to dig out a Windows box to recompile the driver. ;) 3) Will psql from another machine fail to work if that machine's pgsql hasn't been compiled with the modified MAXDATALEN? Is this the same question as (2)? 4) If 3 is yes, maybe negotiating MAXDATALEN in the protocol would be a good idea? 5) If we assume equal lengths for table and column names (for the sake of argument), when a UNIQUE constraint is present, the effective non-unique length of a column name in pgsql, as distributed, is about 12 characters (31-'__' -'__key')? Isn't that kind of short? If there is a replacement for OIDNAMELEN, NAMEDATALEN could be set to 248 and both could still be under 256 on a 64-bit machine. I'm going to give it a whirl anyway - I just don't want to get stung later. Thanks, -Bill On Tuesday, May 15, 2001, at 06:47 PM, Tom Lane wrote: > If you want to use names like that, you'd be well advised to increase > NAMEDATALEN. See the archives.
Bill McGonigle <mcgonigle@medicalmedia.com> writes:
> There is one discussion of NAMEDATALEN in the archives, pertaining to
> version 6.4.2. That discussion brings up a few questions/assumptions
> related to getting this to work reliably. I'd love to hear any
> comments/corrections/amplifications:
> 1) It was required that OIDNAMELEN be set to sizeof(Oid) + NAMEDATALEN.
OIDNAMELEN is long gone. You don't have to change anything except
NAMEDATALEN.
> 3) Will psql from another machine fail to work if that machine's pgsql
> hasn't been compiled with the modified MAXDATALEN? Is this the same
> question as (2)?
The reason NAMEDATALEN is in postgres_ext.h is that it's visible to (and
used by) clients as well as the backend. So yes, you'd better recompile
everything. I am not sure what problems you would have with mismatched
clients. If you're lucky, they'll merely truncate your longer names,
and not coredump ...
regards, tom lane