Re: per user/database connections limit again - Mailing list pgsql-patches

From Bruce Momjian
Subject Re: per user/database connections limit again
Date
Msg-id 200507282211.j6SMBLp02787@candle.pha.pa.us
Whole thread Raw
In response to Re: per user/database connections limit again  (Petr Jelinek <pjmodos@parba.cz>)
Responses Re: per user/database connections limit again  (Petr Jelinek <pjmodos@parba.cz>)
Re: per user/database connections limit again  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-patches
I have "worked over" your patch and I think it is ready for application.

I changed the syntax to CONNECTION LIMIT, which seems most natural.  We
could skip CONNECTION and just use a LIMIT keyword, but that seems too
terse.

I removed your use of the pg_auth flat file.  By the time you have the
PROC entry to do your lookups, you might as well just use the system
cache.

There is a race condition in the code because we set our PROC entry
before we check for other entries.  If there is one connection left and
two backends do this at the same time, they would both fail, while one
should fail and the other succeed. Without a lock, I see no way to avoid
it so I just commented it in the code.

Also, I felt that zero should mean allow no/zero connections, rather
than representing unlimited connections.  I used -1 for unlimited.  We
can either document the use of -1, or add syntax to allow NO CONNECTION
LIMIT, or something like that.

The patch requires a catalog version update when applied.

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

Petr Jelinek wrote:
> Stephen Frost wrote:
>
> >This should almost certainly be a pg_database_ownercheck() call instead.
> >
> >
> Right there wasn't pg_database_ownercheck at the time I was writing it,
> fixed
>
> >The rest needs to be updated for roles, but looks like it should be
> >pretty easy to do.  Much of it just needs to be repatched, the parts
> >that do need to be changed look to be pretty simple changes.
> >
> >
> Done.
>
> >I believe the use of SessionUserId is probably correct in this patch.
> >This does mean that this patch will only be for canlogin roles, but that
> >seems like it's probably correct.  Handling roles w/ members would
> >require much more thought.
> >
> >
> I don't think that having max connection for roles w/ members is doable
> because you can have 5 roles which has 1 user as member and each role
> has different number of max conections and there is no right way to
> decide what to do.
>
>
> New version which works with roles is attached (diffed against cvs),
> everything else is mostly same.
> I also had to readd roleid to flatfiles because I need it in
> InitProcess() function.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073
Index: src/backend/catalog/system_views.sql
===================================================================
RCS file: /cvsroot/pgsql/src/backend/catalog/system_views.sql,v
retrieving revision 1.17
diff -c -c -r1.17 system_views.sql
*** src/backend/catalog/system_views.sql    26 Jul 2005 16:38:26 -0000    1.17
--- src/backend/catalog/system_views.sql    28 Jul 2005 21:56:17 -0000
***************
*** 15,20 ****
--- 15,21 ----
          rolcreatedb,
          rolcatupdate,
          rolcanlogin,
+         rolconnlimit,
          '********'::text as rolpassword,
          rolvaliduntil,
          rolconfig,
***************
*** 28,33 ****
--- 29,35 ----
          rolcreatedb AS usecreatedb,
          rolsuper AS usesuper,
          rolcatupdate AS usecatupd,
+         rolconnlimit AS useconnlimit,
          rolpassword AS passwd,
          rolvaliduntil::abstime AS valuntil,
          rolconfig AS useconfig
Index: src/backend/commands/dbcommands.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/commands/dbcommands.c,v
retrieving revision 1.167
diff -c -c -r1.167 dbcommands.c
*** src/backend/commands/dbcommands.c    14 Jul 2005 21:46:29 -0000    1.167
--- src/backend/commands/dbcommands.c    28 Jul 2005 21:56:18 -0000
***************
*** 54,67 ****

  /* non-export function prototypes */
  static bool get_db_info(const char *name, Oid *dbIdP, Oid *ownerIdP,
!             int *encodingP, bool *dbIsTemplateP, bool *dbAllowConnP,
!             Oid *dbLastSysOidP,
              TransactionId *dbVacuumXidP, TransactionId *dbFrozenXidP,
              Oid *dbTablespace);
  static bool have_createdb_privilege(void);
  static void remove_dbtablespaces(Oid db_id);

-
  /*
   * CREATE DATABASE
   */
--- 54,66 ----

  /* non-export function prototypes */
  static bool get_db_info(const char *name, Oid *dbIdP, Oid *ownerIdP,
!             int *encodingP, int *dbConnLimitP, bool *dbIsTemplateP,
!             bool *dbAllowConnP,    Oid *dbLastSysOidP,
              TransactionId *dbVacuumXidP, TransactionId *dbFrozenXidP,
              Oid *dbTablespace);
  static bool have_createdb_privilege(void);
  static void remove_dbtablespaces(Oid db_id);

  /*
   * CREATE DATABASE
   */
***************
*** 75,80 ****
--- 74,80 ----
      int            src_encoding;
      bool        src_istemplate;
      bool        src_allowconn;
+     int            src_connlimit;
      Oid            src_lastsysoid;
      TransactionId src_vacuumxid;
      TransactionId src_frozenxid;
***************
*** 92,101 ****
--- 92,103 ----
      DefElem    *downer = NULL;
      DefElem    *dtemplate = NULL;
      DefElem    *dencoding = NULL;
+     DefElem    *dconnlimit = NULL;
      char       *dbname = stmt->dbname;
      char       *dbowner = NULL;
      const char *dbtemplate = NULL;
      int            encoding = -1;
+     int            dbconnlimit = InvalidConnectionLimit;

  #ifndef WIN32
      char        buf[2 * MAXPGPATH + 100];
***************
*** 141,146 ****
--- 143,156 ----
                           errmsg("conflicting or redundant options")));
              dencoding = defel;
          }
