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:
Show quoted text
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
Import Notes
Reply to msg id not found: 200105161448.f4GEmrR17272@sss.pgh.pa.usReference msg id not found: 200105161448.f4GEmrR17272@sss.pgh.pa.us | Resolved by subject fallback