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

From Petr Jelinek
Subject per user/database connections limit again
Date
Msg-id 42C14245.2090101@parba.cz
Whole thread Raw
Responses Re: per user/database connections limit again  (Bruce Momjian <pgman@candle.pha.pa.us>)
Re: per user/database connections limit again  (Stephen Frost <sfrost@snowman.net>)
List pgsql-patches
Hi,

I attached  second try of per-database and per-user connection limit for
your review.

This time I am using information stored in ProcArray to get number of
connections - I modified PGPROC struct to also include userid.

Limits for user and database are stored in catalog tables. This aproach
led to implementation of "universal" ALTER DATABASE query (I followed
ALTER USER ad ALTER DATABASE ... RENAME implementatons). So queries for
setting maximum connections look like this: CREATE|ALTER DATABASE|USER
name MAX CONNECTIONS = 20;
Maximum connections defaults to zero which means unlimited (limited by
global maximum only) and isn't enforced for superusers.

The actual check for maximum conenctions is done in ReverifyMyDatabase
for database and InitializeSessionUser for user because we don't have
information from system catalog before so we don't know how many
connections are allowed.

Patch includes only changes to backend, I will make pg_dump, ecpg and
documentation patches once this is completed and accepted by team.

Diff is made against cvs from today morning GMT (apply with -p1 if you
want to test it) - cvs is down now so I can't make diff against repository.

--
Regards
Petr Jelinek (PJMODOS)


diff -Nacr my-cvs/src/backend/commands/dbcommands.c my-aproach2/src/backend/commands/dbcommands.c
*** my-cvs/src/backend/commands/dbcommands.c    Sun Jun 26 00:47:30 2005
--- my-aproach2/src/backend/commands/dbcommands.c    Tue Jun 28 11:26:08 2005
***************
*** 53,60 ****

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

  /* non-export function prototypes */
  static bool get_db_info(const char *name, Oid *dbIdP, int4 *ownerIdP,
!             int *encodingP, int *dbMaxConnP, bool *dbIsTemplateP,
!             bool *dbAllowConnP,    Oid *dbLastSysOidP,
              TransactionId *dbVacuumXidP, TransactionId *dbFrozenXidP,
              Oid *dbTablespace);
  static bool have_createdb_privilege(void);
***************
*** 74,79 ****
--- 74,80 ----
      int            src_encoding;
      bool        src_istemplate;
      bool        src_allowconn;
+     int            src_maxconn;
      Oid            src_lastsysoid;
      TransactionId src_vacuumxid;
      TransactionId src_frozenxid;
***************
*** 91,100 ****
--- 92,103 ----
      DefElem    *downer = NULL;
      DefElem    *dtemplate = NULL;
      DefElem    *dencoding = NULL;
+     DefElem    *dmaxconn = NULL;
      char       *dbname = stmt->dbname;
      char       *dbowner = NULL;
      const char *dbtemplate = NULL;
      int            encoding = -1;
+     int            dbmaxconn = -1;

  #ifndef WIN32
      char        buf[2 * MAXPGPATH + 100];
***************
*** 140,145 ****
--- 143,156 ----
                           errmsg("conflicting or redundant options")));
              dencoding = defel;
          }
+         else if (strcmp(defel->defname, "maxconnections") == 0)
+         {
+             if (dmaxconn)
+                 ereport(ERROR,
+                         (errcode(ERRCODE_SYNTAX_ERROR),
+                          errmsg("conflicting or redundant options")));
+             dmaxconn = defel;
+         }
          else if (strcmp(defel->defname, "location") == 0)
          {
              ereport(WARNING,
***************
*** 185,190 ****
--- 196,203 ----
              elog(ERROR, "unrecognized node type: %d",
                   nodeTag(dencoding->arg));
      }
+     if (dmaxconn && dmaxconn->arg)
+         dbmaxconn = intVal(dmaxconn->arg);

      /* obtain sysid of proposed owner */
      if (dbowner)
***************
*** 218,224 ****
       * 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),
--- 231,237 ----
       * 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),
***************
*** 231,238 ****
          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)));