+         else if (strcmp(defel->defname, "connectionlimit") == 0)
+         {
+             if (dconnlimit)
+                 ereport(ERROR,
+                         (errcode(ERRCODE_SYNTAX_ERROR),
+                          errmsg("conflicting or redundant options")));
+             dconnlimit = defel;
+         }
          else if (strcmp(defel->defname, "location") == 0)
          {
              ereport(WARNING,
***************
*** 186,191 ****
--- 196,203 ----
              elog(ERROR, "unrecognized node type: %d",
                   nodeTag(dencoding->arg));
      }
+     if (dconnlimit && dconnlimit->arg)
+         dbconnlimit = intVal(dconnlimit->arg);

      /* obtain OID of proposed owner */
      if (dbowner)
***************
*** 215,221 ****
       * idea, so accept possibility of race to create.  We will check again
       * after we grab the exclusive lock.
       */
!     if (get_db_info(dbname, NULL, NULL, NULL,
                      NULL, NULL, NULL, NULL, NULL, NULL))
          ereport(ERROR,
                  (errcode(ERRCODE_DUPLICATE_DATABASE),
--- 227,233 ----
       * idea, so accept possibility of race to create.  We will check again
       * after we grab the exclusive lock.
       */
!     if (get_db_info(dbname, NULL, NULL, NULL, NULL,
                      NULL, NULL, NULL, NULL, NULL, NULL))
          ereport(ERROR,
                  (errcode(ERRCODE_DUPLICATE_DATABASE),
***************
*** 228,235 ****
          dbtemplate = "template1";        /* Default template database name */

      if (!get_db_info(dbtemplate, &src_dboid, &src_owner, &src_encoding,
!                      &src_istemplate, &src_allowconn, &src_lastsysoid,
!                      &src_vacuumxid, &src_frozenxid, &src_deftablespace))
          ereport(ERROR,
                  (errcode(ERRCODE_UNDEFINED_DATABASE),
           errmsg("template database \"%s\" does not exist", dbtemplate)));
--- 240,248 ----
          dbtemplate = "template1";        /* Default template database name */

      if (!get_db_info(dbtemplate, &src_dboid, &src_owner, &src_encoding,
!                      &src_connlimit, &src_istemplate, &src_allowconn,
!                      &src_lastsysoid, &src_vacuumxid, &src_frozenxid,
!                      &src_deftablespace))
          ereport(ERROR,
                  (errcode(ERRCODE_UNDEFINED_DATABASE),
           errmsg("template database \"%s\" does not exist", dbtemplate)));
***************
*** 263,268 ****
--- 276,285 ----
      if (encoding < 0)
          encoding = src_encoding;

+     /* If dbconnlimit is defaulted, use source's dbconnlimit */
+     if (dbconnlimit == InvalidConnectionLimit)
+         dbconnlimit = src_connlimit;
+
      /* Some encodings are client only */
      if (!PG_VALID_BE_ENCODING(encoding))
          ereport(ERROR,
***************
*** 458,464 ****
      pg_database_rel = heap_open(DatabaseRelationId, ExclusiveLock);

      /* Check to see if someone else created same DB name meanwhile. */
!     if (get_db_info(dbname, NULL, NULL, NULL,
                      NULL, NULL, NULL, NULL, NULL, NULL))
      {
          /* Don't hold lock while doing recursive remove */
--- 475,481 ----
      pg_database_rel = heap_open(DatabaseRelationId, ExclusiveLock);

      /* Check to see if someone else created same DB name meanwhile. */
!     if (get_db_info(dbname, NULL, NULL, NULL, NULL,
                      NULL, NULL, NULL, NULL, NULL, NULL))
      {
          /* Don't hold lock while doing recursive remove */
***************
*** 484,489 ****
--- 501,507 ----
      new_record[Anum_pg_database_encoding - 1] = Int32GetDatum(encoding);
      new_record[Anum_pg_database_datistemplate - 1] = BoolGetDatum(false);
      new_record[Anum_pg_database_datallowconn - 1] = BoolGetDatum(true);
+     new_record[Anum_pg_database_datconnlimit - 1] = Int32GetDatum(dbconnlimit);
      new_record[Anum_pg_database_datlastsysoid - 1] = ObjectIdGetDatum(src_lastsysoid);
      new_record[Anum_pg_database_datvacuumxid - 1] = TransactionIdGetDatum(src_vacuumxid);
      new_record[Anum_pg_database_datfrozenxid - 1] = TransactionIdGetDatum(src_frozenxid);
***************
*** 590,596 ****
       */
      pgdbrel = heap_open(DatabaseRelationId, ExclusiveLock);

!     if (!get_db_info(dbname, &db_id, NULL, NULL,
                       &db_istemplate, NULL, NULL, NULL, NULL, NULL))
          ereport(ERROR,
                  (errcode(ERRCODE_UNDEFINED_DATABASE),
--- 608,614 ----
       */
      pgdbrel = heap_open(DatabaseRelationId, ExclusiveLock);

!     if (!get_db_info(dbname, &db_id, NULL, NULL, NULL,
                       &db_istemplate, NULL, NULL, NULL, NULL, NULL))
          ereport(ERROR,
                  (errcode(ERRCODE_UNDEFINED_DATABASE),
***************
*** 791,796 ****
--- 809,899 ----


  /*
+  * ALTER DATABASE name ...
+  */
+ void
+ AlterDatabase(AlterDatabaseStmt *stmt)
+ {
+     Datum        new_record[Natts_pg_database];
+     char        new_record_nulls[Natts_pg_database];
+     char        new_record_repl[Natts_pg_database];
+     Relation    rel;
+     HeapTuple    tuple,
+                 newtuple;
+     ScanKeyData scankey;
+     SysScanDesc scan;
+     ListCell   *option;
+     int            connlimit = InvalidConnectionLimit;
+
+     DefElem    *dconnlimit = NULL;
+
+     /* Extract options from the statement node tree */
+     foreach(option, stmt->options)
+     {
+         DefElem    *defel = (DefElem *) lfirst(option);
+
+         if (strcmp(defel->defname, "connectionlimit") == 0)
+         {
+             if (dconnlimit)
+                 ereport(ERROR,
+                         (errcode(ERRCODE_SYNTAX_ERROR),
+                          errmsg("conflicting or redundant options")));
+             dconnlimit = defel;
+         }
+     }
+
+     if (dconnlimit)
+         connlimit = intVal(dconnlimit->arg);
+
+     /*
+      * We don't need ExclusiveLock since we aren't updating the
+      * flat file.
+      */
+     rel = heap_open(DatabaseRelationId, RowExclusiveLock);
+     ScanKeyInit(&scankey,
+                 Anum_pg_database_datname,
+                 BTEqualStrategyNumber, F_NAMEEQ,
+                 NameGetDatum(stmt->dbname));
+     scan = systable_beginscan(rel, DatabaseNameIndexId, true,
+                               SnapshotNow, 1, &scankey);
+     tuple = systable_getnext(scan);
+     if (!HeapTupleIsValid(tuple))
+         ereport(ERROR,
+                 (errcode(ERRCODE_UNDEFINED_DATABASE),
+                  errmsg("database \"%s\" does not exist", stmt->dbname)));
+
+     if (!have_createdb_privilege())
+         aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_DATABASE,
+                        stmt->dbname);
+
+     /*
+      * Build an updated tuple, perusing the information just obtained
+      */
+     MemSet(new_record, 0, sizeof(new_record));
+     MemSet(new_record_nulls, ' ', sizeof(new_record_nulls));
+     MemSet(new_record_repl, ' ', sizeof(new_record_repl));
+
+     if (connlimit != InvalidConnectionLimit)
+     {
+         new_record[Anum_pg_database_datconnlimit - 1] = Int32GetDatum(connlimit);
+         new_record_repl[Anum_pg_database_datconnlimit - 1] = 'r';
+     }
+
+     newtuple = heap_modifytuple(tuple, RelationGetDescr(rel), new_record,
+                                  new_record_nulls, new_record_repl);
+     simple_heap_update(rel, &tuple->t_self, newtuple);
+
+     /* Update indexes */
+     CatalogUpdateIndexes(rel, newtuple);
+
+     systable_endscan(scan);
+
+     /* Close pg_database, but keep lock till commit */
+     heap_close(rel, NoLock);
+ }
+
+
+ /*
   * ALTER DATABASE name SET ...
   */
  void
***************
*** 998,1005 ****

  static bool
  get_db_info(const char *name, Oid *dbIdP, Oid *ownerIdP,
!             int *encodingP, bool *dbIsTemplateP, bool *dbAllowConnP,
!             Oid *dbLastSysOidP,
              TransactionId *dbVacuumXidP, TransactionId *dbFrozenXidP,
              Oid *dbTablespace)
  {
--- 1101,1108 ----

  static bool
  get_db_info(const char *name, Oid *dbIdP, Oid *ownerIdP,
!             int *encodingP, int *dbConnLimitP, bool *dbIsTemplateP,
!             bool *dbAllowConnP,    Oid *dbLastSysOidP,
              TransactionId *dbVacuumXidP, TransactionId *dbFrozenXidP,
              Oid *dbTablespace)
  {
***************
*** 1044,1049 ****
--- 1147,1155 ----
          /* allowing connections? */
          if (dbAllowConnP)
              *dbAllowConnP = dbform->datallowconn;
+         /* connection limit */
+         if (dbConnLimitP)
+             *dbConnLimitP = dbform->datconnlimit;
          /* last system OID used in database */
          if (dbLastSysOidP)
              *dbLastSysOidP = dbform->datlastsysoid;
Index: src/backend/commands/user.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/commands/user.c,v
retrieving revision 1.159
diff -c -c -r1.159 user.c
*** src/backend/commands/user.c    26 Jul 2005 22:37:49 -0000    1.159
--- src/backend/commands/user.c    28 Jul 2005 21:56:18 -0000
***************
*** 86,91 ****
--- 86,92 ----
      bool        createrole = false;        /* Can this user create roles? */
      bool        createdb = false;        /* Can the user create databases? */
      bool        canlogin = false;        /* Can this user login? */
+     int            connlimit = -1;            /* maximum connections allowed */
      List       *addroleto = NIL;        /* roles to make this a member of */
      List       *rolemembers = NIL;        /* roles to be members of this role */
      List       *adminmembers = NIL;        /* roles to be admins of this role */
***************
*** 96,101 ****
--- 97,103 ----
      DefElem    *dcreaterole = NULL;
      DefElem    *dcreatedb = NULL;
      DefElem    *dcanlogin = NULL;
+     DefElem    *dconnlimit = NULL;
      DefElem    *daddroleto = NULL;
      DefElem    *drolemembers = NULL;
      DefElem    *dadminmembers = NULL;
***************
*** 178,183 ****
--- 180,193 ----
                           errmsg("conflicting or redundant options")));
              dcanlogin = defel;
          }
+         else if (strcmp(defel->defname, "connectionlimit") == 0)
+         {
+             if (dconnlimit)
+                 ereport(ERROR,
+                         (errcode(ERRCODE_SYNTAX_ERROR),
+                          errmsg("conflicting or redundant options")));
+             dconnlimit = defel;
+         }
          else if (strcmp(defel->defname, "addroleto") == 0)
          {
              if (daddroleto)
***************
*** 227,232 ****
--- 237,255 ----
          createdb = intVal(dcreatedb->arg) != 0;
      if (dcanlogin)
          canlogin = intVal(dcanlogin->arg) != 0;
+     if (dconnlimit)
+     {
+         connlimit = intVal(dconnlimit->arg);
+         if (connlimit < -1)
+             ereport(ERROR,
+                     (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+                      errmsg("CONNECTION LIMIT must greater or equal to -1")));
+
+         if (!canlogin && connlimit >= 0)
+             ereport(ERROR,
+                     (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+                      errmsg("CONNECTION LIMIT can be specified only for roles which can login")));
+     }
      if (daddroleto)
          addroleto = (List *) daddroleto->arg;
      if (drolemembers)
***************
*** 292,297 ****
--- 315,321 ----
      /* superuser gets catupdate right by default */
      new_record[Anum_pg_authid_rolcatupdate - 1] = BoolGetDatum(issuper);
      new_record[Anum_pg_authid_rolcanlogin - 1] = BoolGetDatum(canlogin);
+     new_record[Anum_pg_authid_rolconnlimit - 1] = Int32GetDatum(connlimit);

      if (password)
      {
***************
*** 401,406 ****
--- 425,431 ----
      int            createrole = -1;        /* Can this user create roles? */
      int            createdb = -1;            /* Can the user create databases? */
      int            canlogin = -1;            /* Can this user login? */
+     int            connlimit = InvalidConnectionLimit;    /* Connection limit */
      List       *rolemembers = NIL;        /* roles to be added/removed */
      char       *validUntil = NULL;        /* time the login is valid until */
      DefElem    *dpassword = NULL;
***************
*** 409,414 ****
--- 434,440 ----
      DefElem    *dcreaterole = NULL;
      DefElem    *dcreatedb = NULL;
      DefElem    *dcanlogin = NULL;
+     DefElem    *dconnlimit = NULL;
      DefElem    *drolemembers = NULL;
      DefElem    *dvalidUntil = NULL;
      Oid            roleid;
***************
*** 472,477 ****
--- 498,511 ----
                           errmsg("conflicting or redundant options")));
              dcanlogin = defel;
          }
+         else if (strcmp(defel->defname, "connectionlimit") == 0)
+         {
+             if (dconnlimit)
+                 ereport(ERROR,
+                         (errcode(ERRCODE_SYNTAX_ERROR),
+                          errmsg("conflicting or redundant options")));
+             dconnlimit = defel;
+         }
          else if (strcmp(defel->defname, "rolemembers") == 0 &&
                   stmt->action != 0)
          {
***************
*** 506,511 ****
--- 540,558 ----
          createdb = intVal(dcreatedb->arg);
      if (dcanlogin)
          canlogin = intVal(dcanlogin->arg);
+     if (dconnlimit)
+     {
+         connlimit = intVal(dconnlimit->arg);
+         if (connlimit < -1)
+             ereport(ERROR,
+                     (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+                      errmsg("CONNECTION LIMIT must greater or equal to -1")));
+
+         if (canlogin == 0 && connlimit >= 0)
+             ereport(ERROR,
+                     (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+                      errmsg("CONNECTION LIMIT can be specified only for roles which can login")));
+     }
      if (drolemembers)
          rolemembers = (List *) drolemembers->arg;
      if (dvalidUntil)
***************
*** 545,550 ****
--- 592,598 ----
                createrole < 0 &&
                createdb < 0 &&
                canlogin < 0 &&
+               connlimit == InvalidConnectionLimit &&
                !rolemembers &&
                !validUntil &&
                password &&
***************
*** 602,607 ****
--- 650,661 ----
          new_record_repl[Anum_pg_authid_rolcanlogin - 1] = 'r';
      }

+     if (connlimit != InvalidConnectionLimit)
+     {
+         new_record[Anum_pg_authid_rolconnlimit - 1] = Int32GetDatum(connlimit);
+         new_record_repl[Anum_pg_authid_rolconnlimit - 1] = 'r';
+     }
+
      /* password */
      if (password)
      {
Index: src/backend/nodes/copyfuncs.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/nodes/copyfuncs.c,v
retrieving revision 1.312
diff -c -c -r1.312 copyfuncs.c
*** src/backend/nodes/copyfuncs.c    26 Jul 2005 16:38:27 -0000    1.312
--- src/backend/nodes/copyfuncs.c    28 Jul 2005 21:56:19 -0000
***************
*** 2204,2209 ****
--- 2204,2220 ----
      return newnode;
  }

+ static AlterDatabaseStmt *
+ _copyAlterDatabaseStmt(AlterDatabaseStmt *from)
+ {
+     AlterDatabaseStmt *newnode = makeNode(AlterDatabaseStmt);
+
+     COPY_STRING_FIELD(dbname);
+     COPY_NODE_FIELD(options);
+
+     return newnode;
+ }
+
  static AlterDatabaseSetStmt *
  _copyAlterDatabaseSetStmt(AlterDatabaseSetStmt *from)
  {
***************
*** 3011,3016 ****
--- 3022,3030 ----
          case T_CreatedbStmt:
              retval = _copyCreatedbStmt(from);
              break;
+         case T_AlterDatabaseStmt:
+             retval = _copyAlterDatabaseStmt(from);
+             break;
          case T_AlterDatabaseSetStmt:
              retval = _copyAlterDatabaseSetStmt(from);
              break;
Index: src/backend/nodes/equalfuncs.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/nodes/equalfuncs.c,v
retrieving revision 1.249
diff -c -c -r1.249 equalfuncs.c
*** src/backend/nodes/equalfuncs.c    26 Jul 2005 16:38:27 -0000    1.249
--- src/backend/nodes/equalfuncs.c    28 Jul 2005 21:56:20 -0000
***************
*** 1152,1157 ****
--- 1152,1166 ----
  }

  static bool
+ _equalAlterDatabaseStmt(AlterDatabaseStmt *a, AlterDatabaseStmt *b)
+ {
+     COMPARE_STRING_FIELD(dbname);
+     COMPARE_NODE_FIELD(options);
+
+     return true;
+ }
+
+ static bool
  _equalAlterDatabaseSetStmt(AlterDatabaseSetStmt *a, AlterDatabaseSetStmt *b)
  {
      COMPARE_STRING_FIELD(dbname);
***************
*** 2059,2064 ****
--- 2068,2076 ----
          case T_CreatedbStmt:
              retval = _equalCreatedbStmt(a, b);
              break;
+         case T_AlterDatabaseStmt:
+             retval = _equalAlterDatabaseStmt(a, b);
+             break;
          case T_AlterDatabaseSetStmt:
              retval = _equalAlterDatabaseSetStmt(a, b);
              break;
Index: src/backend/parser/gram.y
===================================================================
RCS file: /cvsroot/pgsql/src/backend/parser/gram.y,v
retrieving revision 2.504
diff -c -c -r2.504 gram.y
*** src/backend/parser/gram.y    26 Jul 2005 22:37:50 -0000    2.504
--- src/backend/parser/gram.y    28 Jul 2005 21:56:24 -0000
***************
*** 131,139 ****
  }

  %type <node>    stmt schema_stmt
!         AlterDatabaseSetStmt AlterDomainStmt AlterGroupStmt AlterOwnerStmt
!         AlterSeqStmt AlterTableStmt AlterUserStmt AlterUserSetStmt
!         AlterRoleStmt AlterRoleSetStmt
          AnalyzeStmt ClosePortalStmt ClusterStmt CommentStmt
          ConstraintsSetStmt CopyStmt CreateAsStmt CreateCastStmt
          CreateDomainStmt CreateGroupStmt CreateOpClassStmt CreatePLangStmt
--- 131,139 ----
  }

  %type <node>    stmt schema_stmt
!         AlterDatabaseStmt AlterDatabaseSetStmt AlterDomainStmt AlterGroupStmt
!         AlterOwnerStmt AlterSeqStmt AlterTableStmt
!         AlterUserStmt AlterUserSetStmt AlterRoleStmt AlterRoleSetStmt
          AnalyzeStmt ClosePortalStmt ClusterStmt CommentStmt
          ConstraintsSetStmt CopyStmt CreateAsStmt CreateCastStmt
          CreateDomainStmt CreateGroupStmt CreateOpClassStmt CreatePLangStmt
***************
*** 165,172 ****

  %type <dbehavior>    opt_drop_behavior

! %type <list>    createdb_opt_list copy_opt_list transaction_mode_list
! %type <defelt>    createdb_opt_item copy_opt_item transaction_mode_item

  %type <ival>    opt_lock lock_type cast_context
  %type <boolean>    opt_force opt_or_replace
--- 165,174 ----

  %type <dbehavior>    opt_drop_behavior

! %type <list>    createdb_opt_list alterdb_opt_list copy_opt_list
!                 transaction_mode_list
! %type <defelt>    createdb_opt_item alterdb_opt_item copy_opt_item
!                 transaction_mode_item

  %type <ival>    opt_lock lock_type cast_context
  %type <boolean>    opt_force opt_or_replace
***************
*** 257,263 ****

  %type <boolean> copy_from opt_hold

! %type <ival>    fetch_count    opt_column event cursor_options
  %type <objtype>    reindex_type drop_type comment_type

  %type <node>    fetch_direction select_limit_value select_offset_value
--- 259,265 ----

  %type <boolean> copy_from opt_hold

! %type <ival>    iconst_opt_minus    opt_column event cursor_options
  %type <objtype>    reindex_type drop_type comment_type

  %type <node>    fetch_direction select_limit_value select_offset_value
***************
*** 342,348 ****
      CACHE CALLED CASCADE CASE CAST CHAIN CHAR_P
      CHARACTER CHARACTERISTICS CHECK CHECKPOINT CLASS CLOSE
      CLUSTER COALESCE COLLATE COLUMN COMMENT COMMIT
!     COMMITTED CONSTRAINT CONSTRAINTS CONVERSION_P CONVERT COPY CREATE CREATEDB
      CREATEROLE CREATEUSER CROSS CSV CURRENT_DATE CURRENT_ROLE CURRENT_TIME
      CURRENT_TIMESTAMP CURRENT_USER CURSOR CYCLE

--- 344,350 ----
      CACHE CALLED CASCADE CASE CAST CHAIN CHAR_P
      CHARACTER CHARACTERISTICS CHECK CHECKPOINT CLASS CLOSE
      CLUSTER COALESCE COLLATE COLUMN COMMENT COMMIT
!     COMMITTED CONNECTION CONSTRAINT CONSTRAINTS CONVERSION_P CONVERT COPY CREATE CREATEDB
      CREATEROLE CREATEUSER CROSS CSV CURRENT_DATE CURRENT_ROLE CURRENT_TIME
      CURRENT_TIMESTAMP CURRENT_USER CURSOR CYCLE

***************
*** 486,492 ****
          ;

  stmt :
!             AlterDatabaseSetStmt
              | AlterDomainStmt
              | AlterFunctionStmt
              | AlterGroupStmt
--- 488,495 ----
          ;

  stmt :
!             AlterDatabaseStmt
!             | AlterDatabaseSetStmt
              | AlterDomainStmt
              | AlterFunctionStmt
              | AlterGroupStmt
***************
*** 672,677 ****
--- 675,684 ----
                  {
                      $$ = makeDefElem("canlogin", (Node *)makeInteger(FALSE));
                  }
+             | CONNECTION LIMIT iconst_opt_minus
+                 {
+                     $$ = makeDefElem("connectionlimit", (Node *)makeInteger($3));
+                 }
              | IN_P ROLE name_list
                  {
                      $$ = makeDefElem("addroleto", (Node *)$3);
***************
*** 3044,3064 ****
                      n->howMany = -1;
                      $$ = (Node *)n;
                  }
!             | ABSOLUTE_P fetch_count
                  {
                      FetchStmt *n = makeNode(FetchStmt);
                      n->direction = FETCH_ABSOLUTE;
                      n->howMany = $2;
                      $$ = (Node *)n;
                  }
!             | RELATIVE_P fetch_count
                  {
                      FetchStmt *n = makeNode(FetchStmt);
                      n->direction = FETCH_RELATIVE;
                      n->howMany = $2;
                      $$ = (Node *)n;
                  }
!             | fetch_count
                  {
                      FetchStmt *n = makeNode(FetchStmt);
                      n->direction = FETCH_FORWARD;
--- 3051,3071 ----
                      n->howMany = -1;
                      $$ = (Node *)n;
                  }
!             | ABSOLUTE_P iconst_opt_minus
                  {
                      FetchStmt *n = makeNode(FetchStmt);
                      n->direction = FETCH_ABSOLUTE;
                      n->howMany = $2;
                      $$ = (Node *)n;
                  }
!             | RELATIVE_P iconst_opt_minus
                  {
                      FetchStmt *n = makeNode(FetchStmt);
                      n->direction = FETCH_RELATIVE;
                      n->howMany = $2;
                      $$ = (Node *)n;
                  }
!             | iconst_opt_minus
                  {
                      FetchStmt *n = makeNode(FetchStmt);
                      n->direction = FETCH_FORWARD;
***************
*** 3079,3085 ****
                      n->howMany = 1;
                      $$ = (Node *)n;
                  }
!             | FORWARD fetch_count
                  {
                      FetchStmt *n = makeNode(FetchStmt);
                      n->direction = FETCH_FORWARD;
--- 3086,3092 ----
                      n->howMany = 1;
                      $$ = (Node *)n;
                  }
!             | FORWARD iconst_opt_minus
                  {
                      FetchStmt *n = makeNode(FetchStmt);
                      n->direction = FETCH_FORWARD;
***************
*** 3100,3106 ****
                      n->howMany = 1;
                      $$ = (Node *)n;
                  }
!             | BACKWARD fetch_count
                  {
                      FetchStmt *n = makeNode(FetchStmt);
                      n->direction = FETCH_BACKWARD;
--- 3107,3113 ----
                      n->howMany = 1;
                      $$ = (Node *)n;
                  }
!             | BACKWARD iconst_opt_minus
                  {
                      FetchStmt *n = makeNode(FetchStmt);
                      n->direction = FETCH_BACKWARD;
***************
*** 3116,3126 ****
                  }
          ;

- fetch_count:
-             Iconst                                    { $$ = $1; }
-             | '-' Iconst                            { $$ = - $2; }
-         ;
-
  from_in:    FROM                                    {}
              | IN_P                                    {}
          ;
--- 3123,3128 ----
***************
*** 4473,4478 ****
--- 4475,4484 ----
                  {
                      $$ = makeDefElem("encoding", NULL);
                  }
+             | CONNECTION LIMIT opt_equal iconst_opt_minus
+                 {
+                     $$ = makeDefElem("connectionlimit", (Node *)makeInteger($4));
+                 }
              | OWNER opt_equal name
                  {
                      $$ = makeDefElem("owner", (Node *)makeString($3));
***************
*** 4499,4504 ****
--- 4505,4520 ----
   *
   *****************************************************************************/

+ AlterDatabaseStmt:
+              ALTER DATABASE database_name opt_with alterdb_opt_list
+                  {
+                     AlterDatabaseStmt *n = makeNode(AlterDatabaseStmt);
+                     n->dbname = $3;
+                     n->options = $5;
+                     $$ = (Node *)n;
+                  }
+         ;
+
  AlterDatabaseSetStmt:
              ALTER DATABASE database_name SET set_rest
                  {
***************
*** 4519,4524 ****
--- 4535,4553 ----
          ;


+ alterdb_opt_list:
+             alterdb_opt_list alterdb_opt_item        { $$ = lappend($1, $2); }
+             | /* EMPTY */                            { $$ = NIL; }
+         ;
+
+ alterdb_opt_item:
+             CONNECTION LIMIT opt_equal iconst_opt_minus
+                 {
+                     $$ = makeDefElem("connectionlimit", (Node *)makeInteger($4));
+                 }
+         ;
+
+
  /*****************************************************************************
   *
   *        DROP DATABASE
***************
*** 7875,7880 ****
--- 7904,7914 ----
  Sconst:        SCONST                                    { $$ = $1; };
  RoleId:        ColId                                    { $$ = $1; };

+ iconst_opt_minus:
+             Iconst                                    { $$ = $1; }
+             | '-' Iconst                            { $$ = - $2; }
+         ;
+
  /*
   * Name classification hierarchy.
   *
***************
*** 7959,7964 ****
--- 7993,7999 ----
              | COMMENT
              | COMMIT
              | COMMITTED
+             | CONNECTION
              | CONSTRAINTS
              | CONVERSION_P
              | COPY
Index: src/backend/parser/keywords.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/parser/keywords.c,v
retrieving revision 1.163
diff -c -c -r1.163 keywords.c
*** src/backend/parser/keywords.c    26 Jul 2005 16:38:27 -0000    1.163
--- src/backend/parser/keywords.c    28 Jul 2005 21:56:24 -0000
***************
*** 83,88 ****
--- 83,89 ----
      {"comment", COMMENT},
      {"commit", COMMIT},
      {"committed", COMMITTED},
+     {"connection", CONNECTION},
      {"constraint", CONSTRAINT},
      {"constraints", CONSTRAINTS},
      {"conversion", CONVERSION_P},
Index: src/backend/storage/ipc/procarray.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/storage/ipc/procarray.c,v
retrieving revision 1.3
diff -c -c -r1.3 procarray.c
*** src/backend/storage/ipc/procarray.c    17 Jun 2005 22:32:45 -0000    1.3
--- src/backend/storage/ipc/procarray.c    28 Jul 2005 21:56:27 -0000
***************
*** 734,739 ****
--- 734,790 ----
  }


+ /*
+  * CountDBBackends --- count backends that are using specified database
+  */
+ int
+ CountDBBackends(Oid databaseid)
+ {
+     ProcArrayStruct *arrayP = procArray;
+     int            count = 0;
+     int            index;
+
+     LWLockAcquire(ProcArrayLock, LW_SHARED);
+
+     for (index = 0; index < arrayP->numProcs; index++)
+     {
+         PGPROC       *proc = arrayP->procs[index];
+
+         if (proc->pid != 0 && proc->databaseId == databaseid)
+             count++;
+     }
+
+     LWLockRelease(ProcArrayLock);
+
+     return count;
+ }
+
+ /*
+  * CountUserBackends --- count backends that are used by specified user
+  */
+ int
+ CountUserBackends(Oid roleid)
+ {
+     ProcArrayStruct *arrayP = procArray;
+     int            count = 0;
+     int            index;
+
+     LWLockAcquire(ProcArrayLock, LW_SHARED);
+
+     for (index = 0; index < arrayP->numProcs; index++)
+     {
+         PGPROC       *proc = arrayP->procs[index];
+
+         if (proc->pid != 0 && proc->roleId == roleid)
+             count++;
+     }
+
+     LWLockRelease(ProcArrayLock);
+
+     return count;
+ }
+
+
  #define XidCacheRemove(i) \
      do { \
          MyProc->subxids.xids[i] = MyProc->subxids.xids[MyProc->subxids.nxids - 1]; \
Index: src/backend/storage/lmgr/proc.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/storage/lmgr/proc.c,v
retrieving revision 1.160
diff -c -c -r1.160 proc.c
*** src/backend/storage/lmgr/proc.c    17 Jun 2005 22:32:45 -0000    1.160
--- src/backend/storage/lmgr/proc.c    28 Jul 2005 21:56:27 -0000
***************
*** 46,51 ****
--- 46,52 ----

  #include "miscadmin.h"
  #include "access/xact.h"
+ #include "catalog/pg_authid.h"
  #include "storage/bufmgr.h"
  #include "storage/ipc.h"
  #include "storage/proc.h"
***************
*** 254,259 ****
--- 255,262 ----
      MyProc->xmin = InvalidTransactionId;
      MyProc->pid = MyProcPid;
      MyProc->databaseId = MyDatabaseId;
+     /* Will be set properly after the session role id is determined */
+     MyProc->roleId = InvalidOid;
      MyProc->lwWaiting = false;
      MyProc->lwExclusive = false;
      MyProc->lwWaitLink = NULL;
Index: src/backend/tcop/utility.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/tcop/utility.c,v
retrieving revision 1.241
diff -c -c -r1.241 utility.c
*** src/backend/tcop/utility.c    14 Jul 2005 05:13:41 -0000    1.241
--- src/backend/tcop/utility.c    28 Jul 2005 21:56:28 -0000
***************
*** 275,280 ****
--- 275,281 ----

      switch (nodeTag(parsetree))
      {
+         case T_AlterDatabaseStmt:
          case T_AlterDatabaseSetStmt:
          case T_AlterDomainStmt:
          case T_AlterFunctionStmt:
***************
*** 788,793 ****
--- 789,798 ----
              createdb((CreatedbStmt *) parsetree);
              break;

+         case T_AlterDatabaseStmt:
+             AlterDatabase((AlterDatabaseStmt *) parsetree);
+             break;
+
          case T_AlterDatabaseSetStmt:
              AlterDatabaseSet((AlterDatabaseSetStmt *) parsetree);
              break;
***************
*** 1504,1509 ****
--- 1509,1518 ----
              tag = "CREATE DATABASE";
              break;

+         case T_AlterDatabaseStmt:
+             tag = "ALTER DATABASE";
+             break;
+
          case T_AlterDatabaseSetStmt:
              tag = "ALTER DATABASE";
              break;
Index: src/backend/utils/init/miscinit.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/init/miscinit.c,v
retrieving revision 1.147
diff -c -c -r1.147 miscinit.c
*** src/backend/utils/init/miscinit.c    25 Jul 2005 22:12:33 -0000    1.147
--- src/backend/utils/init/miscinit.c    28 Jul 2005 21:56:29 -0000
***************
*** 36,41 ****
--- 36,43 ----
  #include "storage/fd.h"
  #include "storage/ipc.h"
  #include "storage/pg_shmem.h"
+ #include "storage/proc.h"
+ #include "storage/procarray.h"
  #include "utils/builtins.h"
  #include "utils/guc.h"
  #include "utils/lsyscache.h"
***************
*** 415,420 ****
--- 417,448 ----
      /* This sets OuterUserId/CurrentUserId too */
      SetSessionUserId(roleid, AuthenticatedUserIsSuperuser);

+     /* Set it now that we have a user id */
+     MyProc->roleId = GetSessionUserId();
+
+     /*
+      * Check connection limit for user
+      * There is a race condition here --- we create our PROC before
+      * checking for other PROCs.  If two backends did this at the
+      * same time, they might think they were over the limit, while
+      * one should succeed and one fail.
+      */
+     datum = SysCacheGetAttr(AUTHNAME, roleTup,
+                             Anum_pg_authid_rolconnlimit, &isnull);
+     if (IsUnderPostmaster && !isnull)
+     {
+         int32 connlimit = DatumGetInt32(datum);
+
+         if (connlimit >= 0 && !AuthenticatedUserIsSuperuser &&
+             CountUserBackends(AuthenticatedUserId) > connlimit)
+         {
+             ereport(FATAL,
+                 (errcode(ERRCODE_TOO_MANY_CONNECTIONS),
+              errmsg("sorry, too many clients already for role \"%s\"",
+                 rolename)));
+         }
+     }
+
      /* Record username and superuser status as GUC settings too */
      SetConfigOption("session_authorization", rolename,
                      PGC_BACKEND, PGC_S_OVERRIDE);
Index: src/backend/utils/init/postinit.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/init/postinit.c,v
retrieving revision 1.153
diff -c -c -r1.153 postinit.c
*** src/backend/utils/init/postinit.c    14 Jul 2005 05:13:41 -0000    1.153
--- src/backend/utils/init/postinit.c    28 Jul 2005 21:56:29 -0000
***************
*** 167,183 ****
                          name, MyDatabaseId)));
      }

-     /*
-      * Also check that the database is currently allowing connections.
-      * (We do not enforce this in standalone mode, however, so that there is
-      * a way to recover from "UPDATE pg_database SET datallowconn = false;")
-      */
      dbform = (Form_pg_database) GETSTRUCT(tup);
!     if (IsUnderPostmaster && !dbform->datallowconn)
!         ereport(FATAL,
!                 (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
!          errmsg("database \"%s\" is not currently accepting connections",
                  name)));

      /*
       * OK, we're golden.  Next to-do item is to save the encoding
--- 167,205 ----
                          name, MyDatabaseId)));
      }

      dbform = (Form_pg_database) GETSTRUCT(tup);
!     if (IsUnderPostmaster)
!     {
!         /*
!          * Also check that the database is currently allowing connections.
!          * (We do not enforce this in standalone mode, however, so that there is
!          * a way to recover from "UPDATE pg_database SET datallowconn = false;")
!          */
!         if (!dbform->datallowconn)
!         {
!             ereport(FATAL,
!                     (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
!              errmsg("database \"%s\" is not currently accepting connections",
!                     name)));
!         }
!
!         /*
!          * Here we check connection limit for this database
!          * There is a race condition here --- we create our PROC before
!          * checking for other PROCs.  If two backends did this at the
!          * same time, they might think they were over the limit, while
!          * one should succeed and one fail.
!          */
!         if (dbform->datconnlimit >= 0 && !superuser() &&
!                 CountDBBackends(MyDatabaseId) > dbform->datconnlimit)
!         {
!             ereport(FATAL,
!                 (errcode(ERRCODE_TOO_MANY_CONNECTIONS),
!              errmsg("sorry, too many clients already for database \"%s\"",
                  name)));
+         }
+     }
+

      /*
       * OK, we're golden.  Next to-do item is to save the encoding
Index: src/bin/pg_dump/pg_dumpall.c
===================================================================
RCS file: /cvsroot/pgsql/src/bin/pg_dump/pg_dumpall.c,v
retrieving revision 1.65
diff -c -c -r1.65 pg_dumpall.c
*** src/bin/pg_dump/pg_dumpall.c    25 Jul 2005 04:52:32 -0000    1.65
--- src/bin/pg_dump/pg_dumpall.c    28 Jul 2005 21:56:30 -0000
***************
*** 394,409 ****
      PGresult   *res;
      int            i;

!     if (server_version >= 70100)
          res = executeQuery(conn,
                          "SELECT usename, usesysid, passwd, usecreatedb, "
!                            "usesuper, valuntil, "
                             "(usesysid = (SELECT datdba FROM pg_database WHERE datname = 'template0')) AS clusterowner
"
                             "FROM pg_shadow");
      else
          res = executeQuery(conn,
                          "SELECT usename, usesysid, passwd, usecreatedb, "
!                            "usesuper, valuntil, "
                             "(usesysid = (SELECT datdba FROM pg_database WHERE datname = 'template1')) AS clusterowner
"
                             "FROM pg_shadow");

--- 394,415 ----
      PGresult   *res;
      int            i;

!     if (server_version >= 80100)
!         res = executeQuery(conn,
!                         "SELECT usename, usesysid, passwd, usecreatedb, "
!                            "usesuper, valuntil, useconnlimit, "
!                            "(usesysid = (SELECT datdba FROM pg_database WHERE datname = 'template0')) AS clusterowner
"
!                            "FROM pg_shadow");
!     else if (server_version >= 70100)
          res = executeQuery(conn,
                          "SELECT usename, usesysid, passwd, usecreatedb, "
!                            "usesuper, valuntil, -1 AS useconnlimit, "
                             "(usesysid = (SELECT datdba FROM pg_database WHERE datname = 'template0')) AS clusterowner
"
                             "FROM pg_shadow");
      else
          res = executeQuery(conn,
                          "SELECT usename, usesysid, passwd, usecreatedb, "
!                            "usesuper, valuntil, -1 AS useconnlimit, "
                             "(usesysid = (SELECT datdba FROM pg_database WHERE datname = 'template1')) AS clusterowner
"
                             "FROM pg_shadow");

***************
*** 453,458 ****
--- 459,468 ----
              appendPQExpBuffer(buf, " VALID UNTIL '%s'",
                                PQgetvalue(res, i, 5));

+         if (strcmp(PQgetvalue(res, i, 6), "-1") != 0)
+             appendPQExpBuffer(buf, " CONNECTION LIMIT %s",
+                               PQgetvalue(res, i, 6));
+
          appendPQExpBuffer(buf, ";\n");

          printf("%s", buf->data);
***************
*** 612,623 ****

      printf("--\n-- Database creation\n--\n\n");

!     if (server_version >= 80000)
          res = executeQuery(conn,
                             "SELECT datname, "
                             "coalesce(usename, (select usename from pg_shadow where usesysid=(select datdba from
pg_databasewhere datname='template0'))), " 
                             "pg_encoding_to_char(d.encoding), "
!                            "datistemplate, datacl, "
                             "(SELECT spcname FROM pg_tablespace t WHERE t.oid = d.dattablespace) AS dattablespace "
          "FROM pg_database d LEFT JOIN pg_shadow u ON (datdba = usesysid) "
                             "WHERE datallowconn ORDER BY 1");
--- 622,642 ----

      printf("--\n-- Database creation\n--\n\n");

!     if (server_version >= 80100)
          res = executeQuery(conn,
                             "SELECT datname, "
                             "coalesce(usename, (select usename from pg_shadow where usesysid=(select datdba from
pg_databasewhere datname='template0'))), " 
                             "pg_encoding_to_char(d.encoding), "
!                            "datistemplate, datacl, datconnlimit, "
!                            "(SELECT spcname FROM pg_tablespace t WHERE t.oid = d.dattablespace) AS dattablespace "
!         "FROM pg_database d LEFT JOIN pg_shadow u ON (datdba = usesysid) "
!                            "WHERE datallowconn ORDER BY 1");
!     else if (server_version >= 80000)
!         res = executeQuery(conn,
!                            "SELECT datname, "
!                            "coalesce(usename, (select usename from pg_shadow where usesysid=(select datdba from
pg_databasewhere datname='template0'))), " 
!                            "pg_encoding_to_char(d.encoding), "
!                            "datistemplate, datacl, -1 as datconnlimit, "
                             "(SELECT spcname FROM pg_tablespace t WHERE t.oid = d.dattablespace) AS dattablespace "
          "FROM pg_database d LEFT JOIN pg_shadow u ON (datdba = usesysid) "
                             "WHERE datallowconn ORDER BY 1");
***************
*** 626,632 ****
                             "SELECT datname, "
                             "coalesce(usename, (select usename from pg_shadow where usesysid=(select datdba from
pg_databasewhere datname='template0'))), " 
                             "pg_encoding_to_char(d.encoding), "
!                            "datistemplate, datacl, "
                             "'pg_default' AS dattablespace "
          "FROM pg_database d LEFT JOIN pg_shadow u ON (datdba = usesysid) "
                             "WHERE datallowconn ORDER BY 1");
--- 645,651 ----
                             "SELECT datname, "
                             "coalesce(usename, (select usename from pg_shadow where usesysid=(select datdba from
pg_databasewhere datname='template0'))), " 
                             "pg_encoding_to_char(d.encoding), "
!                            "datistemplate, datacl, -1 as datconnlimit, "
                             "'pg_default' AS dattablespace "
          "FROM pg_database d LEFT JOIN pg_shadow u ON (datdba = usesysid) "
                             "WHERE datallowconn ORDER BY 1");
***************
*** 637,643 ****
                  "(select usename from pg_shadow where usesysid=datdba), "
                             "(select usename from pg_shadow where usesysid=(select datdba from pg_database where
datname='template0')))," 
                             "pg_encoding_to_char(d.encoding), "
!                            "datistemplate, '' as datacl, "
                             "'pg_default' AS dattablespace "
                             "FROM pg_database d "
                             "WHERE datallowconn ORDER BY 1");
--- 656,662 ----
                  "(select usename from pg_shadow where usesysid=datdba), "
                             "(select usename from pg_shadow where usesysid=(select datdba from pg_database where
datname='template0')))," 
                             "pg_encoding_to_char(d.encoding), "
!                            "datistemplate, '' as datacl, -1 as datconnlimit, "
                             "'pg_default' AS dattablespace "
                             "FROM pg_database d "
                             "WHERE datallowconn ORDER BY 1");
***************
*** 652,658 ****
                  "(select usename from pg_shadow where usesysid=datdba), "
                             "pg_encoding_to_char(d.encoding), "
                             "'f' as datistemplate, "
!                            "'' as datacl, "
                             "'pg_default' AS dattablespace "
                             "FROM pg_database d "
                             "ORDER BY 1");
--- 671,677 ----
                  "(select usename from pg_shadow where usesysid=datdba), "
                             "pg_encoding_to_char(d.encoding), "
                             "'f' as datistemplate, "
!                            "'' as datacl, -1 as datconnlimit, "
                             "'pg_default' AS dattablespace "
                             "FROM pg_database d "
                             "ORDER BY 1");
***************
*** 666,672 ****
          char       *dbencoding = PQgetvalue(res, i, 2);
          char       *dbistemplate = PQgetvalue(res, i, 3);
          char       *dbacl = PQgetvalue(res, i, 4);
!         char       *dbtablespace = PQgetvalue(res, i, 5);
          char       *fdbname;

          buf = createPQExpBuffer();
--- 685,692 ----
          char       *dbencoding = PQgetvalue(res, i, 2);
          char       *dbistemplate = PQgetvalue(res, i, 3);
          char       *dbacl = PQgetvalue(res, i, 4);
!         char       *dbconnlimit = PQgetvalue(res, i, 5);
!         char       *dbtablespace = PQgetvalue(res, i, 6);
          char       *fdbname;

          buf = createPQExpBuffer();
***************
*** 698,703 ****
--- 718,727 ----
                  appendPQExpBuffer(buf, " TABLESPACE = %s",
                                    fmtId(dbtablespace));

+             if (strcmp(dbconnlimit, "-1") != 0)
+                 appendPQExpBuffer(buf, " CONNECTION LIMIT = %s",
+                                   dbconnlimit);
+
              appendPQExpBuffer(buf, ";\n");

              if (strcmp(dbistemplate, "t") == 0)
Index: src/include/miscadmin.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/miscadmin.h,v
retrieving revision 1.178
diff -c -c -r1.178 miscadmin.h
*** src/include/miscadmin.h    25 Jul 2005 22:12:34 -0000    1.178
--- src/include/miscadmin.h    28 Jul 2005 21:56:31 -0000
***************
*** 213,218 ****
--- 213,219 ----
  extern int    VacuumCostBalance;
  extern bool VacuumCostActive;

+ #define InvalidConnectionLimit    (-2)

  /* in tcop/postgres.c */
  extern void check_stack_depth(void);
Index: src/include/catalog/pg_authid.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/catalog/pg_authid.h,v
retrieving revision 1.2
diff -c -c -r1.2 pg_authid.h
*** src/include/catalog/pg_authid.h    26 Jul 2005 16:38:28 -0000    1.2
--- src/include/catalog/pg_authid.h    28 Jul 2005 21:56:31 -0000
***************
*** 49,54 ****
--- 49,55 ----
      bool        rolcreatedb;    /* allowed to create databases? */
      bool        rolcatupdate;    /* allowed to alter catalogs manually? */
      bool        rolcanlogin;    /* allowed to log in as session user? */
+     int4        rolconnlimit;        /* maximum connections allowed */

      /* remaining fields may be null; use heap_getattr to read them! */
      text        rolpassword;    /* password, if any */
***************
*** 70,76 ****
   *        compiler constants for pg_authid
   * ----------------
   */
! #define Natts_pg_authid                    10
  #define Anum_pg_authid_rolname            1
  #define Anum_pg_authid_rolsuper            2
  #define Anum_pg_authid_rolinherit        3
--- 71,77 ----
   *        compiler constants for pg_authid
   * ----------------
   */
! #define Natts_pg_authid                    11
  #define Anum_pg_authid_rolname            1
  #define Anum_pg_authid_rolsuper            2
  #define Anum_pg_authid_rolinherit        3
***************
*** 78,86 ****
  #define Anum_pg_authid_rolcreatedb        5
  #define Anum_pg_authid_rolcatupdate        6
  #define Anum_pg_authid_rolcanlogin        7
! #define Anum_pg_authid_rolpassword        8
! #define Anum_pg_authid_rolvaliduntil    9
! #define Anum_pg_authid_rolconfig        10

  /* ----------------
   *        initial contents of pg_authid
--- 79,88 ----
  #define Anum_pg_authid_rolcreatedb        5
  #define Anum_pg_authid_rolcatupdate        6
  #define Anum_pg_authid_rolcanlogin        7
! #define Anum_pg_authid_rolconnlimit        8
! #define Anum_pg_authid_rolpassword        9
! #define Anum_pg_authid_rolvaliduntil    10
! #define Anum_pg_authid_rolconfig        11

  /* ----------------
   *        initial contents of pg_authid
***************
*** 89,95 ****
   * user choices.
   * ----------------
   */
! DATA(insert OID = 10 ( "POSTGRES" t t t t t t _null_ _null_ _null_ ));

  #define BOOTSTRAP_SUPERUSERID 10

--- 91,97 ----
   * user choices.
   * ----------------
   */
! DATA(insert OID = 10 ( "POSTGRES" t t t t t t -1 _null_ _null_ _null_ ));

  #define BOOTSTRAP_SUPERUSERID 10

Index: src/include/catalog/pg_database.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/catalog/pg_database.h,v
retrieving revision 1.36
diff -c -c -r1.36 pg_database.h
*** src/include/catalog/pg_database.h    28 Jun 2005 05:09:06 -0000    1.36
--- src/include/catalog/pg_database.h    28 Jul 2005 21:56:31 -0000
***************
*** 40,45 ****
--- 40,46 ----
      int4        encoding;        /* character encoding */
      bool        datistemplate;    /* allowed as CREATE DATABASE template? */
      bool        datallowconn;    /* new connections allowed? */
+     int4        datconnlimit;        /* maximum connections allowed */
      Oid            datlastsysoid;    /* highest OID to consider a system OID */
      TransactionId datvacuumxid; /* all XIDs before this are vacuumed */
      TransactionId datfrozenxid; /* all XIDs before this are frozen */
***************
*** 59,78 ****
   *        compiler constants for pg_database
   * ----------------
   */
! #define Natts_pg_database                11
  #define Anum_pg_database_datname        1
  #define Anum_pg_database_datdba            2
  #define Anum_pg_database_encoding        3
  #define Anum_pg_database_datistemplate    4
  #define Anum_pg_database_datallowconn    5
! #define Anum_pg_database_datlastsysoid    6
! #define Anum_pg_database_datvacuumxid    7
! #define Anum_pg_database_datfrozenxid    8
! #define Anum_pg_database_dattablespace    9
! #define Anum_pg_database_datconfig        10
! #define Anum_pg_database_datacl            11

! DATA(insert OID = 1 (  template1 PGUID ENCODING t t 0 0 0 1663 _null_ _null_ ));
  DESCR("Default template database");
  #define TemplateDbOid            1

--- 60,80 ----
   *        compiler constants for pg_database
   * ----------------
   */
! #define Natts_pg_database                12
  #define Anum_pg_database_datname        1
  #define Anum_pg_database_datdba            2
  #define Anum_pg_database_encoding        3
  #define Anum_pg_database_datistemplate    4
  #define Anum_pg_database_datallowconn    5
! #define Anum_pg_database_datconnlimit        6
! #define Anum_pg_database_datlastsysoid    7
! #define Anum_pg_database_datvacuumxid    8
! #define Anum_pg_database_datfrozenxid    9
! #define Anum_pg_database_dattablespace    10
! #define Anum_pg_database_datconfig        11
! #define Anum_pg_database_datacl            12

! DATA(insert OID = 1 (  template1 PGUID ENCODING t t -1 0 0 0 1663 _null_ _null_ ));
  DESCR("Default template database");
  #define TemplateDbOid            1

Index: src/include/commands/dbcommands.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/commands/dbcommands.h,v
retrieving revision 1.40
diff -c -c -r1.40 dbcommands.h
*** src/include/commands/dbcommands.h    8 Jul 2005 04:12:27 -0000    1.40
--- src/include/commands/dbcommands.h    28 Jul 2005 21:56:31 -0000
***************
*** 55,60 ****
--- 55,61 ----
  extern void createdb(const CreatedbStmt *stmt);
  extern void dropdb(const char *dbname);
  extern void RenameDatabase(const char *oldname, const char *newname);
+ extern void AlterDatabase(AlterDatabaseStmt *stmt);
  extern void AlterDatabaseSet(AlterDatabaseSetStmt *stmt);
  extern void AlterDatabaseOwner(const char *dbname, Oid newOwnerId);

Index: src/include/nodes/nodes.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/nodes/nodes.h,v
retrieving revision 1.172
diff -c -c -r1.172 nodes.h
*** src/include/nodes/nodes.h    28 Jun 2005 05:09:13 -0000    1.172
--- src/include/nodes/nodes.h    28 Jul 2005 21:56:31 -0000
***************
*** 270,275 ****
--- 270,276 ----
      T_ReindexStmt,
      T_CheckPointStmt,
      T_CreateSchemaStmt,
+     T_AlterDatabaseStmt,
      T_AlterDatabaseSetStmt,
      T_AlterRoleSetStmt,
      T_CreateConversionStmt,
Index: src/include/nodes/parsenodes.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/nodes/parsenodes.h,v
retrieving revision 1.286
diff -c -c -r1.286 parsenodes.h
*** src/include/nodes/parsenodes.h    26 Jul 2005 16:38:28 -0000    1.286
--- src/include/nodes/parsenodes.h    28 Jul 2005 21:56:33 -0000
***************
*** 1624,1629 ****
--- 1624,1636 ----
   *    Alter Database
   * ----------------------
   */
+ typedef struct AlterDatabaseStmt
+ {
+     NodeTag        type;
+     char       *dbname;            /* name of database to alter */
+     List       *options;        /* List of DefElem nodes */
+ } AlterDatabaseStmt;
+
  typedef struct AlterDatabaseSetStmt
  {
      NodeTag        type;
Index: src/include/storage/proc.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/storage/proc.h,v
retrieving revision 1.79
diff -c -c -r1.79 proc.h
*** src/include/storage/proc.h    17 Jun 2005 22:32:50 -0000    1.79
--- src/include/storage/proc.h    28 Jul 2005 21:56:33 -0000
***************
*** 71,76 ****
--- 71,77 ----

      int            pid;            /* This backend's process id, or 0 */
      Oid            databaseId;        /* OID of database this backend is using */
+     Oid            roleId;            /* OID of role using conencted to backend */

      /* Info about LWLock the process is currently waiting for, if any. */
      bool        lwWaiting;        /* true if waiting for an LW lock */
Index: src/include/storage/procarray.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/storage/procarray.h,v
retrieving revision 1.2
diff -c -c -r1.2 procarray.h
*** src/include/storage/procarray.h    17 Jun 2005 22:32:50 -0000    1.2
--- src/include/storage/procarray.h    28 Jul 2005 21:56:33 -0000
***************
*** 31,36 ****
--- 31,38 ----
  extern bool DatabaseHasActiveBackends(Oid databaseId, bool ignoreMyself);

  extern int    CountActiveBackends(void);
+ extern int    CountDBBackends(Oid databaseid);
+ extern int    CountUserBackends(Oid roleid);

  extern void XidCacheRemoveRunningXids(TransactionId xid,
                            int nxids, TransactionId *xids);
Index: src/test/regress/expected/rules.out
===================================================================
RCS file: /cvsroot/pgsql/src/test/regress/expected/rules.out,v
retrieving revision 1.105
diff -c -c -r1.105 rules.out
*** src/test/regress/expected/rules.out    26 Jul 2005 16:38:29 -0000    1.105
--- src/test/regress/expected/rules.out    28 Jul 2005 21:56:35 -0000
***************
*** 1281,1290 ****
   pg_indexes               | SELECT n.nspname AS schemaname, c.relname AS tablename, i.relname AS indexname, t.spcname
AS"tablespace", pg_get_indexdef(i.oid) AS indexdef FROM ((((pg_index x JOIN pg_class c ON ((c.oid = x.indrelid))) JOIN
pg_classi ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) LEFT JOIN pg_tablespace
tON ((t.oid = i.reltablespace))) WHERE ((c.relkind = 'r'::"char") AND (i.relkind = 'i'::"char")); 
   pg_locks                 | SELECT l.locktype, l."database", l.relation, l.page, l.tuple, l.transactionid, l.classid,
l.objid,l.objsubid, l."transaction", l.pid, l."mode", l."granted" FROM pg_lock_status() l(locktype text, "database"
oid,relation oid, page integer, tuple smallint, transactionid xid, classid oid, objid oid, objsubid smallint,
"transaction"xid, pid integer, "mode" text, "granted" boolean); 
   pg_prepared_xacts        | SELECT p."transaction", p.gid, p."prepared", u.rolname AS "owner", d.datname AS
"database"FROM ((pg_prepared_xact() p("transaction" xid, gid text, "prepared" timestamp with time zone, ownerid oid,
dbidoid) LEFT JOIN pg_authid u ON ((p.ownerid = u.oid))) LEFT JOIN pg_database d ON ((p.dbid = d.oid))); 
!  pg_roles                 | SELECT pg_authid.rolname, pg_authid.rolsuper, pg_authid.rolinherit,
pg_authid.rolcreaterole,pg_authid.rolcreatedb, pg_authid.rolcatupdate, pg_authid.rolcanlogin, '********'::text AS
rolpassword,pg_authid.rolvaliduntil, pg_authid.rolconfig, pg_authid.oid FROM pg_authid; 
   pg_rules                 | SELECT n.nspname AS schemaname, c.relname AS tablename, r.rulename, pg_get_ruledef(r.oid)
ASdefinition FROM ((pg_rewrite r JOIN pg_class c ON ((c.oid = r.ev_class))) LEFT JOIN pg_namespace n ON ((n.oid =
c.relnamespace)))WHERE (r.rulename <> '_RETURN'::name); 
   pg_settings              | SELECT a.name, a.setting, a.category, a.short_desc, a.extra_desc, a.context, a.vartype,
a.source,a.min_val, a.max_val FROM pg_show_all_settings() a(name text, setting text, category text, short_desc text,
extra_desctext, context text, vartype text, source text, min_val text, max_val text); 
!  pg_shadow                | SELECT pg_authid.rolname AS usename, pg_authid.oid AS usesysid, pg_authid.rolcreatedb AS
usecreatedb,pg_authid.rolsuper AS usesuper, pg_authid.rolcatupdate AS usecatupd, pg_authid.rolpassword AS passwd,
(pg_authid.rolvaliduntil)::abstimeAS valuntil, pg_authid.rolconfig AS useconfig FROM pg_authid WHERE
pg_authid.rolcanlogin;
   pg_stat_activity         | SELECT d.oid AS datid, d.datname, pg_stat_get_backend_pid(s.backendid) AS procpid,
pg_stat_get_backend_userid(s.backendid)AS usesysid, u.rolname AS usename, pg_stat_get_backend_activity(s.backendid) AS
current_query,pg_stat_get_backend_activity_start(s.backendid) AS query_start, pg_stat_get_backend_start(s.backendid) AS
backend_start,pg_stat_get_backend_client_addr(s.backendid) AS client_addr, pg_stat_get_backend_client_port(s.backendid)
ASclient_port FROM pg_database d, (SELECT pg_stat_get_backend_idset() AS backendid) s, pg_authid u WHERE
((pg_stat_get_backend_dbid(s.backendid)= d.oid) AND (pg_stat_get_backend_userid(s.backendid) = u.oid)); 
   pg_stat_all_indexes      | SELECT c.oid AS relid, i.oid AS indexrelid, n.nspname AS schemaname, c.relname, i.relname
ASindexrelname, pg_stat_get_numscans(i.oid) AS idx_scan, pg_stat_get_tuples_returned(i.oid) AS idx_tup_read,
pg_stat_get_tuples_fetched(i.oid)AS idx_tup_fetch FROM (((pg_class c JOIN pg_index x ON ((c.oid = x.indrelid))) JOIN
pg_classi ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind =
'r'::"char");
   pg_stat_all_tables       | SELECT c.oid AS relid, n.nspname AS schemaname, c.relname, pg_stat_get_numscans(c.oid) AS
seq_scan,pg_stat_get_tuples_returned(c.oid) AS seq_tup_read, sum(pg_stat_get_numscans(i.indexrelid)) AS idx_scan,
sum(pg_stat_get_tuples_fetched(i.indexrelid))AS idx_tup_fetch, pg_stat_get_tuples_inserted(c.oid) AS n_tup_ins,
pg_stat_get_tuples_updated(c.oid)AS n_tup_upd, pg_stat_get_tuples_deleted(c.oid) AS n_tup_del FROM ((pg_class c LEFT
JOINpg_index i ON ((c.oid = i.indrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind =
'r'::"char")GROUP BY c.oid, n.nspname, c.relname; 
--- 1281,1290 ----
   pg_indexes               | SELECT n.nspname AS schemaname, c.relname AS tablename, i.relname AS indexname, t.spcname
AS"tablespace", pg_get_indexdef(i.oid) AS indexdef FROM ((((pg_index x JOIN pg_class c ON ((c.oid = x.indrelid))) JOIN
pg_classi ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) LEFT JOIN pg_tablespace
tON ((t.oid = i.reltablespace))) WHERE ((c.relkind = 'r'::"char") AND (i.relkind = 'i'::"char")); 
   pg_locks                 | SELECT l.locktype, l."database", l.relation, l.page, l.tuple, l.transactionid, l.classid,
l.objid,l.objsubid, l."transaction", l.pid, l."mode", l."granted" FROM pg_lock_status() l(locktype text, "database"
oid,relation oid, page integer, tuple smallint, transactionid xid, classid oid, objid oid, objsubid smallint,
"transaction"xid, pid integer, "mode" text, "granted" boolean); 
   pg_prepared_xacts        | SELECT p."transaction", p.gid, p."prepared", u.rolname AS "owner", d.datname AS
"database"FROM ((pg_prepared_xact() p("transaction" xid, gid text, "prepared" timestamp with time zone, ownerid oid,
dbidoid) LEFT JOIN pg_authid u ON ((p.ownerid = u.oid))) LEFT JOIN pg_database d ON ((p.dbid = d.oid))); 
!  pg_roles                 | SELECT pg_authid.rolname, pg_authid.rolsuper, pg_authid.rolinherit,
pg_authid.rolcreaterole,pg_authid.rolcreatedb, pg_authid.rolcatupdate, pg_authid.rolcanlogin, pg_authid.rolconnlimit,
'********'::textAS rolpassword, pg_authid.rolvaliduntil, pg_authid.rolconfig, pg_authid.oid FROM pg_authid; 
   pg_rules                 | SELECT n.nspname AS schemaname, c.relname AS tablename, r.rulename, pg_get_ruledef(r.oid)
ASdefinition FROM ((pg_rewrite r JOIN pg_class c ON ((c.oid = r.ev_class))) LEFT JOIN pg_namespace n ON ((n.oid =
c.relnamespace)))WHERE (r.rulename <> '_RETURN'::name); 
   pg_settings              | SELECT a.name, a.setting, a.category, a.short_desc, a.extra_desc, a.context, a.vartype,
a.source,a.min_val, a.max_val FROM pg_show_all_settings() a(name text, setting text, category text, short_desc text,
extra_desctext, context text, vartype text, source text, min_val text, max_val text); 
!  pg_shadow                | SELECT pg_authid.rolname AS usename, pg_authid.oid AS usesysid, pg_authid.rolcreatedb AS
usecreatedb,pg_authid.rolsuper AS usesuper, pg_authid.rolcatupdate AS usecatupd, pg_authid.rolconnlimit AS
useconnlimit,pg_authid.rolpassword AS passwd, (pg_authid.rolvaliduntil)::abstime AS valuntil, pg_authid.rolconfig AS
useconfigFROM pg_authid WHERE pg_authid.rolcanlogin; 
   pg_stat_activity         | SELECT d.oid AS datid, d.datname, pg_stat_get_backend_pid(s.backendid) AS procpid,
pg_stat_get_backend_userid(s.backendid)AS usesysid, u.rolname AS usename, pg_stat_get_backend_activity(s.backendid) AS
current_query,pg_stat_get_backend_activity_start(s.backendid) AS query_start, pg_stat_get_backend_start(s.backendid) AS
backend_start,pg_stat_get_backend_client_addr(s.backendid) AS client_addr, pg_stat_get_backend_client_port(s.backendid)
ASclient_port FROM pg_database d, (SELECT pg_stat_get_backend_idset() AS backendid) s, pg_authid u WHERE
((pg_stat_get_backend_dbid(s.backendid)= d.oid) AND (pg_stat_get_backend_userid(s.backendid) = u.oid)); 
   pg_stat_all_indexes      | SELECT c.oid AS relid, i.oid AS indexrelid, n.nspname AS schemaname, c.relname, i.relname
ASindexrelname, pg_stat_get_numscans(i.oid) AS idx_scan, pg_stat_get_tuples_returned(i.oid) AS idx_tup_read,
pg_stat_get_tuples_fetched(i.oid)AS idx_tup_fetch FROM (((pg_class c JOIN pg_index x ON ((c.oid = x.indrelid))) JOIN
pg_classi ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind =
'r'::"char");
   pg_stat_all_tables       | SELECT c.oid AS relid, n.nspname AS schemaname, c.relname, pg_stat_get_numscans(c.oid) AS
seq_scan,pg_stat_get_tuples_returned(c.oid) AS seq_tup_read, sum(pg_stat_get_numscans(i.indexrelid)) AS idx_scan,
sum(pg_stat_get_tuples_fetched(i.indexrelid))AS idx_tup_fetch, pg_stat_get_tuples_inserted(c.oid) AS n_tup_ins,
pg_stat_get_tuples_updated(c.oid)AS n_tup_upd, pg_stat_get_tuples_deleted(c.oid) AS n_tup_del FROM ((pg_class c LEFT
JOINpg_index i ON ((c.oid = i.indrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind =
'r'::"char")GROUP BY c.oid, n.nspname, c.relname; 

pgsql-patches by date:

Previous
From: "Magnus Hagander"
Date:
Subject: Win32 eventlog level fix
Next
From: Bruce Momjian
Date:
Subject: Re: psql tab-completion for COMMIT/ROLLBACK PREPARED