Re: [SQL] 16 parameter limit - Mailing list pgsql-patches

The following patch adds --maxindfuncparams to configure to allow you to
more easily set the maximum number of function parameters and columns
in an index.  (Can someone come up with a better name?)

The patch also removes --def_maxbackends, which Tom reported a few weeks
ago he wanted to remove.  Can people review this?  To test it, you have
to run autoconf.

Are we staying at 16 as the default?   I personally think we can
increase it to 32 with little penalty, and that we should increase
NAMEDATALEN to 64.

---------------------------------------------------------------------------

Tom Lane wrote:
> "Josh Berkus" <josh@agliodbs.com> writes:
> > Personally, as a heavy user of PL/pgSQL procedures, I'm not sure you
> >  need to increase the *default* number of parameters.  Postgres just
> >  needs to implement a parameter number change as part of a documented
> >  command-line compile-time option, i.e. "--with-parameters=32".
>
> I would not object to providing such a configure option; it seems a
> reasonable thing to do.  But the real debate here seems to be what
> the default should be.  The ACS people would like their code to run
> on a "stock" Postgres installation, so they've been lobbying to change
> the default, not just to make it fractionally easier to build a
> non-default configuration.
>
> > Also, what is the practical maximum number of parameters?
>
> If you tried to make it more than perhaps 500, you'd start to see
> index-tuple-too-big failures in the pg_proc indexes.  Realistically,
> though, I can't see people calling procedures with hundreds of
> positionally-specified parameters --- such code would be unmanageably
> error-prone.
>
> I was surprised that people were dissatisfied with 16 (it was 8 not very
> long ago...).  Needing more strikes me as a symptom of either bad coding
> practices or missing features of other sorts.
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026
Index: configure.in
===================================================================
RCS file: /cvsroot/pgsql/configure.in,v
retrieving revision 1.178
diff -c -r1.178 configure.in
*** configure.in    14 Apr 2002 17:23:20 -0000    1.178
--- configure.in    16 Apr 2002 01:47:00 -0000
***************
*** 215,229 ****
  AC_SUBST(default_port)

  #
! # Maximum number of allowed connections (--with-maxbackends), default 32
  #
! AC_MSG_CHECKING([for default soft limit on number of connections])
! PGAC_ARG_REQ(with, maxbackends, [  --with-maxbackends=N    set default maximum number of connections [32]],
               [],
!              [with_maxbackends=32])
! AC_MSG_RESULT([$with_maxbackends])
! AC_DEFINE_UNQUOTED([DEF_MAXBACKENDS], [$with_maxbackends],
!                    [The default soft limit on the number of concurrent connections, i.e., the default for the
postmaster-N switch (--with-maxbackends)]) 


  #
--- 215,229 ----
  AC_SUBST(default_port)

  #
! # Maximum number of index/function parameters (--with-maxindfuncparams), default 16
  #
! AC_MSG_CHECKING([maximum number of index/function parameters])
! PGAC_ARG_REQ(with, maxindfuncparams, [  --with-maxindfuncparams=N    maximum number of index/function parameters
[16]],
               [],
!              [with_maxindfuncparams=16])
! AC_MSG_RESULT([$with_maxindfuncparams])
! AC_DEFINE_UNQUOTED([MAXINDFUNCPARAMS], [$with_maxindfuncparams],
!                    [The maximum number of index/function parameters])


  #
Index: src/backend/utils/misc/guc.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/misc/guc.c,v
retrieving revision 1.65
diff -c -r1.65 guc.c
*** src/backend/utils/misc/guc.c    3 Apr 2002 05:39:32 -0000    1.65
--- src/backend/utils/misc/guc.c    16 Apr 2002 01:47:02 -0000
***************
*** 408,419 ****
       */
      {
          "max_connections", PGC_POSTMASTER, PGC_S_DEFAULT, &MaxBackends,
!         DEF_MAXBACKENDS, 1, INT_MAX, NULL, NULL
      },

      {
          "shared_buffers", PGC_POSTMASTER, PGC_S_DEFAULT, &NBuffers,
!         DEF_NBUFFERS, 16, INT_MAX, NULL, NULL
      },

      {
--- 408,419 ----
       */
      {
          "max_connections", PGC_POSTMASTER, PGC_S_DEFAULT, &MaxBackends,
!         32, 1, INT_MAX, NULL, NULL
      },

      {
          "shared_buffers", PGC_POSTMASTER, PGC_S_DEFAULT, &NBuffers,
!         64, 16, INT_MAX, NULL, NULL
      },

      {
Index: src/include/pg_config.h.in
===================================================================
RCS file: /cvsroot/pgsql/src/include/pg_config.h.in,v
retrieving revision 1.21
diff -c -r1.21 pg_config.h.in
*** src/include/pg_config.h.in    10 Apr 2002 22:47:09 -0000    1.21
--- src/include/pg_config.h.in    16 Apr 2002 01:47:03 -0000
***************
*** 77,87 ****
  #undef DEF_PGPORT_STR

  /*
!  * Default soft limit on number of backend server processes per postmaster;
!  * this is just the default setting for the postmaster's -N switch.
!  * (--with-maxbackends=N)
   */
! #undef DEF_MAXBACKENDS

  /* --enable-nls */
  #undef ENABLE_NLS
--- 77,86 ----
  #undef DEF_PGPORT_STR

  /*
!  * The maximum number of columns in an index and the maximum number of
!  * parameters to a function.  This controls the length of oidvector.
   */
! #undef MAXINDFUNCPARAMS

  /* --enable-nls */
  #undef ENABLE_NLS
***************
*** 107,121 ****
   */

  /*
-  * Default number of buffers in shared buffer pool (each of size BLCKSZ).
-  * This is just the default setting for the postmaster's -B switch.
-  * Perhaps it ought to be configurable from a configure switch.
-  * NOTE: default setting corresponds to the minimum number of buffers
-  * that postmaster.c will allow for the default MaxBackends value.
-  */
- #define DEF_NBUFFERS (DEF_MAXBACKENDS > 8 ? DEF_MAXBACKENDS * 2 : 16)
-
- /*
   * Size of a disk block --- this also limits the size of a tuple.
   * You can set it bigger if you need bigger tuples (although TOAST
   * should reduce the need to have large tuples, since fields can now
--- 106,111 ----
***************
*** 162,169 ****
   * switch statement in fmgr_oldstyle() in src/backend/utils/fmgr/fmgr.c.
   * But consider converting such functions to new-style instead...
   */
! #define INDEX_MAX_KEYS        16
! #define FUNC_MAX_ARGS        INDEX_MAX_KEYS

  /*
   * System default value for pg_attribute.attstattarget
--- 152,159 ----
   * switch statement in fmgr_oldstyle() in src/backend/utils/fmgr/fmgr.c.
   * But consider converting such functions to new-style instead...
   */
! #define INDEX_MAX_KEYS        MAXINDFUNCPARAMS
! #define FUNC_MAX_ARGS        MAXINDFUNCPARAMS

  /*
   * System default value for pg_attribute.attstattarget