--- 244,252 ----
          dbtemplate = "template1";        /* Default template database name */

      if (!get_db_info(dbtemplate, &src_dboid, &src_owner, &src_encoding,
!                      &src_maxconn, &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)));
***************
*** 266,271 ****
--- 280,289 ----
      if (encoding < 0)
          encoding = src_encoding;

+     /* If dbmaxconn is defaulted, use source's dbmaxconn */
+     if (dbmaxconn < 0)
+         dbmaxconn = src_maxconn;
+
      /* Some encodings are client only */
      if (!PG_VALID_BE_ENCODING(encoding))
          ereport(ERROR,
***************
*** 461,467 ****
      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 */
--- 479,485 ----
      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 */
***************
*** 487,492 ****
--- 505,511 ----
      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_datmaxconn - 1] = Int32GetDatum(dbmaxconn);
      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);
***************
*** 588,594 ****
       */
      pgdbrel = heap_open(DatabaseRelationId, ExclusiveLock);

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

!     if (!get_db_info(dbname, &db_id, &db_owner, NULL, NULL,
                       &db_istemplate, NULL, NULL, NULL, NULL, NULL))
          ereport(ERROR,
                  (errcode(ERRCODE_UNDEFINED_DATABASE),
***************
*** 784,789 ****
--- 803,894 ----


  /*
+  * 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            maxconn = -1;    /* Maximum connections allowed */
+
+     DefElem    *dmaxconn = NULL;
+
+     /* Extract options from the statement node tree */
+     foreach(option, stmt->options)
+     {
+         DefElem    *defel = (DefElem *) lfirst(option);
+
+         if (strcmp(defel->defname, "maxconnections") == 0)
+         {
+             if (dmaxconn)
+                 ereport(ERROR,
+                         (errcode(ERRCODE_SYNTAX_ERROR),
+                          errmsg("conflicting or redundant options")));
+             dmaxconn = defel;
+         }
+     }
+
+     if (dmaxconn)
+         maxconn = intVal(dmaxconn->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 (!(superuser()
+         || ((Form_pg_database) GETSTRUCT(tuple))->datdba == GetUserId()))
+         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 (maxconn >= 0)
+     {
+         new_record[Anum_pg_database_datmaxconn - 1] = Int32GetDatum(maxconn);
+         new_record_repl[Anum_pg_database_datmaxconn - 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
***************
*** 973,980 ****

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

  static bool
  get_db_info(const char *name, Oid *dbIdP, int4 *ownerIdP,
!             int *encodingP, int *dbMaxConnP, bool *dbIsTemplateP,
!             bool *dbAllowConnP,    Oid *dbLastSysOidP,
              TransactionId *dbVacuumXidP, TransactionId *dbFrozenXidP,
              Oid *dbTablespace)
  {
***************
*** 1019,1024 ****
--- 1124,1132 ----
          /* allowing connections? */
          if (dbAllowConnP)
              *dbAllowConnP = dbform->datallowconn;
+         /* maximum connections */
+         if (dbMaxConnP)
+             *dbMaxConnP = dbform->datmaxconn;
          /* last system OID used in database */
          if (dbLastSysOidP)
              *dbLastSysOidP = dbform->datlastsysoid;
diff -Nacr my-cvs/src/backend/commands/user.c my-aproach2/src/backend/commands/user.c
*** my-cvs/src/backend/commands/user.c    Thu Apr 14 22:03:24 2005
--- my-aproach2/src/backend/commands/user.c    Tue Jun 28 11:26:18 2005
***************
*** 64,69 ****
--- 64,70 ----
      int            sysid = 0;        /* PgSQL system id (valid if havesysid) */
      bool        createdb = false;        /* Can the user create databases? */
      bool        createuser = false;        /* Can this user create users? */
+     int            maxconn = false;        /* maximum connections allowed */
      List       *groupElts = NIL;    /* The groups the user is a member of */
      char       *validUntil = NULL;        /* The time the login is valid
                                           * until */
***************
*** 73,78 ****
--- 74,80 ----
      DefElem    *dcreateuser = NULL;
      DefElem    *dgroupElts = NULL;
      DefElem    *dvalidUntil = NULL;
+     DefElem    *dmaxconn = NULL;

      /* Extract options from the statement node tree */
      foreach(option, stmt->options)
***************
*** 117,122 ****
--- 119,132 ----
                           errmsg("conflicting or redundant options")));
              dcreateuser = defel;
          }
+         else if (strcmp(defel->defname, "maxconnections") == 0)
+         {
+             if (dmaxconn)
+                 ereport(ERROR,
+                         (errcode(ERRCODE_SYNTAX_ERROR),
+                          errmsg("conflicting or redundant options")));
+             dmaxconn = defel;
+         }
          else if (strcmp(defel->defname, "groupElts") == 0)
          {
              if (dgroupElts)
***************
*** 142,147 ****
--- 152,165 ----
          createdb = intVal(dcreatedb->arg) != 0;
      if (dcreateuser)
          createuser = intVal(dcreateuser->arg) != 0;
+     if (dmaxconn)
+     {
+         maxconn = intVal(dmaxconn->arg);
+         if (maxconn < 0)
+             ereport(ERROR,
+                     (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+                      errmsg("MAX CONNECTIONS must not be negative")));
+     }
      if (dsysid)
      {
          sysid = intVal(dsysid->arg);
***************
*** 233,238 ****
--- 251,257 ----
      new_record[Anum_pg_shadow_usesuper - 1] = BoolGetDatum(createuser);
      /* superuser gets catupd right by default */
      new_record[Anum_pg_shadow_usecatupd - 1] = BoolGetDatum(createuser);
+     new_record[Anum_pg_shadow_usemaxconn - 1] = Int32GetDatum(maxconn);

      if (password)
      {
***************
*** 317,328 ****
--- 336,349 ----
      char        encrypted_password[MD5_PASSWD_LEN + 1];
      int            createdb = -1;    /* Can the user create databases? */
      int            createuser = -1;    /* Can this user create users? */
+     int            maxconn = -1;    /* Maximum connections allowed */
      char       *validUntil = NULL;        /* The time the login is valid
                                           * until */
      DefElem    *dpassword = NULL;
      DefElem    *dcreatedb = NULL;
      DefElem    *dcreateuser = NULL;
      DefElem    *dvalidUntil = NULL;
+     DefElem    *dmaxconn = NULL;

      /* Extract options from the statement node tree */
      foreach(option, stmt->options)
***************
*** 359,364 ****
--- 380,393 ----
                           errmsg("conflicting or redundant options")));
              dcreateuser = defel;
          }
+         else if (strcmp(defel->defname, "maxconnections") == 0)
+         {
+             if (dmaxconn)
+                 ereport(ERROR,
+                         (errcode(ERRCODE_SYNTAX_ERROR),
+                          errmsg("conflicting or redundant options")));
+             dmaxconn = defel;
+         }
          else if (strcmp(defel->defname, "validUntil") == 0)
          {
              if (dvalidUntil)
***************
*** 376,381 ****
--- 405,412 ----
          createdb = intVal(dcreatedb->arg);
      if (dcreateuser)
          createuser = intVal(dcreateuser->arg);
+     if (dmaxconn)
+         maxconn = intVal(dmaxconn->arg);
      if (dvalidUntil)
          validUntil = strVal(dvalidUntil->arg);
      if (dpassword)
***************
*** 427,432 ****
--- 458,469 ----
      {
          new_record[Anum_pg_shadow_usecreatedb - 1] = BoolGetDatum(createdb > 0);
          new_record_repl[Anum_pg_shadow_usecreatedb - 1] = 'r';
+     }
+
+     if (maxconn >= 0)
+     {
+         new_record[Anum_pg_shadow_usemaxconn - 1] = Int32GetDatum(maxconn);
+         new_record_repl[Anum_pg_shadow_usemaxconn - 1] = 'r';
      }

      /*
diff -Nacr my-cvs/src/backend/nodes/copyfuncs.c my-aproach2/src/backend/nodes/copyfuncs.c
*** my-cvs/src/backend/nodes/copyfuncs.c    Mon Jun 27 00:05:38 2005
--- my-aproach2/src/backend/nodes/copyfuncs.c    Tue Jun 28 06:07:50 2005
***************
*** 2191,2196 ****
--- 2191,2207 ----
      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)
  {
diff -Nacr my-cvs/src/backend/nodes/equalfuncs.c my-aproach2/src/backend/nodes/equalfuncs.c
*** my-cvs/src/backend/nodes/equalfuncs.c    Mon Jun 27 00:05:38 2005
--- my-aproach2/src/backend/nodes/equalfuncs.c    Tue Jun 28 06:07:50 2005
***************
*** 1141,1146 ****
--- 1141,1155 ----
  }

  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);
diff -Nacr my-cvs/src/backend/parser/gram.y my-aproach2/src/backend/parser/gram.y
*** my-cvs/src/backend/parser/gram.y    Mon Jun 27 00:05:38 2005
--- my-aproach2/src/backend/parser/gram.y    Tue Jun 28 11:26:30 2005
***************
*** 131,139 ****
  }

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

  %type <node>    stmt schema_stmt
!         AlterDatabaseStmt AlterDatabaseSetStmt AlterDomainStmt AlterGroupStmt
!         AlterOwnerStmt AlterSeqStmt AlterTableStmt AlterUserStmt
!         AlterUserSetStmt AnalyzeStmt ClosePortalStmt ClusterStmt CommentStmt
          ConstraintsSetStmt CopyStmt CreateAsStmt CreateCastStmt
          CreateDomainStmt CreateGroupStmt CreateOpClassStmt CreatePLangStmt
          CreateSchemaStmt CreateSeqStmt CreateStmt CreateTableSpaceStmt
***************
*** 164,171 ****

  %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
--- 164,173 ----

  %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
***************
*** 346,352 ****
      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
      CREATEUSER CROSS CSV CURRENT_DATE CURRENT_TIME
      CURRENT_TIMESTAMP CURRENT_USER CURSOR CYCLE

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

***************
*** 377,383 ****
      LIKE LIMIT LISTEN LOAD LOCAL LOCALTIME LOCALTIMESTAMP LOCATION
      LOCK_P

!     MATCH MAXVALUE MINUTE_P MINVALUE MODE MONTH_P MOVE

      NAMES NATIONAL NATURAL NCHAR NEW NEXT NO NOCREATEDB
      NOCREATEUSER NONE NOT NOTHING NOTIFY NOTNULL NOWAIT NULL_P
--- 379,385 ----
      LIKE LIMIT LISTEN LOAD LOCAL LOCALTIME LOCALTIMESTAMP LOCATION
      LOCK_P

!     MATCH MAX MAXVALUE MINUTE_P MINVALUE MODE MONTH_P MOVE

      NAMES NATIONAL NATURAL NCHAR NEW NEXT NO NOCREATEDB
      NOCREATEUSER NONE NOT NOTHING NOTIFY NOTNULL NOWAIT NULL_P
***************
*** 490,496 ****
          ;

  stmt :
!             AlterDatabaseSetStmt
              | AlterDomainStmt
              | AlterFunctionStmt
              | AlterGroupStmt
--- 492,499 ----
          ;

  stmt :
!             AlterDatabaseStmt
!             | AlterDatabaseSetStmt
              | AlterDomainStmt
              | AlterFunctionStmt
              | AlterGroupStmt
***************
*** 688,693 ****
--- 691,700 ----
                  {
                      $$ = makeDefElem("createuser", (Node *)makeInteger(FALSE));
                  }
+             | MAX CONNECTIONS Iconst
+                 {
+                     $$ = makeDefElem("maxconnections", (Node *)makeInteger($3));
+                 }
              | IN_P GROUP_P user_list
                  {
                      $$ = makeDefElem("groupElts", (Node *)$3);
***************
*** 4294,4299 ****
--- 4301,4310 ----
                  {
                      $$ = makeDefElem("encoding", NULL);
                  }
+             | MAX CONNECTIONS opt_equal Iconst
+                 {
+                     $$ = makeDefElem("maxconnections", (Node *)makeInteger($4));
+                 }
              | OWNER opt_equal name
                  {
                      $$ = makeDefElem("owner", (Node *)makeString($3));
***************
*** 4320,4325 ****
--- 4331,4346 ----
   *
   *****************************************************************************/

+ 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
                  {
***************
*** 4340,4345 ****
--- 4361,4379 ----
          ;


+ alterdb_opt_list:
+             alterdb_opt_list alterdb_opt_item        { $$ = lappend($1, $2); }
+             | /* EMPTY */                            { $$ = NIL; }
+         ;
+
+ alterdb_opt_item:
+             MAX CONNECTIONS opt_equal Iconst
+                 {
+                     $$ = makeDefElem("maxconnections", (Node *)makeInteger($4));
+                 }
+         ;
+
+
  /*****************************************************************************
   *
   *        DROP DATABASE
***************
*** 7770,7775 ****
--- 7804,7810 ----
              | COMMENT
              | COMMIT
              | COMMITTED
+             | CONNECTIONS
              | CONSTRAINTS
              | CONVERSION_P
              | COPY
***************
*** 7835,7840 ****
--- 7870,7876 ----
              | LOCATION
              | LOCK_P
              | MATCH
+             | MAX
              | MAXVALUE
              | MINUTE_P
              | MINVALUE
diff -Nacr my-cvs/src/backend/parser/keywords.c my-aproach2/src/backend/parser/keywords.c
*** my-cvs/src/backend/parser/keywords.c    Mon Jun 27 00:05:40 2005
--- my-aproach2/src/backend/parser/keywords.c    Tue Jun 28 06:07:50 2005
***************
*** 82,87 ****
--- 82,88 ----
      {"comment", COMMENT},
      {"commit", COMMIT},
      {"committed", COMMITTED},
+     {"connections", CONNECTIONS},
      {"constraint", CONSTRAINT},
      {"constraints", CONSTRAINTS},
      {"conversion", CONVERSION_P},
***************
*** 198,203 ****
--- 199,205 ----
      {"location", LOCATION},
      {"lock", LOCK_P},
      {"match", MATCH},
+     {"max", MAX},
      {"maxvalue", MAXVALUE},
      {"minute", MINUTE_P},
      {"minvalue", MINVALUE},
diff -Nacr my-cvs/src/backend/storage/ipc/procarray.c my-aproach2/src/backend/storage/ipc/procarray.c
*** my-cvs/src/backend/storage/ipc/procarray.c    Sat Jun 18 00:32:46 2005
--- my-aproach2/src/backend/storage/ipc/procarray.c    Tue Jun 28 06:07:50 2005
***************
*** 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(AclId userid)
+ {
+     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->userId == userid)
+             count++;
+     }
+
+     LWLockRelease(ProcArrayLock);
+
+     return count;
+ }
+
+
  #define XidCacheRemove(i) \
      do { \
          MyProc->subxids.xids[i] = MyProc->subxids.xids[MyProc->subxids.nxids - 1]; \
diff -Nacr my-cvs/src/backend/storage/lmgr/proc.c my-aproach2/src/backend/storage/lmgr/proc.c
*** my-cvs/src/backend/storage/lmgr/proc.c    Sat Jun 18 00:32:46 2005
--- my-aproach2/src/backend/storage/lmgr/proc.c    Tue Jun 28 06:39:46 2005
***************
*** 254,259 ****
--- 254,260 ----
      MyProc->xmin = InvalidTransactionId;
      MyProc->pid = MyProcPid;
      MyProc->databaseId = MyDatabaseId;
+     MyProc->userId = GetSessionUserId();
      MyProc->lwWaiting = false;
      MyProc->lwExclusive = false;
      MyProc->lwWaitLink = NULL;
diff -Nacr my-cvs/src/backend/tcop/utility.c my-aproach2/src/backend/tcop/utility.c
*** my-cvs/src/backend/tcop/utility.c    Wed Jun 22 23:14:30 2005
--- my-aproach2/src/backens/tcop/utility.c    Tue Jun 28 06:07:50 2005
***************
*** 276,281 ****
--- 276,282 ----

      switch (nodeTag(parsetree))
      {
+         case T_AlterDatabaseStmt:
          case T_AlterDatabaseSetStmt:
          case T_AlterDomainStmt:
          case T_AlterFunctionStmt:
***************
*** 786,791 ****
--- 787,796 ----

          case T_CreatedbStmt:
              createdb((CreatedbStmt *) parsetree);
+             break;
+
+         case T_AlterDatabaseStmt:
+             AlterDatabase((AlterDatabaseStmt *) parsetree);
              break;

          case T_AlterDatabaseSetStmt:
diff -Nacr my-cvs/src/backend/utils/init/miscinit.c my-aproach2/src/backend/utils/init/miscinit.c
*** my-cvs/src/backend/utils/init/miscinit.c    Mon Jun 20 04:17:30 2005
--- my-aproach2/src/backend/utils/init/miscinit.c    Tue Jun 28 06:41:40 2005
***************
*** 315,320 ****
--- 315,321 ----
      Datum        datum;
      bool        isnull;
      AclId        usesysid;
+     Form_pg_shadow    userform;

      /*
       * Don't do scans if we're bootstrapping, none of the system catalogs
***************
*** 333,344 ****
                  (errcode(ERRCODE_UNDEFINED_OBJECT),
                   errmsg("user \"%s\" does not exist", username)));

!     usesysid = ((Form_pg_shadow) GETSTRUCT(userTup))->usesysid;

      AuthenticatedUserId = usesysid;
!     AuthenticatedUserIsSuperuser = ((Form_pg_shadow) GETSTRUCT(userTup))->usesuper;

      SetSessionUserId(usesysid); /* sets CurrentUserId too */

      /* Record username and superuser status as GUC settings too */
      SetConfigOption("session_authorization", username,
--- 334,358 ----
                  (errcode(ERRCODE_UNDEFINED_OBJECT),
                   errmsg("user \"%s\" does not exist", username)));

!     userform = ((Form_pg_shadow) GETSTRUCT(userTup));
!     usesysid = userform->usesysid;

      AuthenticatedUserId = usesysid;
!     AuthenticatedUserIsSuperuser = userform->usesuper;

      SetSessionUserId(usesysid); /* sets CurrentUserId too */
+
+     /*
+      * Check connection limit for user
+      */
+     if (userform->usemaxconn > 0 && !AuthenticatedUserIsSuperuser &&
+             CountUserBackends(AuthenticatedUserId) > userform->usemaxconn)
+     {
+         ereport(FATAL,
+             (errcode(ERRCODE_TOO_MANY_CONNECTIONS),
+          errmsg("sorry, too many clients already for user \"%s\"",
+             username)));
+     }

      /* Record username and superuser status as GUC settings too */
      SetConfigOption("session_authorization", username,
diff -Nacr my-cvs/src/backend/utils/init/postinit.c my-aproach2/src/backend/utils/init/postinit.c
*** my-cvs/src/backend/utils/init/postinit.c    Fri Jun 24 03:06:26 2005
--- my-aproach2/src/backend/utils/init/postinit.c    Tue Jun 28 10:11:10 2005
***************
*** 50,55 ****
--- 50,56 ----
  static void InitCommunication(void);
  static void ShutdownPostgres(int code, Datum arg);
  static bool ThereIsAtLeastOneUser(void);
+ static bool FindMyUser(const char *name, AclId *user_id);


  /*** InitPostgres support ***/
***************
*** 100,105 ****
--- 101,137 ----
  }

  /*
+  * Get user id from flatfiles
+  *
+  * We need this because we need to know userid before
+  * InitProcess() is called
+  */
+ static bool
+ FindMyUser(const char *name, AclId *user_id)
+ {
+     List      **line;
+     ListCell   *token;
+     char        thisname[NAMEDATALEN];
+
+     if ((line = get_user_line(name)) == NULL)
+         ereport(FATAL,
+                 (ERRCODE_INVALID_AUTHORIZATION_SPECIFICATION, /* ? */
+                  errmsg("could not find user \"%s\": %m", name)));
+
+     /* Skip over username */
+     token = list_head(*line);
+     if (token)
+         token = lnext(token);
+     if (token)
+     {
+         *user_id = atoi((char*)lfirst(token));
+         return true;
+     }
+
+     return false;
+ }
+
+ /*
   * ReverifyMyDatabase -- recheck info obtained by FindMyDatabase
   *
   * Since FindMyDatabase cannot lock pg_database, the information it read
***************
*** 165,181 ****
                          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
--- 197,231 ----
                          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 cxonenction limit for this database
!          */
!         if (dbform->datmaxconn > 0 && !superuser() &&
!                 CountDBBackends(MyDatabaseId) > dbform->datmaxconn)
!         {
!             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
***************
*** 350,355 ****
--- 400,424 ----
       * Code after this point assumes we are in the proper directory!
       */

+     /*
+      * We need to know userid in InitProcess() so we have read it from
+      * flatfile, real user inicialization is done later
+      */
+     if (IsUnderPostmaster)
+     {
+         AclId userid;
+
+         if (!FindMyUser(username, &userid))
+             ereport(FATAL,
+                     (ERRCODE_INVALID_AUTHORIZATION_SPECIFICATION, /* ? */
+                      errmsg("user \"%s\" does not exist",
+                             username)));
+
+         SetSessionUserId(userid);
+     }
+     else
+         SetSessionUserId(BOOTSTRAP_USESYSID);
+
      /*
       * Set up my per-backend PGPROC struct in shared memory.    (We need
       * to know MyDatabaseId before we can do this, since it's entered into
diff -Nacr my-cvs/src/include/catalog/pg_database.h my-aproach2/src/include/catalog/pg_database.h
*** my-cvs/src/include/catalog/pg_database.h    Thu Apr 14 03:38:20 2005
--- my-aproach2/src/include/catalog/pg_database.h    Tue Jun 28 06:07:50 2005
***************
*** 40,45 ****
--- 40,46 ----
      int4        encoding;        /* character encoding */
      bool        datistemplate;    /* allowed as CREATE DATABASE template? */
      bool        datallowconn;    /* new connections allowed? */
+     int4        datmaxconn;        /* 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_datmaxconn        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 0 0 0 0 1663 _null_ _null_ ));
  DESCR("Default template database");
  #define TemplateDbOid            1

diff -Nacr my-cvs/src/include/catalog/pg_shadow.h my-aproach2/src/include/catalog/pg_shadow.h
*** my-cvs/src/include/catalog/pg_shadow.h    Thu Apr 14 03:38:22 2005
--- my-aproach2/src/include/catalog/pg_shadow.h    Tue Jun 28 06:07:50 2005
***************
*** 36,41 ****
--- 36,42 ----
      bool        usecreatedb;
      bool        usesuper;        /* read this field via superuser() only */
      bool        usecatupd;
+     int4        usemaxconn;        /* maximum connections allowed */

      /* remaining fields may be null; use heap_getattr to read them! */
      text        passwd;
***************
*** 54,68 ****
   *        compiler constants for pg_shadow
   * ----------------
   */
! #define Natts_pg_shadow                    8
  #define Anum_pg_shadow_usename            1
  #define Anum_pg_shadow_usesysid            2
  #define Anum_pg_shadow_usecreatedb        3
  #define Anum_pg_shadow_usesuper            4
  #define Anum_pg_shadow_usecatupd        5
! #define Anum_pg_shadow_passwd            6
! #define Anum_pg_shadow_valuntil            7
! #define Anum_pg_shadow_useconfig        8

  /* ----------------
   *        initial contents of pg_shadow
--- 55,70 ----
   *        compiler constants for pg_shadow
   * ----------------
   */
! #define Natts_pg_shadow                    9
  #define Anum_pg_shadow_usename            1
  #define Anum_pg_shadow_usesysid            2
  #define Anum_pg_shadow_usecreatedb        3
  #define Anum_pg_shadow_usesuper            4
  #define Anum_pg_shadow_usecatupd        5
! #define Anum_pg_shadow_usemaxconn        6
! #define Anum_pg_shadow_passwd            7
! #define Anum_pg_shadow_valuntil            8
! #define Anum_pg_shadow_useconfig        9

  /* ----------------
   *        initial contents of pg_shadow
***************
*** 71,77 ****
   * user choices.
   * ----------------
   */
! DATA(insert ( "POSTGRES" PGUID t t t _null_ _null_ _null_ ));

  #define BOOTSTRAP_USESYSID 1

--- 73,79 ----
   * user choices.
   * ----------------
   */
! DATA(insert ( "POSTGRES" PGUID t t t 0 _null_ _null_ _null_ ));

  #define BOOTSTRAP_USESYSID 1

diff -Nacr my-cvs/src/include/commands/dbcommands.h my-aproach2/src/include/commands/dbcommands.h
*** my-cvs/src/include/commands/dbcommands.h    Mon Jun 06 19:01:26 2005
--- my-aproach2/src/include/commands/dbcommands.h    Tue Jun 28 06:07:50 2005
***************
*** 64,69 ****
--- 64,70 ----
  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, AclId newOwnerSysId);

diff -Nacr my-cvs/src/include/nodes/nodes.h my-aproach2/src/include/nodes/nodes.h
*** my-cvs/src/include/nodes/nodes.h    Mon Jun 27 00:05:42 2005
--- my-aproach2/src/include/nodes/nodes.h    Tue Jun 28 06:07:50 2005
***************
*** 272,277 ****
--- 272,278 ----
      T_ReindexStmt,
      T_CheckPointStmt,
      T_CreateSchemaStmt,
+     T_AlterDatabaseStmt,
      T_AlterDatabaseSetStmt,
      T_AlterUserSetStmt,
      T_CreateConversionStmt,
diff -Nacr my-cvs/src/include/nodes/parsenodes.h my-aproach2/src/include/nodes/parsenodes.h
*** my-cvs/src/include/nodes/parsenodes.h    Wed Jun 22 23:14:32 2005
--- my-aproach2/src/include/nodes/parsenodes.h    Tue Jun 28 06:07:50 2005
***************
*** 1620,1625 ****
--- 1620,1632 ----
   *    Alter Database
   * ----------------------
   */
+ typedef struct AlterDatabaseStmt
+ {
+     NodeTag        type;
+     char       *dbname;            /* name of database to create */
+     List       *options;        /* List of DefElem nodes */
+ } AlterDatabaseStmt;
+
  typedef struct AlterDatabaseSetStmt
  {
      NodeTag        type;
diff -Nacr my-cvs/src/include/storage/proc.h my-aproach2/src/include/storage/proc.h
*** my-cvs/src/include/storage/proc.h    Sat Jun 18 00:32:50 2005
--- my-aproach2/src/include/storage/proc.h    Tue Jun 28 06:07:50 2005
***************
*** 71,76 ****
--- 71,77 ----

      int            pid;            /* This backend's process id, or 0 */
      Oid            databaseId;        /* OID of database this backend is using */
+     AclId        userId;            /* user connected to this backend */

      /* Info about LWLock the process is currently waiting for, if any. */
      bool        lwWaiting;        /* true if waiting for an LW lock */
diff -Nacr my-cvs/src/include/storage/procarray.h my-aproach2/src/include/storage/procarray.h
*** my-cvs/src/include/storage/procarray.h    Sat Jun 18 00:32:50 2005
--- my-aproach2/src/include/storage/procarray.h    Tue Jun 28 06:07:50 2005
***************
*** 31,36 ****
--- 31,38 ----
  extern bool DatabaseHasActiveBackends(Oid databaseId, bool ignoreMyself);

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

  extern void XidCacheRemoveRunningXids(TransactionId xid,
                            int nxids, TransactionId *xids);
diff -Nacr my-cvs/src/tools/pgindent/pgindent my-aproach2/src/tools/pgindent/pgindent
*** my-cvs/src/tools/pgindent/pgindent    Thu Oct 07 16:15:50 2004
--- my-aproach2/src/tools/pgindent/pgindent    Tue Jun 28 06:07:50 2005
***************
*** 175,180 ****
--- 175,181 ----
  -TAllocSetContext \
  -TAllocateDesc \
  -TAllocateDescKind \
+ -TAlterDatabaseStmt \
  -TAlterDatabaseSetStmt \
  -TAlterDomainStmt \
  -TAlterGroupStmt \

pgsql-patches by date:

Previous
From: Bruce Momjian
Date:
Subject: Re: [BUGS] BUG #1707: statistics collector starts with stats_start_collector
Next
From: "Dave Page"
Date:
Subject: Dbsize backend integration