Thread: Re: More than 16 args to postgres

Re: More than 16 args to postgres

From
"Josh Berkus"
Date:
Elliot,

> I can't find the thread you mentioned concerning configuring postgres
> to accept more than 16 args in a pgpsql function.  Kindly send a
> reference to wolin@jlab.org.

Option #1:
(on linux)
0. Back up your databases and shut down postgres.*
1. go to your PostgreSQL source files (if you did not install Postgres
from source, you will need to).
2. Go to the file .. src/include/pg_config.h.
3. Open it with your preferred text editor.
4. Search for the string "INDEX_MAX_KEYS"
5. You will see that INDEX_MAX_KEYS is set to 16, and that
FUNC_MAX_ARGS is equal to INDEX_MAX_KEYS.  You must preserve this
relationship, so set INDEX_MAX_KEYS to 32 or 64 or whatever you need.**
6. Save
7. Go back to the source root.   Do:  ./configure (with the options you
need), make clean, make, and make install.  Don't forget the make
clean.
8. Finish the installation process normally.  change permissions, clean
out the /data directory, and re-run initdb.
9. Restart and Restore your databases from backup.

*You will want to test backup and restore on all production databases,
to make sure it works cleanly.  pg_dumpall works best for backing up a
whole installation, but is not infallible.  You also may want to back
up postgresql.conf and pg_hba.conf to preserve your settings.

** Warning:  several postgresql developers have claimed that increasing
this number reduces index efficiency and therefore overall DB
performance.   No one, AFAIK, has tested this, but you may want to run
a test before setting a production system to 128 params or higher.  If
you do test this, please publish your results to the list.


Option #2:  Wait for PostgreSQL 7.3, which will be out in a few weeks,
and defaults to 32 parameters.

-Josh Berkus