Thread: Cannot use more than 16 attributes in an index

On Tue, 6 Aug 2002, Thomas Bolden wrote:

> I have a table with 17 attributes in the primary key.  When I try to
> create the table I get the message
> "ERROR:  Cannot use more than 16 attributes in an index".
> The table description are set by a multi-state consortium.  This is a
> make or break for Postgresql.  Is there a way to increase this limit.

ISTM you should be able to increase INDEX_MAX_KEYS in pg_config and
recompile (but I haven't tried) and re-initdb.

On Tue, 6 Aug 2002, Thomas Bolden wrote:

> I have a table with 17 attributes in the primary key.  When I try to create the table I get the message
> "ERROR:  Cannot use more than 16 attributes in an index".
> The table description are set by a multi-state consortium.  This is a make or break for Postgresql.  Is there a way
toincrease this limit. 
> I have installed Ver 7.2.1 Postgresql on FreeBSD 4.6.

Thank god for having the source eh?

Just edit this file:

/path/to/src/postgresql-7.2.1/src/include/pg_config.h.in

and on about line 167 you'll find a couple of lines like this:

#define INDEX_MAX_KEYS          16
#define FUNC_MAX_ARGS           INDEX_MAX_KEYS

Just change that 16 to 32 or something like it, go the the
/path/to/src/postgresql-7.2.1 directory and do

./configure (your --configuration-switches go here...)
make
make install

and you're set.

On Tue, 2002-08-06 at 16:43, Thomas Bolden wrote:
> I have a table with 17 attributes in the primary key.  When I try to create the table I get the message
> "ERROR:  Cannot use more than 16 attributes in an index".
> The table description are set by a multi-state consortium.  This is a make or break for Postgresql.  Is there a way
toincrease this limit. 
> I have installed Ver 7.2.1 Postgresql on FreeBSD 4.6.

This is fairly straightforward.

Assuming that you are building from source, alter the definition of
INDEX_MAX_KEYS in src/include/pg_config.h.in
i.e.
#define INDEX_MAX_KEYS 32

...and recompile. Note that you will have to use initdb again to rebuild
your database cluster, so be sure to dump any data that you want to keep
from the database.

There is no problem (other than a minor performance penalty) associated
with changing this definition.(It is quite possible that it will be
raised to 32 anyway in version 7.3)

If this isn't clear, get back in touch and someone will give clearer
instructions!

Regards

John




--
John Gray
Azuli IT
www.azuli.co.uk

> I have a table with 17 attributes in the primary key.  When I try to create the table I get the message
> "ERROR:  Cannot use more than 16 attributes in an index".

After configuring your source, you should edit src/include/pg_config.h and
set

#define INDEX_MAX_KEYS          64

to a higher value. Default is 16. I increased mine to 64 for
example. Remember that you have to re-initdb your database after setting
this value to a higher one and recompile/installing your postgresql
version.

Kind regards
... Ralph ...

On Tuesday 06 Aug 2002 4:43 pm, Thomas Bolden wrote:
> I have a table with 17 attributes in the primary key.  When I try to create
> the table I get the message "ERROR:  Cannot use more than 16 attributes in
> an index".
> The table description are set by a multi-state consortium.  This is a make
> or break for Postgresql.  Is there a way to increase this limit. I have
> installed Ver 7.2.1 Postgresql on FreeBSD 4.6.

There's a constant INDEX_MAX_KEYS (pg_config.h) which looks like it could be
increased at the cost of a recompile. I'm sure one of the developers will say
if this is possible.

Failing that, you could use a functional index, although I think there's a max
number of parameters constant you'd have to increase for that. I know you can
increase the number of parameters for a function though.

HTH

- Richard Huxton