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
Re: per user/database connections limit again |
| 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: