Thread: trouble with the automatic indexes on CREATE TABLE

trouble with the automatic indexes on CREATE TABLE

From
Bill McGonigle
Date:
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

Re: trouble with the automatic indexes on CREATE TABLE

From
Tom Lane
Date:
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

Re: trouble with the automatic indexes on CREATE TABLE

From
Bill McGonigle
Date:
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.

Re: trouble with the automatic indexes on CREATE TABLE

From
Tom Lane
Date:
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